使用窗口函数分析数据¶

本主题内容：

本主题包含有关窗口函数的介绍性概念信息。如果您已经熟悉窗口函数的用法，可能会发现以下参考信息已足够：

窗口函数，其中包含函数列表和指向各个函数描述的链接。

窗口函数的语法和用法，描述了所有窗口函数的一般语法规则。

简介¶

窗口函数是一个分析 SQL 函数，作用于一组称为分区的相关行。分区通常是沿着某个熟悉的维度（例如产品类别、位置、时间段或业务单位）的一组逻辑行。函数结果是根据隐式或显式 窗口框架 在每个分区上计算出来的。窗口框架是相对于 当前行 的一组固定或可变的行。当前行是正在计算函数结果的单个输入行。函数结果在每个分区内逐行计算，窗口框架中的每一行轮流作为当前行。

定义此行为的语法是函数的 OVER 子句。在许多情况下，OVER 子句会将窗口函数与同名的正则 SQL 函数区分开（例如 AVG 或 SUM）。OVER 子句由三个主要组成部分组成：

PARTITION BY 子句
ORDER BY 子句
窗口框架规范

根据所讨论的函数或查询，所有这些组件可能是可选的；带有空 OVER 子句的窗口函数是有效的：OVER()。但是，在大多数分析查询中，窗口函数需要一个或多个显式 OVER 子句组件。您可以在支持其他 SQL 函数的任何上下文中调用窗口函数。以下部分更详细地解释了窗口函数背后的概念，并提供了一些入门示例。有关完整的语法信息，请参阅窗口函数的语法和用法。

窗口函数与聚合函数¶

开始学习窗口函数的一个好方法是将正则聚合函数与其对应的窗口函数进行比较。几个标准的聚合函数，例如 SUM、COUNT 和 AVG，具有相同名称的相应窗口函数。要区分这两者，请注意：

对于聚合函数，输入为一组行，输出为一行。
在窗口函数中，输入是分区内的每一行，输出是 每个输入行 对应的一行。

例如，SUM 聚合函数返回所有输入行的单个总值，而窗口函数返回多个总值：相对于分区中的所有其他行，每一行（当前行）都有一个总值。

要了解其工作原理，首先创建并加载 menu_items 表，该表包含餐车菜单项目的销售成本和价格。使用正则 AVG 函数求出不同类别菜单商品的平均成本：

SELECT menu_category,
    AVG(menu_cogs_usd) avg_cogs
  FROM menu_items
  GROUP BY 1
  ORDER BY menu_category;

Copy

+---------------+------------+
| MENU_CATEGORY |   AVG_COGS |
|---------------+------------|
| Beverage      | 0.60000000 |
| Dessert       | 1.79166667 |
| Main          | 6.11046512 |
| Snack         | 3.10000000 |
+---------------+------------+

请注意，该函数返回一个分组结果 avg_cogs。

或者，您可以指定一个 OVER 子句并使用 AVG 作为窗口函数。（结果仅限于 60 行表中的 15 行。）

SELECT menu_category,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;

Copy

+---------------+----------+
| MENU_CATEGORY | AVG_COGS |
|---------------+----------|
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Beverage      |  0.60000 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Dessert       |  1.79166 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
| Main          |  6.11046 |
+---------------+----------+

请注意，该函数为每个分区中的每一行返回一个平均值，并在分区列值发生变化时重置计算。为了使窗口函数的值更加明显，请在函数定义中添加一个 ORDER BY 子句和一个窗口框架。此外，还要返回原始 menu_cogs_usd 值，以及平均值，以便可以看到具体的计算原理。此查询是一个简单的“移动平均线”示例，一种依赖于显式窗口框架的滚动计算。有关更多类似的示例，请参阅分析时间序列数据。

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category ORDER BY menu_price_usd ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd
  LIMIT 15;

Copy

