WINDOW FUNCTION

description

分析函数是一类特殊的内置函数。和聚合函数类似，分析函数也是对于多个输入行做计算得到一个数据值。不同的是，分析函数是在一个特定的窗口内对输入数据做处理，而不是按照 group by 来分组计算。每个窗口内的数据可以用 over() 从句进行排序和分组。分析函数会对结果集的每一行计算出一个单独的值，而不是每个 group by 分组计算一个值。这种灵活的方式允许用户在 select 从句中增加额外的列，给用户提供了更多的机会来对结果集进行重新组织和过滤。分析函数只能出现在 select 列表和最外层的 order by 从句中。在查询过程中，分析函数会在最后生效，就是说，在执行完 join，where 和 group by 等操作之后再执行。分析函数在金融和科学计算领域经常被使用到，用来分析趋势、计算离群值以及对大量数据进行分桶分析等。

分析函数的语法：

  1. function(args) OVER(partition_by_clause order_by_clause [window_clause])    
  2. partition_by_clause ::= PARTITION BY expr [, expr ...]    
  3. 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 列排序。

  1. SELECT   
  2. row_number() OVER (ORDER BY date_and_time) AS id,   
  3. c1, c2, c3, c4   
  4. FROM events;

Window 从句

Window 从句用来为分析函数指定一个运算范围，以当前行为准，前后若干行作为分析函数运算的对象。Window 从句支持的方法有：AVG(), COUNT(), FIRST_VALUE(), LAST_VALUE() 和 SUM()。对于 MAX() 和 MIN(), window 从句可以指定开始范围 UNBOUNDED PRECEDING

语法：

  1. ROWS BETWEEN [ { m | UNBOUNDED } PRECEDING | CURRENT ROW] [ AND [CURRENT ROW | { UNBOUNDED | n } FOLLOWING] ]

example

假设我们有如下的股票数据，股票代码是 JDR，closing price 是每天的收盘价。

  1. create table stock_ticker (stock_symbol string, closing_price decimal(8,2), closing_date timestamp);    
  2. ...load some data...    
  3. select * from stock_ticker order by stock_symbol, closing_date
  4.  | stock_symbol | closing_price | closing_date        |
  5.  |--------------|---------------|---------------------|
  6.  | JDR          | 12.86         | 2014-10-02 00:00:00 |
  7.  | JDR          | 12.89         | 2014-10-03 00:00:00 |
  8.  | JDR          | 12.94         | 2014-10-04 00:00:00 |
  9.  | JDR          | 12.55         | 2014-10-05 00:00:00 |
  10.  | JDR          | 14.03         | 2014-10-06 00:00:00 |
  11.  | JDR          | 14.75         | 2014-10-07 00:00:00 |
  12.  | JDR          | 13.98         | 2014-10-08 00:00:00 |

这个查询使用分析函数产生 moving_average 这一列，它的值是 3 天的股票均价，即前一天、当前以及后一天三天的均价。第一天没有前一天的值，最后一天没有后一天的值，所以这两行只计算了两天的均值。这里 Partition By 没有起到作用，因为所有的数据都是 JDR 的数据，但如果还有其他股票信息，Partition By 会保证分析函数值作用在本 Partition 之内。

  1. select stock_symbol, closing_date, closing_price,    
  2. avg(closing_price) over (partition by stock_symbol order by closing_date    
  3. rows between 1 preceding and 1 following) as moving_average    
  4. from stock_ticker;
  5.  | stock_symbol | closing_date        | closing_price | moving_average |
  6.  |--------------|---------------------|---------------|----------------|
  7.  | JDR          | 2014-10-02 00:00:00 | 12.86         | 12.87          |
  8.  | JDR          | 2014-10-03 00:00:00 | 12.89         | 12.89          |
  9.  | JDR          | 2014-10-04 00:00:00 | 12.94         | 12.79          |
  10.  | JDR          | 2014-10-05 00:00:00 | 12.55         | 13.17          |
  11.  | JDR          | 2014-10-06 00:00:00 | 14.03         | 13.77          |
  12.  | JDR          | 2014-10-07 00:00:00 | 14.75         | 14.25          |
  13.  | JDR          | 2014-10-08 00:00:00 | 13.98         | 14.36          |

keywords

  1. WINDOW,FUNCTION