实时聚合投影示例

此示例显示了如何使用下面的 clicks 表跟踪给定网页上的用户点击：

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

您可以使用以下查询聚合用户特定的活动：

=> 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;

要提升此查询的性能，请创建可以统计每位用户的单击数的实时聚合投影：

=> 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;

查询 clicks 表了解用户点击数时，Vertica 通常会将查询定向到实时聚合投影 clicks_agg。随着更多数据加载到 clicks，Vertica 会预聚合新数据并更新 clicks_agg，所以查询始终都会返回最新数据。

例如：

=> 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)