Creating Live Aggregate Projections

You define a live aggregate projection with the following syntax:

=> CREATE PROJECTION proj-name AS
      SELECT select-expression FROM table
      GROUP BY group-expression;

For full syntax options, see CREATE PROJECTION (Live Aggregate Projections).

For example:

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

For an extended discussion, see Live Aggregate Projection Example.

Requirements

The following requirements apply to live aggregate projections:

• Live aggregate projections must be enabled: configuration parameter EnableGroupByProjections must be set to 1 (default).
• The projection cannot be unsegmented.
• SELECT and GROUP BY columns must be in the same order. GROUP BY expressions must be at the beginning of the SELECT list.

Restrictions

The following restrictions apply to live aggregate projections:

• Vertica does not regard the projections as superprojections, even one that includes all table columns.
• You cannot perform the following operations on anchor table data:
  DELETE
  UPDATE
  MERGE

  Note: To modify existing anchor table data, you must first drop all live aggregate and Top-K projections that are associated with it, using DROP PROJECTION.

• The projections can reference only one table.