SAP HANA函数汇总（5）——窗口函数

  刚好最近在看官方文档，做个记录。 

 一、窗口函数定义

窗口函数允许将查询的结果集，或查询的逻辑划分，划分为称为窗口分区的行组。

通俗来说，窗口函数的名字来自于它的工作方式，你可以想象它就像在你的数据上打开一个'窗口'，然后对这个'窗口'内的数据进行某种计算。这个'窗口'可以是整个数据集，也可以是数据集的一部分。这个'窗口'可以根据需要移动，也就是说，窗口函数可以在每一行的不同'窗口'中进行计算。

<窗口函数> (<expression>) OVER (
    [PARTITION BY <expression list>]
    [ORDER BY <expression list>]
    [ROWS <frame specification>]
)

• <窗口函数>：这是你想要使用的窗口函数，比如 SUM，AVG，ROW_NUMBER 等。

• <expression>：这是你想要对其进行操作的列或表达式。

• PARTITION BY <expression list>：这是可选的，被用来定义窗口的分区。如果指定了 PARTITION BY，那么每一个分区都会有一个新的窗口，并且独立进行计算。

• ORDER BY <expression list>：这是可选的，被用来确定窗口内的行顺序。

• ROWS <frame specification>：这是可选的，用来定义窗口帧。窗口帧是窗口内的一部分，用于确定在计算当前行的窗口函数值时，应该使用窗口的哪些行。

结果集首先按照PARTITION BY子句指定的方式进行分组，然后在每个分组内按照ORDER BY子句的规则进行排序。最后，窗口函数会对每个分组内的每一行数据进行处理。

窗口函数的默认操作范围会根据是否指定了窗口ORDER BY子句而改变。如果指定了窗口ORDER BY子句，那么默认的操作范围是从分组的第一行到当前行。这就意味着，窗口函数会处理当前行及其之前的所有行，因此函数的返回结果会是一个累积值。

如果没有指定窗口ORDER BY子句，那么默认的操作范围是整个分组，从第一行到最后一行。这就意味着，窗口函数会处理分组内的所有行，因此无论当前行在哪里，函数的返回结果在同一个分组内都是一样的。

二、窗口函数汇总

 聚合窗口函数

<aggregate_function_or_expression_as_window_function> ::= 
 <aggregate_function_name> ( <arguments> ) <window_specification>

<aggregate_function_name> ::= 
 AVG
 | CORR 
 | CORR_SPEARMAN 
 | COUNT
 | FIRST_VALUE 
 | LAST_VALUE 
 | MAX 
 | MEDIAN 
 | MIN 
 | NTH_VALUE 
 | STDDEV 
 | SUM
 | VAR

非聚合窗口函数

<function_name> ::= 
 BINNING 
 | CUBIC_SPLINE_APPROX 
 | CUME_DIST   
 | DENSE_RANK
 | LAG 
 | LEAD 
 | LINEAR_APPROX 
 | NTILE  
 | PERCENT_RANK 
 | PERCENTILE_CONT
 | PERCENTILE_DISC
 | RANDOM_PARTITION
 | RANK               
 | ROW_NUMBER
 | SERIES_FILTER                              
 | WEIGHTED_AVG
 | <window_aggregate_functions>
 | <spatial_functions>

三、窗口函数用法 

因窗口函数用法相似，这里不会举例全部窗口函数，以几个常用的窗口函数为例，做一个实操，让大家知晓它们的用法，更多内容大家可以在官网上查询。

3.1 聚合窗口函数

这类函数除了可以在窗口函数中使用，单独也是可以使用的，如sum(),avg()不加其他语法即可。 

函数	说明
AVG函数	返回表达式的算术平均值。
CORR函数	计算两列之间的皮尔逊乘积动量相关系数。
CORR_SPEARMAN函数	返回在两列对应行中找到的值的斯皮尔曼等级相关系数。
COUNT函数	计算查询返回的行数。
FIRST_VALUE函数	返回表达式的第一个元素的值。
LAST_VALUE函数	返回表达式的最后一个元素的值。
MAX函数	返回表达式的最大值。
MEDIAN函数	找出具有数值数据类型的输入表达式的统计中位数。
MIN函数	返回表达式的最小值。
NTH_VALUE函数	返回表达式中特定位置的元素的值。
STDDEV函数	返回给定表达式的标准偏差，作为VAR函数的平方根。
SUM函数	返回表达式的总和。
VAR函数	返回给定表达式的方差，作为标准偏差的平方。

 聚合窗口函数实例

-- 新建一张表T
CREATE  TABLE T (class nvarchar(10), val INT, offset INT);
 INSERT INTO T VALUES('A', 1, 1);
 INSERT INTO T VALUES('A', 3, 3);
 INSERT INTO T VALUES('A', 5, null);
 INSERT INTO T VALUES('A', 5, 2);
 INSERT INTO T VALUES('A', 10, 0);
 INSERT INTO T VALUES('B', 1, 3);
 INSERT INTO T VALUES('B', 1, 1);
 INSERT INTO T VALUES('B', 7, 1);

 下边的例子中，以几个常用的窗口函数为例，对于空值、是否加ORDER BY和partition by 都有不同的结果。

如我们上边所说，如果加了order by，那么会在partition by 的窗口内继续分小窗口，如下边例子的s1和s2，s1未加order by，汇总的就是整个partition by 即不同 class的总值，s2加了根据val排序，因此每次汇总，都是从第一行到当前行。只有在计算最后一个值的时候，值才跟s1先等。

