В ответственность работы маркетинга входит детальный анализ данных целевой аудитории и собственного клиентского списка.
В наше время выполнять эти задачи при помощи работы с современными инструментами аналитики становиться все проще. Но даже сейчас без знаний SQL иногда приходит сложно.
Давайте вместе разберем пример, как нам присвоить ярлык первой или повторной покупки каждой транзакции пользователей в нашей базе данных.
Предположим, что у вас есть следующие данные в СУБД:
- Дата
- Идентификатор пользователя
- Индектификатор транзакции
Рис. Исходная таблица.
К данной таблице нам нужно добавить столбец, который будет содержать напротив каждой транзакции информацию о том первичная она или вторичная.
Задача кажется очень простой, но вся загвоздка в том, что в нашем датасете нет времени покупки, есть только дата. Поэтому правильным решением будет использование следующей конструкции, содержащей оконную функцию:
SELECT date , user_id , transaction_id , CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first' ELSE 'replay' END AS transaction_Type FROM dataupload-230410.Clients_Data.alisa_test ORDER BY user_id , date
В результате мы получим следующую результирующую таблицу:
Вся хитрость запроса заключается в том, что мы проверяем не только минимальную дату, но еще и минимальное значение транзакции
OVER (PARTITION BY user_id ORDER BY date, transaction_id)
для присвоения ей порядкового номера при помощи
WHEN ROW_NUMBER()
и в том случае, если номер такой строки равен 1
WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1
, то присваиваем ему признак ‘first’, в ином случае ELSE ‘replay’ и выводим результат в столбце END AS transaction_Type
Здорово, но пока это еще не похоже на какое-то аналитическое решение. А что делать, если нас попросят вывести таблицу, где в первом столбце будет дата, а в оставшихся двух название типа транзакции (ярлыка) со значение количества таких событий на указанную дату?
В этом случае нам нужно воспользоваться конструкцией вида:
WITH result AS (
SELECT
date
, user_id
, transaction_id
, CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first'
ELSE 'replay' END AS transaction_Type
FROM ВАША ТАБЛИЦА
ORDER BY
user_id
, date
)
SELECT * FROM
(SELECT date, transaction_Type FROM result)
PIVOT (COUNT(*) FOR transaction_Type IN ('first', 'replay'))
ORDER BY date
В результате мы увидим следующее:
Согласитесь, теперь мы можем построить красивый график, который покажет нам, сколько новых и повторных продаж у нас приходится на ту или иную дату.
Но как все это работает?
Для начала нужно рассказать про оператор WITH, который создает временный результирующий набор данных, над которым мы уже выполняем другие операции. В нашем случае мы даем этому набору название result.
Result представляет собой таблицу, которую мы получали на прошлом шаге, за счет запроса
( SELECT date , user_id , transaction_id , CASE WHEN ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date, transaction_id) = 1 THEN 'first' ELSE 'replay' END AS transaction_Type FROM ВАША ТАБЛИЦА ORDER BY user_id , date )
Далее следует запрос, который мы выполняем над временной таблицей.
SELECT * FROM
(SELECT date, transaction_Type FROM result)
PIVOT (COUNT(*) FOR transaction_Type IN ('first', 'replay'))
ORDER BY date
В рамках этого запроса используем PIVOT для построения сводной таблицы на основе содержимого столбца transaction_Type.
В результате получаем искомое решение нашей задачи.
