oracle的sqlplus下的配置.docx

oracle的sqlplus下的配置.docx

《oracle的sqlplus下的配置.docx》由会员分享，可在线阅读，更多相关《oracle的sqlplus下的配置.docx（27页珍藏版）》请在冰点文库上搜索。

oracle的sqlplus下的配置

oracle分析函数--SQL*PLUS环境

一、总体介绍

12.1分析函数如何工作

语法FUNCTION_NAME（<参数>,…）OVER（>）PARTITION子句ORDERBY子句WINDOWING子句缺省时相当于RANGEUNBOUNDEDPRECEDING

1.值域窗（RANGEWINDOW）

RANGENPRECEDING仅对数值或日期类型有效,选定窗为排序后当前行之前,某列（即排序列）值大于/小于（当前行该列值–/+N）的所有行,因此与ORDERBY子句有关系。

2.行窗（ROWWINDOW）

ROWSNPRECEDING选定窗为当前行及之前N行。

还可以加上BETWEENAND形式,例如RANGEBETWEENmPRECEDINGANDnFOLLOWING

函数AVG（eXPr）

一组或选定窗中表达式的平均值CORR（expr,expr）即COVAR_POP（exp1,exp2）/（STDDEV_POP（expr1）*STDDEV_POP（expr2））,两个表达式的互相关,-1（反相关）~1（正相关）,0表示不相关

COUNT（<*>）计数

COVAR_POP（expr,expr）总体协方差

COVAR_SAMP（expr,expr）样本协方差

CUME_DIST累积分布,即行在组中的相对位置,返回0~1

DENSE_RANK行的相对排序（与ORDERBY搭配）,相同的值具有一样的序数（NULL计为相同）,并不留空序数

FIRST_VALUE一个组的第一个值

LAG（expr,,）访问之前的行,OFFSET是缺省为1的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）

LAST_VALUE一个组的最后一个值

LEAD（expr,,）访问之后的行,OFFSET是缺省为1的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值（如最后行不存在之前行）

MAX（expr）最大值

MIN（expr）最小值

NTILE（expr）按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1~4的值,而不能等分则多出的部分在值最小的那组

PERCENT_RANK类似CUME_DIST,1/（行的序数-1）

RANK相对序数,答应并列,并空出随后序号

RATIO_TO_REPORT（expr）表达式值/SUM（表达式值）

ROW_NUMBER排序的组中行的偏移

STDDEV（expr）标准差

STDDEV_POP（expr）总体标准差

STDDEV_SAMP（expr）样本标准差

SUM（expr）合计

VAR_POP（expr）总体方差

VAR_SAMP（expr）样本方差

VARIANCE（expr）方差

REGR_xxxx（expr,expr）线性回归函数

REGR_SLOPE：

返回斜率，等于COVAR_POP（expr1,expr2）/VAR_POP（expr2）

REGR_INTERCEPT：

返回回归线的y截距，等于

AVG（expr1）-REGR_SLOPE（expr1,expr2）*AVG（expr2）

REGR_COUNT：

返回用于填充回归线的非空数字对的数目

REGR_R2：

返回回归线的决定系数，计算式为：

IfVAR_POP（expr2）=0thenreturnNULL

IfVAR_POP（expr1）=0andVAR_POP（expr2）!

=0thenreturn1

IfVAR_POP（expr1）>0andVAR_POP（expr2!

=0then

returnPOWER（CORR（expr1,expr）,2）

REGR_AVGX：

计算回归线的自变量（expr2）的平均值，去掉了空对（expr1,expr2）后，等于AVG（expr2）

REGR_AVGY：

计算回归线的应变量（expr1）的平均值，去掉了空对（expr1,expr2）后，等于AVG（expr1）

REGR_SXX：

返回值等于REGR_COUNT（expr1,expr2）*VAR_POP（expr2）

REGR_SYY：

返回值等于REGR_COUNT（expr1,expr2）*VAR_POP（expr1）

REGR_SXY:

返回值等于REGR_COUNT（expr1,expr2）*COVAR_POP（expr1,expr2）

首先：

创建表及接入测试数据

createtablestudents

（idnumber（15,0）,

areavarchar2（10）,

stu_typevarchar2

（2）,

scorenumber（20,2））;

insertintostudentsvalues（1,'111','g',80）;

insertintostudentsvalues（1,'111','j',80）;

insertintostudentsvalues（1,'222','g',89）;

insertintostudentsvalues（1,'222','g',68）;

insertintostudentsvalues（2,'111','g',80）;

insertintostudentsvalues（2,'111','j',70）;

insertintostudentsvalues（2,'222','g',60）;

insertintostudentsvalues（2,'222','j',65）;

insertintostudentsvalues（3,'111','g',75）;

insertintostudentsvalues（3,'111','j',58）;

