窗口函数¶

窗口函数对一组相关行（这组行称为“窗口”）进行操作。

每次调用窗口函数时，都会传递一行（窗口中的当前行）以及包含当前行的行窗口。窗口函数为每个输入行返回一个输出行。输出取决于传递给函数的单个行，以及传递给函数的窗口中其他行的值。

某些窗口函数对顺序敏感。主要有两种类型的顺序敏感型窗口函数：

与排名相关的函数。

窗口框架函数。

与排名相关的函数根据行的“排名”列出信息。例如，如果您按每年利润对商店进行降序排名，则利润最高的商店的排名是 1；利润第二的商店的排名是 2，依此类推。

窗口框架函数允许您对窗口中的行子集执行滚动操作，例如计算运行总数，或者移动平均值。

不熟悉窗口函数、排名相关函数或窗口框架函数的用户可参阅使用窗口函数中的概念讲解材料。

本主题内容：

概述¶

什么是窗口？¶

窗口就是一组相关的行。例如，可以根据时间戳定义一个窗口，将同一月份的所有行分组到同一个窗口中。或者，可以根据地点定义窗口，将特定城市的所有行分组到同一窗口中。

一个窗口可以包含零行、一行或多行。为简单起见，Snowflake 文档通常认为一个窗口包含多行。

什么是窗口函数？¶

窗口函数是对行窗口执行操作的任何函数。

一个窗口函数通常会传递两个参数：

一个行。更准确地说，一个窗口函数会传递 0 个或多个表达式。在几乎所有情况下，这些表达式中都至少有一个引用该行中的列。（大多数窗口函数至少需要一个列或表达式，但一些窗口函数（例如某些与排名相关的函数）不需要显式指定列或表达式。）
包含该行的相关行窗口。窗口可以是整个表，也可以是表中行的子集。

对于非窗口函数，通常所有实参都显式传递给函数，例如：

MY_FUNCTION(argument1, argument2, ...)

窗口函数的行为各有不同；尽管当前行以常规方式作为实参传递，但窗口是通过一个单独的子句（称为 OVER 子句）传递的。该子句的 OVER 语法将在后文说明。

支持窗口的函数列表¶

下面的列表显示了所有窗口函数。

请注意，列表中指明为窗口框架函数的某些函数并不支持所有可能的窗口框架类型。

函数名称	窗口	窗口框架	排名相关	备注
常规窗口
ANY_VALUE	✔
AVG	✔	✔
CONDITIONAL_CHANGE_EVENT	✔
CONDITIONAL_TRUE_EVENT	✔
CORR	✔
COUNT	✔	✔
COUNT_IF	✔	✔
COVAR_POP	✔
COVAR_SAMP	✔
LISTAGG	✔			不使用标准窗口语法。
MAX	✔	✔
MEDIAN	✔
MIN	✔	✔
MODE	✔
PERCENTILE_CONT	✔			使用不同于其他窗口函数的语法。
PERCENTILE_DISC	✔			使用不同于其他窗口函数的语法。
RATIO_TO_REPORT	✔
STDDEV、STDDEV_SAMP	✔	✔		STDDEV 和 STDDEV_SAMP 为别名。
STDDEV_POP	✔	✔
SUM	✔	✔
VAR_POP	✔	✔
VAR_SAMP	✔	✔
VARIANCE_POP	✔	✔		VAR_POP 的别名。
VARIANCE、VARIANCE_SAMP	✔	✔		VAR_SAMP 的别名。

