SQL для аналитики — рейтинг прикладных задач с решениями / forpes.ru

Главная
SQL для аналитики — рейтинг прикладных задач с решениями

SQL для аналитики — рейтинг прикладных задач с решениями +19

11.02.2021 08:24

kzzzr 5 6600 Источник

Привет, Хабр! У кого из вас black belt на sql-ex.ru, признавайтесь? На заре своей карьеры я немало времени провел на этом сайте, практикуясь и оттачивая навыки. Должен отметить, что это было увлекательное и вознаграждающее путешествие. Пришло время воздать должное.

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

SQL is intergalactic data speak. SQL - это межгалактический язык данных
- Michael Stonebraker

Моя цель - показать походы и самые распространенные проблемы на понятных и доступных примерах. Конечно, СУБД, на которой решается задача имеет значение. Поддержка функций и синтаксиса варьируется. В SQL Fiddle я задействовал PostgreSQL, Oracle, SQL Server. Для решения серьезных аналитических задач сегодня я чаще всего использую специальные СУБД, такие как Redshift, Vertica, BigQuery, Clickhouse, Snowflake.

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

Конкатенация значений из нескольких строк в одну через разделитель

Когда это может быть полезно? К примеру, если исходный набор данных хранит каждый тег, присвоенный сделке, в отдельной строке (это же получится при соединении таблиц лидов и тегов), и есть необходимость собрать все теги, обойдясь при этом без дублирования строк по каждой сделке.

Формулировка задачи: Для каждого лида вывести список тегов, разделенных запятой в одном столбце

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/f3ace/2/0

Входные данные:

Пример решения:

select
 lead_id
 ,string_agg(tag, ', ') as tags
from leads
group by lead_id
;

Результат:

Аналитические функции при сохранении всех строк выборки

Речь пойдет о так называемых analytic functions, которые оперируют над партициями данных (окна, windows), возвращая результат для каждой строки. В отличие от aggregate functions, “схлопывающих” строки, оконные функции оставляют все строки выборки.

Окно определяется спецификацией (выражение OVER) и основывается на трех основных концепциях:

Разбиение строк на группы (выражение PARTITION BY)
Порядок сортировки строк в каждой группе (выражение ORDER BY)
Рамки, которые определяют ограничения по количеству строк относительно каждой строки (выражение ROWS)

Таких функций существует немало, от аналитических: всем известные SUM, AVG, COUNT, менее известные LAG, LEAD, CUMEDIST, и до ранжирующих: RANK, ROWNUMBER, NTILE. Я же приведу несколько простых примеров часто встречающихся запросов:

Ко всем транзакциям пользователя вывести дату первой покупки
К каждой транзакции добавить дату предыдущей транзакции пользователя
Показать сумму покупок пользователя нарастающим итогом
Присвоить всем транзакциям пользователя / продавца / отделения порядковый номер

SQL Fiddle: http://sqlfiddle.com/#!17/ee00f/13

Входные данные:

Пример решения:

select
  salesid
 ,dateid
 ,sellerid
 ,buyerid
 ,qty
 ,first_value(dateid) over (partition by buyerid order by dateid) as first_purchase_dt
 ,lag(dateid) over (partition by buyerid order by dateid) as previous_purchase_dt
 ,sum(qty) over (partition by buyerid order by dateid rows between unbounded preceding and current row) as moving_qty
 ,row_number() over (partition by buyerid order by dateid) as order_number
from winsales
;

Результат:

Работа с NULL и применение логики ветвления IF-THEN-ELSE в SQL

Про COALESCE / NVL знают все, и нет смысла останавливаться на них подробно. Зато с NVL2 и NULLIF знакомы уже не так много людей.

NULLIF сравнивает два значения и возвращает NULL, если аргументы равны. По сути эта функция - обратна к NVL / COALESCE. Формулировка задачи:

Как обработать ошибку деления на 0 (divide by zero error)
Как выводить NULL вместо пустых строк (‘’)

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/bf56e/2

