Атрибуция транзакции SQL

Мне нужно извлечь общее количество пользователей, совершивших транзакции, на основе различных периодов ретроспективного анализа.

Для начала у меня есть такая таблица:

user_id     date        action
1           2018/01/01  click
3           2018/01/01  view
5           2018/01/01  click
1           2018/01/02  view
1           2018/01/02  view
3           2018/01/03  click
2           2018/01/05  click
1           2018/01/05  transact
3           2018/01/05  transact
2           2018/01/06  view
1           2018/01/06  click
3           2018/01/06  view
2           2018/01/08  transact
2           2018/01/08  click
1           2018/01/08  click
1           2018/01/09  click
4           2018/01/09  click
3           2018/01/12  view
4           2018/01/12  transact
3           2018/01/13  view
5           2018/01/13  transact
4           2018/01/15  view
5           2018/01/15  click
4           2018/01/16  view
2           2018/01/17  transact
5           2018/01/18  click

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

user_id     date        action      prevdate_nontrans       prev_nontrans_action        days_since
1           2018/01/01  click           
3           2018/01/01  view            
5           2018/01/01  click           
1           2018/01/02  view            
1           2018/01/02  view            
3           2018/01/03  click           
2           2018/01/04  click           
1           2018/01/05  transact    2/01/2018               view                        3
3           2018/01/05  transact    3/01/2018               click                       2
2           2018/01/06  view            
1           2018/01/06  click           
3           2018/01/06  view            
2           2018/01/08  transact    5/01/2018               click                       3
2           2018/01/08  click           
1           2018/01/08  click           
1           2018/01/09  click           
4           2018/01/09  click           
3           2018/01/12  view            
4           2018/01/12  transact    9/01/2018               click                       3
3           2018/01/13  view            
5           2018/01/13  transact    1/01/2018               click                       12
4           2018/01/15  view            
5           2018/01/15  click           
4           2018/01/16  view            
2           2018/01/17  transact    8/01/2018               click                       9
5           2018/01/18  click

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

date (weekly)       total   1-day lookback  2-day lookback  3-day lookback
1/01/2018           4       0               1               3
8/01/2018           5       0               0               1
15/01/2018          3       0               0               0

На основе недели, начинающейся 1 января, пользователи с идентификаторами 1,2,3 и 5 выполнили действия, не связанные с транзакциями, в результате чего в общей сложности получилось 4. На основе однодневного ретроспективного анализа транзакций пользователей не обнаружено. На основе 2-дневного ретроспективного анализа был обнаружен идентификатор пользователя 3, в результате чего в таблице был указан 1. На основе 3-дневного ретроспективного анализа были обнаружены идентификаторы пользователя 3,1 и 2, в результате чего в таблице было 3.

Подсчет в столбцах ретроспективного анализа будет основан на том, когда произошло действие, не связанное с транзакцией, что объясняет, почему транзакция пользователя с идентификатором 2 8 января была отнесена к неделе 1 января (3-дневный ретроспективный анализ).

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


sql amazon-athena presto
person lostinsql    schedule 07.08.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Я использовал запрос ниже для достижения вашего результата, но тип данных столбца даты должен быть DATETIME, а столбец действия должен быть INT со ссылкой на action_id (таблица действий), чтобы повысить производительность.

CREATE TABLE user_action(user_id BIGINT, Date DATE, action VARCHAR(100)); 

INSERT INTO user_action(user_id, Date, action)
VALUES
(1,'2018/01/01', 'click'),
(3,'2018/01/01',  'view'),
(5,'2018/01/01',  'click'),
(1,'2018/01/02',  'view'),
(1,'2018/01/02',  'view'),
(3,'2018/01/03',  'click'),
(2,'2018/01/05',  'click'),
(1,'2018/01/05',  'transact'),
(3,'2018/01/05',  'transact'),
(2,'2018/01/06',  'view'),
(1,'2018/01/06',  'click'),
(3,'2018/01/06',  'view'),
(2,'2018/01/08',  'transact'),
(2,'2018/01/08',  'click'),
(1,'2018/01/08',  'click'),
(1,'2018/01/09',  'click'),
(4,'2018/01/09',  'click'),
(3,'2018/01/12',  'view'),
(4,'2018/01/12',  'transact'),
(3,'2018/01/13',  'view'),
(5,'2018/01/13',  'transact'),
(4,'2018/01/15',  'view'),
(5,'2018/01/15',  'click'),
(4,'2018/01/16',  'view'),
(2,'2018/01/17',  'transact'),
(5,'2018/01/18',  'click');

SELECT ua.*, ROW_NUMBER() OVER(PARTITION BY user_id order by date, action) as ranks
INTO #temp
FROM user_action ua
order by user_id, ranks;

select t1.*, t2.date, t2.action, DATEDIFF(DAY, t2.date, t1.date)
from #temp t1
LEFT JOIN #temp t2 ON t1.user_id = t2.user_id AND t2.action IN ('view','click') and t1.action = 'transact' and (t1.ranks-1) = t2.ranks
order by t1.user_id, t1.ranks;
person JERRY    schedule 07.08.2018
comment
Удивительный! Большое спасибо! - person lostinsql; 07.08.2018