The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Sessionization with Event-Based Windows

edited May 2018 in General Discussion

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!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file