Входные данные:

Пример решения:

select
 lead_id
 ,nullif(tag, '') as tag
from leads
;

Результат:

NVL2 в свою очередь вернет одно из значений, в зависимости от того, является ли входной аргумент NULL или NOT NULL. Например, если в таблице транзакций есть ссылка на invoiceid, значит транзакция в сегменте B2B, и ее следует пометить соответствующим образом.

SQL Fiddle (Oracle 11g R2): http://sqlfiddle.com/#!4/4cac9/11

Входные данные:

Пример решения:

select
 "transaction_id"
 ,"ts"
 ,"invoice_id"
 ,nvl2("invoice_id", 1, 0) as "is_b2b"
from transactions
;

Результат:

Но больше всего мне нравится функция DECODE. Она в буквальном смысле позволяет расшифровать значения согласно заданной вами логике:

DECODE ( expression, search, result [, search, result ]… [ ,default ] ).

Формулировка задачи: Присвоить численному коду (или, например, битовой маске) текстовые наименования.

SQL Fiddle (Oracle 11g R2): http://sqlfiddle.com/#!4/60341/1

Входные данные:

Пример решения:

select
 "transaction_id"
 ,decode("status", 0, 'charge', 1, 'authorize', 2, 'settle', 'void') as "status"
from transactions
;

Результат:

Опережая вопрос, конечно, эту же логику можно выразить через всем известное выражение CASE. Задача показать что-то интересное, и чем меньше кода - тем красивее, на мой взгляд.

Дедупликация данных

Это классика. Задачу часто спрашивают на собеседованиях в формулировке “как удалить дубли / копии строк”, и решить ее можно несколькими способами. Я привык мыслить в терминах историзации данных в Хранилище, и удаление мне ни к чему, поэтому для решения задачи я воспользуюсь ранжирующей функцией ROWNUMBER().

Формулировка задачи: Выбрать самую актуальную запись с учетом статуса (успешная / отмененная транзакция) и временнОй метки

SQL Fiddle (Oracle 11g R2): http://sqlfiddle.com/#!4/ad305/1

Входные данные:

Пример решения:

with decoded as (
   select
   "transaction_id"
   ,"is_successful"
   ,"ts"
   ,decode("is_successful", 'true', 0, 'false', 1, 2) as "order_is_successful"
   from transactions
),
ordered as (
   select
   "transaction_id"
   ,"is_successful"
   ,"ts"
   ,row_number() over(partition by "transaction_id" order by "order_is_successful" asc, "ts" desc) as rn
   from decoded
)
select
 "transaction_id"
 ,"is_successful"
 ,"ts"
from ordered
where rn = 1
;

Результат:

Некоторые СУБД, например, Teradata позволяют сделать запрос короче при помощи выражения QUALIFY:

select *
from students_db.exam_results
qualify row_number() over (partition by subject order by marks desc) = 1
;

Анализ временных рядов

Просто не могу обойти это стороной. ВременнАя шкала - это, безусловно, одно из наиболее часто используемых измерений. Отчетность зачастую строится вокруг измерения метрик и их динамики относительно периодов: неделя, месяц, время суток и т.д.

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

Получение текущей даты (+ время) - CURRENTDATE, CURRENTTIMESTAMP
Разница между событием и текущим временем - DATEDIFF
Подсчет времени истечения срока действия события - DATEADD
Дата начала недели, в которой произошло событие - DATETRUNC
Конвертация Unix Timestamp (epoch) в человекочитаемый формат

SQL Fiddle (MS SQL Server 2017): http://sqlfiddle.com/#!18/618cf/6

Входные данные:

Пример решения:

select
 ts
 ,_metadata_ts_epoch
 ,convert(date, getdate()) as current_dt
 ,current_timestamp as current_ts
 ,datediff(minute, ts, getdate()) as minutes_since_ts
 ,dateadd(hour, 36, ts) as ts_expiration_ts 
 ,dateadd(week, datediff(week, 0, ts), 0) as ts_week
 ,dateadd(S, (_metadata_ts_epoch / 1000), '1970-01-01') as _metadata_ts
