Практические приемы работы с SQL, которые должен знать каждый специалист по анализу данных.

В этой статье мы рассмотрим основные шаблоны и рабочие процессы SQL, которые делают повседневный анализ данных более чистым, быстрым и масштабируемым.

# Введение

Для базовой агрегации достаточно сосредоточиться только на операторах SELECT, WHERE и GROUP BY, но многие реальные аналитические задачи требуют применения шаблонов, выходящих за рамки простых запросов. Примерами могут служить выявление последовательных серий активности, сегментация клиентов по уровню расходов, сглаживание зашумленных данных временных рядов или отслеживание путей обновления тарифных планов по строкам.

В этой статье рассматриваются 7 практических шаблонов SQL, выходящих за рамки основ, с акцентом на методах, решающих реальные аналитические задачи.

# Настройка набора данных

Мы воспользуемся примером таблицы транзакций клиентов из вымышленной компании, предоставляющей программное обеспечение как услугу (SaaS) по подписке:

CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, customer_id INT, plan_type VARCHAR(20), — 'starter', 'pro', 'enterprise' amount NUMERIC(10,2), status VARCHAR(20), — 'completed', 'refunded', 'failed' created_at TIMESTAMP );

Полный набор данных, включающий 36 транзакций от 7 клиентов за период с сентября 2023 года по июнь 2024 года, доступен в файле seed.sql. Запустите его, прежде чем переходить к запросам.

# 1. Измерение времени между событиями с помощью LAG()

Функции LAG() и LEAD() позволяют получить доступ к значению предыдущей или следующей строки без самосоединения. Они особенно полезны для расчета промежутков между событиями, такими как частота продления подписки, сигналы оттока клиентов и задержки повторного вовлечения.

Задание : Рассчитайте, сколько дней прошло между последовательными завершенными транзакциями каждого клиента.

SELECT customer_id, created_at, LAG(created_at) OVER ( PARTITION BY customer_id ORDER BY created_at ) AS previous_transaction_at, ROUND( EXTRACT(EPOCH FROM ( created_at — LAG(created_at) OVER ( PARTITION BY customer_id ORDER BY created_at ) )) / 86400 ) AS days_since_last FROM transactions WHERE status = 'completed' ORDER BY customer_id, created_at;

Вывод (сокращенный):

customer_id | created_at | previous_transaction_at | days_since_last ————-+———————+————————-+—————— 3317 | 2024-01-03 11:02:00 | | 3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00 | 72 3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00 | 68 4482 | 2023-09-10 09:00:00 | | 4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00 | 30 4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00 | 31 4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00 | 54 4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00 | 60 4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00 | 31 4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00 | 28 … 7891 | 2024-02-01 09:00:00 | | 7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00 | 60 7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00 | 44 8810 | 2024-01-05 12:00:00 | | 8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00 | 31 8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00 | 60 (29 строк)

В первой строке для каждого клиента оба столбца всегда содержат NULL — нет предыдущего события, на которое можно было бы сослаться. Функция EXTRACT(EPOCH …) преобразует интервал временной метки в секунды; деление на 86400 дает количество дней.

Функция LEAD() работает аналогично, но анализирует данные за прошедшее время, а не за прошедшее, что делает ее полезной для расчета времени до следующего продления подписки или для выявления последней транзакции перед оттоком клиентов.

# 2. Сравнение строки с другими строками в той же таблице с помощью самосоединения.

Самосоединение связывает строки в одной и той же таблице друг с другом. Это подходящий инструмент, когда необходимо сравнить два события для одной и той же сущности во времени — обновления, понижения уровня, повторные активации или любые другие события типа «до/после».

Задача : Найти клиентов, которые в какой-либо момент перешли с тарифного плана Starter на Pro (или с Pro на Enterprise).

