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

返回本页常规视图.

窗口框架

分析函数的窗口框架由一组与函数当前正在评估的行相对的行组成。分析函数处理此行及其窗口框架之后，Vertica 会将当前行向前移动，并相应地调整框架边界。如果 OVER 子句还指定了分区，Vertica 也会检查框架边界是否跨越了分区边界。此过程将不断重复，直到函数评估了最后一个分区的最后一个行。

指定窗口框架

您可以在分析函数的 OVER 子句中指定窗口框架，如下所示。

{ ROWS | RANGE } { BETWEEN start‑point AND end‑point } | start‑point

start-point / end‑point：

{ UNBOUNDED {PRECEDING | FOLLOWING}
  | CURRENT ROW
  | constant-value  {PRECEDING | FOLLOWING}}

start‑point 和 end‑point 指定相对于当前行的窗口框架偏移。ROWS 和 RANGE 关键字指定偏移是物理偏移还是逻辑偏移。如果仅指定起始点，Vertica 将创建一个从此点到当前行的窗口。

有关语法的详细信息，请参阅窗口框架子句。

要求

要指定窗口框架，OVER 还必须指定窗口顺序 (ORDER BY) 子句。如果 OVER 子句包含窗口顺序子句，但没有指定窗口框架，则函数会创建从当前分区的第一行扩展到当前行的默认框架。它等同于以下子句：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

窗口聚合函数

支持窗口框架的分析函数称为窗口聚合函数。它们返回移动平均数和累计结果等信息。要将以下函数用作窗口（分析）聚合函数，而不是基本聚合函数，OVER 子句必须指定窗口顺序子句和（可选）窗口框架子句。如果 OVER 子句没有指定窗口框架，则函数将如前所述创建默认窗口框架。

以下分析函数支持窗口框架：

包含空 OVER 子句的窗口聚合函数不会创建窗口框架。如果所有输入都视为一个分区，则函数将用作报告函数。

1 - 具有物理偏移的窗口 (ROWS)

窗口物理框架子句中的 ROWS 关键字将窗口大小指定为相对于当前行的行数。值只能为 INTEGER 数据类型。

示例

此页面中的示例使用 emp 表架构：

CREATE TABLE emp(deptno INT, sal INT, empno INT);
INSERT INTO emp VALUES(10,101,1);
INSERT INTO emp VALUES(10,104,4);
INSERT INTO emp VALUES(20,100,11);
INSERT INTO emp VALUES(20,109,7);
INSERT INTO emp VALUES(20,109,6);
INSERT INTO emp VALUES(20,109,8);
INSERT INTO emp VALUES(20,110,10);
INSERT INTO emp VALUES(20,110,9);
INSERT INTO emp VALUES(30,102,2);
INSERT INTO emp VALUES(30,103,3);
INSERT INTO emp VALUES(30,105,5);
COMMIT;

以下查询调用 COUNT 来对当前行及其前面的行（最多两行）进行计数：

