窗口函数的语法和用法¶

本主题内容：

Snowflake 支持大量称为 窗口函数 的分析 SQL 函数。每个函数的详细信息记录在单独的参考页面上。本部分的目的是提供适用于部分或全部窗口函数的一般参考信息，包括 OVER 子句的主要组件的详细语法：

PARTITION BY 子句
ORDER BY 子句
窗口框架语法

不熟悉窗口函数的用户可能需要阅读使用窗口函数分析数据中的概念讲解材料。

语法¶

<function> ( [ <arguments> ] ) OVER ( [ <windowDefinition> ] )

Copy

其中：

windowDefinition ::=

[ PARTITION BY <expr1> [, ...] ]
[ ORDER BY <expr2> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ <windowFrameClause> ]

Copy

其中：

windowFrameClause ::=

{
    { ROWS | RANGE } UNBOUNDED PRECEDING
  | { ROWS | RANGE } <n> PRECEDING
  | { ROWS | RANGE } CURRENT ROW
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND <n> { PRECEDING | FOLLOWING }
  | { ROWS | RANGE } BETWEEN <n> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

Copy

参数¶

OVER( [ windowDefinition ] )

指定函数用作窗口函数，并指定函数的运行窗口。OVER 子句必须包含括号，但可以为空，具体取决于相关函数的要求。空白 OVER 子句没有分区和隐含的默认窗口框架。

PARTITION BY expr1

例如，按产品、城市或年份将行分组到分区。输入行按分区分组，然后在每个分区上计算函数。PARTITION BY 子句可选；您可以将一组行作为单个分区进行分析。

ORDER BY expr2

对每个分区内的行进行排序，如果未指定分区，则对整组行进行排序。此 ORDER BY 子句与 ORDER BY 子句不同，后者控制查询的最终结果中返回的所有行的顺序。尽管 ORDER BY 子句对于某些窗口函数来说是可选的，但对于其他窗口函数来说是必需的。例如，RANK 和 NTILE 等排名窗口函数要求其输入数据采用有意义的顺序。

关于 ASC/DESC（升序/降序）顺序和 NULL 处理，窗口函数的 ORDER BY 子句遵循的规则类似于查询中主要 ORDER BY 子句的规则。有关详细信息，请参阅 ORDER BY。

不同的函数以不同的方式处理 ORDER BY 子句：

某些窗口函数需要 ORDER BY 子句。
某些窗口函数禁止 ORDER BY 子句。
某些窗口函数使用 ORDER BY 子句（如果存在），但不需要该子句。
某些窗口函数将隐式窗口框架应用于 ORDER BY 子句。（有关更多信息，请参阅窗口框架的使用注意事项。）

{ ROWS | RANGE }

指定窗口框架的类型或模式，定义物理行数 (ROWS) 或一组逻辑计算的行 (RANGE)。请参阅基于范围的窗口框架与基于行的窗口框架。

这两种类型的框架都使用隐式命名边界或显式偏移值来指定起点和终点。使用关键字 CURRENT ROW、UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING 定义命名边界。使用数字或间隔（n PRECEDING 或 n FOLLOWING）定义显式偏移。

{ RANGE BETWEEN n PRECEDING | n FOLLOWING }

指定采用显式偏移的基于范围的窗口框架。

采用显式偏移的 RANGE BETWEEN 窗口框架必须只有一个 ORDER BY 表达式。该表达式支持以下数据类型：

DATE、TIMESTAMP、TIMESTAMP_LTZ、TIMESTAMP_NTZ (DATETIME)、TIMESTAMP_TZ
NUMBER，包括 INT、FLOAT 等

使用这种类型的窗口框架时，不支持 TIME 和其他 Snowflake 数据类型。对于其他窗口框架，可以在 ORDER BY 表达式中使用其他数据类型，例如 VARCHAR。

对于 RANGE BETWEEN 窗口框架，n 必须是无符号常量（正数值，包括 0）或正 INTERVAL 常量：

如果 expr2 是数值数据类型，则 n 必须是无符号常量。
如果 expr2 是 TIMESTAMP 数据类型，则 n 必须是 INTERVAL 常量。例如：INTERVAL '12 hours' 或 INTERVAL '3 days'。
如果 expr2 是 DATE 数据类型，则 n 可以是无符号常量或 INTERVAL 常量，但框架的开始和结束必须为 n 值使用相同的数据类型。

当 ORDER BY 表达式为升序 (ASC) 时，语法 n FOLLOWING 表示“值大于（或晚于）*x* 的行”，n PRECEDING 表示“值小于（或早于）*x* 的行”，其中 x 是当前行的 ORDER BY 值。当 ORDER BY 表达式为降序 (DESC) 时，情况正好相反。（偏移 0 PRECEDING 和 0 FOLLOWING 等于 CURRENT ROW。）

RANGE BETWEEN 限制¶

以下窗口函数子集支持采用显式偏移的 RANGE BETWEEN 语法：

COUNT、SUM、MIN、MAX、AVG

STDDEV、STDDEV_SAMP、:doc:`/sql-reference/functions/stddev_pop`（以及别名）

VARIANCE、VARIANCE_SAMP、:doc:`/sql-reference/functions/variance_pop`（以及别名）

COUNT_IF

FIRST_VALUE、LAST_VALUE

ARRAY_AGG

此外，请注意：

这些函数的 DISTINCT 版本不支持此语法。
当 COUNT 窗口函数使用此语法时，以下限制适用。
- 仅支持一个输入实参。
- 不支持 COUNT(table.*) 通配符查询。例如，您不能指定：
  COUNT(t1.*) OVER(ORDER BY col1 RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  Copy
您不能指定会导致框架开始和结束位置逻辑反转的框架。例如，以下框架返回错误，因为框架的结束行在开始行之前：
```
ORDER BY col1 ASC RANGE BETWEEN 2 PRECEDING AND 4 PRECEDING
ORDER BY col1 ASC RANGE BETWEEN 2 FOLLOWING AND 2 PRECEDING
```
Copy

当 ORDER BY 表达式包含 NULL 值时的 RANGE BETWEEN 行为¶

当使用 RANGE BETWEEN 窗口框架并且 ORDER BY 列包含 NULL 值时，请注意以下行为：

当 ORDER BY 子句指定 NULLS FIRST 时，ORDER BY 列中带 NULL 的行包含在 UNBOUNDED PRECEDING 框架中。
当 ORDER BY 子句指定 NULLS LAST 时，ORDER BY 列中带 NULL 的行包含在 UNBOUNDED FOLLOWING 框架中。
仅当当前行的 ORDER BY 值为 NULL 时，ORDER BY 列中带 NULL 的行才会包含在显式偏移框架边界中。

请参阅 ORDER BY 子句中具有 NULL 值的 RANGE BETWEEN 示例。

窗口框架的使用注意事项¶

所有窗口函数都支持窗口框架。但是，对窗口框架语法的支持因函数而异。如果未指定窗口框架，则默认值取决于以下函数：
- 对于非排名函数（例如 COUNT、MAX、MIN 和 SUM），默认为以下窗口框架（遵循 ANSI 标准）：
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  Copy
- 对于排名函数（例如 FIRST_VALUE、LAST_VALUE、NTH_VALUE），默认为整个窗口：
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  Copy
  请注意，这种行为 不符合 ANSI 标准。
  
  备注
  
  为清晰起见，Snowflake 建议避免使用隐式窗口框架。如果查询使用窗口框架，请定义显式窗口框架。
窗口框架要求窗口中的数据按已知顺序排列。因此，在窗口框架语法中，OVER 子句内的 ORDER BY 子句是 必需的，但 ORDER BY 子句通常是可选的。

示例¶

本部分包含示例，展示了如何以不同方式使用窗口函数。有关其他示例，请参阅使用窗口函数分析数据和各个函数的页面。

介绍性示例¶

假设您是某连锁店的所有者。以下查询会显示每家商店产生的利润在连锁店总利润中所占的百分比。查询使用 RATIO_TO_REPORT 函数，该函数从当前行中获取一个值 (net_profit)，并将其除以所有其他行中相应值的总和：

创建并加载表：

CREATE TRANSIENT TABLE store_sales (
    branch_ID    INTEGER,
    city        VARCHAR,
    gross_sales NUMERIC(9, 2),
    gross_costs NUMERIC(9, 2),
    net_profit  NUMERIC(9, 2)
    );

INSERT INTO store_sales (branch_ID, city, gross_sales, gross_costs)
    VALUES
    (1, 'Vancouver', 110000, 100000),
    (2, 'Vancouver', 140000, 125000),
    (3, 'Montreal', 150000, 140000),
    (4, 'Montreal', 155000, 146000);

UPDATE store_sales SET net_profit = gross_sales - gross_costs;

Copy

查询表：

SELECT branch_ID,
       net_profit,
       100 * RATIO_TO_REPORT(net_profit) OVER () AS percent_of_chain_profit
    FROM store_sales AS s1
    ORDER BY branch_ID;
+-----------+------------+-------------------------+
| BRANCH_ID | NET_PROFIT | PERCENT_OF_CHAIN_PROFIT |
|-----------+------------+-------------------------|
|         1 |   10000.00 |             22.72727300 |
|         2 |   15000.00 |             34.09090900 |
|         3 |   10000.00 |             22.72727300 |
|         4 |    9000.00 |             20.45454500 |
+-----------+------------+-------------------------+

Copy

起始位置未限定的窗口框架¶

创建表并使用以下值填充表：

CREATE OR REPLACE TABLE example_cumulative (p INT, o INT, i INT);

INSERT INTO example_cumulative VALUES
    (  0, 1, 10), (0, 2, 20), (0, 3, 30),
    (100, 1, 10),(100, 2, 30),(100, 2, 5),(100, 3, 11),(100, 3, 120),
    (200, 1, 10000),(200, 1, 200),(200, 1, 808080),(200, 2, 33333),(200, 3, null), (200, 3, 4),
    (300, 1, null), (300, 1, null);

Copy

运行一个使用起始位置未限定的窗口框架的查询，然后显示输出。返回每个分区中每行的累积 COUNT、SUM、AVG、MIN 和 MAX 值：

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Rows_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Rows_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Rows_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Rows_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_i_Rows_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+
|   P | O |      I | COUNT_I_ROWS_PRE | SUM_I_ROWS_PRE | AVG_I_ROWS_PRE | MIN_I_ROWS_PRE | MAX_I_ROWS_PRE |
|-----+---+--------+------------------+----------------+----------------+----------------+----------------|
|   0 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
|   0 | 2 |     20 |                2 |             30 |         15.000 |             10 |             20 |
|   0 | 3 |     30 |                3 |             60 |         20.000 |             10 |             30 |
| 100 | 1 |     10 |                1 |             10 |         10.000 |             10 |             10 |
| 100 | 2 |     30 |                2 |             40 |         20.000 |             10 |             30 |
| 100 | 2 |      5 |                3 |             45 |         15.000 |              5 |             30 |
| 100 | 3 |     11 |                4 |             56 |         14.000 |              5 |             30 |
| 100 | 3 |    120 |                5 |            176 |         35.200 |              5 |            120 |
| 200 | 1 |  10000 |                1 |          10000 |      10000.000 |          10000 |          10000 |
| 200 | 1 |    200 |                2 |          10200 |       5100.000 |            200 |          10000 |
| 200 | 1 | 808080 |                3 |         818280 |     272760.000 |            200 |         808080 |
| 200 | 2 |  33333 |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |   NULL |                4 |         851613 |     212903.250 |            200 |         808080 |
| 200 | 3 |      4 |                5 |         851617 |     170323.400 |              4 |         808080 |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
| 300 | 1 |   NULL |                0 |           NULL |           NULL |           NULL |           NULL |
+-----+---+--------+------------------+----------------+----------------+----------------+----------------+

Copy

使用默认窗口框架返回与上述查询相同的结果（即 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）：

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o) max_i_Range_Pre
  FROM example_cumulative
  ORDER BY p,o;
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+
|   P | O |      I | COUNT_I_RANGE_PRE | SUM_I_RANGE_PRE | AVG_I_RANGE_PRE | MIN_I_RANGE_PRE | MAX_I_RANGE_PRE |
|-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------|
|   0 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
|   0 | 2 |     20 |                 2 |              30 |       15.000000 |              10 |              20 |
|   0 | 3 |     30 |                 3 |              60 |       20.000000 |              10 |              30 |
| 100 | 1 |     10 |                 1 |              10 |       10.000000 |              10 |              10 |
| 100 | 2 |     30 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 2 |      5 |                 3 |              45 |       15.000000 |               5 |              30 |
| 100 | 3 |     11 |                 5 |             176 |       35.200000 |               5 |             120 |
| 100 | 3 |    120 |                 5 |             176 |       35.200000 |               5 |             120 |
| 200 | 1 |  10000 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 |    200 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 1 | 808080 |                 3 |          818280 |   272760.000000 |             200 |          808080 |
| 200 | 2 |  33333 |                 4 |          851613 |   212903.250000 |             200 |          808080 |
| 200 | 3 |   NULL |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 200 | 3 |      4 |                 5 |          851617 |   170323.400000 |               4 |          808080 |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
| 300 | 1 |   NULL |                 0 |            NULL |            NULL |            NULL |            NULL |
+-----+---+--------+-------------------+-----------------+-----------------+-----------------+-----------------+

Copy

带显式偏移的窗口框架¶

创建表并使用以下值填充表：

CREATE TABLE example_sliding
  (p INT, o INT, i INT, r INT, s VARCHAR(100));

INSERT INTO example_sliding VALUES
  (100,1,1,70,'seventy'),(100,2,2,30, 'thirty'),(100,3,3,40,'forty'),(100,4,NULL,90,'ninety'),
  (100,5,5,50,'fifty'),(100,6,6,30,'thirty'),
  (200,7,7,40,'forty'),(200,8,NULL,NULL,'n_u_l_l'),(200,9,NULL,NULL,'n_u_l_l'),(200,10,10,20,'twenty'),
  (200,11,NULL,90,'ninety'),
  (300,12,12,30,'thirty'),
  (400,13,NULL,20,'twenty');

Copy

返回在当前行（含）之前、之后的滑动窗口中两列（数字和字符串）的 MIN 函数结果：

select p, o, i AS i_col,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_i_3P_1P,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_i_1F_3F,
    MIN(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_i_1P_3F,
    s,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) min_s_3P_1P,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) min_s_1F_3F,
    MIN(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) min_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MIN_I_3P_1P | MIN_I_1F_3F | MIN_I_1P_3F | S       | MIN_S_3P_1P | MIN_S_1F_3F | MIN_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           2 |           1 | seventy | NULL        | forty       | forty       |
