SQL 窗口函数

窗口函数允许您计算运行总计、移动平均或其他类似聚合的结果，而无需将行折叠成组。它们在其计算范围内对行的“窗口”执行计算，您可以以各种方式对其进行分区和排序，并为集合中的每一行返回一个计算值。

与非窗口聚合函数（将每个组组合成单行）不同，窗口函数保留每一行的标识，并为分区中的每一行计算一个附加值。

例如，以下查询使用家庭传感器示例数据，并返回每个温度读数，以及查询时间范围内每个房间的平均温度

SELECT
  time,
  room,
  temp,
  avg(temp) OVER (PARTITION BY room) AS avg_room_temp
FROM
  home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time <= '2022-01-01T09:00:00Z'
ORDER BY
  room,
  time

• 窗口框架
• 窗口函数语法
  • OVER 子句
  • PARTITION BY 子句
  • ORDER BY 子句
  • Frame 子句
    • Frame 单位
    • Frame 边界
  • WINDOW 子句
• 聚合函数
• 排名函数
  • cume_dist
  • dense_rank
  • ntile
  • percent_rank
  • rank
  • row_number
• 分析函数
  • first_value
  • lag
  • last_value
  • lead
  • nth_value

窗口框架

由于窗口函数对行进行操作，因此在行的分区中有一组行，窗口函数使用该组行来执行操作。这组行称为窗口帧。窗口帧边界可以使用相对于当前行的 RANGE、ROW 或 GROUPS 帧单位来定义 - 例如

SELECT
  time,
  temp,
  avg(temp) OVER (
    ORDER BY time
    RANGE INTERVAL '3 hours' PRECEDING
  ) AS 3h_moving_avg
FROM home
WHERE room = 'Kitchen'

SELECT
  time,
  temp,
  avg(temp) OVER (
    ROWS 3 PRECEDING
  ) AS moving_avg
FROM home
WHERE room = 'Kitchen'

SELECT
  time,
  room,
  temp,
  avg(temp) OVER (
    ORDER BY room
    GROUPS 1 PRECEDING
  ) AS moving_avg
FROM home

有关窗口帧如何工作的更多信息，请参阅 frame 子句。

如果您未指定窗口帧，则窗口函数将使用当前分区中的所有行来执行其操作。

function([expr])
  OVER(
    [PARTITION BY expr[, …]]
    [ORDER BY expr [ ASC | DESC ][, …]]
    [ frame_clause ]
    )

OVER 子句

窗口函数使用直接跟随窗口函数的 OVER 子句
名称和参数。
OVER 子句在语法上区分窗口
函数与非窗口函数或聚合函数，并定义如何对行进行分组和排序以进行窗口操作。

PARTITION BY 子句

OVER 子句中的 PARTITION BY 子句将行划分为组或分区，这些组或分区共享 PARTITION BY 表达式的相同值。窗口函数对与当前行位于同一分区中的所有行进行操作。

ORDER BY 子句

OVER 子句内的 ORDER BY 子句控制窗口函数处理每个分区中行的顺序。当窗口子句包含 ORDER BY 子句时，窗口帧边界可以是显式的或隐式的，从而限制窗口帧大小在相对于当前行的两个方向上。

Frame 子句

frame 子句定义窗口帧边界，可以是以下之一

{ RANGE | ROWS | GROUPS } frame_start
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end

• Frame 单位
  • RANGE
  • ROWS
  • GROUPS
• Frame 边界
  • UNBOUNDED PRECEDING
  • offset PRECEDING
  • CURRENT ROW
  • offset FOLLOWING
  • UNBOUNDED FOLLOWING

Frame 单位

定义窗口帧时，可以使用以下帧单位之一

• RANGE
• ROWS
• GROUPS

RANGE

使用在 ORDER BY 子句中指定的列的值，在相对于当前行值的value范围内定义帧边界。

偏移量是当前行值与周围行值之间的差值。RANGE 支持以下偏移量类型

• 数值（非负数）
• 数值字符串（非负数）
• 间隔

... OVER (
  ORDER BY wind_direction
  RANGE BETWEEN 45 PRECEDING AND 45 FOLLOWING
)