SELECT DISTINCT t1.customer_id FROM transactions t1 JOIN transactions t2 ON t1.customer_id = t2.customer_id AND t1.plan_type = 'starter' AND t2.plan_type = 'pro' AND t2.created_at > t1.created_at WHERE t1.status = 'completed' AND t2.status = 'completed' ORDER BY t1.customer_id;

Выход:

customer_id ————- 4482 6204 7891 (3 строки)

В таблице есть два псевдонима (t1, t2), поэтому каждый псевдоним может представлять разный момент времени для одного и того же клиента. Условие t2.created_at > t1.created_at обеспечивает временной порядок — без него вы бы сопоставляли клиентов, у которых были оба типа тарифных планов в любом порядке, включая неправильный. Условие DISTINCT объединяет случаи, когда у клиента было несколько стартовых транзакций до обновления, что в противном случае привело бы к появлению дублирующихся строк.

Эта же структура подходит для обнаружения понижения уровня обслуживания, поиска клиентов, которые ушли, а затем вернулись, или для сравнения любых двух состояний, которые необходимо упорядочить по времени.

# 3. Выбор верхней строки в каждой группе с помощью функции ROW_NUMBER()

Когда вам нужны N строк с наибольшим количеством транзакций по каждой категории — самая крупная транзакция на клиента, самое последнее событие на счету, первая покупка на когорту — использование функции ROW_NUMBER() внутри общего табличного выражения (CTE) является стандартным подходом.

Задача : Определить наивысшую сумму единовременной совершенной транзакции каждого клиента.

WITH ranked AS ( SELECT customer_id, transaction_id, amount, plan_type, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY amount DESC, created_at DESC ) AS rn FROM transactions WHERE status = 'completed' ) SELECT customer_id, transaction_id, amount, plan_type FROM ranked WHERE rn = 1 ORDER BY customer_id;

Выход:

customer_id | transaction_id | amount | plan_type ————-+—————-+———+———— 3317 | 12 | 19.00 | starter 4482 | 8 | 299.00 | enterprise 5901 | 19 | 299.00 | enterprise 6103 | 25 | 299.00 | enterprise 6204 | 28 | 79.00 | pro 7891 | 32 | 79.00 | pro 8810 | 36 | 79.00 | pro (7 строк)

Функция ROW_NUMBER() присваивает значение 1 строке, которая отсортирована первой в каждом разделе. Затем внешний запрос фильтрует данные, оставляя только эти строки. Вторичная сортировка по полю created_at DESC выступает в качестве критерия разрешения конфликтов; если у двух транзакций одинаковая сумма, побеждает более поздняя транзакция.

Если вы хотите, чтобы равные значения учитывались, а не разрешались, замените ROW_NUMBER() на RANK(). RANK() присваивает одинаковый номер равным строкам и пропускает следующий ранг (1, 1, 3), в то время как DENSE_RANK() делает то же самое, но без пропуска (1, 1, 2).

# 4. Сегментация клиентов по объему расходов с помощью NTILE(n)

Функция NTILE(n) делит упорядоченные строки на n приблизительно равных групп и присваивает каждой строке номер группы. Это подходящий инструмент для ранжирования клиентов, определения квартилей расходов или построения когорт для A/B-анализа без жесткого задания пороговых значений.

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

WITH customer_spend AS ( SELECT customer_id, SUM(amount) AS total_spend, COUNT(*) AS total_transactions FROM transactions WHERE status = 'completed' GROUP BY customer_id ) SELECT customer_id, total_spend, total_transactions, NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile FROM customer_spend ORDER BY total_spend DESC;

Выход:

customer_id | total_spend | total_transactions | spend_quartile ————-+————-+———————+—————- 5901 | 1495.00 | 5 | 4 6103 | 835.00 | 5 | 3 4482 | 653.00 | 7 | 3 8810 | 237.00 | 3 | 2 6204 | 177.00 | 3 | 2 7891 | 177.00 | 3 | 1 3317 | 57.00 | 3 | 1 (7 строк)