from transactions
;

Результат:

Анализ истории со Slowly Changing Dimensions (SCD)

В основе Хранилища Данных лежит принцип историзации. Иначе говоря - это возможность получить состояние той или иной сущности на определенный момент времени, а также проследить цепочку событий и изменений атрибутов и показателей. Существует несколько способов организации хранения истории. Один из наиболее популярных подходов - запись новой строки на любое изменение атрибутного состава, с указанием даты начала и окончания действия каждой строки. Есть несколько задач, с которыми вы с большой долей вероятности можете встретиться.

Формулировка задачи: Какой статус был у клиентов на 3-й день месяца?

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/743e9/6

Входные данные:

Пример решения:

select
  client_id
  ,status
from clients
where '2021-02-03' >= valid_from and '2021-02-03' < coalesce(valid_to, '2100-01-01')
;

Результат:

Формулировка задачи: Как в течение недели росло количество активных клиентов?

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/743e9/11

Пример решения:

select
   c.dt
  ,h.status
  ,count(distinct h.client_id)
from calendar c
  left join clients h
      on c.dt >= valid_from and c.dt < coalesce(valid_to, '2100-01-01')::date
where true
and c.dt between '2021-02-01' and '2021-02-07'
and h.status in ('active')
group by
   c.dt
  ,h.status
order by 1, 3 desc
;

Результат:

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

Использование выражения CASE в агрегирующих функциях

Агрегирующие функции могут принимать в качестве аргумента результат оценки выражения CASE. Таким образом можно к агрегируемым строкам применить псевдофильтр. Это напоминает мне использование формулы СУММЕСЛИ из старого доброго Excel, только для реляционных баз данных. Смотрите сами:

Подсчитать все лиды и выручку
Подсчитать количество лидов со статусом success
Подсчитать выручку лидов с тегом python

SQL Fiddle (MS SQL Server 2017): http://sqlfiddle.com/#!18/dc01d5/4

Входные данные:

Пример решения:

select
 dt
 ,count(1) as leads_total
 ,sum(case status when 'success' then 1 else 0 end) as leads_success
 ,sum(case when tags like '%python%' then 1 else 0 end) as leads_python
 ,sum(amount) as amount_total
 ,sum(case status when 'success' then amount else 0 end) as amount_success
from leads
group by dt
order by dt
;

Результат:

Парсинг колонки с разделением на отдельные атрибуты

Чаще всего так поступают в условиях внешних ограничений, когда иного выхода нет. Например, при ограниченном наборе полей в CRM системе. Или при передаче нескольких UTM-меток в одной строковой переменной. Еще так могут делать люди, которые не слышали про нормализацию данных.

Формулировка задачи: Выделить закодированные в названии кампании атрибуты в отдельные колонки: сеть, регион, категория, температура, бренд.

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/205e7b/6

Входные данные:

Пример решения:

select
 campaign
 ,split_part(campaign, '-', 1) as network
 ,split_part(campaign, '-', 2) as region
 ,split_part(campaign, '-', 3) as category 
 ,nullif(split_part(campaign, '-', 4), 'None') as temperature 
 ,split_part(campaign, '-', 5) as brand 
from campagins
;

Результат:

Чуть более сложная ситуация с парсингом UTM-меток, а именно UTMContent, которая по сути является контейнером для произвольного набора атрибутов, разделенных любым символом. Поэтому стоит быть последовательным и аккуратным при формировании таких меток, хотя зачастую инженер вынужден работать с тем, что есть.

Формулировка задачи: Разбить строку UTMContent на отдельные атрибуты cid, gid, aid, kwd с соблюдением соответствия ключ-значение. Каждое значение предваряется наименованием ключа, все значения разделены вертикальной строкой (|).

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/4f65e/4

Входные данные:

Пример решения:

