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:

• 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 modify the anchor table metadata of columns that are included in projections. You also cannot drop these columns. To make these changes, you must first drop all live aggregate and Top-K projections that are associated with the table.

• You cannot perform the following operations on anchor table data:

  - DELETE
  - UPDATE
  - MERGE

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

• The projections can reference only one table.