窗口函数¶

窗口函数在查询结果的多行之间执行计算。它们在 HAVING 子句之后、最终查询的 ORDER BY 子句之前运行。

调用窗口函数需要使用 OVER 子句：

function(args) OVER (
    [PARTITION BY expression]
    [ORDER BY expression [ASC|DESC]]
    [frame]
)

PARTITION BY 子句将行划分为独立的窗口分区。窗口 ORDER BY 子句决定窗口函数使用的顺序；窗口 ORDER BY 子句不支持 ORDER BY 1 这类序号引用。

已验证的窗口框架形式为：

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

frame_start 和 frame_end 可以是：

UNBOUNDED PRECEDING
expression PRECEDING
CURRENT ROW
expression FOLLOWING
UNBOUNDED FOLLOWING

如果未指定框架，带 ORDER BY 的窗口使用从 RANGE UNBOUNDED PRECEDING 到 CURRENT ROW 的范围。不带 ORDER BY 的窗口使用整个分区。

聚合窗口函数¶

为聚合函数添加 OVER 子句即可将其用作窗口函数。聚合会针对每一行，在当前窗口框架内的行上计算。

SELECT grp, v,
       sum(v) OVER (
           PARTITION BY grp
           ORDER BY v
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_sum
FROM (VALUES (1, 10), (1, 20), (2, 5), (2, 7)) AS t(grp, v)
ORDER BY grp, v;
-- (1, 10, 10), (1, 20, 30), (2, 5, 5), (2, 7, 12)

排名函数¶

cume_dist() -> double()¶

返回当前行在窗口分区中的累积分布。窗口排序中并列的行返回相同值。

dense_rank() -> bigint()¶

返回当前行的排名，并列组之间不留空位。

ntile(n) -> bigint()¶

将每个窗口分区中的行划分为 n 个桶，编号从 1 到 n。桶数量必须是正的常量整数。

percent_rank() -> double()¶

返回 (r - 1) / (n - 1)，其中 r 是当前行的 rank()，n 是分区中的行数。单行分区返回 0.0。

rank() -> bigint()¶

返回当前行的排名。并列行具有相同排名，下一个排名会为这些并列行留下空位。

row_number() -> bigint()¶

根据窗口分区内的排序，为每一行返回从一开始的连续编号。

SELECT v,
       rank() OVER (ORDER BY v),
       dense_rank() OVER (ORDER BY v),
       percent_rank() OVER (ORDER BY v),
       cume_dist() OVER (ORDER BY v)
FROM (VALUES (10), (20), (20), (30)) AS t(v)
ORDER BY v;
-- (10, 1, 1, 0.0, 0.25)
-- (20, 2, 2, 0.3333333333333333, 0.75)
-- (20, 2, 2, 0.3333333333333333, 0.75)
-- (30, 4, 3, 1.0, 1.0)

值函数¶

值函数支持在函数调用之后、OVER 之前指定 RESPECT NULLS 和 IGNORE NULLS。默认会保留 null 值。如果指定 IGNORE NULLS，值表达式为 null 的行会从值函数计算中排除。

first_value(x) -> [same as input]()¶

返回窗口框架中的第一个值。

last_value(x) -> [same as input]()¶

返回窗口框架中的最后一个值。

nth_value(x, offset) -> [same as input]()¶

返回窗口框架开头起指定的从 1 开始计数偏移处的值。偏移量必须是正的常量整数。如果偏移量大于框架中的值数量，则返回 null。

lead(x[, offset [, default_value]]) -> [same as input]()¶

返回窗口分区中当前行之后 offset 行处的值。默认偏移量为 1。如果偏移量指向分区外的行，则返回 default_value；未指定默认值时返回 null。偏移量和默认值参数必须是常量。

lag(x[, offset [, default_value]]) -> [same as input]()¶

返回窗口分区中当前行之前 offset 行处的值。默认偏移量为 1。如果偏移量指向分区外的行，则返回 default_value；未指定默认值时返回 null。偏移量和默认值参数必须是常量。

SELECT id,
       first_value(v) OVER win,
       last_value(v) OVER win,
       nth_value(v, 2) OVER win,
       lag(v) OVER win,
       lead(v, 2) OVER win
FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) AS t(id, v)
WINDOW win AS (
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY id;
-- (1, 10, 40, 20, NULL, 30)
-- (2, 10, 40, 20, 10, 40)
-- (3, 10, 40, 20, 20, NULL)
-- (4, 10, 40, 20, 30, NULL)