标准 SQL 中的分析函数概念

分析函数针对一组行计算值,并为每行返回一个结果。这与聚合函数不同;聚合函数会为一组行返回一个结果。

分析函数包含一个 OVER 子句,该子句定义了涵盖所要计算行的行窗口。对于每一行,系统会使用选定的行窗口作为输入来计算分析函数结果,并可能进行聚合。

借助分析函数,您可以计算移动平均值、对各项进行排名、计算累计总和,以及执行其他分析。

以下函数可用作分析函数:导航函数编号函数聚合分析函数

分析函数语法

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

表示法规则

  • 方括号“[ ]”表示可选的子句。
  • 圆括号“( )”表示文本括号。
  • 竖线“|”表示逻辑“或”(OR) 。
  • 大括号“{ }”括起一组选项。
  • 方括号内后跟省略号的逗号“[, ... ]”表示可以在逗号分隔列表中重复其前面的项。

说明

分析函数针对一组行计算结果。您可以使用以下语法来构建分析函数:

  • analytic_function_name:执行分析操作的函数。 例如,此处可以使用编号函数 RANK()
  • argument_list:特定于分析函数的参数。 有些函数具有参数,而有些函数则没有。
  • OVER:分析函数语法中位于 OVER 子句前面的必需关键字。
  • over_clause:引用一个窗口,该窗口定义了表中要使用分析函数的一组行。
  • window_specification:定义窗口的规范。
  • window_frame_clause:定义窗口的窗口框架。
  • rows_range:定义窗口框架的物理行或逻辑范围。

备注

分析函数可以在查询的以下两处位置显示为标量表达式操作数:

  • SELECT 列表。如果分析函数显示在 SELECT 列表中,则其参数列表和 OVER 子句不能引用同一 SELECT 列表中引入的别名。
  • ORDER BY 子句。如果分析函数显示在查询的 ORDER BY 子句中,则其参数列表可以引用 SELECT 列表别名。

分析函数的参数列表或 OVER 子句不能引用其他分析函数,即使通过别名间接引用也不行。

分析函数在聚合后进行计算。例如,GROUP BY 子句和非分析聚合函数先进行计算。 由于聚合函数是在分析函数之前进行计算,因此聚合函数可用作分析函数的输入操作数。

返回结果

针对输入中的每一行返回一个结果。

定义 OVER 子句

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

说明

OVER 子句引用一个窗口,该窗口定义了表中要使用分析函数的一组行。您可以提供在查询中定义named_window,也可以定义新窗口的规范

备注

如果命名窗口和窗口规范均未提供,则所有输入行都将包含在每行的窗口中。

使用 OVER 子句的示例

以下查询使用窗口规范:

以下查询使用命名窗口:

定义窗口规范

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
  [ window_frame_clause ]

说明

定义窗口的规范。

  • PARTITION BY:将输入行划分为不同的分区,针对这些分区分别计算分析函数。
    • PARTITION BY 子句可包含多个分区表达式。
    • 表达式不能包含浮点类型、不可分组的类型、常量或分析函数。
    • 如果未使用此可选子句,则输入表中的所有行都归为一个分区。
  • ORDER BY:定义分区中各行的排序方式。 在大多数情况下,此子句是可选的,但在某些情况下是导航函数的必需子句。
  • window_frame_clause:对于聚合分析函数,定义当前分区中的窗口框架。 该窗口框架决定了窗口中要包含的内容。 如果使用此子句,则需要 ORDER BY(完全无界限窗口除外)。

备注

如果 ORDER BY 子句和窗口框架子句均不存在,则窗口框架将包含该分区中的所有行。

对于聚合分析函数,如果存在 ORDER BY 子句但不存在窗口框架子句,则默认使用以下窗口框架子句:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

例如,以下查询是等效的:

SELECT book, LAST_VALUE(item)
  OVER (ORDER BY year)
FROM Library
SELECT book, LAST_VALUE(item)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library

在窗口规范中使用命名窗口的规则

