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.