| 100 |  2 |     2 |           1 |           3 |           1 | thirty  | seventy     | fifty       | fifty       |
| 100 |  3 |     3 |           1 |           5 |           2 | forty   | seventy     | fifty       | fifty       |
| 100 |  4 |  NULL |           1 |           5 |           3 | ninety  | forty       | fifty       | fifty       |
| 100 |  5 |     5 |           2 |           6 |           5 | fifty   | forty       | thirty      | fifty       |
| 100 |  6 |     6 |           3 |        NULL |           5 | thirty  | fifty       | NULL        | fifty       |
| 200 |  7 |     7 |        NULL |          10 |           7 | forty   | NULL        | n_u_l_l     | forty       |
| 200 |  8 |  NULL |           7 |          10 |           7 | n_u_l_l | forty       | n_u_l_l     | forty       |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | ninety      | n_u_l_l     |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | forty       | ninety      | n_u_l_l     |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | n_u_l_l     | NULL        | ninety      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

返回在当前行（含）之前、之后的滑动窗口中两列（数字和字符串）的 MAX 函数结果：

SELECT p, o, i AS i_col,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_i_3P_1P,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_i_1F_3F,
    MAX(i) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_i_1P_3F,
    s,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) max_s_3P_1P,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING) max_s_1F_3F,
    MAX(s) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING) max_s_1P_3F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+