+---------------+----------------+---------------+----------+
| MENU_CATEGORY | MENU_PRICE_USD | MENU_COGS_USD | AVG_COGS |
|---------------+----------------+---------------+----------|
| Beverage      |           2.00 |          0.50 |  0.58333 |
| Beverage      |           3.00 |          0.50 |  0.57500 |
| Beverage      |           3.00 |          0.75 |  0.63333 |
| Beverage      |           3.50 |          0.65 |  0.65000 |
| Dessert       |           3.00 |          0.50 |  0.91666 |
| Dessert       |           4.00 |          1.00 |  1.58333 |
| Dessert       |           5.00 |          1.25 |  2.08333 |
| Dessert       |           6.00 |          2.50 |  2.66666 |
| Dessert       |           6.00 |          2.50 |  2.75000 |
| Dessert       |           7.00 |          3.00 |  3.00000 |
| Main          |           5.00 |          1.50 |  2.03333 |
| Main          |           6.00 |          2.60 |  3.00000 |
| Main          |           6.00 |          2.00 |  2.33333 |
| Main          |           6.00 |          2.40 |  3.13333 |
| Main          |           8.00 |          4.00 |  3.66666 |
+---------------+----------------+---------------+----------+

窗口框架会调整平均值的计算，使其只考虑当前行及其后两行（在分区内）。分区中的最后一行没有后续行，因此最后一行的平均值，例如 Beverage，与相应的 menu_cogs_usd 值 (0.65) 相同。窗口函数的输出取决于传递给函数的单个行以及符合窗口框架的其他行的值。

窗口函数行排序¶

前面的 AVG 窗口函数示例在函数定义中使用了一个 ORDER BY 子句，以确保窗口框架受排序数据的影响（在本例中按 menu_price_usd 排序）。

两类窗口函数需要一个 ORDER BY 子句：

具有显式窗口框架的窗口函数，在每个分区的行子集上执行滚动操作，例如计算累计总数或移动平均线。没有 ORDER BY 子句的窗口框架是没有意义的；“前”行和“后”行的集合必须是确定的。

窗口函数排名，如 CUME_DIST、RANK 和 DENSE_RANK，根据行的“排名”返回信息。例如，如果您按每月利润对商店进行降序排名，则利润最高的商店的排名是 1；利润第二的商店的排名是 2，依此类推。

窗口函数的 ORDER BY 子句支持与主 ORDER BY 子句相同的语法，该子句对查询的最终结果进行排序。这两个 ORDER BY 子句是独立且不同的。OVER 子句中的 ORDER BY 子句仅控制窗口函数处理行的顺序；不控制整个查询的输出。在许多情况下，窗口函数查询将包含这两种类型的 ORDER BY 子句。

OVER 子句中的 PARTITION BY 和 ORDER BY 子句也是独立的。您可以使用 ORDER BY 子句而不使用 PARTITION BY 子句，反之亦然。

在编写查询之前，请检查各个窗口函数的语法。ORDER BY 子句的语法要求因功能而异：

某些窗口函数需要 ORDER BY 子句。
某些窗口函数使用 ORDER BY 子句（如果存在），但不需要该子句。
某些窗口函数不允许使用 ORDER BY 子句。
某些窗口函数将 ORDER BY 子句解释为隐式窗口框架。

小心

一般来说，SQL 是一种显式语言，含有很少的隐式语句。然而，对于某些窗口函数，ORDER BY 子句表示一个窗口框架。有关详细信息，请参阅窗口框架的使用注意事项。

由于隐式行为（而非显式行为）可能会导致难以理解的结果，因此 Snowflake 建议显式声明窗口框架。

使用不同类型的窗口框架¶

窗口框架的定义为显式定义或隐式定义。它们依赖于 ORDER BY 子句在 OVER 子句中的存在：

有关显式框架语法，请参阅语法下的 windowFrameClause。您可以定义开放的边界：从分区的开始到当前行；从当前行到分区的结束；或完全“无界”的端到端。或者，您可以使用相对于分区中当前行的显式偏移（含）。
默认情况下，当 OVER 子句不包含 windowFrameClause 时，将使用隐式框架。默认框架取决于相关功能。另请参阅窗口框架的使用注意事项。

基于范围的窗口框架与基于行的窗口框架¶

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

基于行的窗口框架:

根据从当前行开始的物理偏移，此框架含一个精确的行序列。例如，5 PRECEDING 表示当前行之前的五行。偏移必须是一个数字。ROWS 模式包含两端的行，并且始终相对于当前行。如果指定的前后行数超出了分区的限制，则 Snowflake 会将该值视为 NULL。

