Creating Top-K Projections

You define a Top-K projection with the following syntax:

CREATE PROJECTION proj-name [(proj-column-spec)]
	AS SELECT select-expression FROM table
	LIMIT num-rows OVER (PARTITION BY expression ORDER BY column-expr);

For full syntax options, see CREATE PROJECTION.

For example:

=> CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value)
	AS SELECT meter_id, reading_date, reading_value FROM readings
	LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);

For an extended discussion, see Top-K Projection Examples.

Requirements

The following requirements apply to Top-K projections:

• The projection cannot be unsegmented.
• The window partition clause must use PARTITION BY.
• Columns in PARTITION BY and ORDER BY clauses must be the first columns specified in the SELECT list.
• You must use the LIMIT option to create a Top-K projection, instead of subqueries. For example, the following SELECT statements are equivalent:

=> SELECT symbol, trade_time last_trade, price last_price FROM (
    SELECT symbol, trade_time, price, ROW_NUMBER() 
    OVER(PARTITION BY symbol ORDER BY trade_time DESC) rn FROM trades) trds WHERE rn <=1;

=> SELECT symbol, trade_time last_trade, price last_price FROM trades 
    LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);

Both return the same results:

      symbol      |      last_trade       | last_price
------------------+-----------------------+------------
 AAPL             | 2011-11-10 10:10:20.5 |   108.4000
 HPQ              | 2012-10-10 10:10:10.4 |    42.0500
(2 rows)

A Top-K projection that pre-aggregates data for use by both queries must include the LIMIT option:

=> CREATE PROJECTION trades_topk AS 
     SELECT symbol, trade_time last_trade, price last_price FROM trades 
     LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);

Restrictions

The following restrictions apply to Top-K projections:

• Top-K projections can reference only one table.
• Vertica does not regard Top-K projections as superprojections, even those that include all table columns.
• You cannot modify the anchor table metadata of columns that are included in Top-K projections—for example, a column's data type or default value. You also cannot drop these columns. To make these changes, first drop all live aggregate and Top-K projections that are associated with the table.

  One exception applies: You can set and drop NOT NULL on columns that are included in a live aggregate projection.