Глава 8. Группировка и агрегация

import pandas as pd
import sqlalchemy as sa
import sql

pd.set_option('display.max_rows', 20)

connection_url = sa.engine.URL.create(
    drivername="mysql+pymysql",
    host="localhost",
    port=3306,
    database="sakila",
    username="root",
    password="********",
)
engine = sa.create_engine(connection_url)

%load_ext sql

# %config SqlMagic.displaylimit = 20
%config SqlMagic.displaycon = False
# %config SqlMagic.feedback = False
%config SqlMagic.autopandas = True

%sql engine

print(f"Pandas ver. {pd.__version__}: порог усечения строк уменьшен до 20")
print(f"SQLAlchemy ver. {sa.__version__}: подключение создано")
print(f"JupySQL ver. {sql.__version__}: подключен через SQLAlchemy Engine")

Pandas ver. 3.0.5: порог усечения строк уменьшен до 20
SQLAlchemy ver. 2.0.52: подключение создано
JupySQL ver. 0.11.1: подключен через SQLAlchemy Engine

Концепция группировки¶

При наличии 599 клиентов, имеющих более 16000 записей об аренде, просмотрев необработанные данные, невозможно определить какие клиенты взяли напрокат больше всего фильмов:

%%sql
SELECT customer_id FROM rental;

Вместо этого мы можем попросить сервер базы данных сгруппировать данные с помощью предложения group_by для группировки данных о прокате по идентификатору клиента:

%%sql
SELECT customer_id
FROM rental
GROUP BY customer_id;

Результирующий набор содержит по одной строке для каждого отдельного значения в столбце customer_id. Причина меньшего результирующего набора в том, что некоторые клиенты брали напрокат более одного фильма.

Чтобы увидеть, сколько фильмов было взято напрокат каждым клиентом, можно использовать агрегатную функцию count(*) в предложении select для подсчета количества строк в каждой группе:

%%sql
SELECT
    customer_id,
    COUNT(*) AS rental_count
FROM rental
GROUP BY customer_id;

Агрегатная функция count()

Подсчитывает количество строк в каждой группе. Звездочка сообщает серверу, что нужно считать все что есть в группе.

COUNT(*)
COUNT(имя_колонки)
COUNT(DISTINCT имя_колонки)

Результат зависит от аргумента в скобках

COUNT(*) — производит подсчет абсолютно всех физических строк в группе. Не важно, что находится внутри ячеек: он учитывает строки, полностью состоящие из NULL-значений, и дубликаты.
COUNT(имя_колонки) — считает количество заполненных значений в конкретной колонке. Игнорирует (пропускает) значения NULL. Если в группе из 10 строк у двух строк в этой колонке стоит NULL, функция вернет 8.
COUNT(DISTINCT имя_колонки) — считает только уникальные заполненные значения в колонке. Так же игнорирует NULL, а все одинаковые значения считает за один раз.

Чтобы определить, какие клиенты взяли напрокат больше всего фильмов, добавим предложение order by:

%%sql
SELECT
    customer_id,
    COUNT(*) AS rental_count
FROM rental
GROUP BY customer_id
ORDER BY 2 DESC;

При группировке данных может потребоваться отфильтровать нежелательные данные из набора результатов на основе групп данных (а не необработанных данных). Поскольку предложение group by выполняется после того, как вычислено предложение where, добавить условия фильтрации к предложению where для этой цели нельзя.

Например, вот к чему приводит попытка отфильтровать клиентов, бравших напрокат менее 40 фильмов:

%%sql
SELECT
    customer_id,
    COUNT(*)
FROM rental
WHERE COUNT(*) >= 40
GROUP BY customer_id;