如果框架具有开放式而非明确编号的边界，则适用类似的物理偏移。例如，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 指该框架由在当前行之前物理存在的整组行（零行或多行）以及当前行本身组成。

基于范围:

在给定当前行的 ORDER BY 值偏移的情况下，此框架含一组逻辑行。例如，5 PRECEDING 表示具有ORDER BY 值的行，这些值等于当前行的 ORDER BY 值加上或减去最多 5（DESC 顺序则为加，ASC 顺序则为减）。偏移值可以是一个数字或一个区间。

如果框架具有开放式而非编号边界，则适用类似的逻辑偏移。例如，RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示该框架由所有物理上位于当前行之前的行、当前行本身，以及任何与当前行具有相同 ORDER BY 值的相邻行组成。对于一个 RANGE 窗口框架，CURRENT ROW 并非指当前物理行，而是指与当前物理行具有相同 ORDER BY 值的所有行。

ROWS BETWEEN 和 RANGE BETWEEN 窗口框架中的区别很重要，因为窗口函数查询可能会返回非常不同的结果，这取决于 ORDER BY 表达式、表中的数据以及框架的确切定义。以下示例演示了行为的差异。

使用显式偏移比较 RANGE BETWEEN 和 ROWS BETWEEN¶

基于范围的窗口框架需要一个 ORDER BY 列或表达式和一个 RANGE BETWEEN 规范。窗口框架的逻辑边界取决于当前行的 ORDER BY 值（一个数字常量或区间字面量）。

例如，一个名为 heavy_weather 的时间序列表定义如下：

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

Copy

该表中的示例行如下所示：

+-------------------------+--------+-------+-------------+
| START_TIME              | PRECIP | CITY  | COUNTY      |
|-------------------------+--------+-------+-------------|
| 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 |
+-------------------------+--------+-------+-------------+

假设查询使用按 start_time 排序的窗口框架计算 ``precip``（降水量）列的 3 小时移动平均线 (AVG)：

AVG(precip)
  OVER(ORDER BY start_time
    RANGE BETWEEN CURRENT ROW AND INTERVAL '3 hours' FOLLOWING)

Copy

根据上面的示例行，若当前行为 2021-12-30 11:23:00.000``（第一示例行），则只有接下来的两行在框架内（``2021-12-30 11:43:00.000 和 2021-12-30 13:53:00.000）。后续时间戳大于 3 小时。

然而，如果您将窗口框架更改为 1 天间隔，则当前行之后的所有样本行都在框架内，因为它们都具有相同日期的时间戳（2021-12-30）：

RANGE BETWEEN CURRENT ROW AND INTERVAL '1 day' FOLLOWING

Copy

若将此语法从 RANGE BETWEEN 更改为 ROWS BETWEEN，则框架必须指定固定边界，代表确切的行数：当前行加上以下确切的有序行数，例如 1、3 或 10 行，无论 ORDER BY 表达式返回的值如何。

另请参阅采用显式数字偏移的 RANGE BETWEEN 示例。

以开放式边界对 RANGE BETWEEN 和 ROWS BETWEEN 进行比较¶

以下示例比较了在相同的行集上计算以下窗口框架时的结果：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Copy

该示例选自一个名为 menu_items 的小表。请参阅创建并加载 menu_items 表。

该 SUM 窗口函数对每个 menu_category 分区的 menu_price_usd 值进行汇总。使用 ROWS BETWEEN 语法，可以很容易地看到每个分区内的运行总数是如何累积的。

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;

Copy

+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      5.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     18.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |      6.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

当使用 RANGE BETWEEN 语法与其他相同的查询时，计算一开始并不那么明显；它们依赖于对 当前行 的不同解释：当前行本身加上任何与该行具有相同 ORDER BY 值的相邻行。

例如，结果中第二行和第三行的 sum_price 值都是 8.00，因为这些行的 ORDER BY 值是相同的。在结果集中的其他两个地方也出现了这种行为，其中 sum_price 连续计算为 24.00 和 12.00。