select
 substring("UTMContent" from '%cid_#"%#"_gid%' FOR '#' ) AS cid
 ,substring("UTMContent" from '%gid_#"%#"_aid%' FOR '#' ) AS gid
 ,substring("UTMContent" from '%aid_#"%#"_dvc%' FOR '#' ) AS aid
 ,substring("UTMContent" from '%kwd_#"%#"_pos%' FOR '#' ) AS kwd
from utm
;

Результат:

FULL JOIN для соединений без потери строк

Уверен, что все знают про FULL JOIN, но кто хоть иногда использует этот тип соединения? Это незаменимый подход в ситуациях, когда я хочу сохранить все исходные строки с каждой стороны джоина. Иначе говоря, недопустимо терять факты трат денежных средств, даже если для них не нашлось соответствующих лидов в таблицах CRM.

А теперь представьте ситуацию, когда таблиц больше двух. Это может быть веб-аналитика, выгрузки из рекламных кабинетов, CRM. В этом случае я дополнительно формирую мета-колонки isrowmatched (нашлось ли совпадение - да / нет) и roworigin (источник данных для конкретной строки).

Формулировка задачи: Подготовить витрину-трекер для сквозной аналитики лидов из CRM и трат из Рекламных Кабинетов (Яндекс.Директ, Google Adwords, Facebook).

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/227eaf/1

Входные данные:

Пример решения:

select
 coalesce(c.hash_key, l.hash_key) as hash_key
 ,coalesce(c.dt, l.dt) as dt
 ,coalesce(c.campaign_id, l.campaign_id) as campaign_id
 -- costs
 ,coalesce(c.platform, null) as platform
 ,coalesce(c.clicks, 0) as clicks
 ,coalesce(c.costs, 0) as costs
 -- leads
 ,coalesce(l.leads, 0) as leads
 ,coalesce(l.amount, 0) as amount
 -- meta
 ,case
     when c.dt is not null then c.platform
     when l.dt is not null then 'crm'
   end as meta_row_origin
 ,case
     when c.hash_key = l.hash_key then 1
     else 0
   end as meta_is_row_match
from costs as c
   full join leads as l on l.hash_key = c.hash_key
;

Результат:

Пример упрощен и умозрителен. Однако этой задаче я посвятил одну из своих предыдущих публикаций: Сквозная Аналитика на Azure SQL + dbt + Github Actions + Metabase и недавнее выступление на вебинаре: Путь Инженера Аналитики: Решение для Маркетинга. Тема заслуживает отдельного внимания.

Разбиение пользовательских событий на сессии

Сессионизация - весьма интересная и сложная задача, сочетающая в себе сразу комплекс инженерных и аналитических решений. С ростом популярности и востребованности всевозможных трекеров, таких как Google Analytics, Snowplow, Amplutide кратно возрастает спрос на решение подобного рода задач.

Для чего это можно использовать? Прежде всего, для того, чтобы перейти от анализа хитов (кликов) к полноценному анализу пользовательского взаимодействия и поведения. Во-вторых, улучшение UX и качества сервисов, проведение A/B тестирования. Наконец, поиск паттернов, определенных сегментов пользователей, в том числе fraud monitoring (защита от мошенничества и ботов).

Чуть подробнее про дефиницию сессии от Google Analytics: How a web session is defined in Universal Analytics. Резюмируя, сессия - это набор пользовательских действий в рамках заданного промежутка времени. Сессия завершается при следующих событиях:

30 минут бездействия
Начало новых суток
Смена источника трафика (возврат на сайт по клику на новый рекламный баннер)

Базовая задача сессионизации сводится к следующему: превратить последовательность кликов из лога веб-сервера в набор сессий.

SQL Fiddle (PostgreSQL 9.6): http://sqlfiddle.com/#!17/17271/3

Попробуем декомпозировать и решить задачу по частям:

Шаг 1. Для каждого пользователя берем идентификатор просмотра, время просмотра, источник трафика (хеш-сумма). Хеш-сумма берется от текстовой конкатенации атрибутов источника трафика: utm_source + utm_medium + utm_campaign. При этом обрабатываются null-значения в любом из столбцов (заменяются на литерал 'null'). По хеш-сумме легко проверить смену источника трафика.

