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

- - - - - - - - - -

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

🔥 Разбор тестового задания в Тиньков [SQL]

Итак, нам дана база клиентов, сотрудников и обзвонов (структура базы прикреплена ниже).

В какой СУБД мы будем работать — не сказано. По косвенным признакам мы предполагаем, что это PostgreSQL.


Задача 1.

Получить список сотрудников в формате: «Иванова — Наталья – Юрьевна». ФИО должно быть прописано в одном столбике, разделение "—".

Вывести: новое поле, назовем его fio, birth_dt

Решение

Эта задача достаточно простая — здесь даже нет необходимости джойнить другие таблицы, достаточно поработать с таблицей Employees.

Основная проблема — вывести ФИО через заданный разделитель. Многие решают эту задачу с помощью простой конкатенации:

SELECT
      first_nm || '—' || middle_nm || '—' || last_nm AS fio, 
      birth_dt
FROM employees

Но мы работаем в PostgreSQL, поэтому воспользуемся плюшкой — функцией CONCAT_WS. Она тоже делает конкатенацию строк, но первым аргументом принимает разделитель:

SELECT 
      concat_ws('—', first_nm, middle_nm, last_nm) AS fio, 
      birth_dt
FROM employees

Выглядит посимпатичней. Заодно и перед интервьюером блеснули знаниями 😅


Задача 2.

Вывести %% дозвона для каждого дня. Период с 01.10.2020 по текущий день.

%% дозвона – это доля принятых звонков (dozv_flg = 1) от всех поступивших звонков (dozv_flg = 1 or dozv_flg = 0).

Вывести: date, sla (%% дозвона)

Решение

Здесь задача уже поинтересней — мы все еще работаем с одной таблицей, но многие соискатели на таких задачах начинают городить многоэтажные подзапросы.
А на самом деле, все просто — достаточно просто знать, что условный оператор CASE можно использовать внутри агрегатных функций — например, COUNT.

Итак, чтобы посчитать SLA, нам нужно:

— посчитать кол-во звонков с dozv_flg = 1
— посчитать общее количество звонков
— разделить одно на другое

Давайте сделаем это в одном запросе, без подзапросов и CTE.

SELECT
      start_dttm::date AS "date", 
      COUNT(CASE 
                WHEN dozv_flg=1 
                THEN 1 
            END)/COUNT(CASE 
                           WHEN dozv_flg IN (1, 0) 
                           THEN 1 
                       END) AS sla
FROM calls
WHERE start_dttm::date BETWEEN '2020-10-01' AND now()::date
GROUP BY start_dttm::date

Вот, собственно, и все. Но проговорим несколько важных моментов:

1. Почему мы написали не COUNT(*), а COUNT(CASE WHEN dozv_flg IN (1, 0) THEN 1 END)? Мы просто перестраховались — вдруг там еще какие-то значения могут быть.

2. Зачем мы делаем преобразование с помощью ::date? А потому что оператор between потеряет все записи за сегодня, если не преобразовать эти поля в дату. Опять же мы просто перестраховались.


Задача 3.

Дана таблица clients:

- id клиента
- calendar_at - дата входа в мобильное приложение

Нужно написать запрос для расчета MAU.

Решение

Если что, MAU - monthly active users: количество уникальных клиентов, проявляющих активность в приложении в течение месяца.

Многие по ошибке выводят MAU в виде таблицы со столбцами Месяц — Кол-во активных клиентов. Это неправильно - MAU всегда должно быть одним числом.

Соответственно, решение задачи сводится к следующим пунктам:

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

Для решения задачи мы будем использовать CTE и оператор DISTINCT внутри COUNT:

WITH a AS(
          SELECT to_char(calendar_dt, 'MM') AS mon, 
                 COUNT(distinct id) AS cnt
          FROM clients 
          GROUP BY mon)
SELECT avg(cnt) AS mau 
FROM a

Сразу отметим - MAU можно считать и по-другому. Например, сразу брать цифры на примере одного месяца; находить медиану или как-то еще. Мы просто показали один из вариантов.

- - - - -

🔗 Пройдите Симулятор по SQL, чтобы прокачаться на бизнесовых задачах 👉🏻 https://vk.cc/cfpuft

🔥 Транспонируем матрицу в одну строку в Python

Типичная задача: дана матрица [[1, 2, 3], [1, 2, 3]]. Необходимо ее транспонировать в одну строку →
[[1, 1], [2, 2], [3, 3]].

Как это сделать, подробно рассказываем тут 👉🏻 itresume.ru/problems/transp-matrix 👈🏻 (здесь же можно попробовать решить задачу)

Кстати, недавно видели такую задачу на собеседовании в известную компанию. А вы бы смогли решить? 😏

🔥 Деление целых чисел в PostgreSQL

Когда мы делим одно целое число на другое, мы всегда ожидаем увидеть правильный результат - даже если он дробный. Однако, в PostgreSQL это работает не так.

Простой пример:

select 1/2
# 0

В чем подвох?

Все очень просто - по умолчанию, в PostgreSQL деление целых чисел друг на друга дает также целое число:

select pg_typeof(1/2)
# integer

А в реальности это встречается?

Если говорить про практическую составляющую - это может негативно сказаться при расчете маржинальности, например:

select revenue/cost
# 0

Здесь и доход (revenue), и розничная цена (cost) представлены целыми числами, а маржинальность предполагается менее 100%.

Как быть?

Что делать и как избежать этой неприятной ошибки?

Очень просто - достаточно преобразовать числитель или знаменатель к дробному числу. Вот несколько способов:

* Умножить на дробное число (например, 1.0)
* Выполнить явное преобразование типов с помощью cast
* Выполнить преобразование типов с помощью ::



select 1*1.0/2
# 0.5

select cast(1 as numeric)/2
# 0.5

select 1::numeric/2
# 0.5

Мы обычно используем первый или третий варианты, т.к. второй просто объемней.

- - - - -
🔗 Еще больше фишечек вы узнаете в нашем Симуляторе по SQL 👉🏻 https://vk.cc/cfT9wd