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