SELECT menu_category, menu_price_usd,
    SUM(menu_price_usd)
      OVER(PARTITION BY menu_category ORDER BY menu_price_usd
      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_price
  FROM menu_items
  WHERE menu_category IN('Beverage','Dessert','Snack')
  ORDER BY menu_category, menu_price_usd;

Copy

+---------------+----------------+-----------+
| MENU_CATEGORY | MENU_PRICE_USD | SUM_PRICE |
|---------------+----------------+-----------|
| Beverage      |           2.00 |      2.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.00 |      8.00 |
| Beverage      |           3.50 |     11.50 |
| Dessert       |           3.00 |      3.00 |
| Dessert       |           4.00 |      7.00 |
| Dessert       |           5.00 |     12.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           6.00 |     24.00 |
| Dessert       |           7.00 |     31.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           6.00 |     12.00 |
| Snack         |           7.00 |     19.00 |
| Snack         |           9.00 |     28.00 |
| Snack         |          11.00 |     39.00 |
+---------------+----------------+-----------+

用于累积计算和滑动计算的窗口框架¶

窗口框架是一种非常灵活的机制，用于运行不同类型的分析查询，包括累积计算和移动计算。例如，要返回累计和，可以指定一个窗口框架，从一个固定点开始，在整个分区中逐行移动：

OVER(PARTITION BY col1 ORDER BY col2 ROWS UNBOUNDED PRECEDING)

Copy

这类框架的另一个示例可能是：

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Copy

符合这些框架的行数是变量，但框架的起点和终点是固定的，使用已命名边界而非数字或区间边界。

如果希望窗口函数计算在特定行数（或范围）上向前滑动，可以使用显式偏移：

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)

Copy

在这种情况下，结果就是一个最多由七行（3 + 当前行 + 3）组成的滑动框架。这类框架的另一个示例可能是：

OVER(PARTITION BY col1 ORDER BY col2 ROWS BETWEEN CURRENT ROW AND 3 FOLLOWING)

Copy

窗口框架可以包含已命名边界和显式偏移的混合。

滑动窗口框架¶

滑动窗口框架是一个固定宽度的框架，它在分区的行中“滑动”，每次覆盖分区的不同切片。框架中的行数保持不变，除了在分区的开始或结束处，可能包含更少的行。

滑动窗口通常用于计算移动平均线，这些值基于固定大小的区间（例如几天的数量）。平均值是“移动的”，因为尽管间隔的大小是恒定的，但随着时间（或其他维度）的推移，间隔中的实际值会发生变化。

例如，股票市场分析师通常部分基于股票价格的 13 周移动平均线来分析股票。今天的移动平均线价格是今天结束时的价格和最近 13 周内每天结束时的价格的平均值。如果股票每周交易 5 天，并且在过去 13 周内没有假期，则移动平均线是最近 65 个交易日（包括今天）中每个交易日的平均价格。

以下示例显示了 6 月最后一天和 7 月头几天股票价格的 13 周（91 天）移动平均线的变化情况：

6 月 30 日，函数返回 4 月 1 日至 6 月 30 日（含）的平均价格。
7 月 1 日，函数返回 4 月 2 日至 7 月 1 日（含）的平均价格。
7 月 2 日，函数返回 4 月 3 日至 7 月 2 日（含）的平均价格。

下面的示例在当月的前 7 天使用一个小的（3 天）滑动窗口。这个示例考虑到在周期开始时，分区可能未满。

从相应的查询结果模型中可以看到，最后一列包含最近三天的销售数据的总和。例如，第 4 天的列值为 36，它是第 2、3 和 4 天的销售额的总和（11 + 12 + 13）：

+--------+-------+---------------+
| Day of | Sales | Most Recent   |
| Month  | Today | 3 Days' Sales |
|--------+-------+---------------+
|      1 |    10 |            10 |
|      2 |    11 |            21 |
|      3 |    12 |            33 |
|      4 |    13 |            36 |
|      5 |    14 |            39 |
|    ... |   ... |           ... |
+--------+-------+---------------+

排名窗口函数¶

排名窗口函数的语法与其他窗口函数的语法大致相同。例外情况包括：

排名窗口函数需要在 ORDER BY 子句中包含 OVER 子句。
某些排名函数（例如 RANK 本身）不需要输入实参。对于 RANK 函数，返回的值仅基于数字排名，具体由 OVER子句内的 ORDER BY 子句确定。因此，无需向函数传递列名或表达式。

