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

返回本页常规视图.

投影中的预聚合数据

如果查询使用了聚合函数，例如 SUM 和 COUNT，那么它们在使用已经包含聚合数据的投影时，可以更有效地执行。此效率提升在对大规模数据进行查询时尤其显著。

例如，电网公司要读取 3000 万智能电表的数据，而这些电表每五分钟提供一次数据。公司会将每个读数记录在数据库表中。在某个给定年度，此表中添加了三万亿条记录。

电网公司可以使用包含聚合函数的查询来分析这些记录，以便执行以下任务：

  • 建立使用模式。

  • 检测欺诈。

  • 衡量与天气模式或价格变动等外部事件的相关性。

要优化查询响应时间，您可以创建聚合投影，由该投影存储聚合后的数据。

聚合投影

Vertica 提供几种类型的投影来存储从聚合函数或表达式中返回的数据。

  • 实时聚合投影：包含通过锚表中的列聚合列值的列的投影。您还可以定义包括 用户定义的变换函数 的实时聚合投影。

  • Top-K 投影：一种实时聚合投影，用于从选定行的一个分区中返回前 k 行。创建一个满足 Top-K 查询条件的 Top-K 投影。

  • 预聚合 UDTF 结果的投影：调用用户定义的转换函数 (UDTF) 的实时聚合投影。为了在查询这种类型的投影时最大限度降低开销，Vertica 会在后台处理 UDTF 函数，并将其结果存储在磁盘上。

  • 包含表达式的投影：具有通过锚表列计算列值的列的投影。

建议使用

  • 针对大型数据集进行查询时，聚合投影最为有用。

  • 为获得最佳查询性能，LAP 投影的大小应该是锚表的小子集，理想情况下是锚表的 1% 到 10 % 之间或更小（如有可能）。

限制

  • 如果在具有实时聚合投影的目标表上执行 MERGE 操作，则这些操作必须优化。

  • 您不能使用实时聚合投影更新或删除临时表中的数据。

要求

手动恢复未正常关闭的数据库时，实时聚合投影可能需要一些时间来刷新。

1 - 实时聚合投影

实时聚合投影包含一些列，这些列的值是从其锚表的列中聚合而成的。当您将数据加载到表中时，Vertica 首先会聚合数据，然后将其加载到实时聚合投影中。例如，通过 INSERT 或 COPY 执行后续加载操作时，Vertica 会使用新数据重新计算聚合并更新投影。

1.1 - 实时聚合投影支持的函数

Vertica 可以在以下聚合函数的实时聚合投影中聚合结果：

带 DISTINCT 的聚合函数

实时聚合投影可以支持包含用关键字 DISTINCT 限定的聚合函数的查询。需要满足以下要求：

  • 聚合表达式的求值结果必须为非常数。

  • 投影的 GROUP BY 子句必须指定聚合表达式。

例如，以下查询使用了 SUM(DISTINCT) 来计算给定区域的所有唯一薪金的总额：

SELECT customer_region, SUM(DISTINCT annual_income)::INT
   FROM customer_dimension GROUP BY customer_region;

此查询可以使用以下实时聚合投影，而该投影在其 GROUP BY 子句中指定了聚合列 (annual_income)：

CREATE PROJECTION public.TotalRegionalIncome
(
 customer_region,
 annual_income,
 Count
)
AS
 SELECT customer_dimension.customer_region,
        customer_dimension.annual_income,
        count(*) AS Count
 FROM public.customer_dimension
 GROUP BY customer_dimension.customer_region,
          customer_dimension.annual_income
;

1.2 - 创建实时聚合投影

您可以使用以下语法定义一个实时聚合投影：


=> CREATE PROJECTION proj-name AS
      SELECT select-expression FROM table
      GROUP BY group-expression;

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

例如：

=> CREATE PROJECTION clicks_agg AS
   SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
   GROUP BY page_id, click_time::DATE KSAFE 1;

有关详细讨论，请参阅实时聚合投影示例。

