Блог

У меня есть следующая таблица со многими идентификаторами пользователей (в примере только один идентификатор пользователя для демонстрационных целей):

Для каждого идентификатора пользователя я хочу извлечь две строки:

1. В первой строке должно быть isTransaction = 0 и самая ранняя дата!
2. Вторая строка должна быть isTransaction = 1, устройство должно отличаться от устройства первой строки, isTransaction должно быть равно 1 и самая ранняя дата сразу после первой строки

То есть результат должен быть:

 Time         userId      device    isTransaction
2021-01-27   10187675    mobile    0
2021-01-30   10187675    web       1
 

Я попытался ранжировать строки с разделением и упорядочением, но это не сработало:

 Select * from 
  (SELECT *, rank() over(partition by userId, device, isTransaction order by isTransaction, Time) as rnk  
  FROM table 1)
where rnk=1
order by Time
 

Пожалуйста, помогите! Также было бы неплохо проверить разницу во времени между этими двумя строками, чтобы она не превышала 30 дней. В противном случае идентификатор пользователя должен быть удален.

Вы можете сначала определить самое раннее время для 0 . Затем перечислите строки и возьмите только первую:

 select t.*
from (select t.*,
             row_number() over (partition by userid, status order by time) as seqnum
      from (select t.*,
                   min(case when isTransaction = 0 then time end) over (partition by userid order by time) as time_0
            from t
           ) t
      where time > time_0
     ) t
where seqnum = 1;
 

Это удовлетворяет двум перечисленным вами условиям.

Затем, углубившись в текст, вы хотите исключить строки, в которых разница превышает 30 дней. Это немного сложнее … но не слишком сложно:

 select t.*
from (select t.*,
             min(case when isTransaction = 1 then time end) over (partition by userid) as time_1
             row_number() over (partition by userid, status order by time) as seqnum
      from (select t.*,
                   min(case when isTransaction = 0 then time end) over (partition by userid order by time) as time_0
            from t
           ) t
      where time > time_0
     ) t
where seqnum = 1 and
      time_1 < timestamp_add(time_0, interval 30 day);