В одном запросе postgres определите, произошли ли два события за определенное время для какого-либо отдельного пользователя

Я пытаюсь найти события, которые происходят слишком близко во времени друг к другу, и мне интересно, есть ли способ сделать это только с одним запросом.

Вот сценарий. У меня eventsчто есть А definitionи что происходят за определенное userid. Скажемevent 1, не должно произойти менее чем через неделю после event 2для любого конкретного пользователя. Поэтому я хотел бы определить, произошло ли для любого пользователя, если, учитывая, что событие 1 произошло сегодня, событие 2 произошло для того же пользователя в течение недели. Есть ли способ сделать это с помощью одного SQL-запроса? В идеале это было бы что-то вроде

SELECT event_id, userid as use_userid FROM events WHERE definition = 1 and date > current_date 
- interval '1 day' AND (SELECT COUNT(*) FROM events WHERE userid = use_userid 
AND definition = 2 AND date > current_date - interval '7 days') > 0

Я близко? Я хочу узнать, для каких пользователей это произошло бы…остальная информация менее важна.

2 ответа

  1. Используется lagдля получения определения и даты предыдущей строки, где строки секционированы пользователем и упорядочены в порядке убывания даты. Затем используйте whereусловие, чтобы получить только нужные строки.

    select userid
    from (
    SELECT 
     userid 
    ,definition curr_def
    ,lag(definition) over(partition by userid order by date desc) prev_def
    ,date - coalesce(lag(date) over(partition by userid order by date desc), date) diffrnce
    FROM events 
    ) t
    where diffrnce < 7 and curr_def <> coalesce(prev_def,'xxx')
    
  2. будет ли это работать для вашей цели?

        SELECT distinct userid as use_userid 
        FROM events a 
        JOIN events b using(userid)
        WHERE a.definition = 1 and a.date > current_date - interval '1 day' 
          and b.definition = 2 and b.date > current_date - interval '7 days') > 0