要求

以下要求适用于实时聚合投影：

  • 投影不能未分段。

  • SELECT 和 GROUP BY 列的顺序必须相同。GROUP BY 表达式必须在 SELECT 列表的开头。

限制

以下限制适用于实时聚合投影：

  • 如果在具有实时聚合投影的目标表上执行 MERGE 操作，则这些操作必须优化。

  • 实时聚合投影只能引用一个表。

  • Vertica 不会将实时聚合投影视为超投影，即使是包含所有表格列的投影也是如此。

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

1.3 - 实时聚合投影示例

此示例显示了如何使用下面的 clicks 表跟踪给定网页上的用户点击：


=> CREATE TABLE clicks(
   user_id INTEGER,
   page_id INTEGER,
   click_time TIMESTAMP NOT NULL);

您可以使用以下查询聚合用户特定的活动：


=> SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
   WHERE click_time::DATE = '2015-04-30'
   GROUP BY page_id, click_time::DATE ORDER BY num_clicks DESC;

要提升此查询的性能，请创建可以统计每位用户的单击数的实时聚合投影：

=> CREATE PROJECTION clicks_agg AS
   SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
   GROUP BY page_id, click_time::DATE KSAFE 1;

查询 clicks 表了解用户点击数时，Vertica 通常会将查询定向到实时聚合投影 clicks_agg。随着更多数据加载到 clicks，Vertica 会预聚合新数据并更新 clicks_agg，所以查询始终都会返回最新数据。

例如：

=> SELECT page_id, click_time::DATE click_date, COUNT(*) num_clicks FROM clicks
    WHERE click_time::DATE = '2015-04-30' GROUP BY page_id, click_time::DATE
    ORDER BY num_clicks DESC;
 page_id | click_date | num_clicks
---------+------------+------------
    2002 | 2015-04-30 |         10
    3003 | 2015-04-30 |          3
    2003 | 2015-04-30 |          1
    2035 | 2015-04-30 |          1
   12034 | 2015-04-30 |          1
(5 rows)

2 - 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 通常会将查询重定向到投影，并返回预聚合数据。

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

3 - 预聚合 UDTF 结果

CREATE PROJECTION 可以定义调用用户定义的转换函数 (UDTF) 的实时聚合投影。为了在查询这些投影时最大限度降低开销，Vertica 会在后台处理这些函数，并将其结果存储在磁盘上。

定义含 UDTF 的投影

投影定义通过两种方法之一来确定其 UDTF 特征：

  • 将 UDTF 标识为预传递 UDTF，此时 UDTF 会在新加载的数据存储在投影 ROS 容器之前对其进行转换。

  • 将 UDTF 标识为批量 UDTF，此时 UDTF 会聚合和存储投影数据。

投影定义在窗口分区子句中通过关键字 PREPASS 或 BATCH 将 UDTF 标识为预传递 UDTF 或批量 UDTF。投影可以指定一个预传递或批量 UDTF，或者包含两者（请参阅 UDTF 规范选项）。

在所有情况下，投影都在 PARTITION BY 列上隐式地分段和排序。

UDTF 规范选项

投影可以批量调用和预传递单个 UDTF 或 UDTF 组合。

单个预传递 UDTF：

将数据加载到投影的锚表时（例如通过 COPY 或 INSERT 语句），Vertica 会调用预传递 UDTF。预传递 UDTF 会转换新数据，然后将转换后的数据存储到投影的 ROS 容器中。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]

AS SELECT { table-column | expr-with-table-columns }[,...], prepass-udtf(prepass-args)
    OVER (PARTITION PREPASS BY partition-column-expr[,...])
    [ AS (prepass-output-columns) ] FROM table [[AS] alias]

单个批量 UDTF