|   P |  O | I_COL | MAX_I_3P_1P | MAX_I_1F_3F | MAX_I_1P_3F | S       | MAX_S_3P_1P | MAX_S_1F_3F | MAX_S_1P_3F |
|-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------|
| 100 |  1 |     1 |        NULL |           3 |           3 | seventy | NULL        | thirty      | thirty      |
| 100 |  2 |     2 |           1 |           5 |           5 | thirty  | seventy     | ninety      | thirty      |
| 100 |  3 |     3 |           2 |           6 |           6 | forty   | thirty      | thirty      | thirty      |
| 100 |  4 |  NULL |           3 |           6 |           6 | ninety  | thirty      | thirty      | thirty      |
| 100 |  5 |     5 |           3 |           6 |           6 | fifty   | thirty      | thirty      | thirty      |
| 100 |  6 |     6 |           5 |        NULL |           6 | thirty  | ninety      | NULL        | thirty      |
| 200 |  7 |     7 |        NULL |          10 |          10 | forty   | NULL        | twenty      | twenty      |
| 200 |  8 |  NULL |           7 |          10 |          10 | n_u_l_l | forty       | twenty      | twenty      |
| 200 |  9 |  NULL |           7 |          10 |          10 | n_u_l_l | n_u_l_l     | twenty      | twenty      |
| 200 | 10 |    10 |           7 |        NULL |          10 | twenty  | n_u_l_l     | ninety      | twenty      |
| 200 | 11 |  NULL |          10 |        NULL |          10 | ninety  | twenty      | NULL        | twenty      |
| 300 | 12 |    12 |        NULL |        NULL |          12 | thirty  | NULL        | NULL        | thirty      |
| 400 | 13 |  NULL |        NULL |        NULL |        NULL | twenty  | NULL        | NULL        | twenty      |
+-----+----+-------+-------------+-------------+-------------+---------+-------------+-------------+-------------+