排名相关
CUME_DIST	✔		✔
DENSE_RANK	✔	✔	✔
FIRST_VALUE	✔	✔	✔
LAG	✔		✔
LAST_VALUE	✔	✔	✔
LEAD	✔		✔
NTH_VALUE	✔	✔	✔
NTILE	✔		✔
PERCENT_RANK	✔	✔	✔	支持基于范围的累积窗口框架，但不支持其他类型的窗口框架。
RANK	✔	✔	✔
ROW_NUMBER	✔		✔
按位聚合
BITAND_AGG	✔
BITOR_AGG	✔
BITXOR_AGG	✔
布尔聚合
BOOLAND_AGG	✔
BOOLOR_AGG	✔
BOOLXOR_AGG	✔
哈希
HASH_AGG	✔
半结构化数据聚合
ARRAY_AGG	✔
OBJECT_AGG	✔
计算非重复值
ARRAY_UNION_AGG	✔
ARRAY_UNIQUE_AGG	✔
线性回归
REGR_AVGX	✔
REGR_AVGY	✔
REGR_COUNT	✔
REGR_INTERCEPT	✔
REGR_R2	✔
REGR_SLOPE	✔
REGR_SXX	✔
REGR_SXY	✔
REGR_SYY	✔
统计与概率
KURTOSIS	✔
基数估算 . （使用 HyperLogLog）
APPROX_COUNT_DISTINCT	✔			HLL 的别名。
HLL	✔
HLL_ACCUMULATE	✔
HLL_COMBINE	✔
HLL_ESTIMATE				非聚合函数；使用来自 HLL_ACCUMULATE 或 HLL_COMBINE 的标量输入。
HLL_EXPORT	✔
HLL_IMPORT	✔
相似性估算 . （使用 MinHash）
APPROXIMATE_JACCARD_INDEX	✔			APPROXIMATE_SIMILARITY 的别名。
APPROXIMATE_SIMILARITY	✔
MINHASH	✔
MINHASH_COMBINE	✔
频率估算 . （使用 Space-Saving）
APPROX_TOP_K	✔
APPROX_TOP_K_ACCUMULATE	✔
APPROX_TOP_K_COMBINE	✔
APPROX_TOP_K_ESTIMATE				非聚合函数；使用来自 APPROX_TOP_K_ACCUMULATE 或 APPROX_TOP_K_COMBINE 的标量输入。
百分位估算 . （使用 T-Digest）
APPROX_PERCENTILE	✔
APPROX_PERCENTILE_ACCUMULATE	✔
APPROX_PERCENTILE_COMBINE	✔
APPROX_PERCENTILE_ESTIMATE				非聚合函数；使用来自 APPROX_PERCENTILE_ACCUMULATE 或 APPROX_PERCENTILE_COMBINE 的标量输入。

窗口函数与 NULL 值¶

某些函数会忽略 NULL 值。例如，AVG 根据以下公式将 1、5 和 NULL 值的平均值计算为 3：

(1 + 5) / 2 = 3

在分子和分母中，仅使用非 NULL 值。

如果传递给函数的所有值都是 NULL，则函数返回 NULL。

某些窗口函数可以传递多个列。例如：

SELECT COUNT(col1, col2) FROM table1;

Copy

在这些实例中，如果任何一个列为 NULL，则该函数会忽略相应的行。

例如，在以下查询中，COUNT 返回 1，而不是 4，因为四行中有三行在所选列中至少包含一个 NULL 值：

创建一个表并用值填充它：

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

查询表：

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

类似地，如果使用引用两列或多列的表达式调用 SUM，并且如果其中一列或多列是 NULL，则该表达式的计算结果为 NULL，并且忽略该行：

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

请注意，此行为与 GROUP BY 的行为不同，后者在某些列为 NULL 时不会丢弃行：

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy

介绍性示例¶

假设您是某连锁店的所有者。以下查询会显示每家商店产生的利润在连锁店总利润中所占的百分比。查询使用 RATIO_TO_REPORT 函数，该函数从当前行中获取一个值（例如 net_profit），并将其除以所有其他行中相应值 (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

什么是窗口框架？¶

窗口框架是窗口中行的子集。通过创建子集，您可以仅计算这一指定的行子组的值。窗口框架在 OVER 子句的 ORDER BY 子句中指定为分子句。

窗口框架的类型¶

Snowflake 支持两种类型的窗口框架：

累积:: 允许计算从窗口开始到当前行或从当前行到窗口末尾的滚动值。
滑动:: 允许计算窗口中任意两行（含这两行）之间相对于当前行的滚动值。

有关窗口框架的更多信息，包括用于窗口框架的语法，请参阅窗口框架语法和用法。

窗口语法和用法¶

窗口语法¶

<function> ( [ <arguments> ] ) OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )

Copy

OVER 子句指定将该函数用作窗口函数。

PARTITION BY 子句允许将行划分成子组，例如按城市、按年份等。PARTITION BY 子句为可选。您可以分析一整组行，而无需将其划分为子组。

ORDER BY 子句对窗口中的行进行排序。（这与对查询的输出进行排序不同。查询可能有一个 ORDER BY 子句，用于控制窗口中行的顺序，而除了 OVER 子句之外，还可能有一个单独的 ORDER BY 子句，用于控制整个查询的输出顺序。尽管 ORDER BY 子句对于某些窗口函数是可选的，但对于其他窗口函数是必需的。例如，窗口框架函数和与排名相关的函数要求数据按有意义的顺序排列，因此 ORDER BY 子句是必需的。

备注

支持窗口框架的函数使用修改/增强的语法。有关更多详细信息，请参阅窗口框架语法和用法（本主题内容）。

窗口使用说明¶

