4 реальных кейса применения оконных функцийВсем привет! Я Павел Беляев, ментор курса «Аналитик данных» и ведущий канала Тимлидское об аналитике 👋🏻Хочу показать несколько примеров применения оконок из реальной практики аналитиков Яндекс eLama.
1️⃣
Последняя запись в исторических данныхЕсли имеем дело с таблицей, которая обновляется не перезаписью с нуля, а добавлением новых строк для объектов, которые изменились, вот как можно взять последнюю строку:
SELECT *
FROM
(
SELECT *
MAX(updated_at) OVER (PARTITION BY payment_id) AS last_update
FROM payment
)
WHERE updated_at = last_update
Здесь
payment — таблица, содержащая транзакции пользователя. Одна строка отражает состояние транзакции на момент добавления строки, т. е. на дату
updated_at. Каждая транзакция имеет уникальный идентификатор
payment_id, но он не уникален в рамках всей таблицы.
2️⃣
Топовые юзерыВыясним, кто же главные герои, которые делают нам кассу. Если они уходят, мы получаем заметное проседание доходов. И наоборот, если доходы просели, имеет смысл выяснить, как дела у наших клиентов, не перестали ли пользоваться нашими услугами.
SELECT *
FROM
(
SELECT d, w, user_id, revenue,
ROW_NUMBER() OVER (PARTITION BY d ORDER BY revenue DESC) AS rn -- нумеруем юзеров в порядке убывания их оборота
FROM
(
SELECT DATE(date_paid) AS d,
dateName('weekday', date_payed) AS w,
user_id,
SUM(revenue) AS revenue
FROM datamart.financial_activity
GROUP BY 1,2,3
)
)
WHERE rn<=15 -- Количество топовых юзеров за каждый день
AND w='Wednesday' -- День недели. Часто пользователи активничают ритмично, например по средам.
ORDER BY d DESC, revenue DESC
3️⃣
Lifetime valueLTV часто выбирают как главную метрику компании (North Star Metric). Оконкой можно вывести LTV юзера на каждый момент времени — например, когда он совершает очередной платеж.
SELECT user_id, date_paid,
turnover,
SUM(turnover) OVER (PARTITION BY user_id ORDER BY date_paid) AS LTV
FROM datamart.money
ORDER BY date_paid DESC
Заметим, что при такой записи выводится LTV на момент
date_paid, а не текущий LTV. Так что можно следить за скоростью его роста.
4️⃣
Скользящее среднееПоложим, у нас есть некая величина, которая становится известной только в начале следующего месяца. Например, сумма заактированных оборотов пользователя.
Тогда в течение месяца мы её ещё не знаем. Простой прогноз можно состряпать как скользящее среднее за предыдущие месяцы.
В ClickHouse прошлые значения выводит функция
lagInFrame():
SELECT period, SUM(act_amount) am,
lagInFrame(am,1,0) OVER (ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS am1,
lagInFrame(am,2,0) OVER (ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS am2,
lagInFrame(am,3,0) OVER (ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS am3,
(am1 + am2 + am3)/3 AS am_avg,
FROM datamart.money
GROUP BY ALL
Ставьте огоньки, если пригодится в работе!
📈
Симулейтив |
ВК |
YouTube 