Copy

返回包含当前行之前、之后和当前行的滑动窗口中的数字列的总和：

SELECT p, o, r AS r_col,
    SUM(r) OVER (PARTITION BY p ORDER BY o ROWS BETWEEN 4 PRECEDING AND 2 PRECEDING) sum_r_4P_2P,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 FOLLOWING AND 4 FOLLOWING) sum_r_2F_4F,
    sum(r) over (partition by p ORDER BY o ROWS BETWEEN 2 PRECEDING AND 4 FOLLOWING) sum_r_2P_4F
  FROM example_sliding
  ORDER BY p, o;
+-----+----+-------+-------------+-------------+-------------+
|   P |  O | R_COL | SUM_R_4P_2P | SUM_R_2F_4F | SUM_R_2P_4F |
|-----+----+-------+-------------+-------------+-------------|
| 100 |  1 |    70 |        NULL |         180 |         280 |
| 100 |  2 |    30 |        NULL |         170 |         310 |
| 100 |  3 |    40 |          70 |          80 |         310 |
| 100 |  4 |    90 |         100 |          30 |         240 |
| 100 |  5 |    50 |         140 |        NULL |         210 |
| 100 |  6 |    30 |         160 |        NULL |         170 |
| 200 |  7 |    40 |        NULL |         110 |         150 |
| 200 |  8 |  NULL |        NULL |         110 |         150 |
| 200 |  9 |  NULL |          40 |          90 |         150 |
| 200 | 10 |    20 |          40 |        NULL |         110 |
| 200 | 11 |    90 |          40 |        NULL |         110 |
| 300 | 12 |    30 |        NULL |        NULL |          30 |
| 400 | 13 |    20 |        NULL |        NULL |          20 |
+-----+----+-------+-------------+-------------+-------------+

