Event Based Windows

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Event Based Windows

Post by JimKnicely » Fri Feb 08, 2013 8:53 pm

Event Based Windows in Vertica allow us to break time series data into windows that border on significant events within the data.

There are two event-based window functions in Vertica:

1. CONDITIONAL_CHANGE_EVENT():
  • This function assigns an event window number to each row of a result set, starting from 0, and increments by 1 when the result of evaluating the argument expression on the current row differs from that on the previous row.
Example:

Pretend we’re tracking every 5 minutes the hit counter for a really cool web site. We may want to know how often the hit counter increased during a certain period of time. We can use the CONDITIONAL_CHANGE_EVENT() function to find out.

Code: Select all

dbadmin=> SELECT ts,
dbadmin->        link,
dbadmin->        cnt,
dbadmin->        CONDITIONAL_CHANGE_EVENT(cnt) OVER(ORDER BY ts) cce
dbadmin->    FROM web_hits
dbadmin->   WHERE ts BETWEEN '2013-02-08 12:00:00' AND '2013-02-08 12:30:00'
dbadmin->   ORDER BY ts;
         ts          |           link            | cnt  | cce
---------------------+---------------------------+------+-----
2013-02-08 12:00:00 | http://vertica-forums.com | 5000 |   0
2013-02-08 12:05:00 | http://vertica-forums.com | 5010 |   1
2013-02-08 12:10:00 | http://vertica-forums.com | 5010 |   1
2013-02-08 12:15:00 | http://vertica-forums.com | 5100 |   2
2013-02-08 12:20:00 | http://vertica-forums.com | 5135 |   3
2013-02-08 12:25:00 | http://vertica-forums.com | 5135 |   3
2013-02-08 12:30:00 | http://vertica-forums.com | 6000 |   4
(7 rows)
From the query results we see that our hit counter increased 4 times (there were 4 window events). We can also see exactly when each increase occurred!

2. CONDITIONAL_TRUE_EVENT():
  • Assigns an event window number to each row of a result set, starting from 0, and increments the number by 1 when the result of the Boolean argument expression evaluates true.
Example:

Say we have another table tracking the number hits that occur on the same awesome web site in 5 minute intervals. We may have questions like “When does the hit count change”? (cce), “How often is the hit count greater than 50?” (cte_gt_50) or “How often is the hit count greater than or equal to 50?” (cte_gte_50). To find the answers we can use both the CONDITIONAL_CHANGE_EVENT() and the CONDITIONAL_TRUE_EVENT() functions.

Code: Select all

dbadmin=> SELECT ts,
dbadmin->        link,
dbadmin->        real_cnt,
dbadmin->        CONDITIONAL_CHANGE_EVENT(real_cnt) OVER(ORDER BY ts)     cce,
dbadmin->        CONDITIONAL_TRUE_EVENT(real_cnt > 50) OVER(ORDER BY ts)  cte_gt_50,
dbadmin->        CONDITIONAL_TRUE_EVENT(real_cnt >= 50) OVER(ORDER BY ts) cte_gte_50
dbadmin->    FROM real_web_hits
dbadmin->   WHERE ts BETWEEN '2013-02-07 12:00:00' AND '2013-02-07 12:30:00'
dbadmin->   ORDER BY ts;
         ts          |           link            | real_cnt | cce | cte_gt_50 | cte_gte_50
---------------------+---------------------------+----------+-----+-----------+------------
2013-02-07 12:00:00 | http://vertica-forums.com |       50 |   0 |         0 |          1
2013-02-07 12:05:00 | http://vertica-forums.com |       48 |   1 |         0 |          1
2013-02-07 12:10:00 | http://vertica-forums.com |       50 |   2 |         0 |          2
2013-02-07 12:15:00 | http://vertica-forums.com |       51 |   3 |         1 |          3
2013-02-07 12:20:00 | http://vertica-forums.com |       52 |   4 |         2 |          4
2013-02-07 12:25:00 | http://vertica-forums.com |       52 |   4 |         3 |          5
2013-02-07 12:30:00 | http://vertica-forums.com |       49 |   5 |         3 |          5
(7 rows)
These results provide the answers to our questions, and they also reveal more interesting facts.
  • 1. The hit count changed five times
    2. The hit count was > 50 three times
    3. The hit count was >= 50 five times
    4. We can see every time the hit count was > 50
    5. We can see every time the hit count was >= 50
    6. We can the first and last time the hit count was > 50
    7. We can the first and last time the hit count was >= 50
    8. We can the 4th time the hit count was >= 50
    9. Etc…
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”