窗口函数
窗口函数
窗口函数是一类特殊的内置函数。和聚合函数类似，窗口函数也是对于多个输入行做计算得到一个数据值。不同的是，窗口函数是在一个特定的窗口内对输入数据做处理，而不是按照 group by 来分组计算。并且窗口函数的输入和输出行是一对一的关系，而不是多对一的关系。
每个窗口内的数据可以用 over() 从句进行排序和分组。窗口函数会对结果集的每一行计算出一个单独的值，而不是每个 group by 分组计算一个值。这种灵活的方式允许用户在 select 从句中增加额外的列，给用户提供了更多的机会来对结果集进行重新组织和过滤。
窗口函数只能出现在 select 列表以及最外层的 order by 从句中。在查询过程中，窗口函数会在最后生效，就是说，在执行完join，where 和 group by 等操作之后再执行。窗口函数在金融和科学计算领域经常被使用到，用来分析趋势、计算离群值以及对大量数据进行分桶分析等。
窗口函数语法
1function(args) OVER(partition_by_clause order_by_clause [window_clause]) partition_by_clause:
1PARTITION BY expr [, expr ...] order_by_clause:
1ORDER BY expr [ASC | DESC] [, expr [ASC | DESC] ...]window_clause:
1ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]
2RANGE BETWEEN [ {m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]-
function具体的窗口函数。目前支持的 Function 包括：
- AVG()
- COUNT()
- DENSE_RANK()
- FIRST_VALUE()
- LAG()
- LAST_VALUE()
- LEAD()
- MAX()
- MIN()
- RANK()
- ROW_NUMBER()
- SUM()
-
partition_by_clausePartition By 从句和 Group By 类似，它把输入行按照指定的一列或多列分组，相同值的行会被分到一组。
每一组可以理解为一个窗口。如果不指定 Partition By 从句，则整个数据集为一组，即一个窗口。
-
order_by_clauseOrder By 从句用于指定每一个分组内的数据排列顺序。数据集先按 Partition By 子句分组，每组内再排序，排序后按 Function 对每行进行计算。
与外层 Order By 的不同点是，OVER从句中的
Order By n（n是正整数）相当于不做任何操作，而外层的Order By n表示按照第n列排序。举例说明：
SQL1SELECT 2row_number() OVER (PARTITION BY class_id ORDER BY eventdate) AS id, 3c1, c2, c3, c4 4FROM events;这个SQL会将 events 表中的数据行按
class_id划分成多组后，每组内的数据按eventdate列排序。对排序后的每组内的数据，添加行号 （1、2、3、4、...）。 -
window_clauseWindow从句用来为窗口函数指定一个运算范围，以当前行为准，前后若干行作为窗口函数运算的对象。
Window从句支持的方法有：
- AVG()
- COUNT()
- FIRST_VALUE()
- LAST_VALUE()
- SUM()
- MAX()
- MIN()
对于 MAX() 和 MIN(), window从句可以指定开始范围
UNBOUNDEDPRECEDING假设我们有如下的股票数据，股票代码是JDR，closing price是每天的收盘价。
SQL1> create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp); 2 3> ...load some data... 4 5> select * from stock_ticker order by stock_symbol, closing_date 6 7 | stock_symbol | closing_price | closing_date | 8 |--------------|---------------|---------------------| 9 | JDR | 12.86 | 2014-10-02 00:00:00 | 10 | JDR | 12.89 | 2014-10-03 00:00:00 | 11 | JDR | 12.94 | 2014-10-04 00:00:00 | 12 | JDR | 12.55 | 2014-10-05 00:00:00 | 13 | JDR | 14.03 | 2014-10-06 00:00:00 | 14 | JDR | 14.75 | 2014-10-07 00:00:00 | 15 | JDR | 13.98 | 2014-10-08 00:00:00 | 16 ``` 17 18以下查询使用窗口函数产生 `moving_average` 这一列，它的值是3天的股票均价，即前一天、当前以及后一天三天的均价。 19 20第一天没有前一天的值，最后一天没有后一天的值，所以这两行只计算了两天的均值。 21 22这里 Partition By 没有起到作用，因为所有的数据都是JDR的数据，但如果还有其他股票信息，Partition By 会保证窗口函数值作用在本Partition之内。 23 24```sql 25> select stock_symbol, closing_date, closing_price, 26 avg(closing_price) over (partition by stock_symbol order by closing_date 27 rows between 1 preceding and 1 following) as moving_average 28 from stock_ticker; 29 30 | stock_symbol | closing_date | closing_price | moving_average | 31 |--------------|---------------------|---------------|----------------| 32 | JDR | 2014-10-02 00:00:00 | 12.86 | 12.87 | 33 | JDR | 2014-10-03 00:00:00 | 12.89 | 12.89 | 34 | JDR | 2014-10-04 00:00:00 | 12.94 | 12.79 | 35 | JDR | 2014-10-05 00:00:00 | 12.55 | 13.17 | 36 | JDR | 2014-10-06 00:00:00 | 14.03 | 13.77 | 37 | JDR | 2014-10-07 00:00:00 | 14.75 | 14.25 | 38 | JDR | 2014-10-08 00:00:00 | 13.98 | 14.36 | 39 ```
Function 介绍
本节介绍PALO中可以用作窗口函数的方法。
AVG()
计算平均值。
语法：
1AVG([DISTINCT | ALL] *expression*) [OVER (*analytic_clause*)]举例：
计算当前行和它前后各一行数据的x平均值
1> select x, property,
2 avg(x) over
3 (partition by property order by x rows between 1 preceding and 1 following) as 'moving average'
4 from int_t where property in ('odd','even');
5
6 | x | property | moving average |
7 |----|----------|----------------|
8 | 2 | even | 3 |
9 | 4 | even | 4 |
10 | 6 | even | 6 |
11 | 8 | even | 8 |
12 | 10 | even | 9 |
13 | 1 | odd | 2 |
14 | 3 | odd | 3 |
15 | 5 | odd | 5 |
16 | 7 | odd | 7 |
17 | 9 | odd | 8 |该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，计算其前一行，自身和后一行三行数据的平均值，作为该行的输出，写入 moving average 列。
COUNT()
统计个数。
语法：
1COUNT([DISTINCT | ALL] expression) [OVER (analytic_clause)]举例：
计算从当前行到第一行x出现的次数。
1> select x, property,
2 count(x) over
3 (partition by property order by x rows between unbounded preceding and current row) as 'cumulative total'
4 from int_t where property in ('odd','even');
5
6 | x | property | cumulative count |
7 |----|----------|------------------|
8 | 2 | even | 1 |
9 | 4 | even | 2 |
10 | 6 | even | 3 |
11 | 8 | even | 4 |
12 | 10 | even | 5 |
13 | 1 | odd | 1 |
14 | 3 | odd | 2 |
15 | 5 | odd | 3 |
16 | 7 | odd | 4 |
17 | 9 | odd | 5 |该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，统计从自身算起之前所有行的行数，作为该行的输出，写入 cumulative count 列。
RANK()
RANK() 函数用来表示排名，与 DENSE_RANK()不同的是，如果出现了两个并列的值，RANK() 的第三个数就是 3，而不是 2。
语法：
1RANK() OVER(partition_by_clause order_by_clause)举例：
根据x列进行排名
1> select x, y, rank() over(partition by x order by y) as rank from int_t;
2
3| x | y | rank |
4|---|---|------|
5| 1 | 1 | 1 |
6| 1 | 2 | 2 |
7| 1 | 2 | 2 |
8| 2 | 1 | 1 |
9| 2 | 2 | 2 |
10| 2 | 3 | 3 |
11| 3 | 1 | 1 |
12| 3 | 1 | 1 |
13| 3 | 2 | 3 |该 SQL 首先会对数据集按照 x 分组，每组内按 y 列进行排序。之后对每组内的每行数据，计算该行在组内的名次，写入 rank 列。
DENSE_RANK()
DENSE_RANK() 函数用来表示排名，与 RANK() 不同的是，DENSE_RANK() 不会出现空缺数字。比如，如果出现了两个并列的1，DENSE_RANK() 的第三个数仍然是2，而 RANK() 的第三个数是3。
语法：
1DENSE_RANK() OVER(partition_by_clause order_by_clause)举例：
下例展示了按照property列分组对x列排名：
1> select x, y, dense_rank() over(partition by x order by y) as rank from int_t;
2
3| x | y | rank |
4|----|------|----------|
5| 1 | 1 | 1 |
6| 1 | 2 | 2 |
7| 1 | 2 | 2 |
8| 2 | 1 | 1 |
9| 2 | 2 | 2 |
10| 2 | 3 | 3 |
11| 3 | 1 | 1 |
12| 3 | 1 | 1 |
13| 3 | 2 | 2 |该 SQL 首先会对数据集按照 x 分组，每组内按 y 列进行排序。之后对每组内的每行数据，计算该行在组内的名次，写入 rank 列。
FIRST_VALUE()
返回窗口范围内的第一个值。
语法：
1FIRST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])举例：
我们有如下数据
1> select name, country, greeting from mail_merge;
2
3 | name | country | greeting |
4 |---------|---------|--------------|
5 | Pete | USA | Hello |
6 | John | USA | Hi |
7 | Boris | Germany | Guten tag |
8 | Michael | Germany | Guten morgen |
9 | Bjorn | Sweden | Hej |
10 | Mats | Sweden | Tja |使用 FIRST_VALUE()，根据 country 分组，返回每个分组中第一个 greeting 的值：
1> select country, name,
2 first_value(greeting) over (partition by country order by name, greeting) as greeting
3 from mail_merge;
4
5| country | name | greeting |
6|---------|---------|-----------|
7| Germany | Boris | Guten tag |
8| Germany | Michael | Guten tag |
9| Sweden | Bjorn | Hej |
10| Sweden | Mats | Hej |
11| USA | John | Hi |
12| USA | Pete | Hi |LAST_VALUE()
返回窗口范围内的最后一个值。与 FIRST_VALUE() 相反。
语法：
1LAST_VALUE(expr) OVER(partition_by_clause order_by_clause [window_clause])使用 FIRST_VALUE() 举例中的数据：
1> select country, name,
2 last_value(greeting)
3 over (partition by country order by name, greeting) as greeting
4 from mail_merge;
5
6| country | name | greeting |
7|---------|---------|--------------|
8| Germany | Boris | Guten morgen |
9| Germany | Michael | Guten morgen |
10| Sweden | Bjorn | Tja |
11| Sweden | Mats | Tja |
12| USA | John | Hello |
13| USA | Pete | Hello |LAG()
用来计算当前行向前数若干行的值。
语法：
1LAG(expr, offset, default) OVER (partition_by_clause order_by_clause)举例：
计算前一天的收盘价
1> select stock_symbol, closing_date, closing_price,
2 lag(closing_price, 1, 0) over (partition by stock_symbol order by closing_date) as "yesterday closing"
3 from stock_ticker
4 order by closing_date;
5
6| stock_symbol | closing_date | closing_price | yesterday closing |
7|--------------|---------------------|---------------|-------------------|
8| JDR | 2014-09-13 00:00:00 | 12.86 | 0 |
9| JDR | 2014-09-14 00:00:00 | 12.89 | 12.86 |
10| JDR | 2014-09-15 00:00:00 | 12.94 | 12.89 |
11| JDR | 2014-09-16 00:00:00 | 12.55 | 12.94 |
12| JDR | 2014-09-17 00:00:00 | 14.03 | 12.55 |
13| JDR | 2014-09-18 00:00:00 | 14.75 | 14.03 |
14| JDR | 2014-09-19 00:00:00 | 13.98 | 14.75 |该 SQL 首先会对数据集按照 stock_symbol 分组，每组内按 closing_date 列进行排序。之后对每组内的每行数据，取其前一行的值作为输出，写入 yesterday closing 列。如果没有前一行，则使用默认值 0。
LEAD()
和 LAG() 方法相反。用来计算当前行向后数若干行的值。
语法：
1LEAD (expr, offset, default]) OVER (partition_by_clause order_by_clause)举例：
计算第二天的收盘价对比当天收盘价的走势，即第二天收盘价比当天高还是低。
1> select stock_symbol, closing_date, closing_price,
2 case
3 (lead(closing_price, 1, 0) over (partition by stock_symbol order by closing_date) - closing_price) > 0
4 when true then "higher"
5 when false then "flat or lower"
6 end as "trending"
7 from stock_ticker
8 order by closing_date;
9
10| stock_symbol | closing_date | closing_price | trending |
11|--------------|---------------------|---------------|---------------|
12| JDR | 2014-09-13 00:00:00 | 12.86 | higher |
13| JDR | 2014-09-14 00:00:00 | 12.89 | higher |
14| JDR | 2014-09-15 00:00:00 | 12.94 | flat or lower |
15| JDR | 2014-09-16 00:00:00 | 12.55 | higher |
16| JDR | 2014-09-17 00:00:00 | 14.03 | higher |
17| JDR | 2014-09-18 00:00:00 | 14.75 | flat or lower |
18| JDR | 2014-09-19 00:00:00 | 13.98 | flat or lower |该 SQL 首先会对数据集按照 stock_symbol 分组，每组内按 closing_date 列进行排序。之后对每组内的每行数据，取其后一行的值作为输出，再减去该行的 closing_price 值。如果没有后一行，则使用默认值 0。
MAX()
计算分组内的最大值。
语法：
1MAX([DISTINCT | ALL] expression) [OVER (analytic_clause)]举例：
计算从第一行到当前行之后一行的最大值
1> select x, property,
2 max(x) over (order by property, x rows between unbounded preceding and 1 following) as 'local maximum'
3 from int_t where property in ('prime','square');
4
5| x | property | local maximum |
6|---|----------|---------------|
7| 2 | prime | 3 |
8| 3 | prime | 5 |
9| 5 | prime | 7 |
10| 7 | prime | 7 |
11| 1 | square | 7 |
12| 4 | square | 9 |
13| 9 | square | 9 |该 SQL 的窗口函数没有分组子句，则全部在一个组内。每组内按 property 和 x 列进行排序。之后对每组内的每行数据，取之前的所有行、自身和之后的1行，计算最大值作为输出写入 local maximum 列。
MIN()
计算分组内的最小值。
语法：
1MIN([DISTINCT | ALL] expression) [OVER (analytic_clause)]ROW_NUMBER()
为每个分组的每一行返回一个从1开始连续递增的整数。类似行号。
语法：
1ROW_NUMBER() OVER(partition_by_clause order_by_clause)举例：
1> select x, y, row_number() over(partition by x order by y) as rank from int_t;
2
3| x | y | rank |
4|---|---|------|
5| 1 | 1 | 1 |
6| 1 | 2 | 2 |
7| 1 | 2 | 3 |
8| 2 | 1 | 1 |
9| 2 | 2 | 2 |
10| 2 | 3 | 3 |
11| 3 | 1 | 1 |
12| 3 | 1 | 2 |
13| 3 | 2 | 3 |SUM()
分组内求和。
语法：
1SUM([DISTINCT | ALL] expression) [OVER (analytic_clause)]举例：
按照property进行分组，在组内计算当前行以及前后各一行的x列的和。
1> select x, property,
2 sum(x) over (partition by property order by x rows between 1 preceding and 1 following) as 'moving total'
3 from int_t where property in ('odd','even');
4
5| x | property | moving total |
6|----|----------|--------------|
7| 2 | even | 6 |
8| 4 | even | 12 |
9| 6 | even | 18 |
10| 8 | even | 24 |
11| 10 | even | 18 |
12| 1 | odd | 4 |
13| 3 | odd | 9 |
14| 5 | odd | 15 |
15| 7 | odd | 21 |
16| 9 | odd | 16 |该 SQL 首先会对数据集按照 property 分组，每组内按 x 列进行排序。之后对每组内的每行数据，取其前一行、自身和后一行的值求和，结果写入 moving total 列。