insertintostudentsvalues（3,'222','g',58）;

insertintostudentsvalues（3,'222','j',90）;

insertintostudentsvalues（4,'111','g',89）;

insertintostudentsvalues（4,'111','j',90）;

insertintostudentsvalues（4,'222','g',90）;

insertintostudentsvalues（4,'222','j',89）;

commit;

二、具体应用：

1、分组求和：

1）GROUPBY子句

--A、GROUPINGSETS

selectid,area,stu_type,sum（score）score

fromstudents

groupbygroupingsets（（id,area,stu_type）,（id,area）,id）

orderbyid,area,stu_type;

/*--------理解groupingsets

selecta,b,c,sum（d）fromt

groupbygroupingsets（a,b,c）

等效于

select*from（

selecta,null,null,sum（d）fromtgroupbya

unionall

selectnull,b,null,sum（d）fromtgroupbyb

unionall

selectnull,null,c,sum（d）fromtgroupbyc

）

*/

--B、ROLLUP

selectid,area,stu_type,sum（score）score

fromstudents

groupbyrollup（id,area,stu_type）

orderbyid,area,stu_type;

/*--------理解rollup

selecta,b,c,sum（d）

fromt

groupbyrollup（a,b,c）;

等效于

select*from（

selecta,b,c,sum（d）fromtgroupbya,b,c

unionall

selecta,b,null,sum（d）fromtgroupbya,b

unionall

selecta,null,null,sum（d）fromtgroupbya

unionall

selectnull,null,null,sum（d）fromt

）

*/

--C、CUBE

selectid,area,stu_type,sum（score）score

fromstudents

groupbycube（id,area,stu_type）

orderbyid,area,stu_type;

/*--------理解cube

selecta,b,c,sum（d）fromt

groupbycube（a,b,c）

等效于

selecta,b,c,sum（d）fromt

groupbygroupingsets（

（a,b,c）,

（a,b）,（a）,（b,c）,

（b）,（a,c）,（c）,

（））

*/

--D、GROUPING

/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,

如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!

*/

selectdecode（grouping（id）,1,'allid',id）id,

decode（grouping（area）,1,'allarea',to_char（area））area,

decode（grouping（stu_type）,1,'all_stu_type',stu_type）stu_type,

sum（score）score

fromstudents

groupbycube（id,area,stu_type）

orderbyid,area,stu_type;

二、OVER（）函数的使用

1、统计名次——DENSE_RANK（）,ROW_NUMBER（）

1）允许并列名次、名次不间断，DENSE_RANK（），结果如122344456……

将score按ID分组排名：

dense_rank（）over（partitionbyidorderbyscoredesc）

将score不分组排名：

dense_rank（）over（orderbyscoredesc）

selectid,area,score,

dense_rank（）over（partitionbyidorderbyscoredesc）分组id排序,

dense_rank（）over（orderbyscoredesc）不分组排序

fromstudentsorderbyid,area;

2）不允许并列名次、相同值名次不重复，ROW_NUMBER（），结果如123456……

将score按ID分组排名：

row_number（）over（partitionbyidorderbyscoredesc）

将score不分组排名：

row_number（）over（orderbyscoredesc）

selectid,area,score,

row_number（）over（partitionbyidorderbyscoredesc）分组id排序,

row_number（）over（orderbyscoredesc）不分组排序

fromstudentsorderbyid,area;

3）允许并列名次、复制名次自动空缺，rank（），结果如12245558……

将score按ID分组排名：

rank（）over（partitionbyidorderbyscoredesc）

将score不分组排名：

rank（）over（orderbyscoredesc）

selectid,area,score,

rank（）over（partitionbyidorderbyscoredesc）分组id排序,

rank（）over（orderbyscoredesc）不分组排序

fromstudentsorderbyid,area;

4）名次分析，cume_dist（）——-最大排名/总个数

函数：

cume_dist（）over（orderbyid）

selectid,area,score,

cume_dist（）over（orderbyid）a,--按ID最大排名/总个数

cume_dist（）over（partitionbyidorderbyscoredesc）b,--ID分组中，scroe最大排名值/本组总个数

row_number（）over（orderbyid）记录号

fromstudentsorderbyid,area;

5）利用cume_dist（），允许并列名次、复制名次自动空缺，取并列后较大名次，结果如22355778……

将score按ID分组排名：

cume_dist（）over（partitionbyidorderbyscoredesc）*sum

（1）over（partitionbyid）

将score不分组排名：

cume_dist（）over（orderbyscoredesc）*sum

（1）over（）

selectid,area,score,

sum

（1）over（）as总数,

sum

（1）over（partitionbyid）as分组个数,

（cume_dist（）over（partitionbyidorderbyscoredesc））*（sum

（1）over（partitionbyid））分组id排序,

