分析函数计算基于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;