Copy

排名函数示例¶

以下示例演示了如何根据每位销售人员的销售总额（以美元为单位）对销售人员进行排名。OVER 子句中的 ORDER BY 子句按降序（从高到低）对总额进行排序。查询会计算每个销售人员相对于所有其他销售人员的排名。

创建表并插入数据：

CREATE TABLE sales_table (salesperson_name VARCHAR, sales_in_dollars INTEGER);
INSERT INTO sales_table (salesperson_name, sales_in_dollars) VALUES
    ('Smith', 600),
    ('Jones', 1000),
    ('Torkelson', 700),
    ('Dolenz', 800);

Copy

现在，查询数据：

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

输出不一定按排名排序。要显示按排名排序的结果，请为查询本身指定 ORDER BY 子句（窗口函数的 ORDER BY 子句之外）,如下所示：

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY 3;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Jones            |             1000 |          1 |
| Dolenz           |              800 |          2 |
| Torkelson        |              700 |          3 |
| Smith            |              600 |          4 |
+------------------+------------------+------------+

Copy

前面的示例有两个 ORDER BY 子句：

一个控制排名的顺序。
一个控制输出的顺序。

这些子句是独立的。例如，您可以按总销售额对排名进行排序（如上所示），但按销售人员的姓氏对输出行进行排序：

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table
  ORDER BY salesperson_name;