RuntimeError: (pymysql.err.ProgrammingError) (1111, 'Invalid use of group function')
[SQL: SELECT
    customer_id,
    COUNT(*)
FROM rental
WHERE COUNT(*) >= 40
GROUP BY customer_id;]
(Background on this error at: https://sqlalche.me/e/20/f405)

%config SqlMagic.autopandas = False

Вы не можете обратиться к агрегатной функции count(*) в предложении where потому что во время вычисления предложения where группы еще не были сгенерированы. Вместо этого вы должны поместить условия группового фильтра в предложение having:

%%sql
SELECT
    customer_id,
    COUNT(*)
FROM rental
GROUP BY customer_id
HAVING COUNT(*) >= 40;

%%sql
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    a.address,
    ci.city,
    COUNT(*) AS total_rentals
FROM rental AS r
    INNER JOIN customer AS c ON r.customer_id = c.customer_id
    INNER JOIN address AS a ON c.address_id = a.address_id
    INNER JOIN city AS ci ON a.city_id = ci.city_id
GROUP BY 
    c.customer_id,
    c.first_name,
    c.last_name,
    a.address,
    ci.city
HAVING COUNT(*) >= 40;

Пояснение

В блоке GROUP BY перечислены все неагрегированные колонки, которые указали в SELECT:

Согласно строгому стандарту SQL, если вы выводите в SELECT текстовые поля (имя или адрес), СУБД должна четко понимать, как их группировать.
Хотя customer_id является уникальным ключом и MySQL 8.0 достаточно умен, чтобы понять, что у одного ID не может быть двух разных имен, хорошим тоном и гарантией защиты от ошибок является явное перечисление всех выводимых колонок в блоке GROUP BY.
Когда вы пишете GROUP BY customer_id, first_name, last_name, address, city, для сервера это означает: Создай кучку только тогда, когда совпадают ВСЕ указанные признаки одновременно. Но поскольку имя, фамилия и адрес намертво привязаны к конкретному customer_id (у клиента №1 всегда одно и то же имя, фамилия и адрес), то состав этих кучек физически вообще не изменится!

Мы перечисляем эти поля в GROUP BY исключительно ради того, чтобы удовлетворить строгое требование SQL-движка: Всё, что ты выводишь на экран в SELECT, должно быть либо внутри агрегатной функции, либо зафиксировано в правилах группировки GROUP BY.

SQL-движок видит, что customer_id уникален для каждого человека. Из-за этого добавление в GROUP BY зависимых полей first_name, address и т.д. физически не может раздробить группу сильнее. Движок группирует по совокупности этих полей, но ведущим (определяющим размер кучки) фактором остается customer_id.

Что и как считает COUNT(*)?
Порядок выполнения запроса в SQL выглядит так: FROM (включая все JOIN) ➔ GROUP BY ➔ SELECT / COUNT.

До группировки: сначала SQL-движок выполняет все INNER JOIN. Он берет каждую строчку аренды из таблицы rental, подклеивает к ней имя клиента, затем адрес, затем город. На выходе получается одна огромная плоская временная таблица, где строк ровно столько же, сколько было в rental (ведь у каждой аренды есть один клиент, один адрес и один город). Каждый факт аренды превратился в длинную строку.
В момент группировки: Сервер берет эту огромную соединенную таблицу и раскладывает её на кучки по нашему правилу из первого пункта (по клиентам).
В момент подсчета: функция COUNT(*) расшифровывается как посчитай количество физических строк в получившейся кучке.

GROUP BY по нескольким полям в данном случае не дробит группы сильнее, а просто легализует вывод этих полей в SELECT.

COUNT(*) после JOIN считает количество строк в финальной сгруппированной таблице. Поскольку связь была один ко многим (customer к rental), количество строк в группе всё так же равно количеству аренд клиента.

В таблице rental колонка return_date содержит дату и время возврата диска. Если покупатель еще не принес фильм обратно в прокат, в эту ячейку записывается NULL.

%%sql
SELECT
    staff_id,
    COUNT(*) AS total_rents,
    COUNT(return_date) AS returned_rentals,
    (COUNT(*) - COUNT(return_date)) AS still_rented
FROM rental
GROUP BY staff_id;

Агрегатные функции¶

Агрегатные функции выполняют определенные операции над всеми строками в группе. Распространенные агрегатные функции:

Функция	Описание
max()	Возвращает максимальное значение в наборе
min()	Возвращает минимальное значение в наборе
avg()	Возвращает усредненное значение в наборе
sum()	Возвращает сумму значений в наборе
count()	Возвращает количество значений в наборе

Вот как выглядит запрос, в котором используются все распространенные агрегатные функции для анализа данных по прокату фильмов:

%%sql
SELECT
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(*) num_payments
FROM payment;

Неявная и явная группировка¶

В предыдущем примере каждое значение, возвращаемое запросом, генерируется агрегатной функцией. Поскольку предложения group by в запросе нет, существует единственная неявная группа (все строки в таблице payment).

Однако в большинстве случаев требуется получить дополнительные столбцы вместе со столбцами, генерируемыми агрегатными функциями. Например, можно расширить предыдущий запрос не для всех клиентов одновременно, а для каждого клиента.

Для каждого такого запроса нужно вместе с пятью агрегатными функциями выполнить выборку customer_id:

%%sql
SELECT
    customer_id,
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(*) num_payments
FROM payment;

RuntimeError: (pymysql.err.OperationalError) (1140, "In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sakila.payment.customer_id'; this is incompatible with sql_mode=only_full_group_by")
[SQL: SELECT
    customer_id,
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(*) num_payments
FROM payment;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Однако, мы получим сообщение об ошибке.

Этот запрос не выполняется, потому что в нем не указано явно как должны быть сгруппированы данные. Следовательно, в запрос нужно добавить предложение group by чтобы указать к какой группе строк следует применять агрегатные функции:

%%sql
SELECT
    customer_id,
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(*) num_payments
FROM payment
GROUP BY customer_id;

При включении предложения group by сервер понимает, что надо сначала сгруппировать строки, имеющие одинаковое значение в столбце customer_id, а затем применить к каждой из 599 групп агрегатные функции.

Подсчет различных значений¶

При использовании функции count() для определения количества членов в каждой группе у вас есть выбор: подсчитать все элементы группы или подсчитать только различные значения столбца среди всех элементов группы.

Рассмотрим пример, в котором функция count() и столбец customer_id используются двумя разными способами:

%%sql
SELECT
    COUNT(customer_id) num_rows,
    COUNT(DISTINCT customer_id) num_customers
FROM payment;

При указании ключевого слова distinct функция count() проверяет значения столбца для каждого члена группы, находя и удаляя дубликаты, а не просто подсчитывает количество значений в группе.

Использование выражений¶

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

Например, можно найти максимальное количество дней между моментом, когда фильм был взят напрокат и последующим его возвратом:

%%sql
SELECT MAX(DATEDIFF(return_date, rental_date))
FROM rental;

Функция datediff() используется для вычисления количества дней между датой возврата фильма и датой взятия его напрокат для каждой аренды фильма, а функция max() возвращает максимальное найденное значение.

Обработка значений null¶

При выполнении агрегации (на самом деле – любого вида числовых вычислений) всегда следует учитывать как на результат вычислений могут повлиять значения null.

Для понимания построим простую таблицу для хранения числовых данных и заполним ее множеством {1, 3, 5}:

%%sql
CREATE TABLE number_tbl
    (val SMALLINT);

%%sql
INSERT INTO number_tbl VALUES (1);
INSERT INTO number_tbl VALUES (3);
INSERT INTO number_tbl VALUES (5);

Рассмотрим запрос, выполняющий пять агрегатных функций:

%%sql
SELECT
    COUNT(*) num_rows,
    COUNT(val) num_vals,
    SUM(val) total,
    MAX(val) max_val,
    AVG(val) avg_val
FROM number_tbl

Теперь добавим в таблицу значение NULL и снова выполним тот же запрос:

%%sql
INSERT INTO number_tbl VALUES (NULL);

%%sql
SELECT
    COUNT(*) num_rows,
    COUNT(val) num_vals,
    SUM(val) total,
    MAX(val) max_val,
    AVG(val) avg_val
FROM number_tbl

Функции sum(), max() и avg() возвращают те же значения – игнорируют любые встречающиеся NULL. Функция count(*) возвращает значение 4, поскольку таблица number_tbl сейчас содержит четыре строки. А функция count(val) по-прежнему возвращает значение 3.

Дело в том, что count(*) подсчитывает количество строк, тогда как count(val) подсчитывает количество значений в столбце val и игнорирует любые обнаруженные в нем значения NULL.

Генерация групп¶

Из этого раздела узнаем как группировать данные по одному или нескольким столбцам, как группировать данные с помощью выражений и как создавать сводки внутри группы.

Группировка по одному столбцу¶

Группы из одного столбца – самый простой и наиболее часто используемый тип группировки.

Если, например, хотим найти количество фильмов, связанных с каждым актером, нужна группировка по единственному столбцу film_actor.actor_id:

%%sql
SELECT
    actor_id,
    COUNT(*)
FROM film_actor
GROUP BY actor_id;

Этот запрос генерирует 200 групп, по одной для каждого актера, а затем суммирует количество фильмов для каждого участника группы.

Многостолбцовая группировка¶

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

Расширяя предыдущий пример, представим, что для каждого актера хотим найти общее количество фильмов с разными рейтингами (G, PG, ...). Вот как этого добиться:

%%sql
SELECT
    fa.actor_id,
    f.rating,
    COUNT(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
GROUP BY fa.actor_id, f.rating
ORDER BY fa.actor_id, f.rating;
-- Явное лучше неявного `ORDER BY 1, 2`

Эта версия запроса генерирует 996 групп, по одной для каждой комбинации “актер/рейтинг фильма”, полученной путем соединения таблицы film_actor с таблицей film.

Группировка с помощью выражений¶

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

Рассмотрим запрос, который группирует прокат по годам:

%%sql
SELECT
    EXTRACT(YEAR FROM rental_date) rental_year,
    COUNT(*) how_many
FROM rental
GROUP BY EXTRACT(YEAR FROM rental_date);

В этом запросе применено простое выражение, которое использует функцию extract() чтобы вернуть из даты только год для соответствующей группировки строк в таблице rental.

%%sql
/* Примечание: функция YEAR(date) работает
   идентично EXTRACT(YEAR FROM date), 
   но пишется короче и читается легче
*/
SELECT
    YEAR(rental_date) AS rental_year,
    COUNT(*) AS total_rentals
FROM rental
GROUP BY rental_year;
/* Для MySQL использование алиаса в GROUP BY
   является официально задокументированной
   нормой и стандартом де-факто
*/

Генерация итоговых данных¶

В разделе Многостолбцовая группировка приведен пример, в котором подсчитывается количество фильмов для каждой комбинации “актер/рейтинг фильма”. Допустим, вместе с общим количеством для каждой комбинации нужно получить и общее количество для каждого отдельного актера.

Можно выполнить дополнительный запрос и объединить результаты. Но лучше использовать конструкцию with rollup:

# Увеличим количество строк при усечении до 20 (по 10 сверху и снизу)
pd.set_option('display.min_rows', 20)

%%sql
SELECT
    fa.actor_id,
    f.rating,
    COUNT(*)
FROM film_actor fa
    INNER JOIN film f ON fa.film_id = f.film_id
GROUP BY fa.actor_id, f.rating WITH ROLLUP
ORDER BY fa.actor_id, f.rating;

Теперь в результирующем наборе имеется 201 дополнительная строка по одной для каждого из 200 различных актеров и одна общая (для всех актеров вместе).

В столбце rating для итоговых значений для 200 актеров предоставляется значение NULL (в Pandas NaN), поскольку выполняется накопление по всем рейтингам.

Для строки общего итога (первая строка вывода) значение NULL (NaN) предоставлено как для столбца actor_id так и для столбца rating. Сумма в первой строке вывода равна 5462, что соответствует числу строк в таблице film_actor.

%%sql
SELECT COUNT(*) FROM film_actor;

Если помимо итогов по актерам хотите подсчитать итоги по рейтингу, можно использовать конструкцию with cube, которая будет генерировать итоговые строки для всех возможных комбинаций столбцов группировки.

Однако конструкция with cube недоступна в MySQL версии 8.0.

# Возвращаем стандартное поведение Pandas (по умолчанию min_rows = 10)
pd.reset_option('display.min_rows')

Условия группового фильтра¶

В главе 4 Фильтрация мы познакомились с типами условий фильтрации и узнали как их использовать в предложении where. При группировке данных также можно применить фильтрующее условие к данным после того как были сгенерированы группы.

Типы условий фильтрации должны быть размещены в предложении having.

%%sql
SELECT
    fa.actor_id,
    f.rating,
    COUNT(*)
FROM film_actor fa
    INNER JOIN film f ON fa.film_id = f.film_id
WHERE f.rating IN ('G', 'PG')
GROUP BY fa.actor_id, f.rating
HAVING COUNT(*) > 9;

Этот запрос имеет два условия фильтрации: одно – в предложении where, которое отфильтровывает любые фильмы с рейтингом отличным от G или PG, и еще одно – в предложении having, которое отфильтровывает всех актеров, снявшихся менее чем в 10 фильмах.

Таким образом, один из фильтров действует на данные до* их группировки, а другой – после того, как группы были созданы.

Если поместите оба фильтра в предложение where, то получите сообщение об ошибке:

%%sql
SELECT
    fa.actor_id,
    f.rating,
    COUNT(*)
FROM film_actor fa
    INNER JOIN film f ON fa.film_id = f.film_id
WHERE f.rating IN ('G', 'PG')
    AND COUNT(*) > 9
GROUP BY fa.actor_id, f.rating;

RuntimeError: (pymysql.err.ProgrammingError) (1111, 'Invalid use of group function')
[SQL: SELECT
    fa.actor_id,
    f.rating,
    COUNT(*)
FROM film_actor fa
    INNER JOIN film f ON fa.film_id = f.film_id
WHERE f.rating IN ('G', 'PG')
    AND COUNT(*) > 9
GROUP BY fa.actor_id, f.rating;]
(Background on this error at: https://sqlalche.me/e/20/f405)

Этот запрос не работает, потому что нельзя включать агрегатную функцию в предложение where. Это связано с тем, что фильтры в предложении where вычисляются до группировки, поэтому сервер еще не в состоянии выполнять какие-либо функции для групп.

Warning

При добавлении фильтров в запрос, который включает предложение group by хорошо подумайте, действует ли фильтр на необработанные данные – в этом случае он должен принадлежать предложению where.

Если же фильтр относится к сгруппированным данным, он должен принадлежать предложению having.

Упражнения¶

Упражнение 8.1¶

Создайте запрос, который подсчитывает количество строк в таблице payment.

%%sql
SELECT
    COUNT(*)
FROM payment;

Упражнение 8.2¶

Измените запрос из упражнения 8.1 так, чтобы подсчитать количество платежей, произведенных каждым клиентом. Выведите идентификатор клиента и общую уплаченную сумму для каждого клиента.

%%sql
SELECT
    customer_id,
    COUNT(*) num_payments,
    SUM(amount) total_amount
FROM payment
GROUP BY customer_id;

Упражнение 8.3¶

Измените запрос из упражнения 8.2 включив в него только тех клиентов, у которых имеется не менее 40 выплат.

%%sql
SELECT
    customer_id,
    COUNT(*) num_payments,
    SUM(amount) total_amount
FROM payment
GROUP BY customer_id
HAVING COUNT(*) >= 40;

Вариация 8.3 для тренировки:

%%sql
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) cust_name,
    COUNT(*) num_payments,
    SUM(p.amount) total_amount
FROM payment p
    INNER JOIN customer c ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name
HAVING COUNT(*) >= 40
ORDER BY total_amount DESC;