PLSQL开发札记和小结（7）——分析函数简述（转载）

分析函数计算基于group by的列，分组查询出的行被称为"比照(window)"，在根据over()执行过程中，针对每一行都会重新定义比照。比照为"当前行(current row)"确定执行计算的行的范围。这点一定要理解清楚。它是分析函数生成数据的原理。如果此处模糊，那么你在应用分析函数时恐就不会那么得心应手了。

　　分析函数与聚合函数非常相似，不同于聚合函数的地方在于它们每个分组序列均返回多行。在本节示例中会同时应用两种函数做对比，以更好体现二者的差异。通过本章节练习相信大家就会注意到，部分聚合函数和分析函数是同一个命令，事实确实如此。如果从语法格式上区分的话，没加over()的即是聚合函数，加了over()即是分析函数：

　　有一点需要注意，除了order by子句的运算外，分析函数在SQL语句中将会最后执行。因此，分析函数只能应用于select的列或order by子句中(记住喽，千万别扔到什么where、group by、having之类的地方了)。也正因此，同名的函数在做为聚合函数和分析函数时得出的结果可能不相同，就是因为此处运算逻辑不同造成的。

　　同时，部分分析函数在选择列时支持distinct，如果你指定了该参数，则over条件中就只能指定partition子句，而不能再指定order by 子句了。

　　分析函数的语法结构比较复杂，但多数函数都具有相同的语法结构，所以先在之前进行统一介绍，后续单个函数介绍时就不过多说明函数语法结构了。

　　基本上所有的分析函数均是这种格式：

　　函数名称 ([参数]) OVER (analytic_clause)

analytic_clause包含：[partition 子句][ order 子句 [window子句]]

l? Partition 子句：Partition by exp1[ ,exp2]...

Partition没啥说的，功能强大参数少，主要用于分组，可以理解成select中的group by。不过它跟select语句后跟的group by 子句并不冲突。

l? Order子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]。部分函数支持window子句。

Order by的参数基本与select中的order by相同。大家按那个理解就是了。Nulls first|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认order by的时候nulls总会被排在最前面。如果想控制值为null的列的话呢，nulls first|last参数就能派上用场了。

l? Window子句：贴个图吧

?

　　?????? 看起来复杂其实简单，而且应用的机率相当的低，不详细介绍了。

l? AVG([DISTINCT|ALL] expr) ?OVER(analytic_clause) 计算平均值。

例如：

--聚合函数

SELECT col, AVG(value) FROM tmp1 GROUPBY col ORDERBY col;

--分析函数

SELECT col, AVG(value) OVER(PARTITIONBY col ORDERBY col)

? FROM tmp1

?ORDERBY col;

l? SUM ( [ DISTINCT | ALL ] expr ) ?OVER ( analytic_clause )

例如：

--聚合函数

SELECT col, sum(value) FROM tmp1 GROUPBY col ORDERBY col;

--分析函数

SELECT col, sum(value) OVER(PARTITIONBY col ORDERBY col)

? FROM tmp1

?ORDERBY col;

l? COUNT({* | [DISTINCT | ALL] expr}) ?OVER (analytic_clause) 查询分组序列中各组行数。

例如：

--分组查询col的数量

SELECT col,count(0)over(partitionby col orderby col) ct FROM tmp1;

l? FIRST() / LAST()从DENSE_RANK返回的集合中取出排在第一/最后的行。

?

例如：

--聚合函数

SELECT col,

?????? MIN(value) KEEP(DENSE_RANK FIRSTORDERBY col) "Min Value",

?????? MAX(value) KEEP(DENSE_RANK LASTORDERBY col) "Max Value"

? FROM tmp1

?GROUPBY col;

--分析函数

SELECT col,

?????? MIN(value) KEEP(DENSE_RANK FIRSTORDERBY col) OVER(PARTITIONBY col),

?????? MAX(value) KEEP(DENSE_RANK LASTORDERBY col) OVER(PARTITIONBY col)

? FROM tmp1