如果您在窗口规范中使用命名窗口,请遵循以下规则:

  • 可以使用您在窗口规范子句中定义的新规范扩展命名窗口中的规范。
  • 不得存在冗余定义。如果您在命名窗口和窗口规范子句中使用 ORDER BY 子句,则会抛出错误。
  • 子句顺序很重要。PARTITION BY 必须排在最前面,然后是 ORDER BYwindow_frame_clause。如果您添加了命名窗口,则系统会先处理其窗口规范。

    --this works:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
    FROM Produce
    WINDOW item_window AS (ORDER BY purchases)
    
    --this does not work:
    SELECT item, purchases, LAST_VALUE(item)
      OVER (item_window ORDER BY purchases) AS most_popular
    FROM Produce
    WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
    
  • 命名窗口和 PARTITION BY 不能同时出现在窗口规范中。如果您需要 PARTITION BY,请将其添加到命名窗口中。

  • 您不能在 ORDER BY 子句、外部查询或任何子查询中引用命名窗口。

使用窗口规范的示例

以下查询在分析函数中定义分区:

以下查询在窗口规范中使用命名窗口:

以下查询定义分区中各行的排序方式:

定义窗口框架子句

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c
  }

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

窗口框架子句定义针对其计算分析函数的分区中,当前行周围的窗口框架。 只有聚合分析函数才能使用窗口框架子句。

  • rows_range:一个子句,用于定义具有物理行或逻辑范围的窗口框架。

    • ROWS:根据相对于当前行的物理偏移量计算窗口框架。例如,您可以在当前行前后添加两行。
    • RANGE:根据当前行附近的行的逻辑范围以及当前行的 ORDER BY 键值对,计算窗口框架。 用当前行的键值加上或减去所提供的范围值,以定义窗口框架的开始或结束范围边界。在基于范围的窗口框架中,ORDER BY 子句中必须只有一个表达式,并且该表达式必须为数字类型。

    提示:如果要使用带日期的范围,请将 ORDER BYUNIX_DATE() 函数搭配使用。如果要使用带时间戳的范围,请使用 UNIX_SECONDS()UNIX_MILLIS()UNIX_MICROS() 函数。

  • frame_between:创建具有上下边界的窗口框架。 第一个边界代表下边界。第二个边界代表上边界。只能使用特定的边界组合,如上面的语法所示。

    • 使用 unbounded_precedingnumeric_precedingnumeric_followingcurrent_row 定义窗口框架的开始位置。
      • unbounded_preceding:窗口框架从分区开始处开始。
      • numeric_precedingnumeric_following:窗口框架的开始位置相对于当前行。
      • current_row:窗口框架从当前行处开始。
    • 使用 numeric_precedingnumeric_followingcurrent_rowunbounded_following 定义窗口框架的结束位置。
      • numeric_precedingnumeric_following:窗口框架的结束位置相对于当前行。
      • current_row:窗口框架在当前行处结束。
      • unbounded_following:窗口框架在分区结束处结束。
  • frame_start:创建具有下边界的窗口框架。 窗口框架在当前行处结束。

    • unbounded_preceding:窗口框架从分区开始处开始。
    • numeric_preceding:窗口框架的开始位置相对于当前行。
    • current_row:窗口框架从当前行处开始。
  • numeric_expression:表示数字类型的表达式。 数字表达式必须是常量、非负整数或参数。

备注

如果边界超出分区的开始或结束位置,则窗口框架将仅包含该分区中的行。

您不能将窗口框架子句用于导航函数编号函数,例如 RANK()

使用窗口框架子句的示例

以下查询使用 ROWS 计算值:

以下查询使用 RANGE 计算值:

以下查询使用部分或完全无界限的窗口计算值:

以下查询使用数字边界计算值:

以下查询以当前行为边界计算值:

引用命名窗口

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

命名窗口表示表中要使用分析函数的一组行。命名窗口在 WINDOW 子句中定义,并在分析函数的 OVER 子句中引用。 在 OVER 子句中,命名窗口可以单独出现,也可以嵌入到窗口规范中。

示例

