环境:oracle 10g
搞数据库较大一块部分就是为了计算和统计业务数据,在10G中出现了不少有趣的新函数,所以有必要介绍一下所有的统计函数.
官方的文档中,有各种千奇百怪的函数,有空还是自行练习下更好一些。
先有个印象,以后才好返回来查找需要的内容.
今天暂时没有时间,明天再来考虑这个问题吧。
一,函数列表和概述
AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
二,分析函数的基本语法
如果光光用于统计,很多函数都显得比较简单,但是一旦用于分析,则其语法就变得复杂起来。
基本的分析语法如下:
over (partition by order by range between unbounded preceding and current row)
意思就是按照某些列来分区,然后还好考虑数据范围,典型的就是从第一行到当前行.
2.1 partition by
用于分组查询的结果集合。 如果没有这个句子,那么函数就把所有的输出结果当作一个分组处理.
允许在一个查询语句中使用多个分析函数,并且每个的partition语句可以相同也可以不同。
如果查询的对象支持并行,那么该分析函数也支持并行方式.
2.2 order by /order siblings by
排序语句,在一个分区内部进行排序。
对于所有的分析函数(除了percentile_cont ,percentile_disc)都可以在一个分区之上进行排序
2.3 rows|range between () and () --windowing_clause窗口语句
并非所有的分析函数都支持分窗语句。
rows和range的区别。rows-定义了一个居于物理行的窗口,range定了一个基于逻辑位移的窗口。只有使用了order by 语句,才能用分窗语句.
即使使用了分窗语句,也不能保证分析函数每次返回的结果都是一定的(不变化)(当然这是基于表格的数据不变化的前提做出的论断)。可以设置更好的order by 语句,以便确保输出的结果是确定的.
范围关键字:unbounded preceding |unbounded following|current row
unbounded preceding --此行之前的所有行
unbounded following --此行之后的所有行
current row --当前行
5 preceding and current row --当前行之前5行(包括当前行,共6行)
1 preceding and 1 following --前一行,当前行和后一行.
还可以有其它的组合 。
还有个常见的统计语法是within group (order by ),只用于统计,比较典型的是dense_rank
2.4 其它事项说明
返回基本信息,以及到当前月份为止的平均成交量
SQL> select mons,jjr,cjl,avg(cjl) over ( partition by jjr order by mons rows between unbounded preceding and current row ) from test_value ;
MONS JJR CJL AVG(CJL)OVER(PARTITIONBYJJRORD
---------- ---------- ---------- ------------------------------
200801 LZF 250 250
200802 LZF 200 225
200803 LZF 300 250
200804 LZF 89 209.75
200805 LZF 356 239
200806 LZF 100 215.833333333333
200807 LZF 600 270.714285714286
200808 LZF 23 239.75
200809 LZF 400 257.555555555556
9 rows selected
返回结果和所有成交量的平均值
SQL> select mons,jjr,cjl,avg(cjl) over (partition by jjr order by mons rows between unbounded preceding and unbounded following) from test_value ;
MONS JJR CJL AVG(CJL)OVER(PARTITIONBYJJRORD
---------- ---------- ---------- ------------------------------
200801 LZF 250 257.555555555556
200802 LZF 200 257.555555555556
200803 LZF 300 257.555555555556
200804 LZF 89 257.555555555556
200805 LZF 356 257.555555555556
200806 LZF 100 257.555555555556
200807 LZF 600 257.555555555556
200808 LZF 23 257.555555555556
200809 LZF 400 257.555555555556
9 rows selected
三,具体说明和例子
2.1 avg (平均)
略,在2.4中已经举例。
2.2 collect(收集)
在pl/sql中也许更有施展的余地,必须和cast函数结合起来使用 .
看看这个例子,您就知道collect到底之干什么用的呢。
SQL> select * from table(select cast(collect(rownum) as type_intorder) from tab where rownum<4);
COLUMN_VALUE
------------
1
2
3
3 rows selected
2.3 corr(exp1,exp2) (皮尔逊关系系数)
用于计算两个参数威尔逊关系系数,至于皮尔逊关系是什么样的统计函数,只能看看统计类书籍了。
实际执行的结果是:COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
例子略.
其它corr_*都是和这个函数类似的皮尔逊函数.
2.4 count(计数)
用法和avg类似,不过count永远不会返回null而已,只会返回>=0的整数.
基于分析的方法,可以让count返回一些奇妙的结果。
SQL> select mons ,cjl,count(cjl) over (partition by jjr order by mons) xuhao from test_value;
MONS CJL XUHAO
---------- ---------- ----------
200801 250 1
200802 200 2
200803 300 3
200804 89 4
200805 356 5
200806 100 6
200807 600 7
200808 23 8
200809 400 9
9 rows selected
2.5 COVAR_POP(exp1,exp2)
总体协方差=(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n ,其中的n就是参数对的个数.
SQL> select mons ,cjl,cjje,covar_pop(cjl,cjje) over (partition by jjr order by mons) xuhao from test_value;
MONS CJL CJJE XUHAO
---------- ---------- ---------- ----------
200801 250 1999 0
200802 200 2000 -12.5
SQL> select (250*1999+200*2000-450*3999/2)/2 from dual;
(250*1999+200*2000-450*3999/2)
------------------------------
-12.5
2.6 COVAR_SAMP(EXP1,EXP2) (样本协方差)
计算公式=(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1) ,exp1,exp2必须是非空的.
2.7 CUME_DIST(累计分布) ,PERCENT_RANK
计算一组值中某个值的累计分布,但是作为统计函数和分析函数,其用法还是不一样的。
例子(源于原文):
SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Cume-Dist of 15500"
FROM employees;
Cume-Dist of 15500
------------------
.972222222
SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';
JOB_ID LAST_NAME SALARY CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PERCENT_Rank和cume_dist类似,返回的值也是介于0-1之间。
2.8 DENSE_RANK
这个函数同样可用于统计和分析。 用法同cume_dist
相当不好翻译这个词汇 。
作为一个统计函数,dense_rank是这样来计算值的(值总是大于等于1)
从下面的例子,我们可以看出是如何计算的:
SQL> select * from Test_Value order by cjl; (原始数据)
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200808 LZF 23 123
200804 LZF 89 189
200806 LZF 100 200
200802 LZF 200 2000
200801 LZF 250 1999
200803 LZF 300 1000
200805 LZF 356 456
200809 LZF 400 500
200807 LZF 600 700
9 rows selected
SQL> select dense_Rank(100) within group (order by cjl) from test_value;
(小于100的共有2+1)
DENSE_RANK(100)WITHINGROUP(ORD
------------------------------
3
SQL> select dense_Rank(23) within group (order by cjl) from test_value;
(小于23的共有0+1)
DENSE_RANK(23)WITHINGROUP(ORDE
------------------------------
1
SQL> select dense_Rank(200) within group (order by cjl) from test_value;
(小于200的共有3+1)
DENSE_RANK(200)WITHINGROUP(ORD
------------------------------
4
SQL> select dense_Rank(600) within group (order by cjl) from test_value;
(小于等于600的共有8+1)
DENSE_RANK(600)WITHINGROUP(ORD
------------------------------
9
所以,作为一个统计函数的dense_Rank,其计算方式是参数和order by之后的变量进行比较,如果在其内则加1,最后的结果是所有的累加,并再加1。
作为一个分析函数,不过是再增加一份分区和窗口,有些人就利用这个来进行排序。
很重要一点,partition by xxx这个部分并不是必须,这样就是以所有数据作为一个默认分区,在排序中尤为有效。
SQL> select mons,cjl,dense_rank() over (partition by jjr order by cjl) from test_Value;
MONS CJL DENSE_RANK()OVER(PARTITIONBYJJ
---------- ---------- ------------------------------
200808 23 1
200804 89 2
200806 100 3
200802 200 4
200801 250 5
200803 300 6
200805 356 7
200809 400 8
200807 600 9
9 rows selected
作为分析函数的dense_rank,是不能后跟参数的,例如:
SQL> select mons,jjr,cjl,dense_Rank() over (partition by jjr order by cjl) from test_Value;
MONS JJR CJL DENSE_RANK()OVER(PARTITIONBYJJ
---------- ---------- ---------- ------------------------------
200804 LZF 23 1
200808 LZF 23 1
200806 LZF 100 2
200802 LZF 200 3
200801 LZF 250 4
200803 LZF 300 5
200805 LZF 356 6
200809 LZF 400 7
200807 LZF 600 8
9 rows selected
需要注意的是,如果order by 中对应的值相同,则其rank也相同,对于作为分析函数的dense_rank,权当作一个排序函数使用(不知道oracle搞得这么复杂是什么意思).,和一般排序不同的在于允许有相同的排名,并且不会存在断裂的排名,其次还允许在不同组内各自排名(通过partition by 来实现).
2.9 rank
rank的语法和dense_Rank一致,主要是取值不一样(体现在rank()分析函数上)
SQL> SELECT MONS,JJR,CJL ,RANK() OVER (PARTITION BY JJR ORDER BY CJL) FROM TEST_VALUE;
MONS JJR CJL RANK()OVER(PARTITIONBYJJRORDER
---------- ---------- ---------- ------------------------------
200808 LZF 23 1
200804 LZF 23 1
200806 LZF 100 3
如上例,rank()会跳过一个序号,如果是dense_rank 则为1,1,2,rank则为1,1,3.
关于同时进行多个排名(而且可以是基于不同标准的)
还是以test_value为表格查询:
2.10 FIRST ,LAST
这是两个复杂的函数,必须和其它的函数配套使用方可,而且目前也只有和dense_Rank配套使用.
语法是:
统计函数 统计函数 +keep +(dense_rank first/last +order by ...)
分析函数 统计函数 +keep + (dense_rank first/last +order by ...) + over +( over 语句)
例子:
SQL> select sum(cjl) keep (dense_rank last order by cjl) max_cjl,
2 sum(cjl) keep (dense_rank first order by cjl) min_cjl
3 from test_value where jjr='LZF'
4 /
MAX_CJL MIN_CJL
---------- ----------
600 46
作为first dense_rank的内容,存在两条成交量为23的记录,故min_cl=23*2=46
2.11 GROUP_ID
没有特别用处,和select 语句中的group by 在一起使用,用于标明不同的分组,而且group by 后面只有跟上rollup之类的函数才是有用的,否则总是返回0,因为总是同个group.
SQL> select jjr,sum(cjl) ,group_id() from test_value group by jjr ,rollup(jjr);
JJR SUM(CJL) GROUP_ID()
---------- ---------- ----------
WTH 2252 0
LZF 2252 0
WTH 2252 1
LZF 2252 1
2.12 GROUPING,GROUPING_ID
这两个函数,也主要是用于操作方便用的,没有太多实际的用处.
SQL> select jjr ,sum(cjl) from test_value group by rollup(jjr);
JJR SUM(CJL)
---------- ----------
LZF 2252
WTH 2252
4504
上例演示标明对于超级统计而言,被分组的字段会以null表示。但是我们一般不希望这样,所以可以用
decode结合grouping处理。
SQL> select decode(grouping(jjr),0,jjr,'total cjl') ,sum(cjl) from test_value group by rollup(jjr);
DECODE(GROUPING(JJR),0,JJR,'TO SUM(CJL)
------------------------------ ----------
LZF 2252
WTH 2252
total cjl 4504
也有人直接对分组字段使用decode处理,那样会存在问题,因为可能会有些字段是Null,而导致不能正确地表达,我想这就是oracle设计这个函数的初衷之一吧。
--
grouping_id必须和select的group by 语句使用。
grouping_id和grouping初看起来没有什么区别,不过单单参数形式而言就存在不同, grouping只能接受一个参数,而grouping_id可以接受多个参数.其次grouping_id可以对区别分组的层次,而grouping不能(因为grouping总是只跟踪一个参数或者字段) 。
SQL> select grouping(jjr) ,grouping_id(jjr,mons),sum(cjl) from test_value group by rollup(jjr,mons);
GROUPING(JJR) GROUPING_ID(JJR,MONS) SUM(CJL)
------------- --------------------- ----------
0 0 250
0 0 200
0 0 300
0 0 23
0 0 356
0 0 100
0 0 600
0 0 23
0 0 400
0 1 2252
0 0 250
0 0 200
0 0 300
0 0 23
0 0 356
0 0 100
0 0 600
0 0 23
0 0 400
0 1 2252
GROUPING(JJR) GROUPING_ID(JJR,MONS) SUM(CJL)
------------- --------------------- ----------
1 3 4504
21 rows selected
2.13 max,min --最大,最小
都具有统计和分析功能,用法同avg.具体略。
2.14 median --取中间值
和avg类似用法,兼具统计和分析功能,参数必须是数值或者是可以隐式转换为数值的类型。用于计算一组数中的中间值,具体算法如下:
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
其中rn=(1 + (0.5*(N-1)),n 表示分组中行数.
CRN=CEILING(RN),FRN=FLOOR(RN)
举例:
SQL> select * from test_value;
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200801 LZF 250 1999
200802 LZF 200 2000
200803 LZF 300 1000
200804 LZF 23 189
200805 LZF 356 456
200806 LZF 100 200
6 rows selected
SQL> select jjr,median(cjl),median(cjje) from test_value group by jjr;
JJR MEDIAN(CJL) MEDIAN(CJJE)
---------- ----------- ------------
LZF 225 728
N=6 , RN= (1 + (0.5*(N-1))=3.5 ,FRN=3,CRN=4
则按照算法=(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)=0.5*200+0.5*250=225
2.15 PERCENTILE_CONT,PERCENTILE_DISC
PERCENTILE_CONT也是按照百分比位置来计算值,其形式和dense_Rank类似,但是其值和MEDIAN类似,MEDIAN是PERCENTILE_CONT(0.5)的特例。
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
其中rn=(1 + (p*(N-1)),n 表示分组中行数.p是[0,1]的有理数.
CRN=CEILING(RN),FRN=FLOOR(RN)
PERCENTILE_DISC(p) ,p=[0,1]
算法上和percentile_cont类似,语句形式也相同,所不同的是,这个函数不是简单的返回特定位置上的列的值,而是计算cume_dist的值,而这个值必须是大于等于p的最小cume_dist值。
2.16 STATS_*
属于比较复杂的统计分析函数
2.17 STDDEV*
略。
2.18 VAR*
略。
2.19 REGR_*
线性回归函数系列。
20080408 晴,开始热。
---
2010-09-26 ,近来比较有空,无需和一些不喜欢的人对面。
3 . ratio_to_report
这是一个很有用的函数。
毫无疑问,常常遇到统计占比的情况,用ratio_to_report无疑是十分有效的。
9i及其以前是否支持,就不清楚了。
毫无疑问,ratio_to_report节省了不少麻烦,除了简介,还无需处理除零之类的异常。
搞数据库较大一块部分就是为了计算和统计业务数据,在10G中出现了不少有趣的新函数,所以有必要介绍一下所有的统计函数.
官方的文档中,有各种千奇百怪的函数,有空还是自行练习下更好一些。
先有个印象,以后才好返回来查找需要的内容.
今天暂时没有时间,明天再来考虑这个问题吧。
一,函数列表和概述
AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
二,分析函数的基本语法
如果光光用于统计,很多函数都显得比较简单,但是一旦用于分析,则其语法就变得复杂起来。
基本的分析语法如下:
over (partition by order by range between unbounded preceding and current row)
意思就是按照某些列来分区,然后还好考虑数据范围,典型的就是从第一行到当前行.
2.1 partition by
用于分组查询的结果集合。 如果没有这个句子,那么函数就把所有的输出结果当作一个分组处理.
允许在一个查询语句中使用多个分析函数,并且每个的partition语句可以相同也可以不同。
如果查询的对象支持并行,那么该分析函数也支持并行方式.
2.2 order by /order siblings by
排序语句,在一个分区内部进行排序。
对于所有的分析函数(除了percentile_cont ,percentile_disc)都可以在一个分区之上进行排序
2.3 rows|range between () and () --windowing_clause窗口语句
并非所有的分析函数都支持分窗语句。
rows和range的区别。rows-定义了一个居于物理行的窗口,range定了一个基于逻辑位移的窗口。只有使用了order by 语句,才能用分窗语句.
即使使用了分窗语句,也不能保证分析函数每次返回的结果都是一定的(不变化)(当然这是基于表格的数据不变化的前提做出的论断)。可以设置更好的order by 语句,以便确保输出的结果是确定的.
范围关键字:unbounded preceding |unbounded following|current row
unbounded preceding --此行之前的所有行
unbounded following --此行之后的所有行
current row --当前行
5 preceding and current row --当前行之前5行(包括当前行,共6行)
1 preceding and 1 following --前一行,当前行和后一行.
还可以有其它的组合 。
还有个常见的统计语法是within group (order by ),只用于统计,比较典型的是dense_rank
2.4 其它事项说明
- 首先,大部分的分析函数允许在一个SQL语句中出现多次,以下以Rank为例子
- 其次,大部分的分析函数是可以在同一个SQL中并用的,典型的例如avg(),sum(),count()
- 统计函数和分析函数还是很不一样的(有点废话),分析突出的是比较,统计侧重于计算.
返回基本信息,以及到当前月份为止的平均成交量
SQL> select mons,jjr,cjl,avg(cjl) over ( partition by jjr order by mons rows between unbounded preceding and current row ) from test_value ;
MONS JJR CJL AVG(CJL)OVER(PARTITIONBYJJRORD
---------- ---------- ---------- ------------------------------
200801 LZF 250 250
200802 LZF 200 225
200803 LZF 300 250
200804 LZF 89 209.75
200805 LZF 356 239
200806 LZF 100 215.833333333333
200807 LZF 600 270.714285714286
200808 LZF 23 239.75
200809 LZF 400 257.555555555556
9 rows selected
返回结果和所有成交量的平均值
SQL> select mons,jjr,cjl,avg(cjl) over (partition by jjr order by mons rows between unbounded preceding and unbounded following) from test_value ;
MONS JJR CJL AVG(CJL)OVER(PARTITIONBYJJRORD
---------- ---------- ---------- ------------------------------
200801 LZF 250 257.555555555556
200802 LZF 200 257.555555555556
200803 LZF 300 257.555555555556
200804 LZF 89 257.555555555556
200805 LZF 356 257.555555555556
200806 LZF 100 257.555555555556
200807 LZF 600 257.555555555556
200808 LZF 23 257.555555555556
200809 LZF 400 257.555555555556
9 rows selected
三,具体说明和例子
2.1 avg (平均)
略,在2.4中已经举例。
2.2 collect(收集)
在pl/sql中也许更有施展的余地,必须和cast函数结合起来使用 .
看看这个例子,您就知道collect到底之干什么用的呢。
SQL> select * from table(select cast(collect(rownum) as type_intorder) from tab where rownum<4);
COLUMN_VALUE
------------
1
2
3
3 rows selected
2.3 corr(exp1,exp2) (皮尔逊关系系数)
用于计算两个参数威尔逊关系系数,至于皮尔逊关系是什么样的统计函数,只能看看统计类书籍了。
实际执行的结果是:COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
例子略.
其它corr_*都是和这个函数类似的皮尔逊函数.
2.4 count(计数)
用法和avg类似,不过count永远不会返回null而已,只会返回>=0的整数.
基于分析的方法,可以让count返回一些奇妙的结果。
SQL> select mons ,cjl,count(cjl) over (partition by jjr order by mons) xuhao from test_value;
MONS CJL XUHAO
---------- ---------- ----------
200801 250 1
200802 200 2
200803 300 3
200804 89 4
200805 356 5
200806 100 6
200807 600 7
200808 23 8
200809 400 9
9 rows selected
2.5 COVAR_POP(exp1,exp2)
总体协方差=(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n ,其中的n就是参数对的个数.
SQL> select mons ,cjl,cjje,covar_pop(cjl,cjje) over (partition by jjr order by mons) xuhao from test_value;
MONS CJL CJJE XUHAO
---------- ---------- ---------- ----------
200801 250 1999 0
200802 200 2000 -12.5
SQL> select (250*1999+200*2000-450*3999/2)/2 from dual;
(250*1999+200*2000-450*3999/2)
------------------------------
-12.5
2.6 COVAR_SAMP(EXP1,EXP2) (样本协方差)
计算公式=(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1) ,exp1,exp2必须是非空的.
2.7 CUME_DIST(累计分布) ,PERCENT_RANK
计算一组值中某个值的累计分布,但是作为统计函数和分析函数,其用法还是不一样的。
例子(源于原文):
SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Cume-Dist of 15500"
FROM employees;
Cume-Dist of 15500
------------------
.972222222
SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';
JOB_ID LAST_NAME SALARY CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PERCENT_Rank和cume_dist类似,返回的值也是介于0-1之间。
2.8 DENSE_RANK
这个函数同样可用于统计和分析。 用法同cume_dist
相当不好翻译这个词汇 。
作为一个统计函数,dense_rank是这样来计算值的(值总是大于等于1)
从下面的例子,我们可以看出是如何计算的:
SQL> select * from Test_Value order by cjl; (原始数据)
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200808 LZF 23 123
200804 LZF 89 189
200806 LZF 100 200
200802 LZF 200 2000
200801 LZF 250 1999
200803 LZF 300 1000
200805 LZF 356 456
200809 LZF 400 500
200807 LZF 600 700
9 rows selected
SQL> select dense_Rank(100) within group (order by cjl) from test_value;
(小于100的共有2+1)
DENSE_RANK(100)WITHINGROUP(ORD
------------------------------
3
SQL> select dense_Rank(23) within group (order by cjl) from test_value;
(小于23的共有0+1)
DENSE_RANK(23)WITHINGROUP(ORDE
------------------------------
1
SQL> select dense_Rank(200) within group (order by cjl) from test_value;
(小于200的共有3+1)
DENSE_RANK(200)WITHINGROUP(ORD
------------------------------
4
SQL> select dense_Rank(600) within group (order by cjl) from test_value;
(小于等于600的共有8+1)
DENSE_RANK(600)WITHINGROUP(ORD
------------------------------
9
所以,作为一个统计函数的dense_Rank,其计算方式是参数和order by之后的变量进行比较,如果在其内则加1,最后的结果是所有的累加,并再加1。
作为一个分析函数,不过是再增加一份分区和窗口,有些人就利用这个来进行排序。
很重要一点,partition by xxx这个部分并不是必须,这样就是以所有数据作为一个默认分区,在排序中尤为有效。
SQL> select mons,cjl,dense_rank() over (partition by jjr order by cjl) from test_Value;
MONS CJL DENSE_RANK()OVER(PARTITIONBYJJ
---------- ---------- ------------------------------
200808 23 1
200804 89 2
200806 100 3
200802 200 4
200801 250 5
200803 300 6
200805 356 7
200809 400 8
200807 600 9
9 rows selected
作为分析函数的dense_rank,是不能后跟参数的,例如:
SQL> select mons,jjr,cjl,dense_Rank() over (partition by jjr order by cjl) from test_Value;
MONS JJR CJL DENSE_RANK()OVER(PARTITIONBYJJ
---------- ---------- ---------- ------------------------------
200804 LZF 23 1
200808 LZF 23 1
200806 LZF 100 2
200802 LZF 200 3
200801 LZF 250 4
200803 LZF 300 5
200805 LZF 356 6
200809 LZF 400 7
200807 LZF 600 8
9 rows selected
需要注意的是,如果order by 中对应的值相同,则其rank也相同,对于作为分析函数的dense_rank,权当作一个排序函数使用(不知道oracle搞得这么复杂是什么意思).,和一般排序不同的在于允许有相同的排名,并且不会存在断裂的排名,其次还允许在不同组内各自排名(通过partition by 来实现).
2.9 rank
rank的语法和dense_Rank一致,主要是取值不一样(体现在rank()分析函数上)
SQL> SELECT MONS,JJR,CJL ,RANK() OVER (PARTITION BY JJR ORDER BY CJL) FROM TEST_VALUE;
MONS JJR CJL RANK()OVER(PARTITIONBYJJRORDER
---------- ---------- ---------- ------------------------------
200808 LZF 23 1
200804 LZF 23 1
200806 LZF 100 3
如上例,rank()会跳过一个序号,如果是dense_rank 则为1,1,2,rank则为1,1,3.
关于同时进行多个排名(而且可以是基于不同标准的)
还是以test_value为表格查询:
SQL> Select cjl 成交量, 2 rank() over(Order By cjl Desc) 成交量_排名, 3 cjje 成交金额, 4 rank() over(Order By cjje Desc) 成交金额_排名 5 From test_value 6 / 成交量 成交量_排名 成交金额 成交金额_排名 ---------- ----------- ---------- -------------------------------------------- 200 6 2000 1 250 5 1999 2 300 4 1000 3 600 1 700 4 400 2 500 5 356 3 456 6 100 7 200 7 23 8 189 8 23 8 123 9 |
2.10 FIRST ,LAST
这是两个复杂的函数,必须和其它的函数配套使用方可,而且目前也只有和dense_Rank配套使用.
语法是:
统计函数 统计函数 +keep +(dense_rank first/last +order by ...)
分析函数 统计函数 +keep + (dense_rank first/last +order by ...) + over +( over 语句)
例子:
SQL> select sum(cjl) keep (dense_rank last order by cjl) max_cjl,
2 sum(cjl) keep (dense_rank first order by cjl) min_cjl
3 from test_value where jjr='LZF'
4 /
MAX_CJL MIN_CJL
---------- ----------
600 46
作为first dense_rank的内容,存在两条成交量为23的记录,故min_cl=23*2=46
2.11 GROUP_ID
没有特别用处,和select 语句中的group by 在一起使用,用于标明不同的分组,而且group by 后面只有跟上rollup之类的函数才是有用的,否则总是返回0,因为总是同个group.
SQL> select jjr,sum(cjl) ,group_id() from test_value group by jjr ,rollup(jjr);
JJR SUM(CJL) GROUP_ID()
---------- ---------- ----------
WTH 2252 0
LZF 2252 0
WTH 2252 1
LZF 2252 1
2.12 GROUPING,GROUPING_ID
这两个函数,也主要是用于操作方便用的,没有太多实际的用处.
SQL> select jjr ,sum(cjl) from test_value group by rollup(jjr);
JJR SUM(CJL)
---------- ----------
LZF 2252
WTH 2252
4504
上例演示标明对于超级统计而言,被分组的字段会以null表示。但是我们一般不希望这样,所以可以用
decode结合grouping处理。
SQL> select decode(grouping(jjr),0,jjr,'total cjl') ,sum(cjl) from test_value group by rollup(jjr);
DECODE(GROUPING(JJR),0,JJR,'TO SUM(CJL)
------------------------------ ----------
LZF 2252
WTH 2252
total cjl 4504
也有人直接对分组字段使用decode处理,那样会存在问题,因为可能会有些字段是Null,而导致不能正确地表达,我想这就是oracle设计这个函数的初衷之一吧。
--
grouping_id必须和select的group by 语句使用。
grouping_id和grouping初看起来没有什么区别,不过单单参数形式而言就存在不同, grouping只能接受一个参数,而grouping_id可以接受多个参数.其次grouping_id可以对区别分组的层次,而grouping不能(因为grouping总是只跟踪一个参数或者字段) 。
SQL> select grouping(jjr) ,grouping_id(jjr,mons),sum(cjl) from test_value group by rollup(jjr,mons);
GROUPING(JJR) GROUPING_ID(JJR,MONS) SUM(CJL)
------------- --------------------- ----------
0 0 250
0 0 200
0 0 300
0 0 23
0 0 356
0 0 100
0 0 600
0 0 23
0 0 400
0 1 2252
0 0 250
0 0 200
0 0 300
0 0 23
0 0 356
0 0 100
0 0 600
0 0 23
0 0 400
0 1 2252
GROUPING(JJR) GROUPING_ID(JJR,MONS) SUM(CJL)
------------- --------------------- ----------
1 3 4504
21 rows selected
2.13 max,min --最大,最小
都具有统计和分析功能,用法同avg.具体略。
2.14 median --取中间值
和avg类似用法,兼具统计和分析功能,参数必须是数值或者是可以隐式转换为数值的类型。用于计算一组数中的中间值,具体算法如下:
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
其中rn=(1 + (0.5*(N-1)),n 表示分组中行数.
CRN=CEILING(RN),FRN=FLOOR(RN)
举例:
SQL> select * from test_value;
MONS JJR CJL CJJE
---------- ---------- ---------- ----------
200801 LZF 250 1999
200802 LZF 200 2000
200803 LZF 300 1000
200804 LZF 23 189
200805 LZF 356 456
200806 LZF 100 200
6 rows selected
SQL> select jjr,median(cjl),median(cjje) from test_value group by jjr;
JJR MEDIAN(CJL) MEDIAN(CJJE)
---------- ----------- ------------
LZF 225 728
N=6 , RN= (1 + (0.5*(N-1))=3.5 ,FRN=3,CRN=4
则按照算法=(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)=0.5*200+0.5*250=225
2.15 PERCENTILE_CONT,PERCENTILE_DISC
PERCENTILE_CONT也是按照百分比位置来计算值,其形式和dense_Rank类似,但是其值和MEDIAN类似,MEDIAN是PERCENTILE_CONT(0.5)的特例。
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
其中rn=(1 + (p*(N-1)),n 表示分组中行数.p是[0,1]的有理数.
CRN=CEILING(RN),FRN=FLOOR(RN)
PERCENTILE_DISC(p) ,p=[0,1]
算法上和percentile_cont类似,语句形式也相同,所不同的是,这个函数不是简单的返回特定位置上的列的值,而是计算cume_dist的值,而这个值必须是大于等于p的最小cume_dist值。
2.16 STATS_*
属于比较复杂的统计分析函数
2.17 STDDEV*
略。
2.18 VAR*
略。
2.19 REGR_*
线性回归函数系列。
20080408 晴,开始热。
---
2010-09-26 ,近来比较有空,无需和一些不喜欢的人对面。
3 . ratio_to_report
这是一个很有用的函数。
毫无疑问,常常遇到统计占比的情况,用ratio_to_report无疑是十分有效的。
9i及其以前是否支持,就不清楚了。
SQL> select * from score2; SID SUBNAME CJ COMMENTS --------------------------------------- -------------------- ---------- 3 c 99 生生世世 1 chinese 100 2 2 chinese 80 1 4 chinese 60 0 5 chinese 70 2 6 math 70 3 7 math 80 -1 8 math 100 -2 9 english 67 0 9 rows selected SQL> select sid,cj,to_char(100*(ratio_to_report(cj) over()),'900.000')||'%' bfb from score2 order by bfb; SID CJ BFB --------------------------------------- ---------- --------- 4 60 08.264% 9 67 09.229% 5 70 09.642% 6 70 09.642% 7 80 11.019% 2 80 11.019% 3 99 13.636% 8 100 13.774% 1 100 13.774% 9 rows selected ------------ 如果不希望用ratio_to_report也有等效的方式。 SQL> select sid,cj,100*cj/sum(cj) over( order by cj rows 2 between unbounded preceding and unbounded following ) bfb from score2 order by bfb; SID CJ BFB --------------------------------------- ---------- ---------- 4 60 8.26446280 9 67 9.22865013 5 70 9.64187327 6 70 9.64187327 7 80 11.0192837 2 80 11.0192837 3 99 13.6363636 8 100 13.7741046 1 100 13.7741046 9 rows selected |