（cume_dist（）over（orderbyscoredesc））*（sum

（1）over（））不分组排序

fromstudentsorderbyid,area

2、分组统计－－sum（）,max（）,avg（）,RATIO_TO_REPORT（）

selectid,area,

sum

（1）over（）as总记录数,

sum

（1）over（partitionbyid）as分组记录数,

sum（score）over（）as总计,

sum（score）over（partitionbyid）as分组求和,

sum（score）over（orderbyid）as分组连续求和,

sum（score）over（partitionbyid,area）as分组ID和area求和,

sum（score）over（partitionbyidorderbyarea）as分组ID并连续按area求和,

max（score）over（）as最大值,

max（score）over（partitionbyid）as分组最大值,

max（score）over（orderbyid）as分组连续最大值,

max（score）over（partitionbyid,area）as分组ID和area求最大值,

max（score）over（partitionbyidorderbyarea）as分组ID并连续按area求最大值,

avg（score）over（）as所有平均,

avg（score）over（partitionbyid）as分组平均,

avg（score）over（orderbyid）as分组连续平均,

avg（score）over（partitionbyid,area）as分组ID和area平均,

avg（score）over（partitionbyidorderbyarea）as分组ID并连续按area平均,

RATIO_TO_REPORT（score）over（）as"占所有%",

RATIO_TO_REPORT（score）over（partitionbyid）as"占分组%",

scorefromstudents;

3、LAG（COL,n,default）、LEAD（OL,n,default）--取前后边N条数据

取前面记录的值：

lag（score,n,x）over（orderbyid）

取后面记录的值：

lead（score,n,x）over（orderbyid）

参数：

n表示移动N条记录，X表示不存在时填充值，iD表示排序列

selectid,lag（score,1,0）over（orderbyid）lg,scorefromstudents;

selectid,lead（score,1,0）over（orderbyid）lg,scorefromstudents;

4、FIRST_VALUE（）、LAST_VALUE（）

取第起始1行值：

first_value（score,n）over（orderbyid）

取第最后1行值：

LAST_value（score,n）over（orderbyid）

selectid,first_value（score）over（orderbyid）fv,scorefromstudents;

selectid,last_value（score）over（orderbyid）fv,scorefromstudents

sum（...）over...

【功能】连续求和分析函数

【参数】具体参示例

【说明】Oracle分析函数

NC示例：

selectbdcode,sum

（1）over（orderbybdcode）aafrombd_bdinfo

【示例】

1.原表信息：

SQL>breakondeptnoskip1--为效果更明显，把不同部门的数据隔段显示。

SQL>selectdeptno,ename,sal

2fromemp

3orderbydeptno;

DEPTNOENAMESAL

------------------------------

10CLARK2450

KING5000

MILLER1300

20SMITH800

ADAMS1100

FORD3000

SCOTT3000

JONES2975

30ALLEN1600

BLAKE2850

MARTIN1250

JAMES950

TURNER1500

WARD1250

2.先来一个简单的，注意over（...）条件的不同，

使用sum（sal）over（orderbyename）...查询员工的薪水“连续”求和,

注意over（orderbyename）如果没有orderby子句，求和就不是“连续”的，

放在一起，体会一下不同之处：

SQL>selectdeptno,ename,sal,

2sum（sal）over（orderbyename）连续求和,

3sum（sal）over（）总和,--此处sum（sal）over（）等同于sum（sal）

4100*round（sal/sum（sal）over（）,4）"份额（%）"

5fromemp

6/

DEPTNOENAMESAL连续求和总和份额（%）

------------------------------------------------------------

20ADAMS11001100290253.79

30ALLEN16002700290255.51

30BLAKE28505550290259.82

10CLARK24508000290258.44

20FORD3000110002902510.34

30JAMES95011950290253.27

20JONES2975149252902510.25

10KING5000199252902517.23

30MARTIN125021175290254.31

10MILLER130022475290254.48

20SCOTT3000254752902510.34

20SMITH80026275290252.76

30TURNER150027775290255.17

30WARD125029025290254.31

3.使用子分区查出各部门薪水连续的总和。

注意按部门分区。

注意over（...）条件的不同，

sum（sal）over（partitionbydeptnoorderbyename）按部门“连续”求总和

sum（sal）over（partitionbydeptno）按部门求总和

sum（sal）over（orderbydeptno，ename）不按部门“连续”求总和

sum（sal）over（）不按部门，求所有员工总和，效果等同于sum（sal）。

SQL>selectdeptno,ename,sal,

2sum（sal）over（partitionbydeptnoorderbyename）部门连续求和,--各部门的薪水"连续"求和

3sum（sal）over（partitionbydeptno）部门总和,--部门统计的总和，同一部门总和不变

4100*round（sal/s