Шаг 1

 select
   user_id
   ,hit_id
   ,ts
   ,md5(concat(coalesce(utm_source, 'null'), coalesce(utm_medium, 'null'), coalesce(utm_campaign, 'null'))) as utm_hash
 from hits_raw

Шаг 2. Для каждого хита выводим предыдущий хит и соответствующее ему время. Окно - по пользователю, сортировка по времени хита:

Шаг 2

select
   user_id
   ,hit_id
   ,ts
   ,lag(ts, 1) over (partition by user_id order by ts) as lag_ts
   ,utm_hash
   ,lag(utm_hash, 1) over (partition by user_id order by ts) as lag_utm_hash
 from hits

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

Шаг 3

select
   user_id
   ,hit_id
   ,ts
   ,lag_ts
   ,case
     when utm_hash <> lag_utm_hash then 1
     when date_part('day', ts - lag_ts) <> 0 then 1
     when date_part('hour', ts - lag_ts) * 60 +
             date_part('minute', ts - lag_ts) > 30 then 1
     else 0
    end as is_new_session 
--    ,date_part('day', ts - lag_ts) as days_diff
--    ,date_part('hour', ts - lag_ts) * 60 +
--              date_part('minute', ts - lag_ts) as minutes_diff
   ,utm_hash
   ,lag_utm_hash
 from lags

Шаг 4. Присваиваем каждой сессии уникальный идентификатор. Для этого сначала необходимо пронумеровать сессии одного пользователя монотонно возрастающими числами. Затем построить уникальный суррогатный ключ сессии: к номеру сессии добавить идентификатор пользователя, взять хеш-сумму:

Шаг 4

select
   user_id
   ,hit_id
   ,ts
   ,is_new_session
   ,sum(is_new_session) over (partition by user_id order by ts rows between unbounded preceding and current row) as session_index
   ,md5(concat(user_id, sum(is_new_session) over (partition by user_id order by ts rows between unbounded preceding and current row))) as session_id
 from new_sessions

Результат:

В реальном мире всё сложнее

Помимо логики, выраженной в SQL, не меньшее значение имеет ряд других факторов:

СУБД, с которой вы работаете: то, какие функции и возможности она поддерживает, формат хранения данных: в виде колонок или строк
Фактически используемый план выполнения запроса: алгоритмы соединения таблиц, локальность операций, наличие статистических данных у оптимизатора
Используемые физические и логические модели данных: индексы, материализованные представления, кеш, предварительно отсортированные данные

На занятиях курса Data Engineer я и мои коллеги готовим объемлющий и интересный контент, затрагивающий множество тем, связанных с архитектурой аналитических приложений, внутренним устройством систем обработки больших данных и развертыванием ML.

Советую посетить ближайшие открытые вебинары:

ML в Spark, 11 февраля в 20:00
MPP-базы данных, 17 февраля в 20:00

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

Следить за моими публикациями в авторском канале: https://t.me/enthusiastech

Благодарю за внимание.

Комментарии (5)

kzzzr Автор
11.02.2021 16:28
#22670306
+1
Хороший пример RFM-аналитики на Spark SQL + Databricks от Евгения Кудашева: www.youtube.com/watch?v=72mRlugPKNI

RFM (Recency-Frequency-Monetary value) — анализ Customer Lifetime Value, клиентская аналитика и прогнозирование продаж.

Akina
11.02.2021 20:22
#22671272
Формулировка задачи: Для каждого лида вывести список тегов, разделенных запятой в одном столбце
Очень странно было увидеть недетерминированный запрос в решении. Где сортировка-то?

Рамки, которые определяют ограничения по количеству строк относительно каждой строки (выражение ROWS)
Вы анонсировали ТРИ разные СУБД (а по тексту ещё и на четвёртую ссылаетесь). Соответственно вопрос — где описание/пример (или хотя бы упоминание) RANGE clause?

