Creating top-k projections

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

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.