使用窗口函数¶

与排名相关的窗口函数¶

顺序敏感型函数的最简单子类别是与排名相关的函数子类别。

最简单的与排名相关的函数是 RANK 函数。排名函数仅列出排名，即有序行窗口中行的顺序。窗口中的第一行排名为 1；窗口中的第二行排名为 2；以此类推。下面的示例显示了基于销售额的销售人员排名顺序：

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

Copy

RANK 函数不对行进行排序；在调用 RANK 之前，行必须在窗口中按顺序排列。若要按顺序排列行，请使用 OVER 子句的 ORDER BY 子句分支。

为了说明 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

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

• 传递给函数的单个行。

• 传递给函数的窗口中其他行的值。

• 窗口中所有行的顺序。

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

窗口框架函数¶

窗口框架 是窗口中行的子集。

窗口框架函数使用窗口框架来计算移动平均线等内容。

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

• 累积。

• 滑动。

滑动窗口框架¶

您可以通过设想从正在与栅栏平行行驶的汽车侧窗向外看的情景来理解滑动窗口的概念。如果栅栏柱之间的间距均匀，且车窗宽度是栅栏柱间距的整数倍，那么您看到的栅栏柱数量将保持不变；然而，随着汽车前行，“旧”的栅栏柱会离开您的视线范围，“新”的栅栏柱则进入视线范围，尽管在任何时刻您看到的栅栏柱数量相同，但您看的并不是同一根栅栏柱。

滑动窗口框架是一种固定宽度的窗口框架，它沿着窗口中的行“滑动”，每次都显示窗口的不同切片。与驶过栅栏柱的汽车一样，窗口框架沿着数据移动，旧行从框架中消失，新行出现，因此框架的宽度（框架中的行数）始终相同。

滑动窗口通常用于计算移动平均线。移动平均线是根据固定大小的区间（例如天数）计算的平均值。平均值是“移动的”，因为尽管间隔的大小是恒定的，但随着窗口的滑动，间隔中的实际值会随时间（或其他因素）而变化。

例如，股票市场分析师通常部分基于股票价格的 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 日（含）的平均价格。

• 等

备注

尽管滑动窗口是固定宽度的，但当窗口首次应用于新数据源时，数据源可能没有足够的数据来填充该窗口。例如，如果记录保存从 4 月 1 日开始，则从 4 月 1 日到 6 月 29 日，滑动窗口将包含少于 91 天的数据。只有在窗口完成填充后，窗口的宽度才会保持不变。

下面的示例在当月的前 7 天使用一个小的（3 天）滑动窗口。这演示了窗口框架如何在窗口中滑动，始终保留 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 |
|    ... |   ... |           ... |
+--------+-------+---------------+

Copy

有关顺序敏感型窗口函数的更多信息¶

PARTITION BY 和 ORDER BY 子句是独立的。您可以使用 ORDER BY 子句而不使用 PARTITION BY 子句。要将您的商店与连锁店中的所有其他商店（而不仅仅是您所在城市的其他商店）进行排名，请使用以下查询：

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

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

有些窗口函数对顺序敏感，有些则不然。

• 某些窗口函数需要 ORDER BY 子句。

• 某些窗口函数禁止 ORDER BY 子句。

• 某些窗口函数使用 ORDER BY 子句（如果存在），但不需要该子句。

• 某些窗口函数将 ORDER BY 子句视为隐式累积窗口框架子句。（有关隐式窗口框架的更多信息，请参阅 窗口框架使用说明。）

下图显示了窗口函数、窗口框架函数和排名相关函数之间的关系：

                      Window Functions

  Order-insensitive Functions      Order-sensitive Functions
+-----------------------------+--------------------------------------------------------------------+
|                             | Rank-related Functions                                             |
|                             +--------------------------------------------------------------------+
|                             | Functions that can be both Rank-related and Window Frame functions |
|                             +--------------------------------------------------------------------+
|                             | Window Frame Functions                                             |
+-----------------------------+--------------------------------------------------------------------+

