3 СПОСОБА ФИЛЬТРАЦИИ СТРОК ПЕРЕД АГРЕГИРОВАНИЕМ: SUBQUERY, COU | IT Resume

3 СПОСОБА ФИЛЬТРАЦИИ СТРОК ПЕРЕД АГРЕГИРОВАНИЕМ: SUBQUERY, COUNT + CASE, FILTER


ПРОЛОГ

Сгруппировать строки и посчитать какую-то метрику (например, сумму или среднее) - типичная операция в SQL. Мы знаем, что делается это с помощью оператора GROUP BY.

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

Дана таблица users со столбцами:

* id
* name
* is_verified - подтвердил ли пользователь аккаунт (True/False)
* date_joined

Задача:

Посчитать количество подтвержденных пользователей, зарегистрировавшихся в сервисе в мае 2022 года, с разбивкой по дням.

* Кстати, работаем мы с PostgreSQL.



СПОСОБ 1. ПОДЗАПРОС.

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

В нашем случае запрос будет выглядеть так:

with filtered_users as (
select
id,
is_active,
to_char(date_joined, 'DD') as "day"
from users
where is_active is True
and to_char(date_joined, 'YYYY-MM') = '2022-05'
)
select
day,
count(*) as cnt
from filtered_users
group by day

Очевидные минусы такого подхода:

* На большом количестве строк подзапрос потенциально загрузит в память миллионы строк
* Запрос довольно громоздкий для той задачи, что поставлена перед нами



СПОСОБ 2. COUNT + CASE

Классический прием для решения таких задач - использование оператора CASE внутри агрегатных функций. Ответ можно записать в таком виде:

select
to_char(date_joined, 'DD') as "day",
count(case when is_active is True then 1 end) as cnt
from users
where to_char(date_joined, 'YYYY-MM') = '2022-05'
group by "day"

Видим, что запрос стал намного меньше + мы сразу же проводим все вычисления, не нагружая память.

«Фокус» в том, что внутри функции count мы считаем количество строк, которые удовлетворяют условию. Проверка условия осуществляется с помощью условного оператора CASE.




СПОСОБ 3. FILTER

Аналогичный результат агрегирования мы можем получить с помощью предложения FILTER. Механика действий у него аналогичная - на вход агрегатной функции count подаются только те строки, которые удовлетворяют условию фильтрации.

select
to_char(date_joined, 'DD') as "day",
count(*) filter(where is_active is True) as cnt
from users
where to_char(date_joined, 'YYYY-MM') = '2022-05'
group by "day"

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

- - - - - - - - - -

Освойте еще больше «фишечек» в нашем Симуляторе