GROUP BY: Агрегация данных

PostgreSQL GROUP BY: Агрегация данных

Добро пожаловать обратно в нашу серию уроков по PostgreSQL! В этом уроке мы сосредоточимся на предложении GROUP BY, которое необходимо для агрегирования данных и выполнения вычислений на группах строк. Понимание того, как эффективно использовать предложение GROUP BY, позволяет вам создавать содержательные сводки из ваших данных.

Введение в предложение GROUP BY

Предложение GROUP BY в PostgreSQL используется с агрегатными функциями (такими как COUNT, SUM, AVG, MAX, MIN) для группировки строк, которые имеют одинаковые значения в указанных столбцах, в сводные строки. Это мощный инструмент для анализа и отчетности данных.

Базовый синтаксис

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
  • SELECT: Задает столбцы и агрегатные функции для извлечения.
  • FROM: Указывает таблицу для запроса.
  • GROUP BY: Указывает столбец(-ы) для группировки результатов.

Использование GROUP BY в PostgreSQL

Давайте рассмотрим различные способы использования предложения GROUP BY с практическими примерами, используя нашу библиотеку и базу данных с фильмами.

1. Подсчет строк в группах

Пример: Подсчитайте количество книг для каждого автора

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author;

Просмотреть Инициализацию Схемы

Объяснение:

  • Группирует книги по author.
  • Подсчитывает количество книг для каждого автора.
  • Результат показывает каждого автора и количество его книг в таблице books.

2. Вычисление сумм

Пример: Подсчитайте общее количество страниц для каждого автора

SELECT author, SUM(pages) AS total_pages
FROM books
GROUP BY author;

Объяснение:

  • Группирует книги по author.
  • Суммирует количество pages для каждой книги автора.
  • Результат показывает каждого автора и общее количество страниц его книг.

3. Вычисление средних значений

Пример: Найдите среднюю цену книг для каждого автора

SELECT author, AVG(price) AS average_price
FROM books
GROUP BY author;

Объяснение:

  • Группирует книги по author.
  • Вычисляет среднюю price для каждой книги автора.
  • Результат показывает каждого автора и среднюю цену его книг.

4. Поиск максимальных и минимальных значений

Пример: Найдите самую дорогую и самую дешевую книгу для каждого автора

SELECT author, MAX(price) AS max_price, MIN(price) AS min_price
FROM books
GROUP BY author;

Объяснение:

  • Группирует книги по author.
  • Находит максимум (MAX) и минимум (MIN) price для каждой книги автора.
  • Результат показывает каждого автора, его самую дорогую и самую дешевую книги.

5. Группировка по нескольким столбцам

Пример: Подсчитайте количество книг по автору и цене

Предположим, мы хотим увидеть, сколько книг у каждого автора по каждой ценовой точке.

SELECT author, price, COUNT(*) AS book_count
FROM books
GROUP BY author, price;

Объяснение:

  • Группирует книги по author и price.
  • Подсчитывает количество книг каждого автора по каждой цене.
  • Результат показывает комбинацию авторов и цен с количеством книг для каждого.

6. Использование GROUP BY с HAVING

Предложение HAVING позволяет фильтровать группы на основе агрегатных функций.

Пример: Найдите авторов с более чем одной книгой

SELECT author, COUNT(*) AS book_count
FROM books
GROUP BY author
HAVING COUNT(*) > 1;

Объяснение:

  • Группирует книги по author.
  • Подсчитывает количество книг для каждого автора.
  • Предложение HAVING фильтрует только тех авторов, у которых больше одной книги.

7. Комбинирование GROUP BY с ORDER BY

Вы можете сортировать результаты группировки с использованием ORDER BY.

Пример: Отсортируйте авторов и их общее количество страниц по убыванию

SELECT author, SUM(pages) AS total_pages
FROM books
GROUP BY author
ORDER BY total_pages DESC;

Объяснение:

  • Группирует книги по author.
  • Суммирует количество pages для каждого автора.
  • Сортирует результаты по total_pages в порядке убывания.

8. Группировка и фильтрация данных

Пример: Вычислите среднюю цену книг, стоящих больше $10, сгруппированных по авторам

SELECT author, AVG(price) AS average_price
FROM books
WHERE price > 10
GROUP BY author;

Объяснение:

  • Фильтрует книги с price > 10.
  • Группирует оставшиеся книги по author.
  • Вычисляет среднюю price для каждого автора.

9. Использование GROUP BY с JOIN

Вы можете группировать данные из нескольких таблиц, используя JOIN.