最简单的排名函数被称为 RANK。此功能可用于：

根据收入（销售额）对销售人员进行排名（从高到低）。
根据国家的人均 GDP （人均收入）对国家进行排名（从高到低）。
对各国的空气污染指数进行排名（从低到高）。

此函数能够简单识别有序行集中一行的数字排名位置。第一行排名为 1，第二行的排名为 2，依此类推。以下示例显示了基于 Amount Sold 的销售人员排名顺序：

+-------------+-------------+------+
| Salesperson | Amount Sold | Rank |
|-------------+-------------+------|
| Smith       |        2000 |    1 |
| Jones       |        1500 |    2 |
| Torkelson   |        1200 |    3 |
| Dolenz      |        1100 |    4 |
+-------------+-------------+------+

在分配排名之前，行必须已经排序。因此，必须在 OVER 子句中使用 ORDER BY 子句。

请考虑以下示例：您想知道商店利润在连锁店的各个分店中排名如何（不管商店是排名第一、第二、第三还是其他排名）。这个示例根据每个商店在其城市中的盈利能力进行排名。行按降序排列（最高利润在前），因此盈利能力最高的商店排名为 1：

SELECT city, branch_ID, net_profit,
       RANK() OVER (PARTITION BY city ORDER BY net_profit DESC) AS rank
    FROM store_sales
    ORDER BY city, rank;
+-----------+-----------+------------+------+
| CITY      | BRANCH_ID | NET_PROFIT | RANK |
|-----------+-----------+------------+------|
| Montreal  |         3 |   10000.00 |    1 |
| Montreal  |         4 |    9000.00 |    2 |
| Vancouver |         2 |   15000.00 |    1 |
| Vancouver |         1 |   10000.00 |    2 |
+-----------+-----------+------------+------+

Copy

备注

net_profit 列不需要作为参数传递给 RANK 函数。相反，输入行按 net_profit 排序。RANK 函数只需要返回行在分区中的位置（1、2、3 等）。

排名相关函数的输出取决于：

传递给函数的单个行。
分区中其他行的值。
分区中所有行的顺序。

Snowflake 提供了几种不同的排名函数。有关这些函数的列表以及其语法的更多详细信息，请参阅窗口函数。

要将您的商店与连锁店中的所有其他商店（而不仅仅是您所在城市的其他商店）进行排名，请使用以下查询：

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales

Copy

以下查询使用第一个 ORDER BY 子句来控制窗口函数的处理，使用第二个 ORDER BY 子句来控制整个查询输出的顺序：

SELECT
    branch_ID,
    net_profit,
    RANK() OVER (ORDER BY net_profit DESC) AS sales_rank
  FROM store_sales
  ORDER BY branch_ID;

Copy

图解示例¶

此示例使用一个销售场景来说明本主题前面介绍的许多概念。

假设您需要生成财务报表，该报表显示基于上周销售额的值：

每日销售额
周内排名（即当周从高到低的销售额排名）
本周迄今为止的销售额（即从本周开始到当天 [包括当天] 的所有天数的“累计金额”）
本周总销售额
三天移动平均线（即当天和前两天的平均值）

报表可能如下所示：

+--------+-------+------+--------------+-------------+--------------+
| Day of | Sales | Rank | Sales So Far | Total Sales | 3-Day Moving |
| Week   | Today |      | This Week    | This Week   | Average      |
|--------+-------+------+--------------+-------------|--------------+
|      1 |    10 |    4 |           10 |          84 |         10.0 |
|      2 |    14 |    3 |           24 |          84 |         12.0 |
|      3 |     6 |    5 |           30 |          84 |         10.0 |
|      4 |     6 |    5 |           36 |          84 |          9.0 |
|      5 |    14 |    3 |           50 |          84 |         10.0 |
|      6 |    16 |    2 |           66 |          84 |         11.0 |
|      7 |    18 |    1 |           84 |          84 |         12.0 |
+--------+-------+------+--------------+-------------+--------------+

此查询的 SQL 有点复杂。本讨论不是将示例显示为单次查询，而是将 SQL 细分为单个列。