如上图所示：

• 某些窗口函数对顺序不敏感。

• 某些窗口函数对顺序敏感。

• 顺序敏感型窗口函数分为两类：

  • 与排名相关的函数。

  • 窗口框架函数。

• 一些顺序敏感型函数既是与排名相关的函数，也是窗口框架函数。

窗口函数与聚合函数¶

许多窗口函数和 聚合函数 名称相同。例如，有一个 SUM() 窗口函数和一个 SUM() 聚合函数。

要区分两者的用法，请记住：

• 对于聚合函数，输入为多行，输出为 1 行。

• 对于窗口函数，有两项输入，一项是行窗口，一项是该窗口内的一行，输出为每个 输入行 1 行。

聚合函数会指明有关所有合并行的信息，例如 SUM，但不提供有关单个行的信息。

窗口函数会指明当前行相对于窗口中所有其他行的一些信息。

以下 SQL 语句显示了使用 SUM() 聚合函数（为整个输入行组返回 1 行）和使用 SUM() 窗口函数（为输入行组中的每一行返回 1 行）之间的区别：

SELECT SUM(net_profit) 
    FROM store_sales;
+-----------------+
| SUM(NET_PROFIT) |
|-----------------|
|        44000.00 |
+-----------------+
SELECT branch_ID, SUM(net_profit) OVER ()
    FROM store_sales
    ORDER BY branch_id;
+-----------+-------------------------+
| BRANCH_ID | SUM(NET_PROFIT) OVER () |
|-----------+-------------------------|
|         1 |                44000.00 |
|         2 |                44000.00 |
|         3 |                44000.00 |
|         4 |                44000.00 |
+-----------+-------------------------+

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 子句的窗口¶

窗口子句的 PARTITION BY 子句分支根据输入表达式的值将数据划分为不同的子集； SELECT 语句应用于每个子集，输出具有每个子集的行。

请注意，这与 GROUP BY 子句的工作方式相似，但并不完全相同。

下一个示例显示每个月的销售数量，并使用 PARTITION BY 子句将数据划分为一个月的子集：

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date)) AS QUANTITY
    FROM sales
    ORDER BY sales_date;
+-----------+----------+
| MONTH_NUM | QUANTITY |
|-----------+----------|
|         1 |        9 |
|         1 |        9 |
|         1 |        9 |
|         2 |        2 |
+-----------+----------+

Copy

如您所见，前三行是重复的。月份 #1 有 3 个输入行，窗口函数为每个输入行生成一个输出行，因此输出中有 3 个月份 #1 的输出行。SUM 函数不用作累积函数或滑动函数；它应用于整个窗口，并且每次都返回整个窗口的相同值，因此该函数返回重复值，如上所示。

您可以使用 DISTINCT 关键字减少重复项：

SELECT DISTINCT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity) OVER (PARTITION BY MONTH(sales_date))
    FROM sales
    ORDER BY MONTH_NUM;
+-----------+-----------------------------------------------------+
| MONTH_NUM | SUM(QUANTITY) OVER (PARTITION BY MONTH(SALES_DATE)) |
|-----------+-----------------------------------------------------|
|         1 |                                                   9 |
|         2 |                                                   2 |
+-----------+-----------------------------------------------------+

Copy

在这种特殊情况下，您可以使用 GROUP BY 子句而不是窗口子句。例如：

SELECT MONTH(sales_date) AS MONTH_NUM, 
       SUM(quantity)
    FROM sales
    GROUP BY MONTH_NUM
    ORDER BY MONTH_NUM;
+-----------+---------------+
| MONTH_NUM | SUM(QUANTITY) |
|-----------+---------------|
|         1 |             9 |
|         2 |             2 |
+-----------+---------------+

Copy

但是， GROUP BY 不如窗口子句灵活。此外，在更复杂的查询中，不能总是用 GROUP BY 代替窗口子句。

包含 ORDER BY 子句的窗口¶

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

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

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

累积窗口和滑动窗口如下所述。

包含 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