Пример: Подсчитайте количество книг для авторов, которые есть в таблице authors

SELECT a.name AS author_name, COUNT(b.title) AS book_count
FROM authors a
LEFT JOIN books b ON a.name = b.author
GROUP BY a.name;

Объяснение:

  • Выполняет LEFT JOIN между authors и books.
  • Группирует результаты по a.name (имя автора).
  • Подсчитывает количество книг для каждого автора, включая тех, у кого нет книг.

10. Группировка с использованием выражений

Вы можете использовать выражения в предложении GROUP BY.

Пример: Сгруппируйте книги по ценовым диапазонам

Давайте сгруппируем книги по ценовым диапазонам: Менее $10, от $10 до $12, более $12.

SELECT
    CASE
        WHEN price < 10 THEN 'Under $10'
        WHEN price BETWEEN 10 AND 12 THEN '$10 - $12'
        ELSE 'Over $12'
    END AS price_range,
    COUNT(*) AS book_count
FROM books
GROUP BY price_range;

Объяснение:

  • Использует выражение CASE для создания категорий price_range.
  • Группирует книги по price_range.
  • Подсчитывает количество книг в каждом ценовом диапазоне.

11. Использование DISTINCT с GROUP BY

Хотя GROUP BY часто может заменить DISTINCT, их можно использовать вместе, если необходимо.

Пример: Список уникальных авторов из таблицы books

SELECT DISTINCT author
FROM books;

Или используя GROUP BY:

SELECT author
FROM books
GROUP BY author;

Объяснение:

  • Оба запроса возвращают список уникальных авторов из таблицы books.

12. Группировка с функциями даты

Если бы у нас была информация о датах, мы могли бы сгруппировать данные по частям даты. Поскольку в нашей схеме нет столбца publication_date, мы пропустим этот пример.

Заключение

Предложение GROUP BY в PostgreSQL — это мощный инструмент для агрегирования и обобщения данных. Овладев GROUP BY и агрегатными функциями, вы сможете извлечь значимые выводы из своих данных.

Ключевые моменты:

  • Используйте GROUP BY, чтобы сгруппировать строки, которые имеют одинаковое значение в одном или нескольких столбцах.
  • Агрегатные функции, такие как COUNT, SUM, AVG, MAX и MIN, выполняют вычисления на сгруппированных данных.
  • Предложение HAVING фильтрует группы на основе агрегатных значений.
  • Комбинируйте GROUP BY с ORDER BY, чтобы отсортировать результаты группировки.
  • Вы можете группировать с использованием выражений и применять конструкции CASE для пользовательских группировок.

Schema Initialization

To run the examples provided, ensure your PostgreSQL database is set up with the following schema:

-- Create the books table
CREATE TABLE books (
    title VARCHAR(255),
    author VARCHAR(255),
    price NUMERIC(10, 2),
    pages INTEGER
);
 
-- Insert sample data into the books table
INSERT INTO books (title, author, price, pages) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', 10.99, 180),
('This Side of Paradise', 'F. Scott Fitzgerald', 7.99, 305),
('To Kill a Mockingbird', 'Harper Lee', 7.99, 281),
('Go Set a Watchman', 'Harper Lee', 8.99, 278),
('1984', 'George Orwell', 8.99, 328),
('Animal Farm', 'George Orwell', 5.99, 112),
('Pride and Prejudice', 'Jane Austen', 6.99, 279),
('Sense and Sensibility', 'Jane Austen', 5.99, 226),
('The Catcher in the Rye', 'J.D. Salinger', 9.99, 214),
('Moby-Dick', 'Herman Melville', 11.99, 635),
('War and Peace', 'Leo Tolstoy', 12.99, 1225);
 
-- Create the authors table
CREATE TABLE authors (
    name VARCHAR(255)
);
 
-- Insert sample data into the authors table
INSERT INTO authors (name) VALUES
('F. Scott Fitzgerald'),
('Harper Lee'),
('George Orwell'),
('Jane Austen'),
('J.D. Salinger'),
('Herman Melville'),
('Leo Tolstoy'),
('Mark Twain');  -- Added an author without books for JOIN examples
 
-- Create the movies table
CREATE TABLE movies (
    title VARCHAR(255)
);
 
-- Insert sample data into the movies table
INSERT INTO movies (title) VALUES
('The Shawshank Redemption'),
('The Godfather'),
('The Dark Knight'),
('Pulp Fiction'),
('The Lord of the Rings');
JOIN: Объединение таблицINSERT: Добавление новых данных