Блог

Я пытаюсь определить список пользователей, которые были активны в период1, а также активны в период2 — по сути, это способ расчета удержания пользователей. Это данные, которые у меня есть

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

По сути, окно просмотра в 3 календарных дня, которое определяет, есть ли запись, да или нет.

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

Спасибо.

 WITH data1 as (
  SELECT DATE("2021-01-02") date, 'abc' user UNION ALL
  SELECT DATE("2021-01-03") date, 'abc' user UNION ALL
  SELECT DATE("2021-01-04") date, 'abc' user UNION ALL
  SELECT DATE("2021-01-09") date, 'abc' user UNION ALL
  SELECT DATE("2021-01-10") date, 'abc' user UNION ALL
  SELECT DATE("2021-01-11") date, 'abc' user UNION ALL   
  SELECT DATE("2021-01-04") date, 'aaa' user UNION ALL
  SELECT DATE("2021-01-09") date, 'aaa' user UNION ALL
  SELECT DATE("2021-01-10") date, 'aaa' user 
)

SELECT  
  data1.*
FROM data1 as data1
JOIN data1 as data_forward
  ON data1.user = data_forward.user
  AND data1.date = DATE_ADD(data_forward.date, INTERVAL 2 DAY)
 

Рассмотрим ниже

 select date, user, 
  count(1) over period2 > 0 is_in_period2
from data1
window period2 as (
  partition by user 
  order by unix_date(date)
  range between 3 preceding and 1 preceding
)    
 

если применить к образцу данных в вашем вопросе — вывод

Примечание: период2 в приведенном выше — считается тремя предыдущими днями — вы можете управлять этим в соответствующем выражении ниже

 range between 3 preceding and 1 preceding
 

Если вы хотите просмотреть 3 дня для каждой строки, то я думаю, что самый простой метод — это просто lag() :

 select t.*,
       ( lag(date) over (partition by user order by date) >= date_add(date, interval -3 day)
       ) as is_in_previous_period
from t;
 

lag() просматривает непосредственно предшествующую строку для пользователя. Если дата в этой строке находится в пределах 3 дней, то флаг равен TRUE .