OVER 子句指定函数在哪个窗口上运行。该子句包含以下一个（或两个）组成部分：
- PARTITION BY expr1：该子句定义窗口分区（如有）（即在应用函数之前如何对数据进行分组）。
- ORDER BY expr2：该子句确定窗口中行的顺序。
  
  ORDER BY 子句遵循与查询 ORDER BY 子句类似的规则，例如关于 ASC/DESC （升序/降序）和 NULL 处理的规则。有关其他受支持选项的更多详细信息，请参阅 ORDER BY 查询构造。
  
  不同的函数以不同的方式处理 ORDER BY 子句：
  某些窗口函数需要 ORDER BY 子句。
  
  某些窗口函数禁止 ORDER BY 子句。
  
  某些窗口函数使用 ORDER BY 子句（如果存在），但不需要该子句。
  
  某些窗口函数将 ORDER BY 子句视为隐式累积窗口框架子句。（有关隐式窗口框架的更多信息，请参阅窗口框架使用说明。）
  
  备注
  
  为清晰起见，Snowflake 建议避免使用隐式窗口框架。如果查询使用窗口框架，请将其设为显式窗口框架。

窗口框架语法和用法¶

窗口框架语法¶

<function> ( <arguments> ) OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ cumulativeFrame | slidingFrame ] )

Copy

其中：

cumulativeFrame ::=
    {
       { ROWS | RANGE } BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
     | { ROWS | RANGE } BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    }

Copy

slidingFrame ::=
    {
       ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
     | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
    }

Copy

备注

窗口框架要求窗口中的数据按已知顺序排列。因此，在窗口框架语法中，OVER 子句内的 ORDER BY 是必需的，尽管在通用窗口函数语法中，ORDER BY 子句是可选的。

窗口框架使用说明¶

针对累积窗口框架：
- ROWS 使用从分区的开头或结尾到当前行的所有行（根据指定 ORDER BY 分子句）来计算当前行的结果。
- RANGE 类似于 ROWS，但它只计算与当前行具有相同值的行的结果（根据指定的 ORDER BY 分子句）。
对于滑动窗口框架：
- ROWS 包含两端的行，并且始终相对于当前行。
- 不支持 RANGE。
- 如果指定的前后 ROWS 数量超出了窗口限制，则 Snowflake 会将该值视为 NULL。
如果未指定窗口框架，则默认值取决于以下函数：
- 对于非排名相关函数（COUNT、MAX、MIN、SUM），默认值为以下累积窗口框架（遵循 ANSI 标准）：
  
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 对于与排名相关的函数（FIRST_VALUE、LAST_VALUE、NTH_VALUE），默认值为整个窗口：
  
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  
  请注意，这偏离了 ANSI 标准。

一般提示¶

该语法将 OVER 子句的所有分子句显示为窗口函数的可选子句。这是设计使然（即您可以使用 OVER，而在括号内不带任何分子句）。这在特定情景（例如关闭并行处理）中很有用。
PARTITION BY 并非始终与 GROUP BY 兼容。
如果查询使用多个窗口函数，则通常情况下，应按照相同的方式对每个函数的输入数据集进行分区。例如，下面的第一条语句比第二条语句更有可能是正确的：
```
SELECT SUM(a) OVER (PARTITION BY x), SUM(b) OVER (PARTITION BY x) ... ;

SELECT SUM(a)                      , SUM(b) OVER (PARTITION BY x) ... ;
```
Copy
错误消息 SQL compilation error: ... is not a valid group by expression 通常表明，在 SELECT 语句的“project”子句中，不同列的分区方式不同，因此可能会产生不同的行数。

示例¶

在使用窗口函数中可以找到更多示例。

累积窗口框架示例¶

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

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

运行使用累积窗口框架的查询，并显示输出。返回表的指定窗口中行的累积计数、总和、最小值和最大值：

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

按范围返回表的指定窗口中行的累积计数、总和、最小值和最大值：

SELECT
    p, o, i,
    COUNT(i) OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) count_i_Range_Pre,
    SUM(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_i_Range_Pre,
    AVG(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) avg_i_Range_Pre,
    MIN(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min_i_Range_Pre,
    MAX(i)   OVER (PARTITION BY p ORDER BY o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 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

使用默认窗口框架语义（即 RANGE 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

返回包含当前行之前、之后和当前行的滑动窗口中两列（分别为数字和字符串）的最小值：

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

返回包含当前行之前、之后和当前行的滑动窗口中两列（分别为数字和字符串）的最大值：

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 子句创建一个窗口，其中包含每个销售人员的总销售额。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 子句（在 OVER 子句外层）强制按排名顺序显示输出，如下所示：

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

窗口函数¶

概述¶

什么是窗口？¶

什么是窗口函数？¶

支持窗口的函数列表¶

窗口函数与 NULL 值¶

介绍性示例¶

什么是窗口框架？¶

窗口框架的类型¶

什么是与排名相关的函数？¶

窗口语法和用法¶

窗口语法¶

窗口使用说明¶

窗口框架语法和用法¶

窗口框架语法¶

窗口框架使用说明¶

与排名相关的窗口函数的语法和用法¶

与排名相关的窗口函数语法¶

与排名相关的窗口函数的使用说明¶

一般提示¶

示例¶

累积窗口框架示例¶

滑动窗口框架示例¶

与排名相关的函数的示例¶