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

[kzzzr]

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

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

[Akina]

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

Очень странно было увидеть недетерминированный запрос в решении. Где сортировка-то?

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

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

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

А хэш-то зачем? чтобы сервер не скучал? Простой конкатенации вполне достаточно, тем более что всё использование результата — это сравнить с LAG() на точное совпадение.

Кстати, а как решение справится с делением на сессии, если работать от одной учётной записи в двух разных окнах браузера, параллельно?

[kzzzr]

Очень странно было увидеть недетерминированный запрос в решении. Где сортировка-то?

Для моей задачи сортировка неважна. После формирования колонки применяется фильтр 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]

То, что не вошло в этот выпуск, но тоже часто используется:

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

[Akina]

Вы анонсировали ТРИ разные СУБД (а по тексту ещё и на четвёртую ссылаетесь). Соответственно вопрос — где описание/пример (или хотя бы упоминание) 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 умеет…