这是本节的多页打印视图。 点击此处打印.

返回本页常规视图.

Top-K 投影

Top-K 查询会从选定行的分区返回前 k 个行。Top-K 投影可以显著提升 Top-K 查询的性能。例如，您可以定义一个含有以下三列的表来存储气量表读数：气量表 ID、气量表读数对应的时间以及读数值：


=> CREATE TABLE readings (
    meter_id INT,
    reading_date TIMESTAMP,
    reading_value FLOAT);

以此表为例，以下 Top-K 查询会针对给定气量表返回五个最近的气量表读数：


SELECT meter_id, reading_date, reading_value FROM readings
    LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);

要提升此查询的性能，您可以创建 Top-K 投影，即一种特殊类型的实时聚合投影：


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

创建此 Top-K 投影并加载其数据（通过 START_REFRESH 或 REFRESH）后，Vertica 通常会将查询重定向到投影，并返回预聚合数据。

1 - 创建 Top-K 投影

您可以使用以下语法定义一个 Top-K 投影：


CREATE PROJECTION proj-name [(proj-column-spec)]
    AS SELECT select-expression FROM table
    LIMIT num-rows OVER (PARTITION BY expression ORDER BY column-expr);

有关完整的语法选项，请参阅CREATE PROJECTION。

例如：


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

有关详细讨论，请参阅Top-K 投影示例。

要求

以下要求适用于 Top-K 投影：

  • 投影不能未分段。

  • 窗口分区子句必须使用 PARTITION BY。

  • PARTITION BY 和 ORDER BY 子句中的列必须是 SELECT 列表中指定的第一个列。

  • 您必须使用 LIMIT 选项来创建 Top-K 投影，而不是使用子查询。例如，以下 SELECT 语句是等效的：

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

    两个语句返回了相同的结果：

    
      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)

    预聚合数据以供两个查询使用的 Top-K 投影必须包括 LIMIT 选项：

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

限制

以下限制适用于 Top-K 投影：

  • Top-K 投影只能引用一个表。

  • Vertica 不会将 Top-K 投影视为超投影，即使是包含所有表格列的投影也是如此。

  • 您不能修改 Top-K 投影中包含的列的锚表元数据，例如列的数据类型或默认值。您也不能删除这些列。要进行这些更改，首先删除与表关联的所有实时聚合和 Top-K 投影。

2 - 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 投影，所以查询始终都会返回最新数据。