单个调用时，批量 UDTF 会在执行合并、数据加载和查询操作时转换并聚合投影数据。UDTF 会将聚合结果存储到投影的 ROS 容器中。聚合会在合并和加载操作期间累计，并在查询执行时完成（如有需要）。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ]  }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...], batch-udtf(batch-args)
   OVER (PARTITION BATCH BY partition-column-expr[,...])
   [ AS (batch-output-columns) FROM table [ [AS] alias ]

组合预传递和批量 UDTF

您可以定义一个含子查询的投影，并用它来调用预传递 UDTF。该预传递 UDTF 会将转换后的数据返回给外部批量查询。然后，批量 UDTF 会以迭代方式在多个合并操作期间聚合结果。它会在执行查询时完成聚合（如有需要）。

使用以下语法：


CREATE PROJECTION [ IF NOT EXISTS ] [[database.]schema.]projection
[ (
   { projection-column | grouped-clause
   [ ENCODING encoding-type ]
   [ ACCESSRANK integer ] }[,...]
) ]
AS SELECT { table-column | expr-with-table-columns }[,...], batch-udtf(batch-args)
   OVER (PARTITION BATCH BY partition-column-expr[,...]) [ AS (batch-output-columns) ] FROM (
      SELECT { table-column | expr-with-table-columns }[,...], prepass-udtf (prepass-args)
      OVER (PARTITION PREPASS BY partition-column-expr[,...]) [ AS (prepass-output-columns) ] FROM table ) sq-ref

示例

单个预传递 UDTF：
以下示例说明了如何使用从出现多次的文本文档字符串中提取内容的 UDTFtext_index。

以下投影指定按预传递 UDTF 调用 text_index：


=> CREATE TABLE documents ( doc_id INT PRIMARY KEY, text VARCHAR(140));

=> CREATE PROJECTION index_proj (doc_id, text)
     AS SELECT doc_id, text_index(doc_id, text)
     OVER (PARTITION PREPASS BY doc_id) FROM documents;

将数据加载到锚表 documents 时即会调用 UDTF。 text_index 会转换新加载的数据，并且 Vertica 会将转换后的数据存储在实时聚合投影 ROS 容器中。

所以，如果您将以下数据加载到 documents：

=> INSERT INTO documents VALUES
(100, 'A SQL Query walks into a bar. In one corner of the bar are two tables.
 The Query walks up to the tables and asks - Mind if I join you?');
 OUTPUT
--------
      1
(1 row)

text_index 会转换新加载的数据，并且会将其存储在投影 ROS 容器中。当您查询投影时，它会返回以下结果：


doc_id | frequency |     term
-------+-----------+--------------
100    | 2         | bar
100    | 2         | Query
100    | 2         | tables
100    | 2         | the
100    | 2         | walks

组合预传递和批量 UDTF
以下投影分别指定了预传递和批量 UDTF stv_intersect 及 aggregate_classified_points：


CREATE TABLE points( point_id INTEGER, point_type VARCHAR(10), coordinates GEOMETRY(100));

CREATE PROJECTION aggregated_proj
   AS SELECT point_type, aggregate_classified_points( sq.point_id, sq.polygon_id)
   OVER (PARTITION BATCH BY point_type)
   FROM
      (SELECT point_type, stv_intersect(
         point_id, coordinates USING PARAMETERS index=‘polygons’ )
       OVER (PARTITION PREPASS BY point_type) AS (point_id, polygon_id) FROM points) sq;

预传递查询 UDTF stv_intersect 将其结果（点集和匹配的多边形 ID）返回给外部批量查询。然后，外部批量查询会调用 UDTF aggregate_classified_points。Vertica 会在合并操作整合投影数据时聚合 aggregate_classified_points 返回的结果集。查询投影时会发生最终聚合（如有需要）。

批量 UDTF 参数的名称和顺序必须与预传递 UDTF stv_intersect 返回的输出列完全匹配。在此示例中，预传递子查询显式命名了预传递 UDTF 输出列 point_id 和 polygon_id。因此，批量 UDTF 实参在名称和顺序方面与它们匹配： sq.point_id 和 sq.polygon_id。

4 - 通过表达式聚合数据

您可以创建其中的一个或多个列由表达式定义的投影。表达式可以引用一个或多个锚表列。例如，下表包含两个整数列 a 和 b：

=> CREATE TABLE values (a INT, b INT);

您可以创建含表达式的投影，利用表达式将列 c 的值计算为 a 和 b 的乘积：

=> CREATE PROJECTION values_product (a, b, c)
   AS SELECT a, b, a*b FROM values SEGMENTED BY HASH(a) ALL NODES KSAFE;

当您将数据加载到此投影中时，Vertica 会解析列 c 中的表达式 a*b。然后，您可以查询投影而不是锚表。Vertica 会返回预计算的数据并避免资源密集型计算产生的开销。

在投影中使用表达式还可以让您对表达式的计算结果的数据进行排序或分段，而不是对单个列值进行排序。

支持用户定义的标量函数

Vertica 将用户定义的标量函数 (UDSF) 视为类似于其他表达式。在每个加载操作中，UDSF 会被调用并返回其结果。Vertica 将这些结果存储在磁盘上，并在您直接查询投影时返回这些结果。

在以下示例中，投影 points_p1 指定了在将数据加载到锚表 points 时调用的 UDSF zorder。当数据加载到投影时，Vertica 将调用此函数，并存储其结果，以便在未来查询时能够快速访问。

=> CREATE TABLE points(point_id INTEGER, lat NUMERIC(12,9), long NUMERIC(12,9));

=> CREATE PROJECTION points_p1
     AS SELECT point_id, lat, long, zorder(lat, long) zorder FROM points
     ORDER BY zorder(lat, long) SEGMENTED BY hash(point_id) ALL NODES;

要求

  • 所有 ORDER BY 表达式必须位于 SELECT 列表。

  • 所有投影列必须经过命名。

限制

  • 如果在具有实时聚合投影的目标表上执行 MERGE 操作，则这些操作必须优化。

  • 与实时聚合投影不同，Vertica 不会将含表达式的查询重定向到等效现有投影。

  • 投影表达式必须不可变，换而言之，它们必须始终返回相同的结果。例如，投影不能包括使用 TO CHAR（取决于区域设置）或 RANDOM（在每次调用时返回不同的结果）的表达式。

  • 投影表达式不能包括 Vertica 元函数，例如 ADVANCE_EPOCH、ANALYZE_STATISTICS、EXPORT_TABLES 或 START_REFRESH。

4.1 - 通过表达式查询数据示例

以下示例使用了包含两个整数列 a 和 b 的表：

=> CREATE TABLE values (a INT, b INT);

您可以创建含表达式的投影，利用表达式将列 c 的值计算为 a 和 b 的乘积：

=> CREATE PROJECTION values_product (a, b, c)
   AS SELECT a, b, a*b FROM values SEGMENTED BY HASH(a) ALL NODES KSAFE;
=> COPY values FROM STDIN DELIMITER ',' DIRECT;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 3,11
>> 3,55
>> 8,9
>> 8,23
>> 16,41
>> 22,111
>> \.
=>

要查询此投影，请使用 Vertica 分配给此投影或其伙伴实例投影的名称。例如，以下查询针对之前定义的投影的不同实例，并且返回相同的结果：

=> SELECT * FROM values_product_b0;
=> SELECT * FROM values_product_b1;

以下示例查询了锚表：

=> SELECT * FROM values;
 a  |  b
----+-----
  3 |  11
  3 |  55
  8 |   9
  8 |  23
 16 |  41
 22 | 111

假设使用之前创建的投影，查询该投影会返回以下值：

VMart=> SELECT * FROM values_product_b0;
 a  |  b  | product
----+-----+---------
  3 |  11 |      33
  3 |  55 |     165
  8 |   9 |      72
  8 |  23 |     184
 16 |  41 |     656
 22 | 111 |    2442

5 - 系统表中的聚合信息

您可以查询以下系统表字段，了解有关实时聚合投影、Top-K 投影以及含表达式的投影的信息：