Top-K 投影示例

以下示例说明了如何查询具有两个 Top-K 投影的表，检索每个股票代码的最近交易和交易日的最新交易的信息。

1. 创建一个包含单个股票交易信息的表：

   • 股票代码

   • Timestamp

   • 每股价格

   • 股数

   => 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. 将数据加载到表中：

   
   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. 创建包含 trades 表的以下信息的两个 Top-K 投影：

   • 返回每个股票代码的最近交易。

   • 返回每个交易日的最新交易。

   为每个股票代码返回最近交易。

   
   => 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)
   

   
   为每个股票代码返回每个交易日的最新交易。

   
   => 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)
   

在每个场景中，Vertica 都会将对 trades 表的查询重定向到相应的 Top-K 投影，并从中返回聚合数据。随着更多数据加载到此表，Vertica 会预聚合新数据并更新 Top-K 投影，所以查询始终都会返回最新数据。