Simulative

🔥 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 с агрегатными функциями, когда они выступают в роли оконных.

- - - - - - - - - -

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

👍24

3.25K views16:07