Для каждого пользователя берем идентификатор просмотра, время просмотра, источник трафика (хеш-сумма). Хеш-сумма берется от текстовой конкатенации атрибутов источника трафика: utm_source + utm_medium + utm_campaign.
А хэш-то зачем? чтобы сервер не скучал? Простой конкатенации вполне достаточно, тем более что всё использование результата — это сравнить с LAG() на точное совпадение.

Кстати, а как решение справится с делением на сессии, если работать от одной учётной записи в двух разных окнах браузера, параллельно?
1. kzzzr Автор
  12.02.2021 00:12
  #22671968
  Очень странно было увидеть недетерминированный запрос в решении. Где сортировка-то?
  
  Для моей задачи сортировка неважна. После формирования колонки применяется фильтр LIKE на наличие тега или отсутствие. Но сортировка может иметь значение — это верное замечание. Пример с сортировкой: http://sqlfiddle.com/#!4/16ed58/10
  
  Вы анонсировали ТРИ разные СУБД (а по тексту ещё и на четвёртую ссылаетесь). Соответственно вопрос — где описание/пример (или хотя бы упоминание) RANGE clause?
  
  Например, шаг 4 в задаче сессионизации:
  
  ,sum(is_new_session) over (partition by user_id order by ts rows between unbounded preceding and current row) as session_index
  А хэш-то зачем? чтобы сервер не скучал? Простой конкатенации вполне достаточно, тем более что всё использование результата — это сравнить с LAG() на точное совпадение.
  
  Соглашусь, хеш можно расценить как избыточный шаг здесь, особенно при условии разового использования.
  
  Кстати, а как решение справится с делением на сессии, если работать от одной учётной записи в двух разных окнах браузера, параллельно?
  
  Думаю, кука в браузере будет одна и серия хитов из разных табов браузера будут записана как одна сессия.
  
  Публикация скорее направлена на расширение кругозора и для тех, кто ищет подсказки и подходы к решению.

kzzzr Автор
12.02.2021 12:16
#22673374
То, что не вошло в этот выпуск, но тоже часто используется:
- Подзапросы и коррелированные запросы: EXISTS & IN
- Работа с иерархическими структурами (например, организационная структура)
- User Defined Functions (UDF): Формирование суррогатных ключей, Интересный пример с расшифровкой битовой маски (Bitmask)
- Работа с гео-данными: например, найти ближайшие города к точкам на карте; сгруппировать точки на карте в гексагоны
- Преобразование табличных данных: Pivot + Unpivot

Akina
12.02.2021 20:31
#22675576
Вы анонсировали ТРИ разные СУБД (а по тексту ещё и на четвёртую ссылаетесь). Соответственно вопрос — где описание/пример (или хотя бы упоминание) RANGE clause?
Например, шаг 4 в задаче сессионизации:
```
,sum(is_new_session) over (partition by user_id order by ts rows between unbounded preceding and current row) as session_index
```
Это — ROWS. А вовсе даже не RANGE. Типа
```
(ORDER BY created_at RANGE BETWEEN INTERVAL 1 WEEK PRECEDING AND CURRENT ROW)
```
А ведь Постгресс ещё и GROUPS умеет…

SQL для аналитики — рейтинг прикладных задач с решениями +19

Конкатенация значений из нескольких строк в одну через разделитель

Аналитические функции при сохранении всех строк выборки

Работа с NULL и применение логики ветвления IF-THEN-ELSE в SQL

Дедупликация данных

Анализ временных рядов

Анализ истории со Slowly Changing Dimensions (SCD)

Использование выражения CASE в агрегирующих функциях

Парсинг колонки с разделением на отдельные атрибуты

FULL JOIN для соединений без потери строк

Разбиение пользовательских событий на сессии

В реальном мире всё сложнее

Комментарии (5)

kzzzr Автор

Akina

kzzzr Автор

kzzzr Автор

Akina