分析函数计算基于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;
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
