Вычисление быстрых интервалов использования продукта (конверсии последовательности) в Vertica SQL

Question

У меня есть огромный набор данных (~30 миллиардов строк):

host_id | usr_id | src_id | visit_num | event_ts

где любой пользователь из родительского узла может посетить страницу источника (src_id), где источником является, скажем, их телефон, планшет или компьютер (не может быть идентифицирован). Колонка vis_num заказанное количество посещений на источник на пользователя на хост. Колонка event_ts фиксирует временную метку каждого посещения для каждого источника для каждого хоста. Пример набора данных для одного хоста может выглядеть следующим образом:

   host_id | usr_id  | src_id |  vis_num  |      event_ts
----------------------------------------------------------------
   100     |   10    |  05    |     1     |  2017-08-01 14:52:34
   100     |   10    |  05    |     1     |  2017-08-01 14:56:00
   100     |   10    |  05    |     1     |  2017-08-01 14:58:09
   100     |   10    |  05    |     2     |  2017-08-01 17:08:10
   100     |   10    |  05    |     2     |  2017-08-01 17:16:07
   100     |   10    |  05    |     2     |  2017-08-01 17:23:25
   100     |   10    |  72    |     1     |  2017-07-29 20:03:01
   100     |   10    |  72    |     1     |  2017-07-29 20:04:10
   100     |   10    |  72    |     2     |  2017-07-29 20:45:17
   100     |   10    |  72    |     2     |  2017-07-29 20:56:46
   100     |   10    |  72    |     3     |  2017-07-30 09:30:15
   100     |   10    |  72    |     3     |  2017-07-30 09:34:19
   100     |   10    |  72    |     4     |  2017-08-01 18:16:57
   100     |   10    |  72    |     4     |  2017-08-01 18:26:00
   100     |   10    |  72    |     5     |  2017-08-02 07:53:33
   100     |   22    |  43    |     1     |  2017-07-06 11:45:48
   100     |   22    |  43    |     1     |  2017-07-06 11:46:12
   100     |   22    |  43    |     2     |  2017-07-07 08:41:11

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

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

1) Определите всех пользователей, которые повторили критическое событие как минимум дважды в течение определенного периода времени (45 дней).

2) Для этих пользователей измерьте время, которое они потратили между завершением мероприятия в первый и второй раз.

3) Составьте график кумулятивной функции распределения - т.е. процента пользователей, которые выполнили второе событие за различные промежутки времени.

4) Определите интервал времени, через который 80% пользователей завершили второе событие - это интервал использования вашего продукта.

Страница 23 из:

http://usdatavault.com/library/Product-Analytics-Playbook-vol1-Mastering_Retention.pdf

Вот что я попробовал:

with new_users as (

select host_id || ' ' || usr_id as host_usr_id,
       min(event_ts) as first_login_date

   from tableA
   group by 1
)
,

time_diffs as (
select a.host_id || ' ' || a.usr_id as host_usr_id,
       a.usr_id,
       a.src_id,
       a.event_ts,
       a.vis_num,
       b.first_login_date,  

   case when lag(a.vis_num) over 
                    (partition by a.host_id, a.usr_id, a.src_id 
                      order by a.event_ts) <> a.vis_num
        then a.event_ts -  lag(a.event_ts) over 
                                 (partition by a.host_id, a.usr_id, 
                                               a.src_id 
                                   order by a.event_ts)
        else null end 
          as time_diff                     


    from tableA a
    left join new_users b
    on b.host_usr_id = a.host_id || ' ' || a.usr_id


      where a.event_date > current_date - interval '45 days'
      and a.event_date > b.first_login_date + interval '45 days'

)

select count(distinct case when time_diff < interval '45 days'
                  and event_ts > first_login_date + interval '45 
days'
                  then host_usr_id end) as cnt_45


   from time_diffs

Я пробовал несколько других (очень разных) запросов (см. Ниже), но производительность здесь определенно является проблемой. Присоединение к интервалам дат - также новая концепция для меня. Любая помощь приветствуется.

Другой подход:

with new_users as (

select host_id,
       usr_id,
       min(event_ts) as first_login_date

   from tableA
   group by 1,2

),

x_day_twice as (

select a.host_id, 
       a.usr_id,
       a.src_id,
       max(a.vis_num) - min(a.vis_num) + 1 as num_logins

    from tableA a
    left join new_users b
    on a.host_id || ' ' || a.usr_id = b.host_id || ' ' || b.usr_id
    and a.event_ts > b.first_login_date + interval '45 days'

where event_ts >= current_timestamp - interval '1 days' - 
interval '45 days' and first_login_date < current_date - 1 - 45

group by 1, 2, 3
)


select count(distinct case when num_logins > 1 
                           then host_id || ' ' || usr_id end)
   from x_day_twice