SELECT 
	  class, 
	  val, 
	  offset,
	  COUNT(*) OVER (PARTITION BY class) AS c1,
	  COUNT(offset) OVER (PARTITION BY class) AS c2,
	  COUNT(*) OVER (PARTITION BY class ORDER BY val) AS c3,
	  COUNT(offset) OVER (PARTITION BY class ORDER BY val) AS c4,
	  MAX(val) OVER (PARTITION BY class) AS m1,
	  MAX(val) OVER (PARTITION BY class ORDER BY val) AS m2,
	  sum(val) OVER (PARTITION BY class) AS s1,
	  sum(val) OVER (PARTITION BY class ORDER BY val) AS s2,
	  first_value(val) OVER (PARTITION BY class ORDER BY val desc) AS f1,
	  first_value(val) OVER (ORDER BY val desc) AS f2
 FROM T
 order by class,val
 ;

3.2 非聚合窗口函数

函数	说明
BINNING函数	通过为每一行分配一个分箱号，将输入集划分为不相交的子集。
CUBIC_SPLINE_APPROX函数	基于计算的立方样条插值填补缺失值，并对任何领先或尾随的缺失值进行线性外推。
CUME_DIST函数	返回一行的相对排名。
DENSE_RANK函数	执行与RANK函数相同的排名操作，但是当发现并列时，排名编号不会跳过。
RANK函数	返回分区内一行的排名，从1开始。
ROW_NUMBER函数	在结果集的分区内对行进行顺序编号，每个分区的第一行被指定为1。
LAG函数	返回当前行之前偏移行的值。
LEAD函数	返回当前行之后的行偏移。偏移应为非负数，其默认值为1。
LINEAR_APPROX函数	对整个系列进行操作，生成一个新的系列，通过在相邻的非NULL值之间插值，并外推任何领先或尾随的空值，来替换缺失的值。
PERCENT_RANK函数	根据窗口的ORDER BY规定，计算小于或大于当前值的数值的百分比。
PERCENTILE_CONT函数	使用常数的百分位值返回插值。
PERCENTILE_DISC函数	返回累积分布值大于或等于常数百分位值的第一个值。
RANDOM_PARTITION函数	通过为输入集的每一行分配一个集合号，将输入集随机划分为三个不相交的子集。
SERIES_FILTER函数	对数据系列进行过滤计算。
WEIGHTED_AVG函数	通过使用算术递减权重来计算加权移动平均值。

这个与聚合窗口函数用法虽然相似，但是使用场景是不一样的。在实际的项目中，像LEAD、LAG、排名之类的会使用得比较频繁。

SELECT 
	  class, 
	  val, 
	  offset,
	  --LEAD函数	返回当前行之后的行偏移,LAG函数,返回当前行之前偏移行的值,如果指定了偏移值，则根据偏移值取，其默认值为1
	  LEAD(val) OVER (PARTITION BY class ORDER BY val) AS lead,
	  --LEAD(val, offset, -val)这部分的作用是返回当前行offset偏移量之后的行的val字段的值。如果偏移后的行不存在（即偏移超出了分组的行数），则返回-val。
	  LEAD(val,offset,-val) OVER (PARTITION BY class ORDER BY val) AS lead2,
	  LAG(val) OVER (PARTITION BY class ORDER BY val) AS lag,
	  LAG(val,offset,-val) OVER (PARTITION BY class ORDER BY val) AS lag2,
	  --DENSE_RANK函数	执行与RANK函数相同的排名操作，但是当发现并列时，排名编号不会跳过。
	  --RANK函数	返回分区内一行的排名，从1开始。
	  --ROW_NUMBER函数	在结果集的分区内对行进行顺序编号，每个分区的第一行被指定为1。
	  --总结：row_num，遇到重复的会直接往下排12345，RANK遇到重复的会相同，下一个数排名跳过12335，ROW_NUMBER遇到重复的会相同，下一个数排名不跳过 12334
	  ROW_NUMBER() OVER (PARTITION BY Class ORDER BY val) AS ROW_NUM,
	  RANK() OVER (PARTITION BY Class ORDER BY val) AS RANK,
	  DENSE_RANK() OVER (PARTITION BY Class ORDER BY val) AS DENSE_RANK,
	  --PERCENT_RANK函数	根据窗口的ORDER BY规定，计算小于或大于当前值的数值的百分比。
	  PERCENT_RANK() OVER (PARTITION BY Class ORDER BY val) AS Percent_Rank,
	  --PERCENTILE_DISC函数	返回累积分布值大于或等于常数百分位值的第一个值，可以设置不同的比例
	  PERCENTILE_DISC(0.125) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd1, 
	  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd2, 
	  PERCENTILE_DISC(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pd3,
	  --PERCENTILE_CONT函数	使用常数的百分位值返回插值。
	  --如果 fraction 是 0，PERCENTILE_CONT 返回排序后的第一个非空值。
	  --如果 fraction 是 1，PERCENTILE_CONT 返回排序后的最后一个非空值。
	  --如果 fraction 是在 0 到 1 之间的一个数，那么 PERCENTILE_CONT 会找到两个相邻的数值，它们的位置分别是最接近 fraction 的上界和下界，然后返回这两个数值的线性插值。
	  PERCENTILE_CONT(0) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc1, 
	  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc2, 
	  PERCENTILE_CONT(0.875) WITHIN GROUP (ORDER BY val) OVER (PARTITION BY class) AS pc3 
 FROM T;
 order by class,val 
 ;