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 子句
  • 帧子句
    • 帧单位
    • 帧边界
  • 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

有关窗口帧工作原理的更多信息，请参阅 帧子句。

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

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 子句时，窗口帧边界可以是显式的或隐式的，它会限制窗口帧的大小，相对于当前行在两个方向上。

帧子句

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

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

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

帧单位

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

• RANGE
• ROWS
• GROUPS

RANGE

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

偏移量是当前行值与周围行值之间的差值。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_start 和 frame_end）定义窗口函数操作的每个帧的边界。

• UNBOUNDED PRECEDING
• offset PRECEDING
• 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

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

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，表示值在集合中的相对排名。OVER 子句中的 ORDER BY 子句用于正确计算当前行值的累积分布。

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() 不同）。

OVER 子句中的 ORDER BY 子句决定排名顺序。

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 的值，尽可能平均地分割分区。OVER 子句中的 ORDER BY 子句决定排名顺序。

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)

OVER 子句中的 ORDER BY 子句决定排名顺序。

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() 不同），然后继续用下一个不同值进行排名。

OVER 子句中的 ORDER BY 子句决定排名顺序。

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 开始计数。OVER 子句中的 ORDER BY 子句决定行顺序。

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：要从表达式中检索值的当前行之前的行数。默认为 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

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

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

SELECT
  current.time,
  current.room,
  current.temp AS current_temp,
  previous.temp AS temp_1h_ago,
  current.temp - previous.temp AS hourly_diff
FROM home AS current
LEFT JOIN home AS previous
  ON current.room = previous.room
  AND previous.time = current.time - INTERVAL '1 hour'
WHERE
  current.time >= '2022-01-01T08:00:00Z'
  AND current.time < '2022-01-01T12:00:00Z'
ORDER BY current.room, current.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：要从表达式中检索值的当前行之前的行数。默认为 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 second_temp
FROM home
WHERE
  time >= '2025-02-10T08:00:00Z'
  AND time < '2025-02-10T11:00:00Z'