+------------------+------------------+------------+
| SALESPERSON_NAME | SALES_IN_DOLLARS | SALES_RANK |
|------------------+------------------+------------|
| Dolenz           |              800 |          2 |
| Jones            |             1000 |          1 |
| Smith            |              600 |          4 |
| Torkelson        |              700 |          3 |
+------------------+------------------+------------+

Copy

采用显式数字偏移的 RANGE BETWEEN 示例¶

以下示例使用带显式数字偏移的 RANGE BETWEEN 语法。要运行此示例，请遵循以下说明：创建并加载 menu_items 表。有关使用 INTERVAL 偏移的类似示例，请参阅使用窗口聚合进行滚动计算。

以下查询计算餐车提供的菜单项目类别的平均商品销售成本。窗口函数不划分结果；因此，在基于范围的框架的前提下，在整个结果集中计算平均值。

框架的边界是当前行中的商品价值成本加 2（例如，第一行 = 0.50 + 2.00）。当行在此两美元范围内时，它们才有资格加入框架。

SELECT menu_category, menu_cogs_usd,
    AVG(menu_cogs_usd)
      OVER(ORDER BY menu_cogs_usd RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING) avg_cogs
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  GROUP BY menu_category, menu_cogs_usd
  ORDER BY menu_category, menu_cogs_usd;

Copy

+---------------+---------------+----------+
| MENU_CATEGORY | MENU_COGS_USD | AVG_COGS |
|---------------+---------------+----------|
| Beverage      |          0.50 |  1.18333 |
| Beverage      |          0.65 |  1.37857 |
| Beverage      |          0.75 |  1.50000 |
| Dessert       |          0.50 |  1.18333 |
| Dessert       |          1.00 |  1.87500 |
| Dessert       |          1.25 |  2.05000 |
| Dessert       |          2.50 |  3.16666 |
| Dessert       |          3.00 |  3.50000 |
| Snack         |          1.25 |  2.05000 |
| Snack         |          2.25 |  2.93750 |
| Snack         |          4.00 |  4.00000 |
+---------------+---------------+----------+

