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.
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)
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.
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)
- 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…