?ORDERBY col;

可以看到二者结果基本相似，但是ex1的结果是group by后的列，而ex2则是每一行都有返回。

l? FIRST_VALUE (col)/LAST_VALUE (col) ?OVER ( analytic_clause ) 返回over()条件查询出的第一条/最后一条记录

例如：

SELECT col,

?????? FIRST_VALUE(value) over(partitionby col orderbyvalue) "First",

?????? LAST_VALUE(value) over(partitionby col orderbyvalue) "Last"

? FROM tmp1;

l? LAG(col[,n1][,n2]) over([partition_clause] order_by_clause) 是一个相当有意思的函数，其功能是返回指定列col前n1行的值(如果前n1行已经超出比照范围，则返回n2，如不指定n2则默认返回null)，如不指定n1，其默认值为1。

例如：

SELECT col,

?????? value,

?????? LAG(value) over(orderbyvalue) "Lag",

?????? LEAD(value) over(orderbyvalue) "Lead"

? FROM tmp1;

l? LEAD(col[,n][,n]) over([partition_clause] order_by_clause) 与上函数正好相反，本函数返回指定列col后n1行的值。

例如：见上例

l? MAX (col)/ MIN (col) ?OVER (analytic_clause) 获取分组序列中的最大值/最小值。

例如：

--聚合函数

SELECT col,

??? ???Max(value) "Max",

?????? Min(value) "Min"

? FROM tmp1

?GROUPBY col;

--分析函数

SELECT col,

?????? value,

?????? Max(value) over(partitionby col orderbyvalue) "Max",

?????? Min(value) over(partitionby col orderbyvalue) "Min"

? FROM tmp1;

l? RANK()/DENSE_RANK () ?OVER([partition_clause] order_by_clause) 关于RANK和DENSE_RANK前面聚合函数处介绍过了，这里不废话了，直接看示例吧。

例如：

SELECT col,

?????? value,

?????? RANK() OVER(orderbyvalue) "RANK",

?????? DENSE_RANK() OVER(orderbyvalue) "DENSE_RANK",

?????? ROW_NUMBER() OVER(orderbyvalue) "ROW_NUMBER"

? FROM tmp1;

l? ROW_NUMBER () OVER([partition_clause] order_by_clause) 这个函数需要多说两句，通过上述的对比相信大家应该已经能够看出些端倪。前面讲过，dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值依旧递增，rank则是重复值所在行的序列值相同，但其后的序列值从+重复行数开始递增，而row_number则不管是否有重复行，(分组内)序列值始终递增

例如：见上例。

l? NTILE(n) OVER([partition_clause] order_by_clause) 是个很有意思的统计函数。它会按照你指定的组数(n)对记录做分组

例如：SELECT t.*,ntile(5) over(orderbyvaluedesc) FROM tmp1 t;

l? RATIO_TO_REPORT(col) over ([partition_clause]) 本函数计算本行col列值在该分组序列sum(col)中所占比率。如果col列为空，则返回空值。

例如：

SELECT col, value,

?????? RATIO_TO_REPORT(value) OVER(PARTITIONBY col) "RATIO_TO_REPORT"

? FROM TMP1

l? CUME_DIST() OVER([partition_clause] order_by_clause) 返回该行在分组序列中的相对位置，返回值介于0到1之间。注意哟，如果order by的列是desc，则该分组内最大的行返回列值1，如果order by为asc，则该分组内最小的行返回列值1。

例如：SELECT col, value, CUME_DIST()OVER(ORDERBYvalue) FROM tmp1;

l? PERCENT_RANK() OVER([partition_clause] order_by_clause) 与CUME_DIST类似，返回分组序列中各行在分组序列的相对位置。其返回值也是介于0到1之间，不过其起始值始终为0而终结值始终为1。

例如：SELECT col, value, PERCENT_RANK() OVER(ORDERBYvalue) FROM tmp1;

l? PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

例如：

--聚合函数

SELECT col, max(value), min(value), sum(value),