例如，第一行的 avg_cogs 值为 1.1833。计算方法是，介于 0.50 和 2.50 之间的所有 menu_cogs_usd 值的和除以这些行的计数：

(0.50 + 0.65 + 0.75 + 0.50 + 1.00 + 1.25 + 2.50 + 1.25 + 2.25) / 9 = 1.18333

倒数第二行的 avg_cogs 值为 2.93750。计算方法是：介于 2.25 和 4.25 之间的所有 menu_cogs_usd 值的和除以这些行的计数：

(2.25 + 2.50 + 3.00 + 4.00) / 4 = 2.93750

最后一行为 avg_cogs 和 menu_cogs_usd 返回 4.0。这个结果是准确的，因为只有这一行属于框架；4.0 是整个结果中的最大 menu_cogs_usd 值，所以它变成了单行框架。它没有“跟随”行。

请注意，此查询有窗口函数的 ORDER BY 子句和查询最终结果的 ORDER BY 子句。最终的 ORDER BY 输出不影响窗口函数结果的计算。用于计算函数的有序结果集是最终查询不显示的中间结果集。

ORDER BY 子句中具有 NULL 值的 RANGE BETWEEN 示例¶

nulls 表包含五行，两行在 c1 列中有 NULL 值。创建并加载表，如下所示：

CREATE OR REPLACE TABLE nulls(c1 int, c2 int);

INSERT INTO nulls VALUES
  (1,10),
  (2,20),
  (3,30),
  (NULL,20),
  (NULL,50);

Copy

当指定 NULLS LAST ，并且窗口框架使用显式偏移时，仅当当前行的 ORDER BY 值为 NULL 时，框架才会包含 c1 中带 NULL 的行。当行 3 为当前行时，以下查询返回和 50。框架不包含以下 NULL 行。

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) sum_c2
  FROM nulls;

Copy

+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |     30 |
|             2 | 20 |     60 |
|             3 | 30 |     50 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

当指定 NULLS LAST，而窗口框架使用 UNBOUNDED FOLLOWING 时，框架包含 c1 中带 NULL 的行。当行 120 为当前行时，以下查询返回和 3。框架包含以下两个 NULL 行。

SELECT c1 c1_nulls_last, c2,
    SUM(c2) OVER(ORDER BY c1 NULLS LAST RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) sum_c2
  FROM nulls;

Copy

+---------------+----+--------+
| C1_NULLS_LAST | C2 | SUM_C2 |
|---------------+----+--------|
|             1 | 10 |    130 |
|             2 | 20 |    130 |
|             3 | 30 |    120 |
|          NULL | 20 |     70 |
|          NULL | 50 |     70 |
+---------------+----+--------+

创建并加载 heavy_weather 表¶

要创建 heavy_weather 表并插入行（在一些窗口函数示例中使用），请运行此脚本。

CREATE OR REPLACE TABLE heavy_weather
  (start_time TIMESTAMP, precip NUMBER(3,2), city VARCHAR(20), county VARCHAR(20));