Четвертый квартиль — это клиенты с наибольшими расходами; первый квартиль — с наименьшими. Функция NTILE() не задает жестко пороговые значения расходов, поэтому группы автоматически перенастраиваются по мере добавления новых клиентов. Это делает ее более надежной, чем статические пороговые значения, такие как CASE WHEN total_spend > 500.

# 5. Сглаживание зашумленных данных с помощью скользящего окна

Скользящее среднее сглаживает колебания от месяца к месяцу, что значительно упрощает чтение трендов во временных рядах. Оконные функции с явным ограничением ROWS BETWEEN позволяют точно контролировать количество периодов, которые следует включить.

Задача : Рассчитать скользящее среднее за 3 месяца по ежемесячной выручке, чтобы сгладить шумы.

WITH monthly AS ( SELECT DATE_TRUNC('month', created_at)::DATE AS month, SUM(amount) AS monthly_revenue FROM transactions WHERE status = 'completed' GROUP BY DATE_TRUNC('month', created_at) ) SELECT month, monthly_revenue, ROUND(AVG(monthly_revenue) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2) AS revenue_3mo_avg FROM monthly ORDER BY month;

Выход:

месяц | ежемесячный_доход | средний_доход_за_3месяца ————-+——————+—————— 2023-09-01 | 19.00 | 19.00 2023-10-01 | 19.00 | 19.00 2023-11-01 | 79.00 | 39.00 2024-01-01 | 275.00 | 124.33 2024-02-01 | 476.00 | 276.67 2024-03-01 | 555.00 | 435.33 2024-04-01 | 835.00 | 622.00 2024-05-01 | 775.00 | 721.67 2024-06-01 | 598.00 | 736.00 (9 рядов)

Параметр ROWS BETWEEN 2 PRECEDING AND CURRENT ROW указывает оконной функции просматривать текущую строку и две строки перед ней. Первые две строки используют меньше входных данных, поскольку у них нет истории предыдущих событий, поэтому они выступают в качестве средних значений за 1 и 2 месяца соответственно.

Замените ROWS на RANGE, если хотите включить все строки с одинаковым значением ORDER BY (это полезно, когда несколько строк имеют одинаковую метку времени). Для более длительного сглаживания измените 2 PRECEDING на 5 PRECEDING, чтобы получить окно в 6 месяцев.

# 6. Условная агрегация с помощью FILTER

Функция FILTER позволяет применять условие WHERE к определенному агрегату без разделения запроса на несколько подзапросов. В результате получается несколько условных агрегаций за один проход по данным.

Задача : Составить разбивку по месяцам общей выручки, возвратам и количеству неудачных транзакций — все данные в одной строке за каждый месяц.

SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed, SUM(amount) FILTER (WHERE status = 'refunded') AS revenue_refunded, COUNT(*) FILTER (WHERE status = 'failed') AS failed_count FROM transactions GROUP BY DATE_TRUNC('month', created_at) ORDER BY month;

Выход:

месяц | выручка_завершена | выручка_возвращена | количество_неудачных_завершений ————————+——————-+——————+————— 2023-09-01 00:00:00+00 | 19.00 | | 0 2023-10-01 00:00:00+00 | 19.00 | | 0 2023-11-01 00:00:00+00 | 79.00 | | 0 2024-01-01 00:00:00+00 | 275.00 | | 0 2024-02-01 00:00:00+00 | 476.00 | 79.00 | 1 2024-03-01 00:00:00+00 | 555.00 | 79.00 | 0 2024-04-01 00:00:00+00 | 835.00 | 299.00 | 0 2024-05-01 00:00:00+00 | 775.00 | | 1 2024-06-01 00:00:00+00 | 598.00 | | 2 (9 строк)