在实际场景中，您可能拥有多年的数据，因此要计算特定一周数据的总和和平均值，您需要使用一周窗口，或使用如下所示的筛选器：

... WHERE date >= start_of_relevant_week and date <= end_of_relevant_week ...

Copy

但是，对于此示例，假设该表仅包含最近一周的数据。

CREATE TABLE store_sales_2 (
    day INTEGER,
    sales_today INTEGER
    );
+-------------------------------------------+
| status                                    |
|-------------------------------------------|
| Table STORE_SALES_2 successfully created. |
+-------------------------------------------+
INSERT INTO store_sales_2 (day, sales_today) VALUES
    (1, 10),
    (2, 14),
    (3,  6),
    (4,  6),
    (5, 14),
    (6, 16),
    (7, 18);
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       7 |
+-------------------------+

Copy

计算销售额排名¶

使用 RANK 函数计算 Rank 列：

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+
| DAY | SALES_TODAY | RANK |
|-----+-------------+------|
|   1 |          10 |    5 |
|   2 |          14 |    3 |
|   3 |           6 |    6 |
|   4 |           6 |    6 |
|   5 |          14 |    3 |
|   6 |          16 |    2 |
|   7 |          18 |    1 |
+-----+-------------+------+

Copy

虽然该时间段有 7 天，但只有 5 个不同的排名（1、2、3、5、6）。有两个并列（第 3 名和第 6 名），因此没有排名第 4 或第 7 的行。

计算本周到目前为止的销售额¶

使用 SUM 作为包含窗口框架的窗口函数计算 Sales So Far This Week 列：

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------------------------+
| DAY | SALES_TODAY | SALES SO FAR THIS WEEK |
|-----+-------------+------------------------|
|   1 |          10 |                     10 |
|   2 |          14 |                     24 |
|   3 |           6 |                     30 |
|   4 |           6 |                     36 |
|   5 |          14 |                     50 |
|   6 |          16 |                     66 |
|   7 |          18 |                     84 |
+-----+-------------+------------------------+

Copy

此查询按日期对行进行排序，然后针对每个日期计算从窗口开始到当前日期（含）的销售额总和。

计算本周总销售额¶

使用 SUM 函数计算 Total Sales This Week 列：

SELECT day, 
       sales_today, 
       SUM(sales_today)
           OVER ()
               AS total_sales
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+-------------+
| DAY | SALES_TODAY | TOTAL_SALES |
|-----+-------------+-------------|
|   1 |          10 |          84 |
|   2 |          14 |          84 |
|   3 |           6 |          84 |
|   4 |           6 |          84 |
|   5 |          14 |          84 |
|   6 |          16 |          84 |
|   7 |          18 |          84 |
+-----+-------------+-------------+

Copy

计算 3 天的移动平均线¶

使用 AVG 作为包含窗口框架的窗口函数计算 3-Day Moving Average 列：

SELECT day, 
       sales_today, 
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+----------------------+
| DAY | SALES_TODAY | 3-DAY MOVING AVERAGE |
|-----+-------------+----------------------|
|   1 |          10 |               10.000 |
|   2 |          14 |               12.000 |
|   3 |           6 |               10.000 |
|   4 |           6 |                8.666 |
|   5 |          14 |                8.666 |
|   6 |          16 |               12.000 |
|   7 |          18 |               16.000 |
+-----+-------------+----------------------+

Copy

这个窗口框架与前面描述的窗口框架之间的区别在于起点：一个是固定边界，另一个是显式偏移。

组合使用¶

下面是查询的最终版本，显示了所有列：

