Live aggregate projection example

This example shows how you can track user clicks on a given web page using the following clicks table:

=> CREATE TABLE clicks(
   user_id INTEGER,
   page_id INTEGER,
   click_time TIMESTAMP NOT NULL);

You can aggregate user-specific activity with the following query:

=> SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
   WHERE click_time::DATE = '2015-04-30'
   GROUP BY page_id, click_time::DATE ORDER BY num_clicks DESC;

To facilitate performance of this query, create a live aggregate projection that counts the number of clicks per user:

=> CREATE PROJECTION clicks_agg AS
   SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
   GROUP BY page_id, click_time::DATE KSAFE 1;

When you query the clicks table on user clicks, Vertica typically directs the query to the live aggregate projection clicks_agg. As additional data is loaded into clicks, Vertica pre-aggregates the new data and updates clicks_agg, so queries always return with the latest data.

For example:

=> SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
    WHERE click_time::DATE = '2015-04-30' GROUP BY page_id, click_time::DATE
    ORDER BY num_clicks DESC;
 page_id | click_date | num_clicks
---------+------------+------------
    2002 | 2015-04-30 |         10
    3003 | 2015-04-30 |          3
    2003 | 2015-04-30 |          1
    2035 | 2015-04-30 |          1
   12034 | 2015-04-30 |          1
(5 rows)