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 (Live Aggregate Projections).

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 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.