SELECT deptno, sal, empno,  COUNT(*) OVER
         (PARTITION BY deptno ORDER BY sal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
        AS count FROM emp;

OVER 子句包含三个组件：

  • 窗口分区子句 PARTITION BY deptno

  • 排序依据子句 ORDER BY sal

  • 窗口框架子句ROWS BETWEEN 2 PRECEDING AND CURRENT ROW。此子句将窗口大小定义为从当前行扩展到当前行前面的两个行。

查询返回的结果分为三个分区，在下文中以红线表示。在第二个分区 (deptno=20) 中，COUNT 按以下方式处理窗口框架子句：

  1. 创建第一个窗口（绿方框）。此窗口包含一个行，因为当前行（蓝方框）也是此分区的第一个行。因此，count 列中的值显示了当前窗口中的行数，即 1：

  2. COUNT 处理此分区的第一行之后，它会将当前行重置为此分区的第二个行。窗口此时跨越了当前行和前面的一个行，因此，COUNT 返回了值 2：

  3. COUNT 处理此分区的第二行之后，它会将当前行重置为此分区的第三个行。窗口此时跨越了当前行和前面的两个行，因此，COUNT 返回了值 3：

  4. 此后，COUNT 继续处理剩余的分区行，并相应地移动窗口，但窗口大小 (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 不变，仍为三行。因此，count 列中的值也不变 (3)：




2 - 具有逻辑偏移的窗口 (RANGE)

RANGE 关键字将分析窗口框架定义为当前行的逻辑偏移。

对于每个行，分析函数使用窗口顺序子句 (ORDER_BY) 列或表达式来计算窗口框架大小，具体如下所述：

  1. 在当前分区中，根据连续行的 ORDER_BY 值评估当前行的 ORDER_BY 值。

  2. 确定这些行中有哪些行符合相对于当前行的指定范围要求。

  3. 创建仅包含这些行的窗口框架。

  4. 在当前窗口中执行。

示例

此示例使用 property_sales 表，此表包含街区的住宅销售数据：

=> SELECT property_key, neighborhood, sell_price FROM property_sales ORDER BY neighborhood, sell_price;
 property_key | neighborhood  | sell_price
--------------+---------------+------------
        10918 | Jamaica Plain |     353000
        10921 | Jamaica Plain |     450000
        10927 | Jamaica Plain |     450000
        10922 | Jamaica Plain |     474000
        10919 | Jamaica Plain |     515000
        10917 | Jamaica Plain |     675000
        10924 | Jamaica Plain |     675000
        10920 | Jamaica Plain |     705000
        10923 | Jamaica Plain |     710000
        10926 | Jamaica Plain |     875000
        10925 | Jamaica Plain |     900000
        10930 | Roslindale    |     300000
        10928 | Roslindale    |     422000
        10932 | Roslindale    |     450000
        10929 | Roslindale    |     485000
        10931 | Roslindale    |     519000
        10938 | West Roxbury  |     479000
        10933 | West Roxbury  |     550000
        10937 | West Roxbury  |     550000
        10934 | West Roxbury  |     574000
        10935 | West Roxbury  |     598000
        10936 | West Roxbury  |     615000
        10939 | West Roxbury  |     720000
(23 rows)

AVG 分析函数可以获取每个街区的近似销售价格的平均值。对于每个住宅，下列查询计算了销售价格比该住宅高或低 50000 美元的所有其他街区住宅的平均销售价格：

=> SELECT property_key, neighborhood, sell_price, AVG(sell_price) OVER(
     PARTITION BY neighborhood ORDER BY sell_price
     RANGE BETWEEN 50000 PRECEDING and 50000 FOLLOWING)::int AS comp_sales
     FROM property_sales ORDER BY neighborhood;
 property_key | neighborhood  | sell_price | comp_sales
--------------+---------------+------------+------------
        10918 | Jamaica Plain |     353000 |     353000
        10927 | Jamaica Plain |     450000 |     458000
        10921 | Jamaica Plain |     450000 |     458000
        10922 | Jamaica Plain |     474000 |     472250
        10919 | Jamaica Plain |     515000 |     494500
        10917 | Jamaica Plain |     675000 |     691250
        10924 | Jamaica Plain |     675000 |     691250
        10920 | Jamaica Plain |     705000 |     691250
        10923 | Jamaica Plain |     710000 |     691250
        10926 | Jamaica Plain |     875000 |     887500
        10925 | Jamaica Plain |     900000 |     887500
        10930 | Roslindale    |     300000 |     300000
        10928 | Roslindale    |     422000 |     436000
        10932 | Roslindale    |     450000 |     452333
        10929 | Roslindale    |     485000 |     484667
        10931 | Roslindale    |     519000 |     502000
        10938 | West Roxbury  |     479000 |     479000
        10933 | West Roxbury  |     550000 |     568000
        10937 | West Roxbury  |     550000 |     568000
        10934 | West Roxbury  |     574000 |     577400
        10935 | West Roxbury  |     598000 |     577400
        10936 | West Roxbury  |     615000 |     595667
        10939 | West Roxbury  |     720000 |     720000
(23 rows)

AVG 按以下方式处理此查询：

  1. AVG 评估了第一个分区 (Jamaica Plain) 的第 1 行，但没有发现比此行的 sell_price（353000 美元）高或低 50000 美元范围内的销售价格。 AVG 创建了一个仅包含此行的窗口，并为第 1 行返回了平均值 353000 美元：

  2. AVG 评估了第 2 行，发现了有三个 sell_price 值与当前行的相差幅度在 50000 美元范围内。 AVG 创建了一个包含了这三个行的窗口，并为第 2 行返回了平均值 458000 美元：

  3. AVG 评估了第 3 行，同样发现了有三个 sell_price 值与当前行的相差幅度在 50000 美元范围内。 AVG 创建了一个窗口，它与前面的窗口相同，并为第 3 行返回了相同的平均值 458000 美元：

  4. AVG 评估了第 4 行，发现了有四个 sell_price 值与当前行的相差幅度在 50000 美元范围内。 AVG 将窗口扩展到可包含第 2 行至第 5 行，并为第 4 行返回了平均值 472250 美元：

  5. 同样地，AVG 还评估了此分区的其他行。当函数评估第二个分区 (Roslindale) 的第一行时，它会如下所述重置窗口：

限制

如果 RANGE 指定一个常数值，此值的数据类型与窗口的 ORDER BY 数据类型必须相同。以下情况除外：

  • RANGE 如果窗口顺序子句数据类型为以下之一，可以指定 INTERVAL Year to Month：TIMESTAMP、TIMESTAMP WITH TIMEZONE 或 DATE。 TIME 和 TIME WITH TIMEZONE 均不受支持。

  • RANGE 如果窗口顺序子句数据为以下之一，可以指定 INTERVAL Day to Second：TIMESTAMP、TIMESTAMP WITH TIMEZONE、DATE、TIME 或 TIME WITH TIMEZONE。

窗口顺序子句必须指定以下数据类型之一：NUMERIC、DATE/TIME、FLOAT 或 INTEGER。如果窗口指定了以下框架之一，则会忽视此要求：

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

3 - 报告聚合

采用 window-frame-clause 的某些分析函数是报告聚合函数。通过这些函数，您可以比较分区的聚合值与明细行，而不使用相关子查询或联接。

如果结合空 OVER() 子句使用窗口聚合，分析函数将用作报告函数，而且整个输入会被视为一个分区。

关于标准差函数和方差函数

使用标准差函数时，较小的标准差表示数据点通常非常接近于平均值，而较大的标准差表示数据点分散在一个较大的值范围内。

标准差通常用图形表示，而分布式标准差为典型的钟形曲线。

方差函数会衡量一组数值的分散距离。

示例

将报告聚合的窗口看作是定义为 UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING 的窗口。忽略 window-order-clause 会使分区中的所有行也位于窗口中（报告聚合）。

=> SELECT deptno, sal, empno, COUNT(sal)
  OVER (PARTITION BY deptno) AS COUNT FROM emp;
 deptno | sal | empno | count
--------+-----+-------+-------
     10 | 101 |     1 |     2
     10 | 104 |     4 |     2
------------------------------
     20 | 110 |    10 |     6
     20 | 110 |     9 |     6
     20 | 109 |     7 |     6
     20 | 109 |     6 |     6
     20 | 109 |     8 |     6
     20 | 100 |    11 |     6
------------------------------
     30 | 105 |     5 |     3
     30 | 103 |     3 |     3
     30 | 102 |     2 |     3
(11 rows)

如果上述查询中的 OVER() 子句包含 window-order-clause （例如 ORDER BY sal），它将变为包含 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 默认窗口的移动窗口（窗口聚合）查询：

=> SELECT deptno, sal, empno, COUNT(sal)  OVER (PARTITION BY deptno ORDER BY sal) AS COUNT FROM emp;
 deptno | sal | empno | count
--------+-----+-------+-------
     10 | 101 |     1 |     1
     10 | 104 |     4 |     2
------------------------------
     20 | 100 |    11 |     1
     20 | 109 |     7 |     4
     20 | 109 |     6 |     4
     20 | 109 |     8 |     4
     20 | 110 |    10 |     6
     20 | 110 |     9 |     6
------------------------------
     30 | 102 |     2 |     1
     30 | 103 |     3 |     2
     30 | 105 |     5 |     3
(11 rows)

LAST_VALUE 怎么样？

您可能会想为什么您不能只使用 LAST_VALUE() 分析函数。

例如，对于每个员工，获取部门中最高薪水：

=> SELECT deptno, sal, empno,LAST_VALUE(empno) OVER (PARTITION BY deptno ORDER BY sal) AS lv FROM emp;
![](/images/reporting-aggregates2.png)

受默认窗口语义影响，LAST_VALUE 并非总是会返回分区的最后一个值。如果在分析子句中忽略 window-frame-clause，则 LAST_VALUE 会在默认窗口中运行。因此，结果似乎并不直观，因为函数没有返回当前分区的最后一个值。它只返回了窗口的最后一个值，并且此值随当前正在处理的输入行而不断变化。

请记住默认窗口：

OVER (PARTITION BY deptno ORDER BY sal)

等同于：

OVER(PARTITION BY deptno ORDER BY salROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

如果您要返回分区的最后一个值，请使用 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

=> SELECT deptno, sal, empno, LAST_VALUE(empno)
OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM emp;
![](/images/reporting-aggregates9.png)

Vertica 建议结合 window-order-clause 使用 LAST_VALUE 以生成具有确定性的结果。

在以下示例中，empno 6、7 和 8 的薪水相同，因此它们位于相邻行中。在本例中，empno 8 首先出现，但不能保证该顺序。

请注意，在上述输出中，最后一个值是 7，它是分区 deptno = 20 的最后一个行。如果各行的顺序不同，则函数会返回不同的值：

此时，最后一个值是 6，它是分区 deptno = 20 的最后一个行。解决方案是将唯一键添加到排序顺序中。即使查询的顺序发生变化，结果也始终相同，因此非常确定。

=> SELECT deptno, sal, empno, LAST_VALUE(empno)
OVER (PARTITION BY deptno ORDER BY sal, empno
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lv
FROM emp;

请注意行现在如何按 empno 进行排序，这时最后一个值一直是 8，而且无论查询顺序如何都不会造成影响。