Учимся создавать «окна» в SQL
В прошлом посте мы узнали, зачем нужны оконные функции. Теперь научимся их объявлять. Все начинается с инструкции OVER() - она и определяет наше «окно».
Ключевые команды внутри OVER():
▪️PARTITION BY - разделяет данные на группы (партиции). Как GROUP BY, но без «схлопывания» строк. Считает функцию внутри каждой группы отдельно.
▪️ORDER BY - сортирует строки внутри окна. Критично для функций нарастающего итога, ранжирования и смещения (LAG/LEAD).
Разберем на примере простой таблички, содержащей дату, канал с которого пришел пользователь и количество конверсий:
SELECT
date AS dt
, medium AS med
, conversions AS conv
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium) AS sum
FROM orders
Что произойдет?
▪PARTITION BY Date создаст отдельное «окно» для каждой даты. Сумма будет считаться только в рамках одного дня.
▪ORDER BY medium отсортирует каналы внутри каждой даты.
▪SUM(conversions) в паре с ORDER BY рассчитает нарастающий итог конверсий внутри каждого дня. Для первой строки в окне (дне) sum будет равен ее conversions, для второй - сумме первой и второй, и так далее.
⚠️ Важно: ROWS / RANGE управляют диапазоном строк, по которым считается оконная функция. И даже если ничего не указывать, то по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
В результате выполнения запроса мы получим примерно такую табличку:
dt med conv sum
10.05.20 cpa 1 1
10.05.20 cpc 2 3
10.05.20 organic 1 4
11.05.20 cpa 1 1
11.05.20 cpc 3 4
11.05.20 direct 1 5
11.05.20 organic 2 7
12.05.20 cpc 1 1
12.05.20 organic 2 3
Основы разобрали! Далее я расскажу как сужать фокус окна до «скользящего» диапазона с помощью ROWS BETWEEN.
#харды #sql
В прошлом посте мы узнали, зачем нужны оконные функции. Теперь научимся их объявлять. Все начинается с инструкции OVER() - она и определяет наше «окно».
Ключевые команды внутри OVER():
▪️PARTITION BY - разделяет данные на группы (партиции). Как GROUP BY, но без «схлопывания» строк. Считает функцию внутри каждой группы отдельно.
▪️ORDER BY - сортирует строки внутри окна. Критично для функций нарастающего итога, ранжирования и смещения (LAG/LEAD).
Разберем на примере простой таблички, содержащей дату, канал с которого пришел пользователь и количество конверсий:
SELECT
date AS dt
, medium AS med
, conversions AS conv
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium) AS sum
FROM orders
Что произойдет?
▪PARTITION BY Date создаст отдельное «окно» для каждой даты. Сумма будет считаться только в рамках одного дня.
▪ORDER BY medium отсортирует каналы внутри каждой даты.
▪SUM(conversions) в паре с ORDER BY рассчитает нарастающий итог конверсий внутри каждого дня. Для первой строки в окне (дне) sum будет равен ее conversions, для второй - сумме первой и второй, и так далее.
⚠️ Важно: ROWS / RANGE управляют диапазоном строк, по которым считается оконная функция. И даже если ничего не указывать, то по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
В результате выполнения запроса мы получим примерно такую табличку:
dt med conv sum
10.05.20 cpa 1 1
10.05.20 cpc 2 3
10.05.20 organic 1 4
11.05.20 cpa 1 1
11.05.20 cpc 3 4
11.05.20 direct 1 5
11.05.20 organic 2 7
12.05.20 cpc 1 1
12.05.20 organic 2 3
Основы разобрали! Далее я расскажу как сужать фокус окна до «скользящего» диапазона с помощью ROWS BETWEEN.
#харды #sql