How to combine rows based on timestamp intervals using snowflake sql?

user_14011058 0
Viewed 1067

I have a table that stores userid, sessionid and datetime. The table stores data when a user is logged into a device and stores the user, session, and datetime. There can be multiple entries with one userid and sessionid combination. For example:

USERID | SESSIONID | DATE 
abcd   | 1234      | 2020-05-14 10:30:00 
abcd   | 1234      | 2020-05-14 10:32:00 
abcd   | 1234      | 2020-05-14 10:35:00 
abcd   | 1234      | 2020-05-14 11:32:00 
abcd   | 1234      | 2020-05-14 11:39:00

I am trying to combine these rows into a new table based on initial datetime up to datetime + x for the same session and user. The initial datetime moves if a date exceeds datetime+x. So if x is 30 minutes any date from start to datetime + 30min would be one row. If a date is greater than datetime + 30min it becomes the new start datetime and you perform the datetime+x until all dates have been looked at for a sessionid and userid combination.

The output of the example table should be:

USERID | SESSIONID | START_SESSION_DATE  | END_SESSION_DATE
abcd   | 1234      | 2020-05-14 10:30:00 | 2020-05-14 10:35:00 
abcd   | 1234      | 2020-05-14 11:32:00 | 2020-05-14 11:39:00

I am not sure how to achieve this using only SQL. I was going to create a stored procedure to perform all the logic in javascript and then insert into the new table in Snowflake but that will be very slow and it won't scale. Thanks in advance.

snowflake-cloud-data-platform recursive-query datetime sql
2 Answers

This is a bit tricky. You cannot just compare adjacent rows: you need to keep track of the start date of each series of rows so you can compare it to the following dates, and decide when to break into a new group.

This requires some kind of iterative process. In SQL, this is usually implemented with a recursive query - which, fortunately, Snowflake supports

Consider:

with recursive 
    data as (
        select 
            t.*, 
            row_number() over(partition by userid, sessionid order by date) rn
        from mytable t
    ),
    cte as (
        select 
            userid, 
            sessionid,
            date start_session_date,
            date end_session_date
        from data
        where rn = 1
        union all
        select
            c.userid,
            c.sessionid,
            case when d.date > dateadd(minute, 30, c.start_session_date)
                then d.date
                else c.start_session_date
            end,
            d.date
        from cte c
        inner join data d 
            on  d.userid = c.userid
            and d.sessionid = c.sessionid 
            and d.rn = c.rn + 1 and 
    )
select 
    userid, 
    sessionid, 
    start_session_date, 
    max(end_session_date) end_session_date
from cte
group by userid, sessionid, start_session_date

The first common-table-expression (data) enumerates rows having the same userid and sessionid with row_number(). Then, the second CTE (cte) iteratively walks the groups of rows, starting with the first one, and create new groups as needed. The final step is aggregation.

user_106767160

This method makes use of the Snowflake WIDTH_BUCKET function and appears to work correctly based on some test data I generated:

-- Get the min amd max timestamps for each userid, sessionid
WITH T1 AS (    
SELECT USERID, SESSIONID,MIN(DATE_TIME) MIN_DATE, MAX(DATE_TIME) MAX_DATE
FROM TEST_DATA
GROUP BY USERID, SESSIONID
),
--Get the number of 'buckets', for each userid/sessionid, to divide the data into by defining the time period
--Hardcoded here as MINUTE and 30
T2 AS (
SELECT USERID, SESSIONID, MIN_DATE, MAX_DATE, CEIL(DATEDIFF(MINUTE, MIN_DATE, MAX_DATE)/30,0) NUM_BUCKETS    
FROM T1
 ),
--Assign each record to the appropriate time period bucket
--WIDTH_BUCKET takes numeric parameters hence the conversion to epoch_seconds
T3 AS (
SELECT TD.USERID, TD.SESSIONID, TD.DATE_TIME
,width_bucket(DATE_PART(EPOCH_SECONDS,TD.DATE_TIME), DATE_PART(EPOCH_SECONDS,T2.MIN_DATE), DATE_PART(EPOCH_SECONDS,T2.MAX_DATE), T2.NUM_BUCKETS) as "TIME_GROUP"
FROM TEST_DATA TD
INNER JOIN T2 ON TD.USERID = T2.USERID AND TD.SESSIONID = T2.SESSIONID
)
--Get the min and make timestamps for each userid, sessionid and bucket combination
SELECT USERID, SESSIONID, MIN(DATE_TIME), MAX(DATE_TIME)
FROM T3
GROUP BY USERID, SESSIONID, TIME_GROUP
order BY USERID, SESSIONID, TIME_GROUP
LIMIT 10
 ;
user_136583990
Related