... OVER (
  ORDER BY time
  RANGE BETWEEN
    INTERVAL '3 hours' PRECEDING
    AND INTERVAL '1 hour' FOLLOWING
)

ROWS

使用相对于当前行的行位置定义窗口帧边界。偏移量是与当前行的行位置的差值。ROWS 支持以下偏移量类型

• 数值（非负数）
• 数值字符串（非负数）

... OVER (
  ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
)

GROUPS

使用行组定义窗口帧边界。对于 ORDER BY 子句中的列具有相同值的行，组成一个行组。

偏移量是相对于当前行组的行组位置的差值。GROUPS 支持以下偏移量类型

• 数值（非负数）
• 数值字符串（非负数）

... OVER (
  ORDER BY country, city
  GROUPS ...
)

... OVER (
  ORDER BY country, city
  GROUPS 2 PRECEDING
)

Frame 边界

帧边界（frame_start 和 frame_end）定义窗口函数在其上运行的每个帧的边界。

• UNBOUNDED PRECEDING
• offset PRECEDING
• CURRENT_ROW](#current-row)
• offset> FOLLOWING
• UNBOUNDED FOLLOWING

UNBOUNDED PRECEDING

从分区的首行开始，到当前行结束。

UNBOUNDED PRECEDING

offset PRECEDING

从当前行之前的 offset 帧单位开始，到当前行结束。例如，3 PRECEDING 包括当前行之前的 3 行。

<offset> PRECEDING

CURRENT ROW

用作边界时，在当前行开始和结束。

CURRENT ROW

offset FOLLOWING

从当前行开始，到当前行之后的 offset 帧单位结束。例如，3 FOLLOWING 包括当前行之后的 3 行。

<offset> FOLLOWING

UNBOUNDED FOLLOWING

从当前行开始，到分区的最后一行结束。

offset FOLLOWING

使用指定的 帧单位 偏移量在当前行之后作为帧边界。

offset FOLLOWING

UNBOUNDED FOLLOWING

使用当前行到当前分区末尾的帧边界。

UNBOUNDED FOLLOWING

WINDOW 子句

使用 WINDOW 子句为窗口规范定义可重用的别名。当查询中的多个窗口函数共享相同的窗口定义时，这非常有用。

无需为每个函数重复相同的 OVER 子句，只需定义一次窗口并通过别名引用它 - 例如

SELECT
  sum(net_gain) OVER w,
  avg(net_net) OVER w
FROM
  finance
WINDOW w AS ( PARTITION BY ticker ORDER BY time DESC);

聚合函数

所有 聚合函数 都可以用作窗口函数。

排名函数

• cume_dist
• dense_rank
• ntile
• percent_rank
• rank
• row_number

cume_dist

返回组中值的累积分布。返回的值大于 0 且小于或等于 1，并表示值在值集中的相对排名。ORDER BY 子句在 OVER 子句中用于正确计算当前行值的累积分布。

cume_dist()

SELECT
  time,
  room,
  temp,
  cume_dist() OVER (
    PARTITION BY room
    ORDER BY temp 
  ) AS cume_dist
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T12:00:00Z'

dense_rank

返回当前行在其分区中的排名。排名是连续的；为重复值分配相同的排名编号，并且排名序列继续使用下一个不同的值（与 rank() 不同）。

ORDER BY 子句在 OVER 子句中确定排名顺序。

dense_rank()

SELECT
  time,
  room,
  temp,
  dense_rank() OVER (
    PARTITION BY room
    ORDER BY temp 
  ) AS dense_rank
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T12:00:00Z'

SELECT
  id,
  rank() OVER(ORDER BY id),
  dense_rank() OVER(ORDER BY id),
  row_number() OVER(ORDER BY id)
FROM my_table;

ntile

将有序分区中的行分配到指定数量的组中。每个组都编号，从一开始。对于每一行，ntile 返回该行所属的组号。组号范围从 1 到 expression 值，尽可能均匀地划分分区。ORDER BY 子句在 OVER 子句中确定排名顺序。

ntile(expression)

参数

• expression：整数。要将分区拆分成的组数。

SELECT
  time,
  temp,
  ntile(4) OVER (
    ORDER BY time 
  ) AS ntile
FROM home
WHERE
  room = 'Kitchen'
  AND time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T15:00:00Z'

percent_rank

返回当前行在其分区中的百分比排名。返回的值介于 0 和 1 之间，计算方式为

(rank - 1) / (total_rows - 1)

ORDER BY 子句在 OVER 子句中确定排名顺序。

percent_rank()

SELECT
  time,
  room,
  temp,
  percent_rank() OVER (
    PARTITION BY room
    ORDER BY temp 
  ) AS percent_rank
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'

rank

返回当前行在其分区中的排名。对于重复值，rank 为它们分配相同的排名编号，跳过后续排名（与 dense_rank() 不同），然后继续使用下一个不同的值进行排名。

ORDER BY 子句在 OVER 子句中确定排名顺序。

rank()

SELECT
  time,
  room,
  temp,
  rank() OVER (
    PARTITION BY room
    ORDER BY temp 
  ) AS rank
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'

SELECT
  id,
  rank() OVER(ORDER BY id),
  dense_rank() OVER(ORDER BY id),
  row_number() OVER(ORDER BY id)
FROM my_table;

row_number

返回当前行在其分区中的位置，从 1 开始计数。ORDER BY 子句在 OVER 子句中确定行顺序。

row_number()

SELECT
  time,
  room,
  temp,
  row_number() OVER (
    PARTITION BY room
    ORDER BY temp 
  ) AS row_number
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'

SELECT
  id,
  rank() OVER(ORDER BY id),
  dense_rank() OVER(ORDER BY id),
  row_number() OVER(ORDER BY id)
FROM my_table;

分析函数

• first_value
• lag
• last_value
• lead
• nth_value

first_value

从窗口帧的第一行返回值。

first_value(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及算术运算符的任意组合。

相关函数

last_value

SELECT
  time,
  room,
  temp,
  first_value(temp) OVER (
    PARTITION BY room
    ORDER BY time 
  ) AS first_value
FROM home
WHERE  
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time

lag

从分区中当前行之前指定偏移量的行返回值。如果偏移行在分区之外，则函数返回指定的默认值。

lag(expression, offset, default)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及算术或字符串运算符的任意组合。
• offset：从当前行之前多少行检索 expression 的值。默认值为 1。
• default：如果偏移量在分区中，则返回的默认值。必须与 expression 的类型相同。

相关函数

lead

SELECT
  time,
  room,
  temp,
  lag(temp, 1, 0) OVER (
    PARTITION BY room
    ORDER BY time
  ) AS previous_value
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time

last_value

从窗口帧的最后一行返回值。

last_value(expression)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及算术运算符的任意组合。

相关函数

first_value

SELECT
  time,
  room,
  temp,
  last_value(temp) OVER (
    PARTITION BY room
    ORDER BY time
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  ) AS last_value
FROM home
WHERE  
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time

lead

从分区中当前行之后指定偏移量的行返回值。如果偏移行在分区之外，则函数返回指定的默认值。

lead(expression, offset, default)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及算术或字符串运算符的任意组合。
• offset：从当前行之前多少行检索 expression 的值。默认值为 1。
• default：如果偏移量在分区中，则返回的默认值。必须与 expression 的类型相同。

相关函数

lag

SELECT
  time,
  room,
  temp,
  lead(temp, 1, 0) OVER (
    PARTITION BY room
    ORDER BY time
  ) AS next_value
FROM home
WHERE
  time >= '2022-01-01T08:00:00Z'
  AND time < '2022-01-01T11:00:00Z'
ORDER BY room, time

nth_value

从窗口帧的第 n 行返回值（从 1 开始计数）。如果第 n 行不存在，则函数返回 null。

nth_value(expression, n)

参数

• expression：要操作的表达式。可以是常量、列或函数，以及算术或字符串运算符的任意组合。
• n：指定要引用的当前帧和分区中的行号。

SELECT
  time,
  room,
  temp,
  nth_value(temp, 2) OVER (
    PARTITION BY room
  ) AS "2nd_temp"
FROM home
WHERE  
  time >= '2025-02-10T08:00:00Z'
  AND time < '2025-02-10T11:00:00Z'