🔥 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

🔥 Использование метода get в Python

Наверняка вы знаете, что из словаря в Python элемент можно «вытащить» минимум 2 способами:

* указав ключ в квадратных скобках (d[key])
* воспользовавшись методом get

Однако, очень часто на code review встречаемся с тем, что люди используют его неправильно, сами того не замечая. Поэтому - опрос 📊

Что выведет следующий код?

d = {
    'first': 1,
    'second': 2
}

d.get('third', default=10)

P.S. Вечером будет подробный разбор - не пропустите 😉

🔥 Использование метода get в Python

Итак, опрос показал, что только 23% опрошенных дали правильные ответы 😱 Давайте разбираться, что к чему.

# Зачем нужен get?

В комментариях нас спросили: «А зачем вообще нужен метод get?». Основных причины 2:

1. Если указать в квадратных скобках ключ, которого нет в словаре, мы получим KeyError. Например:

d = {
    'first': 1,
    'second': 2
}

d['third']
# KeyError: 'third'

2. Если мы хотим указать какое-то дефолтное значение, которое будет использоваться по умолчанию в случае отсутствия ключа, нам придется писать лишний код. Например:

try:
    value = d['third']
except KeyError:
    value = 10
    
print(value)
# 10

Решение есть! Это метод get, который:

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

Например, получаем не KeyError, а None, хотя такого ключа в исходном словаре d нет:

d.get('third')
# None

# В чем же ошибка?

Казалось бы, мы делаем все логично - вторым аргументом указываем дефолтное значение default=10. Что не так? 🧐

А ошибка весьма нетривиальная: Метод get не принимает именованные аргументы!

Соответственно, когда мы пишем такой код, мы ожидаемо получаем TypeError:

d.get('third', default=10)
# TypeError: get() takes no keyword arguments

# Как быть?

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

d.get('third', 10)
# 10

Вот такое неожиданное поведение. Согласитесь - легко попасть в эту ловушку... 🥲

- - - - -

🔗 Прокачивайтесь в Python и других языках на интересных задачах с платформой itresume.ru

😱 Будьте аккуратны с последовательностью JOIN в SQL-запросах

Проверяя работы студентов в нашем Симуляторе, мы постоянно сталкиваемся с одной и той же ошибкой:

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

Приведем пример для иллюстрации этой ошибки. Пусть нам даны 3 таблицы:

* Таблица с задачами Problem
* Таблица с тегами TagList
* Таблица со страницами сайта Page
* Таблица-связка задач и тегов ProblemTag

А теперь давайте попробуем написать 2 запроса:

1. Выведем все задачи со всеми соответствующими каждой задаче тегами, а также информацию о странице по каждой задаче
2. Выведем все задачи со всеми соответствующими тегами, а также информацию по каждому тегу

Допустим первый запрос мы написали так:

select *
from problem p 
left join problemtag p2 
on p.id = p2.problem_id 
join page p3
on p.page_id = p3.id

А второй запрос так:

select *
from problem p 
left join problemtag p2 
on p.id = p2.problem_id 
join taglist t
on t.id = p2.tag_id

🟢 А в чем, собственно, ошибка?

По идее, учитывая поставленное задание, количество строк в этих двух запросах должно совпадать. Это логично - в обоих запросах мы отталкиваемся от таблиц Problem и ProblemTag, а меняется только последний джоин. Однако, именно он и является критичным.

Если мы посмотрим на количество строк, то заметим, что во втором запросе мы потеряли строки. Вот так выглядит количество строк:

В первом запросе - 524
Во втором запросе - 451
В таблице ProblemTag - 451
Задач, для которых нет ни одного тега - 73

И вот теперь мы видим, что во втором запросе мы потеряли ровно все задачи, для которых нет ни одного тега!

🟢 Как так получилось?

Очень просто - во всем виновата последовательность джоинов:

1. В первом запросе с помощью left join мы сохраняем все задачи, даже если для них нет тегов. А последний джоин эти задачи не отсеивает, потому что соединяет таблицы Page и Problem, для которых есть соответствие в каждой строке.
2. А во втором запросе последний джоин отбрасывает все задачи без тегов! Это происходит потому что в join фигурирует таблица ProblemTag. Т.к. джоин обычный (inner join), остаются только строки из ProblemTag, а значит все задачи, для которых нет тегов просто выкидываются из расчета.

Получается, во втором запросе left join не имеет никакого смысла и его эффект перебивается последним join? Да, именно так. И это очень частая ошибка, которая встречается во многих боевых задачах. А самое страшное, что ее реально сложно заметить.

🟢 Как исправить?

Есть несколько способов исправить такой запрос:

1. Изменить порядок джоинов (перенести left на последнее место и заменить на right)
2. Вместо последнего inner join также указать left join

Например:

select *
from problemtag p 
join taglist t 
on t.id = p.tag_id
right join problem p2 
on p2.id = p.problem_id 

А вы допускали такую ошибку? На всякий случай проверьте свои рабочие скрипты, вдруг туда затесался враг 😁

- - - - -
🔗 Залетайте в Симулятор «Аналитик данных» 👉🏻 https://vk.cc/ch76dC и не делайте таких ошибок 🙃

🔥 Давайте проведем live coding?

Ребят, у нас возникла идея - а давайте покодим в прямом эфире? Например, решим какое-нибудь тестовое задание по SQL или Python на позицию Аналитика Данных 🙃

🟢 Если вам нравится эта мысль - поставьте, пожалуйста, реакцию на это сообщение. Так мы поймем, что интерес есть и в ближайшее время вернемся с приглашением 🤟🏻

P.S. Если вы хотите другую тему для лайвкодинга - напишите об этом в комментариях. Мы с удовольствием возьмем ее в работу.

P.P.S. Если у вас есть классное тестовое задание, которые было бы круто порешать в прямом эфире - напишите в личку @andron233