全部产品

窗口函数

窗口函数基于查询结果的行数据进行计算。窗口函数运行在HAVING子句之后,ORDER BY子句之前。触发一个窗口函数需要特殊的关键字OVER子句来指定窗口。一个窗口包含三个组成部分:

  • 分区规范,用于将输入行分裂到不同的分区中。这个过程和GROUP BY子句的分裂过程相似。

  • 排序规范,用于决定输入数据行在窗口函数中执行的顺序。

  • 窗口框架,用于指定一个滑动窗口的数据给窗口函数处理给定的行数据。如果这个框架没有指定,它默认的方式是RANGE UNBOUNDED PRECEDING与RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。这个框架包含当前分区中所有从开始到目前行的所有数据。

例如,下面的查询对每一个店员进行订单价钱的大小排序:

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

聚合函数

所有聚合函数可以通过添加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() → bigint

返回一组数值中每个值的累计分布。结果返回的是按照窗口分区下窗口排序后的数据集下,当前行前面包括当前行数据的行数。因此,排序中任何关联值均会计算成相同的分布值。

dense_rank() → bigint

返回一组数值中每个数值的排名。这个函数与rank()相似,除了关联值不会产生顺序上的空隙。

ntile(n) → bigint

为每个窗口分区的数据分裂到桶号从1到最大n的n个桶中。桶号值最多间隔是1。如果窗口分区中的数据行数不能均匀的分到每一个桶中,则剩余值将每一个桶分一个,从第一个桶开始。

比如,6行数据和4个桶,最后桶的值如下所示:1 1 2 2 3 4。

percent_rank() → bigint

返回数据集中每个数据的排名百分比。结果是根据(r - 1) / (n - 1) 其中 r 是由 rank() 计算 的当前行排名,n是当前窗口分区内总的行数。

rank() → bigint

返回数据集中每个值的排名。排名值是根据当前行之前的行数加1,不包含当前行。因此,排序的关联值可能产生顺序上的空隙。这个排名会对每个窗口分区进行计算。

row_number() → bigint

为每行数据返回一个唯一的顺序的行号,从1开始,根据行在窗口分区内的顺序。

值函数

first_value(x) → [与输入类型相同]

返回窗口内的第一个值。

last_value(x) → [与输入类型相同]

返回窗口内的最后一个值。

nth_value(x, offset) → [与输入类型相同]

返回窗口内指定偏移的值。偏移量从1开始。如果偏移量是null或者大于窗口内值的个数,返回null。如果偏移量为0或者负数,则会报错。

lead(x[, offset[, default_value]]) → [与输入类型相同]

返回窗口内,距当前行后偏移offset的值。偏移量起始值是0,就是指当前数据行。偏移量可以是标量表达式。默认offset是1。如果偏移量的值是null或者大于窗口长度,default_value会被返回, 如果没有指定则会返回 null 。

lag(x[, offset[, default_value]]) → [与输入类型相同]

返回窗口内,距当前行前偏移offset的值。偏移量起始值是0,就是指当前数据行。偏移量可以是标量表达式。默认offset是1。如果偏移量的值是null或者大于窗口长度,default_value会被返回,如果没有指定则会返回null。