Блог

у меня есть таблица, подобная этой.пример данных

  ---------------------------- --------------------------- --------- ---------- 
|id                          |email                      |event    |timestamp |
 ---------------------------- --------------------------- --------- ---------- 
|take-2-blazin-it-safe-us-can|1297323246@yahoo.com       |processed|1584974510|
|take-2-blazin-it-safe-us-can|1297323246@yahoo.com         |delivered|1584974513|
|take-2-blazin-it-safe-us-can|1297323246@yahoo.com       |open     |1584978453|
|take-2-blazin-it-safe-us-can|1297323246@yahoo.com     |open     |1584983574|
|take-2-blazin-it-safe-us-can|1297323246@yahoo.com        |open     |1584983578|
 ---------------------------- --------------------------- --------- ---------- 
  

я не хочу, чтобы пользователи, которые открывали электронные письма между 2-5 днями после доставки электронной почты. Пример: если электронное письмо отправлено 21-м, то из пользователей, которые открыли электронную почту между 23-м и 26-м, включая как 23-й, так и 26-й.

До сих пор я пробовал так

 with tab as(
  select  distinct * from table
  where event in ('delivered','open')
  )
select count(case when last_val - first_val between 2 and 5 then 1 else 0 ) from(
select *, first_value(from_unixtime(timestamp,'yyyy-MM-dd') over(partition by id,email order by timestamp asc) as first_val,
last_value(from_unixtime(timestamp,'yyyy-MM-dd') over(partition by id,email order by timestamp asc) last_val from tab

)
  

Но это сработает, только если пользователь открыл электронное письмо один раз, но бывают случаи, когда пользователь открывал электронное письмо более одного раза. Это количество также должно быть включено.

Есть ли какой-либо чистый способ сделать это?

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

 select id, email
from (select t.*,
             min(timestamp) filter (where event = 'delivered') over (partition by id, email) as delivered_timestamp
      from t
     ) t
where event = 'open'
group by id, email
having count(*) filter (where timetamp >= delivered_timestamp   2*24*60*60 and
                              timestamp < delivered_timestamp   6*24*60*60
                       ) > 0;
  

Другой метод exists :

 select t.*
from t
where t.event = 'delivered' and
      exists (select 1
              from t t2
              where t2.id = t.tid and t2.email = t.email and
                    t2.timestamp >= t.timestamp   2*24*6*60 and
                    t2.timestamp < t.timestamp   6*24*6*60
             );