Функции оконного SQL: решение реальных бизнес-задач

Вы знакомы с функциями Windows, но знаете ли вы, как использовать их для решения бизнес-задач? После прочтения этой статьи вы это узнаете.

# Введение

Большинство из вас используют оконные функции SQL, но вы лишь поверхностно знакомы с их возможностями — здесь используется ROW_NUMBER(), там — SUM() OVER(). Настоящий потенциал оконных функций раскрывается при применении их к более сложным задачам. Я расскажу вам о четырех примерах, демонстрирующих их наибольшую полезность.

Приведенные примеры — это реальные вопросы для собеседований, которые вы можете попрактиковать на StrataScratch .

# Итоговые суммы

Расчет накопительных итогов — одно из наиболее распространенных применений функций Windows в бизнесе. Специалисты по финансам просто обожают это! Это используется для отслеживания совокупной ежемесячной выручки, что затем легко позволяет рассчитать, как текущее положение дел соотносится с целевым показателем годовой выручки.

Проблема заключается в том, что, как правило, в одном и том же выводе следует включать как значение за период, так и накопительную сумму. Нельзя использовать GROUP BY с SUM(), поскольку это сворачивает отдельные строки. Поэтому очевидное решение — использование оконной функции, то есть SUM() OVER().

// Пример: Расчет выручки за определенный период времени

В исходном вопросе по Amazon вас просят рассчитать скользящее среднее за 3 месяца. Однако мы проигнорируем это и рассчитаем совокупную выручку за каждый месяц.

Данные: Вот предварительный просмотр таблицы amazon_purchases.

Код: Внутренний запрос преобразует даты в формат ГГГГ-ММ с помощью функции TO_CHAR() и агрегирует ежемесячную выручку, отфильтровывая возвраты по условию WHERE purchase_amt > 0.

Внешний запрос применяет оконную функцию к рассчитанным нами ежемесячным суммам. Я (намеренно) не указываю явное условие фрейма в функции OVER(), поэтому оконная функция по умолчанию использует диапазон RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это означает, что окном являются все строки, предшествующие текущей строке, то есть месяцу. Другими словами, кумулятивная сумма равна: все предыдущие месяцы + текущий месяц. Неудивительно, что это классическое определение кумулятивной суммы.

SELECT t.month, t.monthly_revenue, SUM(t.monthly_revenue) OVER(ORDER BY t.month) AS cumulative_revenue FROM ( SELECT TO_CHAR(created_at::DATE, 'YYYY-MM') AS month, SUM(purchase_amt) AS monthly_revenue FROM amazon_purchases WHERE purchase_amt > 0 GROUP BY TO_CHAR(created_at::date, 'YYYY-MM') ORDER BY TO_CHAR(created_at::date, 'YYYY-MM') ) t ORDER BY t.month ASC;

Выход:

# Пробелы и островки (сессионизация)

Этот метод также предполагает использование последовательных данных, как и накопительная сумма, но при этом применяет другие оконные функции.

Остров — это последовательность строк с одинаковым условием , например, последовательные ежедневные входы в систему. Промежуток — это пространство между островами .

Одно из наиболее распространенных практических применений этого подхода — сессионизация , то есть группировка потока необработанных событий в сессии. Сессия обычно определяется как последовательность событий от одного и того же пользователя, где интервал между последовательными событиями не превышает определенного таймаута (30 минут — это стандарт веб-аналитики).

Сессионизация широко применяется в разработке продуктов и обработке данных . Она используется везде, где необходимо группировать необработанные потоки событий в осмысленные единицы активности.

Классическое обнаружение в SQL состоит из двух этапов:

• Функции LAG() или LEAD() используются для сравнения каждой строки с предыдущей или последующей и для обозначения начала новой серии.
• SUM(flag) OVER (PARTITION BY user ORDER BY date) — для накопления флагов в идентификатор серии, поскольку он остается неизменным внутри серии и увеличивается на каждой границе.

// Пример: Поиск серий действий пользователей

В задании из интервью на LinkedIn и Meta вас просят найти трех пользователей с самой длинной серией посещений платформы до 10 августа 2022 года. Вам следует вывести всех пользователей с тремя самыми длинными сериями, если таких пользователей несколько.

Данные: Таблица называется user_streaks.

Код: Запрос длинный, но он аккуратно структурирован в виде CTE, поэтому его легко понять.

1. unique_visits: Удаляет повторяющиеся записи о посещениях и ограничивает объем данных датой 10 августа 2022 года.
2. streak_flags: Использует функцию LAG() для получения даты предыдущего посещения для каждого пользователя и помечает строку как 0 (продолжение серии, если перерыв составляет 1 день) или 1 (начало новой серии для любого другого перерыва).
3. streak_ids: Преобразует флаги в идентификаторы групп серий, используя кумулятивную сумму SUM().
4. streak_lengths: Подсчитывает количество дней в каждой серии.
5. longest_per_user: Сохраняет только самую длинную серию побед каждого пользователя.
6. ranked_lengths: Ранжирует длины различных серий.
7. top_lengths: Находит 3 самых длинных полосы значений.

В заключительном разделе SELECT все данные объединяются: он показывает всех пользователей с тремя самыми успешными сериями и соответствующую им продолжительность серий в днях.

WITH unique_visits AS ( SELECT DISTINCT user_id, date_visited FROM user_streaks WHERE date_visited = sp.p95;

Выход:

# Заключение

Эти четыре подхода объединяет общая философия: выполнять работу в базе данных, по возможности за один проход, используя всю выразительную мощь спецификации окна SQL.

По-настоящему мощными оконные функции являются не какие-либо отдельные функции в отрыве от контекста. Их преимущество в возможности компоновки: можно объединять CTE (контекстные выражения), применять несколько оконных функций в одном операторе SELECT и создавать сложную аналитическую логику, которая практически полностью описывается самой бизнес-задачей.

Нейт Розиди — специалист по анализу данных и продуктовой стратегии. Он также является адъюнкт-профессором, преподающим аналитику, и основателем StrataScratch, платформы, помогающей специалистам по анализу данных готовиться к собеседованиям с помощью реальных вопросов от ведущих компаний. Нейт пишет о последних тенденциях на рынке труда, дает советы по прохождению собеседований, делится проектами по анализу данных и освещает все аспекты SQL.

Источник: www.kdnuggets.com