?????? PERCENTILE_CONT(0.5) WITHIN GROUP(ORDERBYvalue) a,

?????? PERCENTILE_CONT(0.8) WITHIN GROUP(ORDERBYvalue) b

? FROM TMP1

?groupby col;

--分析函数

SELECT col,

?????? value,

?????? sum(value) over(partitionby col) "Sum",

?????? PERCENTILE_CONT(0.5) WITHIN GROUP( ORDERBYvalue) OVER(PARTITIONBY col) "CONTa",

?????? PERCENTILE_CONT(0.8) WITHIN GROUP( ORDERBYvalue) OVER(PARTITIONBY col) "CONTb"

? FROM TMP1;

l? PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)

本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

例如：

--聚合函数

SELECT col, max(value), min(value), sum(value),

?????? PERCENTILE_DISC(0.5) WITHIN GROUP(ORDERBYvalue) a,

?????? PERCENTILE_DISC(0.8) WITHIN GROUP(ORDERBYvalue) b

? FROM TMP1

?groupby col;

--分析函数

SELECT col,

?????? value,

?????? sum(value) over(partitionby col) "Sum",

?????? PERCENTILE_DISC(0.5) WITHIN GROUP( ORDERBYvalue) OVER(PARTITIONBY col) "CONTa",

?????? PERCENTILE_DISC(0.8) WITHIN GROUP( ORDERBYvalue) OVER(PARTITIONBY col) "CONTb"

? FROM TMP1;

l? STDDEV ([distinct|all] col) OVER (analytic_clause) 返回列的标准偏差。

例如：

--聚合函数

SELECT col, STDDEV(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? STDDEV(value) OVER(PARTITIONBY col ORDERBYvalue) "STDDEV"

? FROM TMP1;

l? STDDEV_SAMP(col) OVER (analytic_clause) 功能与上相同，与STDDEV不同地方在于如果该分组序列只有一行的话，则STDDEV_SAMP函数返回空值，而STDDEV则返回0。

例如：

--聚合函数

SELECT col, STDDEV(value),STDDEV_SAMP(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? STDDEV(value) OVER(PARTITIONBY col ORDERBYvalue) "STDDEV",

?????? STDDEV_SAMP(value) OVER(PARTITIONBY col ORDERBYvalue) "STDDEV_SAMP"

? FROM TMP1;

l? STDDEV_POP(col) OVER (analytic_clause) 返回该分组序列总体标准偏差

例如：

--聚合函数

SELECT col, STDDEV_POP(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? STDDEV_POP(value) OVER(PARTITIONBY col ORDERBYvalue) "STDDEV_POP"

? FROM TMP1;

l? VAR_POP(col) OVER (analytic_clause) 返回分组序列的总体方差，VAR_POP进行如下计算：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

例如：

--聚合函数

SELECT col, VAR_POP(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? VAR_POP(value) OVER(PARTITIONBY col ORDERBYvalue) "VAR_POP"

? FROM TMP1;

l? VAR_SAMP(col) OVER (analytic_clause) 与上类似，该函数返回分组序列的样本方差，其计算公式为：(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / (COUNT(expr) - 1)

例如：

--聚合函数

SELECT col, VAR_SAMP(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? VAR_SAMP(value) OVER(PARTITIONBY col ORDERBYvalue) "VAR_SAMP"

? FROM TMP1;

l? VARIANCE(col) OVER (analytic_clause) 该函数返回分组序列方差，Oracle计算该变量如下：

如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMP

例如：

--聚合函数

SELECT col, VAR_SAMP(value),VARIANCE(value) FROM TMP1 GROUPBY col;

--分析函数

SELECT col, value,

?????? VAR_SAMP(value) OVER(PARTITIONBY col ORDERBYvalue) "VAR_SAMP",

?????? VARIANCE(value) OVER(PARTITIONBY col ORDERBYvalue) "VARIANCE"

? FROM TMP1;

?

转载自：http://space.itpub.net/7607759/viewspace-22282?