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

返回本页常规视图.

分析函数

以下几节介绍了如何优化 Vertica 支持的 SQL-99 分析函数。

1 - 空的 OVER 子句

OVER() 子句不需要窗口化子句。如果您的查询使用类似 SUM(x) 的分析函数，而且您指定了一个空 OVER() 子句，分析函数则会用作报告函数，其中整个输入会被视为单个分区；聚合会为结果集的每行返回相同的聚合值。查询在单个节点上执行，有可能导致性能低下。

如果您将 PARTITION BY 子句添加到 OVER() 子句，查询会在多个节点上执行，从而提升其性能。

2 - NULL 排序顺序

默认情况下，投影列值按升序存储，但是 NULL 值的位置取决于列的数据类型。

ORDER BY 子句的 NULL 位置差异

分析 OVER(window-order-clause) 与 SQL ORDER BY 子句的语义稍有不同：

OVER(ORDER BY ...)

分析窗口排序子句使用 ASC 或 DESC 排序顺序来确定分析函数结果的 NULLS FIRST 或 NULLS LAST 位置。NULL 值按如下方式放置：

  • ASC, NULLS LAST — NULL 值出现在排序结果的末尾。

  • DESC, NULLS FIRST — NULL 值出现在排序结果的开头。

(SQL) ORDER BY

SQL 和 Vertica ORDER BY 子句产生不同的结果。SQL ORDER BY 子句只指定升序或降序排序顺序。Vertica ORDER BY 子句根据列数据类型确定 NULL 位置：

  • NUMERIC、INTEGER、DATE、TIME、TIMESTAMP 和 INTERVAL 列： NULLS FIRST （NULL 值出现在排序投影的开头。）

  • FLOAT、STRING 和 BOOLEAN 列： NULLS LAST （NULL 值出现在排序投影的末尾。）

NULL 排序选项

在执行分析计算的查询中，如果不在意 NULL 的位置，或者您知道列中不包含任何 NULL 值，请指定 NULLS AUTO，而不考虑数据类型。Vertica 选择性能最快的放置，如以下查询所示。否则，请指定 NULLS FIRST 或 NULLS LAST。

=> SELECT x, RANK() OVER (ORDER BY x NULLS AUTO) FROM t;

如以下示例所示，您可以仔细构建查询，使 Vertica 避免对数据排序并提高查询速度。如 OVER(ORDER BY) 子句指定的那样，Vertica 会在列 x 上对来自表 t 的输入进行排序，然后对 RANK() 求值：

=> CREATE TABLE t (
    x FLOAT,
    y FLOAT );
=> CREATE PROJECTION t_p (x, y) AS SELECT * FROM t
   ORDER BY x, y UNSEGMENTED ALL NODES;
=> SELECT x, RANK() OVER (ORDER BY x) FROM t;

在之前的 SELECT 语句中，Vertica 不再使用 ORDER BY 子句，而且能迅速执行查询，因为列 x 是 FLOAT 数据类型。因此，投影排序顺序与分析默认排序 (ASC + NULLS LAST) 相匹配。当底层投影已经经过排序后，Vertica 也可以避免对数据进行排序。

但是，如果列 x 是 INTEGER 数据类型，则 Vertica 必须对数据进行排序，因为 INTEGER 数据类型的投影排序顺序 (ASC + NULLS FIRST) 与默认分析排序 (ASC + NULLS LAST) 不匹配。要帮助 Vertica 消除排序，请指定 NULL 的位置以与默认排序匹配：

=> SELECT x, RANK() OVER (ORDER BY x NULLS FIRST) FROM t;

如果列 x 是 STRING，以下查询就会消除排序：

=> SELECT x, RANK() OVER (ORDER BY x NULLS LAST) FROM t;

如果在之前的查询中忽略了 NULLS LAST，Vertica 会消除排序，因为 ASC + NULLS LAST 对于分析 ORDER BY 子句和 Vertica 中字符串相关的列来说是默认排序规范。

另请参阅

3 - 分析函数中 NULL 值的运行时排序

通过认真编写查询或创建设计（或同时开展这两项工作），您可以帮助 Vertica 查询优化器在执行分析函数时跳过对表中的所有列进行排序这项操作，从而提升查询性能。

要最大程度降低 Vertica 在查询执行期间对投影进行排序的需要，请重新定义 employee 表并指定排序字段不允许出现 NULL 值：

=> DROP TABLE employee CASCADE;
=> CREATE TABLE employee
   (empno INT,
    deptno INT NOT NULL,
    sal INT NOT NULL);
CREATE TABLE
=> CREATE PROJECTION employee_p AS
   SELECT * FROM employee
   ORDER BY deptno, sal;
CREATE PROJECTION
=> INSERT INTO employee VALUES(101,10,50000);
=> INSERT INTO employee VALUES(103,10,43000);
=> INSERT INTO employee VALUES(104,10,45000);
=> INSERT INTO employee VALUES(105,20,97000);
=> INSERT INTO employee VALUES(108,20,33000);
=> INSERT INTO employee VALUES(109,20,51000);
=> COMMIT;
COMMIT

=> SELECT * FROM employee;
 empno | deptno |  sal
-------+--------+-------
   101 |     10 | 50000
   103 |     10 | 43000
   104 |     10 | 45000
   105 |     20 | 97000
   108 |     20 | 33000
   109 |     20 | 51000
(6 rows)
=> SELECT deptno, sal, empno, RANK() OVER
     (PARTITION BY deptno ORDER BY sal)
   FROM employee;
 deptno |  sal  | empno | ?column?
--------+-------+-------+----------
     10 | 43000 |   103 |        1
     10 | 45000 |   104 |        2
     10 | 50000 |   101 |        3
     20 | 33000 |   108 |        1
     20 | 51000 |   109 |        2
     20 | 97000 |   105 |        3
(6 rows)