Top-k projection examples

The following examples show how to query a table with two Top-K projections for the most-recent trade and last trade of the day for each stock symbol.

The following examples show how to query a table with two Top-K projections for the most-recent trade and last trade of the day for each stock symbol.

  1. Create a table that contains information about individual stock trades:

    • Stock symbol

    • Timestamp

    • Price per share

    • Number of shares

    => CREATE TABLE trades(
        symbol CHAR(16) NOT NULL,
        trade_time TIMESTAMP NOT NULL,
        price NUMERIC(12,4),
        volume INT )
        PARTITION BY (EXTRACT(year from trade_time) * 100 +
        EXTRACT(month from trade_time));
    
  2. Load data into the table:

    
    INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10'::TIMESTAMP,100.00,100);
    INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10.3'::TIMESTAMP,101.00,100);
    INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.5'::TIMESTAMP,106.1,1000);
    INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.2'::TIMESTAMP,105.2,500);
    INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.2'::TIMESTAMP,42.01,400);
    INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.3'::TIMESTAMP,42.02,1000);
    INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.4'::TIMESTAMP,42.05,100);
    COMMIT;
    
  3. Create two Top-K projections that obtain the following information from the trades table:

    For each stock symbol, return the most recent trade.

    
    => CREATE PROJECTION trades_topk_a AS SELECT symbol, trade_time last_trade, price last_price
           FROM trades LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);
    
    => SELECT symbol, trade_time last_trade, price last_price FROM trades
       LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);
    
          symbol      |      last_trade       | last_price
    ------------------+-----------------------+------------
     HPQ              | 2012-10-10 10:10:10.4 |    42.0500
     AAPL             | 2011-10-10 10:10:10.5 |   106.1000
    (2 rows)
    


    For each stock symbol, return the last trade on each trading day.

    
    => CREATE PROJECTION trades_topk_b
        AS SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
        FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);
    
    => SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
        FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);
    
          symbol      | trade_date |      trade_time       | close_price | volume
    ------------------+------------+-----------------------+-------------+--------
     HPQ              | 2012-10-10 | 2012-10-10 10:10:10.4 |     42.0500 |    100
     AAPL             | 2011-10-10 | 2011-10-10 10:10:10.5 |    106.1000 |   1000
     AAPL             | 2010-10-10 | 2010-10-10 10:10:10.3 |    101.0000 |    100
    (3 rows)
    

In each scenario, Vertica redirects queries on the trades table to the appropriate Top-K projection and returns the aggregated data from them. As additional data is loaded into this table, Vertica pre-aggregates the new data and updates the Top-K projections, so queries always return with the latest data.