窗口函数¶

窗口函数在查询结果的各行中执行计算。它们在HAVING子句之后但ORDER BY子句之前运行。调用窗口函数需要使用OVER子句的特殊语法来指定窗口，如下所示

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

一个frame是以下之一

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

frame_start和frame_end可以是以下任何一个

UNBOUNDED PRECEDING
expression PRECEDING
CURRENT ROW
expression FOLLOWING
UNBOUNDED FOLLOWING

窗口定义有 3 个组成部分

• PARTITION BY子句将输入行分成不同的分区。这类似于GROUP BY子句如何将行分成不同的组以用于聚合函数。如果没有指定PARTITION BY，则整个输入将被视为单个分区。

• ORDER BY子句确定窗口函数将处理输入行的顺序。如果没有指定ORDER BY，则排序顺序是未定义的。**请注意，窗口函数中的 ORDER BY 子句不支持序数。您需要使用实际的表达式**

• frame子句指定函数为给定输入行处理的行滑动窗口。一个帧可以是ROWS类型，RANGE类型或GROUPS类型，并且它从frame_start运行到frame_end。如果没有指定frame_end，则使用默认值为CURRENT ROW。

  在ROWS模式下，CURRENT ROW特指当前行。在RANGE和GROUPS模式下，CURRENT ROW指的是当前行的任何同行，用于ORDER BY的目的。如果没有指定ORDER BY，则所有行都被认为是当前行的同行。在RANGE和GROUPS模式下，CURRENT ROW的帧开始指的是当前行的第一个同行，而CURRENT ROW的帧结束指的是当前行的最后一个同行。

  在ROWS模式下，expression PRECEDING或expression FOLLOWING的帧开始和结束将帧的开始或结束定义为当前行之前或之后的指定行数。expression必须是INTEGER类型。

  在RANGE模式下，expression PRECEDING或expression FOLLOWING的帧开始和结束将帧的开始或结束定义为排序键与当前行的值差。排序键必须是expression的相同类型，或者可以强制转换为与expression相同的类型。

  在GROUPS模式下，expression PRECEDING或expression FOLLOWING的帧开始和结束将帧的开始或结束定义为当前行的组数。一个组包括排序键上具有相同值的所以行。expression的类型必须是 INTEGER 或 BIGINT。

  如果没有指定帧，则使用默认帧RANGE UNBOUNDED PRECEDING。

示例¶

以下查询按价格对每个店员的订单进行排名

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

以下查询演示了帧定义中ROWS、RANGE和GROUPS之间的区别

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b'], ['a', 'b', 'c'], ['b', 'c', 'd'], ['c', 'd', 'e'], ['d', 'e']

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b'], ['a', 'b'], ['c', 'd'], ['c', 'd'], ['e']

SELECT
    ARRAY_AGG(v) OVER (
        ORDER BY k ASC GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    )
FROM (
    VALUES (1, 'a'), (1, 'b'), (3, 'c'), (3, 'd'), (5, 'e')
) t(k, v); -- ['a', 'b', 'c', 'd'], ['a', 'b', 'c', 'd'], ['a', 'b', 'c', 'd', 'e'], ['a', 'b', 'c', 'd', 'e'], ['c', 'd', 'e']

聚合函数¶

所有聚合函数都可以通过添加OVER子句用作窗口函数。聚合函数是在当前行的窗口帧内的各行上为每行计算的。

例如，以下查询按天对每个店员的订单价格生成滚动总和

SELECT clerk, orderdate, orderkey, totalprice,
       sum(totalprice) OVER (PARTITION BY clerk
                             ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey

排名函数¶

cume_dist() -> double()¶

返回一组值中值的累积分布。结果是窗口分区中窗口排序之前或与行同行的行数除以窗口分区中的总行数。因此，排序中的任何相同值将评估为相同的分布值。

dense_rank() -> bigint()¶

返回一组值中某个值的排名。这与 rank() 类似，但重复的值不会在序列中产生间隙。

ntile(n) -> bigint()¶

将每个窗口分区中的行划分为 n 个桶，范围从 1 到最多 n。桶值相差最多 1。如果分区中的行数不能被桶数整除，则余下的值将被分配到每个桶中，从第一个桶开始。

例如，如果有 6 行和 4 个桶，桶值将如下所示：1 1 2 2 3 4

percent_rank() -> double()¶

返回一组值中某个值的百分比排名。结果为 (r - 1) / (n - 1)，其中 r 是该行的 rank()，n 是窗口分区中行的总数。

rank() -> bigint()¶

返回一组值中某个值的排名。排名为该行之前的行数（不包括与该行相等的）加一。因此，排序中的重复值会在序列中产生间隙。排名针对每个窗口分区执行。

row_number() -> bigint()¶

为每个行返回一个唯一的、连续的编号，从 1 开始，按照窗口分区内行的排序顺序。

值函数¶

值函数提供了一个选项，用于指定在计算函数时如何处理空值。空值可以被忽略 (IGNORE NULLS) 或被考虑 (RESPECT NULLS)。默认情况下，空值会被考虑。如果指定了 IGNORE NULLS，则在表达式为空值的所有行将从计算中排除。如果指定了 IGNORE NULLS 且在所有行中表达式的值为 null，则返回 default_value，或者如果未指定，则返回 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 行的值。偏移量从 0 开始，表示当前行。偏移量可以是任何标量表达式。默认的 offset 是 1。如果偏移量为空，则返回 null。如果偏移量指向不在分区内的行，则返回 default_value，或者如果未指定，则返回 null。

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

返回窗口分区中当前行之前 offset 行的值。偏移量从 0 开始，表示当前行。偏移量可以是任何标量表达式。默认的 offset 是 1。如果偏移量为空，则返回 null。如果偏移量指向不在分区内的行，则返回 default_value，或者如果未指定，则返回 null。