分析函数提供了跨行,多层级聚合引用值的能力,并且可以在数据子集中控制排序粒度。与聚合函数不同,分析函数并不将结果集聚合较少的行。
分析函数是在主查询结果的基础上进行一定的分析,如分部门汇总,分部门求均值等等。
分析函数的模式:
function(arg1,arg2,arg3...argN)over ([partition-by-caluse][order-by-cluase][windowing-clause]);
分区子句按照分区列的值对数据进行分组。所有分区列的值相同的数据行被组合为一个数据分区。
窗口子句指定了分析函数的进行运算的数据子集。
[ROWS|RANGE] BETWEEN <START EXPR> AND <END EXPR>
WHEREAS <START EXPR> IS [UNBOUNDED PRECEDING|CURRENT ROW | n PRECEDING | n FOLOOWING]
<END ECPR> IS [UNBOUNDED FOLLOWING | CURRENT ROW| n PRECEDING | n FOLLOWING]
(1)rows|range:此关键字定义了一个window。
(2)between...and...:为窗品指一个起点和终点。
(3)unbounded preceding:指明窗口是从分区(partition)的第一行开始。
(4)current row:指明窗口是从当前行开始。
NOTE:分析函数不能进行嵌套
常见的分析函数列表:
Lag
Lead
First_value
Last_value
Nth_vlue
Rank
Dense_rank
Row_number
Ratio_to_report
Percent_rank
Percentile_countPercentile_dist
Ntile
Listagg
聚合函数可以在分析模式和非分析模式下进行计算,非分析模式将结果集削减为较少的行,分析模式下并不减少输出结果行数。
并且聚合函数能够在同一行中取得聚合和非聚合的列。
SELECT o.CUST_NBR,o.REGION_ID,SUM(SUM(o.TOTAL_SALES)) OVER(PARTITION BY o.REGION_ID)CUST_SALES FROM MUSIC.ORDER_TMP o where o.YEAR =2001 group by o.REGION_ID,o.CUST_NBR;