Computing session start and end using SQL window functions-CodePudding

I've a table of game logs containing a handDate, like this:

ID	handDate
1	2019-06-30 16:14:02.000
2	2019-07-12 06:18:02.000
3	...

I'd like to compute game sessions from this table (start and end), given that:

A new session is considered if there is no activity since 1 hour.
a session can exist across 2 days

So I'd like results like this:

day	session_start	sesssion_end
2019-06-30	2019-06-15 16:14:02.000	2019-06-15 16:54:02.000
2019-07-02	2019-07-02 16:18:02.000	2019-07-02 17:18:02.000
2019-07-02	2019-07-02 23:18:02.000	2019-07-03 03:18:02.000
2019-07-03	2019-07-03 06:18:02.000	2019-07-03 08:28:02.000

Currently I'm playing with the following code, but cannot achieve what I want:

SELECT *
             
    FROM (
        SELECT *,
         strftime( '%s', handDate)  - strftime( '%s', prev_event) AS inactivity
                 
        FROM (
            SELECT handDate,
                date( handDate) as day,
                FIRST_VALUE( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate) AS first_event,
                MIN(handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate),
                MAX(handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate),
                LAG( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS prev_event,
                LEAD( handDate) OVER (PARTITION BY date( handDate) ORDER BY handDate) AS next_event

                FROM hands
        ) last
    ) final

I'm using SQLite.

CodePudding user response：

I found the following solution:

SELECT day,
  sessionId,
  MIN(handDate) as sessionStart,
  MAX(handDate) as sessionEnd
FROM(
      SELECT day,
          handDate,
          sum(is_new_session) over (
              order by handDate rows between unbounded preceding and current row
          ) as sessionId
      FROM (
              SELECT *,
                  CASE
                      WHEN prev_event IS NULL
                      OR strftime('%s', handDate) - strftime('%s', prev_event) > 3600 THEN true
                      ELSE false
                  END AS is_new_session
              FROM (
                      SELECT handDate,
                          date(handDate) as day,
                          LAG(handDate) OVER (
                              PARTITION BY date(handDate)
                              ORDER BY handDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                          ) AS prev_event
                      FROM hands
                  )
          )
  )
GROUP BY sessionId

CodePudding user response：

DROP TABLE IF EXISTS hands;

CREATE TABLE hands(handDate TIMESTAMP);

INSERT INTO hands(handDate)
VALUES ('2021-10-29 10:30:00')
 , ('2021-10-29 11:35:00')
 , ('2021-10-29 11:36:00')
 , ('2021-10-29 11:37:00')
 , ('2021-10-29 12:38:00')
 , ('2021-10-29 12:39:00')
 , ('2021-10-29 12:39:10')
;
SELECT start_period, end_period
FROM (
  SELECT is_start, handDate AS start_period
      , CASE WHEN is_start AND is_end THEN handDate
          ELSE LEAD(handDate) OVER (ORDER BY handDate) 
          END AS END_period
  FROM (
    SELECT *
    FROM (
      SELECT *
        ,CASE WHEN (event-prev_event) * 1440.0 > 60 OR prev_event IS NULL THEN true ELSE FALSE END AS is_start
        ,CASE WHEN (next_event-event) * 1440.0 > 60 OR next_event IS NULL THEN true ELSE FALSE END AS is_end
      FROM (
        SELECT handDate
            , juliANDay(handDate) event
            , juliANDay(LAG(handDate) OVER (ORDER BY handDate)) AS prev_event
            , juliANDay(LEAD(handDate) OVER (ORDER BY handDate)) AS next_event
        FROM hands
      ) t
    ) t
    WHERE is_start OR is_end
  )t
)t
WHERE is_start