SELECT day, 
       sales_today, 
       RANK()
           OVER (ORDER BY sales_today DESC) AS Rank,
       SUM(sales_today)
           OVER (ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS "SALES SO FAR THIS WEEK",
       SUM(sales_today)
           OVER ()
               AS total_sales,
       AVG(sales_today)
           OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
               AS "3-DAY MOVING AVERAGE"
    FROM store_sales_2
    ORDER BY day;
+-----+-------------+------+------------------------+-------------+----------------------+
| DAY | SALES_TODAY | RANK | SALES SO FAR THIS WEEK | TOTAL_SALES | 3-DAY MOVING AVERAGE |
|-----+-------------+------+------------------------+-------------+----------------------|
|   1 |          10 |    5 |                     10 |          84 |               10.000 |
|   2 |          14 |    3 |                     24 |          84 |               12.000 |
|   3 |           6 |    6 |                     30 |          84 |               10.000 |
|   4 |           6 |    6 |                     36 |          84 |                8.666 |
|   5 |          14 |    3 |                     50 |          84 |                8.666 |
|   6 |          16 |    2 |                     66 |          84 |               12.000 |
|   7 |          18 |    1 |                     84 |          84 |               16.000 |
+-----+-------------+------+------------------------+-------------+----------------------+

Copy

其他示例¶

本节提供了更多窗口函数的示例，并说明了 PARTITION BY 和 ORDER BY 子句是如何协同工作的。

这些示例使用下表和数据：

CREATE TABLE sales (sales_date DATE, quantity INTEGER);

INSERT INTO sales (sales_date, quantity) VALUES
    ('2018-01-01', 1),
    ('2018-01-02', 3),
    ('2018-01-03', 5),
    ('2018-02-01', 2)
    ;

Copy

包含 ORDER BY 子句的窗口函数¶

ORDER BY 子句控制每个窗口（如果有多个分区，则控制每个分区）中数据的顺序。如果要在添加新行时显示一段时间内的“累计总和”，这将非常有用。

可以从窗口的开头到当前行（含）或从当前行到窗口的末尾计算累计总和。

查询可以使用“滑动”窗口，这是一个固定宽度的窗口，用于处理相对于当前行的 n 个指定行（例如，最近的 10 行，包括当前行）。

固定边界的窗口框架¶

当窗口框架具有固定边界时，可以从窗口开始到当前行（或从当前行到窗口的末尾）计算值：

SELECT MONTH(sales_date) AS MONTH_NUM, 
       quantity, 
       SUM(quantity) OVER (ORDER BY MONTH(sales_date)
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Copy

查询结果包括显示 CUMULATIVE_SUM_QUANTITY 列计算方式的其他注释：

+-----------+----------+-------------------------+
| MONTH_NUM | QUANTITY | CUMULATIVE_SUM_QUANTITY |
|-----------+----------+-------------------------|
|         1 |        1 |                       1 |  -- sum = 1
|         1 |        3 |                       4 |  -- sum = 1 + 3
|         1 |        5 |                       9 |  -- sum = 1 + 3 + 5
|         2 |        2 |                      11 |  -- sum = 1 + 3 + 5 + 2
+-----------+----------+-------------------------+

Copy

带显式偏移的窗口框架¶

在金融领域，分析师经常研究“移动平均线”。

例如，您可能有一个图表，其中 X 轴是时间，Y 轴显示股票过去 13 周的平均价格（即，13 周移动平均线）。在股票价格的 13 周移动平均线图中，显示的 6 月 30 日的价格不是 6 月 30 日的股票价格，而是截至 6 月 30 日（包括 6 月 30 日）的 13 周（即 4 月 1 日至 6 月 30 日）的股票平均价格。7 月 1 日的值是 4 月 2 日至 7 月 1 日的平均价格；7 月 2 日的值是 4 月 3 日至 7 月 2 日的平均价格，以此类推。每天，该窗口都会有效地将最近一天的值添加到移动平均线中，并删除最旧的一天的值。这消除了日常波动，并且更便于发现趋势。

移动平均线可以使用滑动窗口框架计算。该框架具有特定的行宽度。在上面的股价示例中，13 周是 91 天，因此滑动窗口将是 91 天。如果每天衡量一次（例如在一天结束时），则窗口将是 91 行“宽”。

要定义 91 行宽的窗口，请执行以下操作：

SELECT AVG(price) OVER(ORDER BY timestamp1 ROWS BETWEEN 90 PRECEDING AND CURRENT ROW)
  FROM sales;

Copy

备注

初始窗口框架可能小于 91 天宽。例如，假设您想要股票的 13 周移动平均价格。如果股票是在 4 月 1 日首次创建的，那么在 4 月 3 日只有 3 天的价格信息存在，因此窗口只有 3 行宽。

以下示例显示了在足以容纳两个样本的滑动窗口框架上求和的结果：

SELECT MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
           AS SLIDING_SUM_QUANTITY
  FROM sales
  ORDER BY sales_date;

Copy

查询结果包括显示 SLIDING_SUM_QUANTITY 列计算方式的其他注释：

+-----------+----------+----------------------+
| MONTH_NUM | QUANTITY | SLIDING_SUM_QUANTITY |
|-----------+----------+----------------------+
|         1 |        1 |                   1  |  -- sum = 1
|         1 |        3 |                   4  |  -- sum = 1 + 3
|         1 |        5 |                   8  |  -- sum = 3 + 5 (1 is no longer in the window)
|         2 |        2 |                   7  |  -- sum = 5 + 2 (3 is no longer in the window)
+-----------+----------+----------------------+

Copy

请注意，“滑动窗口”功能需要 ORDER BY 子句；该功能依赖于进入和退出窗口框架的行顺序。

包含 PARTITION BY 和 ORDER BY 子句的运行总数¶

您可以组合使用 PARTITION BY 和 ORDER BY 子句来获取分区内的累计总和。在此示例中，分区为一个月，并且由于总和仅适用于分区，因此总和将在每个新月份的月初重置为 0：

SELECT MONTH(sales_date) AS MONTH_NUM,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) ORDER BY sales_date)
          AS MONTHLY_CUMULATIVE_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Copy

