Netezza SQL Analytic Functions 分析函数

时间:2022-12-25 01:57:03

应用场景

主要使用在需要分组计算的场景中,根据所需的计算值可以分为两类:

1,排序类:如排序号,相邻记录等

2,聚合类:如平均值,累加求和,最大值,最末值等

语法

分析函数的语法在各大数据库中基本类似,以基于PostgreSQL的Netezza为例:

Func( value_expression) OVER ( [<partition_by_clause>] [<order_by_clause>] [<frame_spec_clause>] )
<partition_by_clause> = PARTITION BY <value_expression> [, ...]+
<order_by_clause> = ORDER BY <value_expression> [asc | desc] [nulls {first|last}] [, ...]+
<frame_spec_clause> = <frame_extent> [<exclusion clause>]
<frame_extent> =
ROWS UNBOUNDED PRECEDING
|ROWS <constant> PRECEDING
|ROWS CURRENT ROW
|RANGE UNBOUNDED PRECEDING
|RANGE <constant> PRECEDING
|RANGE CURRENT ROW
|ROWS BETWEEN {UNBOUNDED PRECEDING| <constant> PRECEDING | CURRENT
ROW } AND { UNBOUNDED FOLLOWING | <constant> FOLLOWING | CURRENT ROW }
|RANGE BETWEEN {UNBOUNDED PRECEDING| <constant> PRECEDING | CURRENT
ROW } AND { UNBOUNDED FOLLOWING | <constant> FOLLOWING | CURRENT ROW }
<exclusion_clause> =  EXCLUDE CURRENT ROW | EXCLUDE TIES | EXCLUDE
GROUP | EXCLUDE NO OTHERS

分析函数中的窗口特点

1,row_number(), lead() , lag()...

该类别over字句中必须有order by,且不能再指定窗口范围(frame_spec_clause)

2, max(), count(), sum(), first(), last()...

将每个partition分组后的所有记录称作集合S.

若没有指定order by语句,默认窗口范围为S;

若指定order by语句,按order by字段排序形成排序集合,默认窗口范围为从队首到当前值[S1-SCURRENT]

若指定order by语句同时也指定窗口范围,则按窗口范围来进行计算

参考文章

http://www.cnblogs.com/CareySon/p/3411176.html

http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1

http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.dbu.doc/c_dbuser_window_aggregation_family_syntax.html?lang=en