Симулейтив: “4 реальных кейса применения оконных функций Всем привет! Я Павел Беляе…”

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 value

LTV часто выбирают как главную метрику компании (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