Альтернативой FILTER являются три отдельных подзапроса, объединенных вместе — больше кода, сложнее для чтения и часто медленнее. Обратите внимание, что SUM с FILTER возвращает NULL (а не ноль), если в данном месяце нет подходящих строк, что соответствует действительности: в эти месяцы действительно не было возвратов средств. Если вам нужны нули, используйте COALESCE(…, 0).

Функция FILTER является стандартной SQL-функцией и работает в PostgreSQL и BigQuery. В Snowflake и некоторых других системах вместо неё используйте SUM(CASE WHEN status = 'completed' THEN amount END).

#7. Выявление последовательных серий активности с помощью оконных функций

Поиск непрерывных последовательностей — активных месяцев без перерывов, последовательных дней с транзакциями, серий подписок — является одной из самых сложных задач SQL. Классическое решение использует оконную функцию для группировки строк в серии без рекурсивного CTE.

Метод: каждому активному месяцу присваивается порядковый номер строки в соответствующем разделе данных о клиентах. Если месяцы действительно идут подряд, вычитание этого номера строки из даты месяца дает одно и то же постоянное значение для каждого месяца в серии. Пробел прерывает это постоянное значение.

Задача : Найти последовательные активные месяцы каждого клиента (месяцы, в которых была совершена хотя бы одна транзакция).

WITH monthly_activity AS ( SELECT customer_id, DATE_TRUNC('month', created_at)::DATE AS active_month FROM transactions WHERE status = 'completed' GROUP BY customer_id, DATE_TRUNC('month', created_at) ), with_prev AS ( SELECT customer_id, active_month, LAG(active_month) OVER ( PARTITION BY customer_id ORDER BY active_month ) AS prev_month FROM monthly_activity ), streak_groups AS ( SELECT customer_id, active_month, SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END) OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id FROM with_prev ), streaks AS ( SELECT customer_id, streak_id, MIN(active_month) AS streak_start, MAX(active_month) AS streak_end, COUNT(*) AS streak_length_months FROM streak_groups GROUP BY customer_id, streak_id ) SELECT customer_id, streak_start, streak_end, streak_length_months FROM streaks ORDER BY customer_id, streak_start;

Выход:

customer_id | streak_start | streak_end | streak_length_months ————-+—————+————+———————- 3317 | 2024-01-01 | 2024-01-01 | 1 3317 | 2024-03-01 | 2024-03-01 | 1 3317 | 2024-05-01 | 2024-05-01 | 1 4482 | 2023-09-01 | 2023-11-01 | 3 4482 | 2024-01-01 | 2024-01-01 | 1 4482 | 2024-03-01 | 2024-05-01 | 3 5901 | 2024-02-01 | 2024-06-01 | 5 6103 | 2024-01-01 | 2024-04-01 | 4 6103 | 2024-06-01 | 2024-06-01 | 1 6204 | 2024-01-01 | 2024-01-01 | 1 6204 | 2024-03-01 | 2024-03-01 | 1 6204 | 2024-05-01 | 2024-05-01 | 1 7891 | 2024-02-01 | 2024-02-01 | 1 7891 | 2024-04-01 | 2024-05-01 | 2 8810 | 2024-01-01 | 2024-02-01 | 2 8810 | 2024-04-01 | 2024-04-01 | 1 (16 строк)

# Краткий справочник

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

Как только вы освоите их, многие многоэтапные преобразования данных, которые часто обрабатываются в Python, можно будет выразить более чисто и эффективно в одном SQL-запросе.

Бала Прия С. — разработчик и технический писатель из Индии. Ей нравится работать на стыке математики, программирования, анализа данных и создания контента. В сферу её интересов и компетенции входят DevOps, анализ данных и обработка естественного языка. Она любит читать, писать, программировать и пить кофе! В настоящее время она работает над изучением и распространением своих знаний среди сообщества разработчиков, создавая учебные пособия, руководства, аналитические статьи и многое другое. Бала также создает увлекательные обзоры ресурсов и обучающие материалы по программированию.

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