Oracle 分析函数

分析函数提供了跨行，多层级聚合引用值的能力，并且可以在数据子集中控制排序粒度。与聚合函数不同，分析函数并不将结果集聚合较少的行。

分析函数是在主查询结果的基础上进行一定的分析，如分部门汇总，分部门求均值等等。

分析函数的模式：

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;