oracle分析函数等高级sqlWord下载.docx
《oracle分析函数等高级sqlWord下载.docx》由会员分享，可在线阅读，更多相关《oracle分析函数等高级sqlWord下载.docx（30页珍藏版）》请在冰豆网上搜索。
FROMemployees;
MANAGER_IDLAST_NAME
HIRE_DATE
SALARY
C_MAVG
----------------------------------------------------------------
100Kochhar
21-SEP-89
17000
17000
100DeHaan
13-JAN-93
15000
100Raphaely
07-DEC-94
11000
100Kaufling
01-MAY-95
7900
100Hartstein
17-FEB-96
13000
100Weiss
18-JUL-96
8000
100Russell
01-OCT-96
14000
.
CORR
返回一对表达式的相关系数，它是如下的缩写：
COVAR_POP（expr1,expr2）/STDDEV_POP（expr1）*STDDEV_POP（expr2））
从统计上讲，相关性是变量之间关联的强度，变量之间的关联意味着在某种程度
上一个变量的值可由其它的值进行预测。
通过返回一个-1~1之间的一个数,相关
系数给出了关联的强度，0表示不相关。
下例返回1998年月销售收入和月单位销售的关系的累积系数（本例在SH用户下运行）
SELECT,
CORR（SUM,SUM）
OVER（ORDERBYasCUM_CORR
FROMsaless,timest
WHERE=ANDcalendar_year=1998
GROUPBY
ORDERBY;
CALENDAR_MONTH_NUMBER
CUM_CORR
-------------------------------
1
2
1
3.2
4.5
5.4
6.8
7.3
8.9
9.6
10
11.5
12.2
COVAR_POP
返回一对表达式的总体协方差。
下例CUM_COVP返回定价和最小产品价格的累积总体协方差
SELECTproduct_id,supplier_id,
COVAR_POP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVP,
COVAR_SAMP（list_price,min_price）
OVER（ORDERBYproduct_id,supplier_id）ASCUM_COVS
FROMproduct_informationp
WHEREcategory_id=29
ORDERBYproduct_id,supplier_id;
PRODUCT_IDSUPPLIER_ID
CUM_COVP
CUM_COVS
-----------------------------------------
1774
103088
0
1775
103087
1794
103096
1825
103093
2004
103086
2005
1815
2416
103088
COVAR_SAMP
返回一对表达式的样本协方差
下例CUM_COVS返回定价和最小产品价格的累积样本协方差
COUNT
对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；
可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。
下面例子中计算每个员工在按薪水排序中当前行附近薪水在[n-50,n+150]之间的行数，n表示当前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行没有，所以count计数值cnt3为2（包括自己当前行）；
cnt2值相当于小于等于当前行的SALARY值的所有行数
SELECTlast_name,salary,COUNT（*）OVER（）AScnt1,
COUNT（*）OVER（ORDERBYsalary）AScnt2,
COUNT（*）OVER（ORDERBYsalaryRANGEBETWEEN50PRECEDING
AND150FOLLOWING）AScnt3FROMemployees;
LAST_NAME
CNT1
CNT2
CNT3
-----------------------------------------------------------------
Olson
2100
107
1
3
Markle
2200
3
2
Philtanker
2200
Landry
2400
5
8
Gee
Colmenares
2500
11
10
Patel
2500
CUME_DIST
计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置。
例如，在一个3行的组中，返回的累计分布值为1/3、2/3、3/3
下例中计算每个工种的员工按薪水排序依次累积出现的分布百分比
SELECTjob_id,last_name,salary,CUME_DIST（）
OVER（PARTITIONBYjob_idORDERBYsalary）AScume_dist
FROMemployees
WHEREjob_idLIKE'
PU%'
;
JOB_ID
CUME_DIST
-------------------------------------------------------
PU_CLERK
.2
Himuro
2600
.4
Tobias
2800
.6
Baida
2900
.8
Khoo
3100
PU_MAN
Raphaely
11000
DENSE_RANK
根据ORDERBY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。
组内的数据按ORDERBY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。
每次ORDERBY表达式的值发生变化时，该序列也随之增加。
有同样值的行得到同样的数字序号（认为null时相等的）。
密集的序列返回的时没有间隔的数
下例中计算每个员工按部门分区再按薪水排序，依次出现的序列号（注意与RANK函数的区别）
SELECT,,,DENSE_RANK（）
OVER（PARTITIONBYORDERBYasdrank
FROMemployeese,departmentsd
WHERE=
ANDIN（'
60'
'
90'
）;
DEPARTMENT_IDLAST_NAME
DRANK
----------------------------------------------------------
60Lorentz
4200
60Austin
4800
60Pataballa
60Ernst
6000
60Hunold
9000
4
90Kochhar
90DeHaan
90King
24000
FIRST
从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录
下面例子中DENSE_RANK按部门分区，再按佣金commission_pct排序，FIRST取出佣金最低的对应的所有行，然后前面的MAX函数从这个集合中取出薪水最低的值；
LAST取出佣金最高的对应的所有行，然后前面的MIN函数从这个集合中取出薪水最高的值
SELECTlast_name,department_id,salary,
MIN（salary）KEEP（DENSE_RANKFIRSTORDERBYcommission_pct）
OVER（PARTITIONBYdepartment_id）"
Worst"
MAX（salary）KEEP（DENSE_RANKLASTORDERBYcommission_pct）
Best"
FROMemployees
WHEREdepartment_idin（20,80）
ORDERBYdepartment_id,salary;
DEPARTMENT_ID
Worst
Best
--------------------------------------------------------------------
Fay
20
6000
13000
Hartstein
13000
Kumar
80
6100
14000
Banda
6200
Johnson
Ande
6400
Lee
80
6800
Tuvault
7000
Sewall
Marvins
7200
Bates
7300
FIRST_VALUE
返回组中数据窗口的第一个值。
下面例子计算按部门分区按薪水排序的数据窗口的第一个值对应的名字，如果薪水的第一个值有多个，则从多个对应的名字中取缺省排序的第一个名字
SELECTdepartment_id,last_name,salary,FIRST_VALUE（last_name）
OVER（PARTITIONBYdepartment_idORDERBYsalaryASC）ASlowest_sal
WHEREdepartment_idin（20,30）;
SALARYLOWEST_SAL
--------------------------------------------------------------
20Fay
6000Fay
20Hartstein
13000Fay
30Colmenares
2500Colmenares
30Himuro
2600Colmenares
30Tobias
2800Colmenares
30Baida
2900Colmenares
30Khoo
3100Colmenares
30Raphaely
11000Colmenares
LAG
可以访问结果集中的其它行而不用进行自连接。
它允许去处理游标，就好像游标是一个数组一样。
在给定组中可参考当前行之前的行，这样就可以从组中与当前行一起选择以前的行。
Offset是一个正整数，其默认值为1，若索引超出窗口的范围，就返回默认值（默认返回的是组中第一行），其相反的函数是LEAD
下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECTlast_name,hire_date,salary,
LAG（salary,1,0）OVER（ORDERBYhire_date）ASprev_sal
FROMemployees
WHEREjob_id='
PU_CLERK'
HIRE_
升级会员 