导航函数通常针对窗口框架中与当前行不同的行计算一些 value_expressionOVER 子句语法因导航函数而异。

OVER 子句的要求:

  • PARTITION BY:可选。
  • ORDER BY
    1. 不可用于 PERCENTILE_CONTPERCENTILE_DISC
    2. 对于 FIRST_VALUELAST_VALUENTH_VALUELEADLAG 为必需项。
  • window_frame_clause
    1. 不可用于 PERCENTILE_CONTPERCENTILE_DISCLEADLAG
    2. FIRST_VALUELAST_VALUENTH_VALUE 的可选项。

在所有导航函数中,结果数据类型与 value_expression 的类型相同。

编号函数概念

编号函数会根据每一行在指定窗口中的位置向该行分配整数值。

RANK()DENSE_RANK()ROW_NUMBER() 示例:

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| x          | rank       | dense_rank | row_num    |
+---------------------------------------------------+
| 1          | 1          | 1          | 1          |
| 2          | 2          | 2          | 2          |
| 2          | 2          | 2          | 3          |
| 5          | 4          | 3          | 4          |
| 8          | 5          | 4          | 5          |
| 10         | 6          | 5          | 6          |
| 10         | 6          | 5          | 7          |
+---------------------------------------------------+
  • RANK():对于 x=5,rank 为 4,因为 RANK() 按前一个窗口排序组中对等项的数量递增。
  • DENSE_RANK():对于 x=5,dense_rank 为 3,因为 DENSE_RANK() 总是递增 1,绝不跳过值。
  • ROW_NUMBER()对于 x=5,row_num 为 4。

聚合分析函数概念

聚合函数是对一组值执行计算的函数。大多数聚合函数都可以在分析函数中使用。这些聚合函数称为聚合分析函数

使用聚合分析函数时,OVER 子句会附加到聚合函数调用;函数调用语法保持不变。与对应的聚合函数一样,这些分析函数执行聚合操作,但专门针对每一行的相关窗口框架执行聚合操作。而且,这些分析函数的结果数据类型与其对应的聚合函数相同。

分析函数示例

在这些示例中,突出显示的项是当前行。加粗的项是分析中包含的行。

示例中使用的通用表

后续聚合分析查询示例会使用以下表:ProduceEmployeesFarm

Produce 表

一些示例引用名为 Produce 的表:

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

Employees 表

一些示例引用名为 Employees 的表:

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

Farm 表

一些示例引用名为 Farm 的表:

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

计算总计

此示例计算 Produce 表中所有项的总计。

  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
  • orangeappleleekcabbagelettucekale)= 总共购买 54 次
SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

计算小计

此示例针对 Produce 表中的每个类别计算一个小计。

  • fruit
    • orangeapple)= 总共购买 10 次
    • orangeapple)= 总共购买 10 次
  • vegetable
    • leekcabbagelettucekale)= 总共购买 44 次
    • leekcabbagelettucekale)= 总共购买 44 次
    • leekcabbagelettucekale)= 总共购买 44 次
    • leekcabbagelettucekale)= 总共购买 44 次
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

计算累计总和

此示例针对 Produce 表中的每个类别计算一个累计总和。函数会根据使用 ORDER BY 子句定义的顺序计算总和。

  • fruit
    • orange、apple)= 总共购买 2 次
    • orangeapple)= 总共购买 10 次
  • vegetable
    • leek、cabbage、lettuce、kale)= 总共购买 2 次
    • leekcabbage、lettuce、kale)= 总共购买 11 次
    • leekcabbagelettuce、kale)= 总共购买 21 次
    • leekcabbagelettucekale)= 总共购买 44 次
SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

以下示例执行与上一示例相同的操作。除非您希望提高可读性,否则无需将 CURRENT ROW 添加为边界。

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

在此示例中,Produce 表中的所有项都包含在分区中。函数只会分析前几行。分析从分区中当前行之前的两行开始。

  • orange、leek、apple、cabbage、lettuce、kale)= NULL
  • (orange、leek、apple、cabbage、lettuce、kale)= NULL
  • orange、leek、apple、cabbage、lettuce、kale)= 2
  • orangeleek、apple、cabbage、lettuce、kale)= 4
  • orangeleekapple、cabbage、lettuce、kale)= 12
  • orangeleekapplecabbage、lettuce、kale)= 21
SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

计算移动平均值

此示例计算 Produce 表中的移动平均值。 下边界是当前行的前一行。上边界是当前行的后一行。

  • orangeleek、apple、cabbage、lettuce、kale)= 平均购买 2 次
  • orangeleekapple、cabbage、lettuce、kale)= 平均购买 4 次
  • (orange、leekapplecabbage、lettuce、kale)= 平均购买 6.3333 次
  • (orange、leek、applecabbagelettuce、kale)=平均购买 9 次
  • (orange、leek、apple、cabbagelettucekale)= 平均购买 14 次
  • (orange、leek、apple、cabbage、lettucekale)= 平均购买 16.5 次
SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

计算某个范围内的项数

在此示例中,我们获取 Farm 表中总数相近的动物数量。

  • goosedogoxgoat、duck、cat)= 4 种动物的总数介于 0-2 范围。
  • goosedogoxgoatduck、cat)= 5 种动物的总数介于 1-3 范围。
  • goosedogoxgoatduck、cat)= 5 种动物的总数介于 1-3 范围。
  • goosedogoxgoatduck、cat)= 5 种动物的总数介于 1-3 范围。
  • (goose、dogoxgoatduck、cat)= 4 种动物的总数介于 2-4 范围。
  • (goose、dog、ox、goat、duck、cat)= 1 种动物的总数介于 22-24 范围。
SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

此示例获取每个类别中最受欢迎的项。其中定义了窗口中的各行在每个分区中的分区和排序方式,并引用了 Produce 表。

  • fruit
    • orangeapple)= apple 最受欢迎
    • orangeapple)= apple 最受欢迎
  • vegetable
    • leekcabbagelettucekale)= kale 最受欢迎
    • leekcabbagelettucekale)= kale 最受欢迎
    • leekcabbagelettucekale)= kale 最受欢迎
    • leekcabbagelettucekale)= kale 最受欢迎
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

获取某一范围内的最后一个值

在此示例中,我们使用 Produce 表获取特定窗口框架中最受欢迎的项。此窗口框架一次最多分析三行。请仔细查看蔬菜对应的 most_popular 列。 与其获取特定类别中最受欢迎的项,不如获取该类别中特定范围内最受欢迎的项。

  • fruit
    • orangeapple)= apple 最受欢迎
    • orangeapple)= apple 最受欢迎
  • vegetable
    • leekcabage、lettuce、kale)= cabage 最受欢迎
    • leekcabbagelettuce、kale)= lettuce 最受欢迎
    • (leek、cabbagelettucekale)= kale 最受欢迎
    • (leek、cabbage、lettucekale)= kale 最受欢迎
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

以下示例返回的结果与上述示例相同,但添加了一个名为 item_window 的命名窗口。一些窗口规范直接在 OVER 子句中定义,而另一些则在命名窗口中定义。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

计算排名

此示例根据每位员工的入职日期计算其在所属部门中的排名。窗口规范直接在 OVER 子句中定义。此示例引用 Employees 表。

  • department 1
    • JacobAnthonyAndrew)= 为 Jacob 分配排名 1
    • JacobAnthonyAndrew)= 为 Anthony 分配排名 2
    • JacobAnthonyAndrew)= 为 Andrew 分配排名 3
  • department 2
    • IsabellaDanielJose)= 为 Isabella 分配排名 1
    • IsabellaDanielJose)= 为 Daniel 分配排名 2
    • IsabellaDanielJose)= 为 Jose 分配排名 3
SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

在窗口框架子句中使用命名窗口

您可以在命名窗口和窗口框架子句中分别定义一部分逻辑。这些逻辑会组合在一起。下面是一个使用 Produce 表的示例。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

您还可以通过以下示例获取前面的结果:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)
SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

由于窗口框架子句被定义了两次,以下示例会产生错误:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)