INSERT INTO heavy_weather VALUES
('2021-12-23 06:56:00.000',0.08,'Mount Shasta','Siskiyou'),
('2021-12-23 07:51:00.000',0.09,'Mount Shasta','Siskiyou'),
('2021-12-23 16:23:00.000',0.56,'South Lake Tahoe','El Dorado'),
('2021-12-23 17:24:00.000',0.38,'South Lake Tahoe','El Dorado'),
('2021-12-23 18:30:00.000',0.28,'South Lake Tahoe','El Dorado'),
('2021-12-23 19:35:00.000',0.37,'Mammoth Lakes','Mono'),
('2021-12-23 19:36:00.000',0.80,'South Lake Tahoe','El Dorado'),
('2021-12-24 04:43:00.000',0.25,'Alta','Placer'),
('2021-12-24 05:26:00.000',0.34,'Alta','Placer'),
('2021-12-24 05:35:00.000',0.42,'Big Bear City','San Bernardino'),
('2021-12-24 06:49:00.000',0.17,'South Lake Tahoe','El Dorado'),
('2021-12-24 07:40:00.000',0.07,'Alta','Placer'),
('2021-12-24 08:36:00.000',0.07,'Alta','Placer'),
('2021-12-24 11:52:00.000',0.08,'Alta','Placer'),
('2021-12-24 12:52:00.000',0.38,'Alta','Placer'),
('2021-12-24 15:44:00.000',0.13,'Alta','Placer'),
('2021-12-24 15:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-24 16:55:00.000',0.09,'Big Bear City','San Bernardino'),
('2021-12-24 21:53:00.000',0.07,'Montague','Siskiyou'),
('2021-12-25 02:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 07:52:00.000',0.07,'Alta','Placer'),
('2021-12-25 08:52:00.000',0.08,'Alta','Placer'),
('2021-12-25 09:48:00.000',0.18,'Alta','Placer'),
('2021-12-25 12:52:00.000',0.10,'Alta','Placer'),
('2021-12-25 17:21:00.000',0.23,'Alturas','Modoc'),
('2021-12-25 17:52:00.000',1.54,'Alta','Placer'),
('2021-12-26 01:52:00.000',0.61,'Alta','Placer'),
('2021-12-26 05:43:00.000',0.16,'South Lake Tahoe','El Dorado'),
('2021-12-26 05:56:00.000',0.08,'Bishop','Inyo'),
('2021-12-26 06:52:00.000',0.75,'Bishop','Inyo'),
('2021-12-26 06:53:00.000',0.08,'Lebec','Los Angeles'),
('2021-12-26 07:52:00.000',0.65,'Alta','Placer'),
('2021-12-26 09:52:00.000',2.78,'Alta','Placer'),
('2021-12-26 09:55:00.000',0.07,'Big Bear City','San Bernardino'),
('2021-12-26 14:22:00.000',0.32,'Alta','Placer'),
('2021-12-26 14:52:00.000',0.34,'Alta','Placer'),
('2021-12-26 15:43:00.000',0.35,'Alta','Placer'),
('2021-12-26 17:31:00.000',5.24,'Alta','Placer'),
('2021-12-26 22:52:00.000',0.07,'Alta','Placer'),
('2021-12-26 23:15:00.000',0.52,'Alta','Placer'),
('2021-12-27 02:52:00.000',0.08,'Alta','Placer'),
('2021-12-27 03:52:00.000',0.14,'Alta','Placer'),
('2021-12-27 04:52:00.000',1.52,'Alta','Placer'),
('2021-12-27 14:37:00.000',0.89,'Alta','Placer'),
('2021-12-27 14:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-27 17:53:00.000',0.07,'South Lake Tahoe','El Dorado'),
('2021-12-30 11:23:00.000',0.12,'Lebec','Los Angeles'),
('2021-12-30 11:43:00.000',0.98,'Lebec','Los Angeles'),
('2021-12-30 13:53:00.000',0.23,'Lebec','Los Angeles'),
('2021-12-30 14:53:00.000',0.13,'Lebec','Los Angeles'),
('2021-12-30 15:15:00.000',0.29,'Lebec','Los Angeles'),
('2021-12-30 17:53:00.000',0.10,'Lebec','Los Angeles'),
('2021-12-30 18:53:00.000',0.09,'Lebec','Los Angeles'),
('2021-12-30 19:53:00.000',0.07,'Lebec','Los Angeles'),
('2021-12-30 20:53:00.000',0.07,'Lebec','Los Angeles')
;

Copy