Sessionization with Event-Based Windows
Hi,
I am interested in adapting the event sessionization example here.
This is the example I would like to adapt.
=> SELECT userId, timestamp,
CONDITIONAL_TRUE_EVENT(timestamp - LAG(timestamp) > '30 seconds')
OVER(PARTITION BY userId ORDER BY timestamp) AS session FROM WebClicks;
userId | timestamp | session
--------+---------------------+---------
1 | 2009-12-08 15:00:00 | 0
1 | 2009-12-08 15:00:25 | 0
1 | 2009-12-08 15:00:45 | 0
1 | 2009-12-08 15:01:45 | 1
2 | 2009-12-08 15:02:45 | 0
2 | 2009-12-08 15:02:55 | 0
2 | 2009-12-08 15:03:55 | 1
Instead of triggering new sessions based on the previous record I would like to trigger a new session based on the starting time of the last session. The current query triggers a new session if the current timestamp is 30 seconds after the previous timestamp. I would like a query that will trigger a new session if the current timestamp is 30 seconds after the start of the previous session. It seems like I would want FIRST_VALUE to do accomplish this.
This is my desired query and result. Unfortunately this query fails because analytic functions cannot be nested.
=> SELECT userId, timestamp,
CONDITIONAL_TRUE_EVENT(timestamp - FIRST_VALUE(timestamp) > '30 seconds')
OVER(PARTITION BY userId ORDER BY timestamp) AS session FROM WebClicks;
userId | timestamp | session
--------+---------------------+---------
1 | 2009-12-08 15:00:00 | 0
1 | 2009-12-08 15:00:25 | 0
1 | 2009-12-08 15:00:45 | 1 (timestamp is > 30 seconds after 2009-12-08 15:00:00)
1 | 2009-12-08 15:01:45 | 2 (timestamp is > 30 seconds after 2009-12-08 15:00:45)
2 | 2009-12-08 15:02:45 | 0
2 | 2009-12-08 15:02:55 | 0
2 | 2009-12-08 15:03:55 | 1 (timestamp is > 30 seconds after 2009-12-08 15:02:45)
Is it possible to get my desired result with Vertica's analytic functions?
Thanks!