查询结果包括显示 MONTHLY_CUMULATIVE_SUM_QUANTITY 列计算方式的其他注释：

+-----------+---------------------------------+
| MONTH_NUM | MONTHLY_CUMULATIVE_SUM_QUANTITY |
|-----------+---------------------------------+
|         1 |                               1 |  -- sum = 1
|         1 |                               4 |  -- sum = 1 + 3
|         1 |                               9 |  -- sum = 1 + 3 + 5
|         2 |                               2 |  -- sum = 0 + 2 (new month)
+-----------+---------------------------------+

Copy

您可以组合使用分区和滑动窗口框架。在下面的示例中，滑动窗口通常有两行宽，但每次到达新分区（即，新月份）时，滑动窗口仅从该分区中的第一行开始：

SELECT
       MONTH(sales_date) AS MONTH_NUM,
       quantity,
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date) 
                           ORDER BY sales_date
                           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 
         AS MONTHLY_SLIDING_SUM_QUANTITY
    FROM sales
    ORDER BY sales_date;

Copy

查询结果包括显示 MONTHLY_SLIDING_SUM_QUANTITY 列计算方式的其他注释：

+-----------+----------+------------------------------+
| MONTH_NUM | QUANTITY | MONTHLY_SLIDING_SUM_QUANTITY |
|-----------+----------+------------------------------+
|         1 |        1 |                           1  |  -- sum = 1
|         1 |        3 |                           4  |  -- sum = 1 + 3
|         1 |        5 |                           8  |  -- sum = 3 + 5
|         2 |        2 |                           2  |  -- sum = 0 + 2 (new month)
+-----------+----------+------------------------------+

Copy

计算一个值与一组值的比率¶

您可以使用 RATIO_TO_REPORT 函数计算一个值与分区中值的总和的比率，然后将该比率作为该总和的百分比返回。该函数将当前行中的值除以分区中所有行的值之和。

SELECT branch_ID,
       city,
       100 * RATIO_TO_REPORT(net_profit) OVER (PARTITION BY city)
    FROM store_sales AS s1
    ORDER BY city, branch_ID;
+-----------+-----------+------------------------------------------------------------+
| BRANCH_ID | CITY      | 100 * RATIO_TO_REPORT(NET_PROFIT) OVER (PARTITION BY CITY) |
|-----------+-----------+------------------------------------------------------------|
|         3 | Montreal  |                                                52.63157900 |
|         4 | Montreal  |                                                47.36842100 |
|         1 | Vancouver |                                                40.00000000 |
|         2 | Vancouver |                                                60.00000000 |
+-----------+-----------+------------------------------------------------------------+

Copy

PARTITION BY 子句定义了 city 列的分区。如果您想查看相对于整个连锁店的利润占比，而不仅仅是特定城市内的商店，请忽略以下 PARTITION BY 子句：

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

Copy