窗口函数是什么?

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

• 分区规范，用于将输入行分裂到不同的分区中。这个过程和 GROUP BY 子句的分裂过程相似。
• 排序规范，用于决定输入数据行在窗口函数中执行的顺序。
• 窗口框架，用于指定一个滑动窗口的数据给窗口函数处理给定的行数据。如果这个框架没有指定，它默认的方式是 RANGE UNBOUNDED PRECEDING，与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。这个框架包含当前分区中所有从开始到目前行所有数据。

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

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

聚合函数


所有 聚合函数 可以通过添加 OVER 子句来作为窗口函数使用。这些聚合函数会基于当前 滑动窗口内的数据行计算每一行数据。
例如，下面的查询语句为每个店员计算每天的滚动订单价格总和：

1. SELECT clerk, orderdate, orderkey, totalprice,
2. sum(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum
3. 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 。