zgb1函数.docx
- 文档编号:13348122
- 上传时间:2023-06-13
- 格式:DOCX
- 页数:21
- 大小:22.74KB
zgb1函数.docx
《zgb1函数.docx》由会员分享,可在线阅读,更多相关《zgb1函数.docx(21页珍藏版)》请在冰点文库上搜索。
zgb1函数
下面主要介绍一下以下几个函数的使用方法
1.Over()开窗函数
2.Nvl()函数
3.Rollup,Cube自动汇总函数
4.Rank,Dense_rank,Row_number函数
5.Lag,Lead函数
6.Sum,Avg,Count,Max函数
7.Ratio_to_report报表处理函数
8.First,Last,First_value,Last_value取基数的分析函数
9.Greatest,Least函数
10.Trunc,Round,Decode,Substr函数
一.Over()开窗函数
Over()开窗函数是Oracle的分析函数,其语法如下:
函数名([参数])over([分区子句][排序子句[滑动窗口子句]])
分区子句类似于聚组函数所需要的groupby,排序子句可看成是SQL语句中的orderby,只不过在此语句中还可指定null值排前(nullsfirst)还是排后(nullslast)。
开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
over(orderbysalary)按照salary排序进行累计,orderby是个默认的开窗函数
over(partitionbydeptno)按照部门分区
over(orderbysalaryrangebetween50precedingand150following)
每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150
over(orderbysalaryrowsbetween50precedingand150following)
每行对应的数据窗口是之前50行,之后150行
over(orderbysalaryrowsbetweenunboundedprecedingandunboundedfollowing)
每行对应的数据窗口是从第一行到最后一行,等效:
over(orderbysalaryrangebetweenunboundedprecedingandunboundedfollowing)
二.Nvl()函数
NVL(EXP1,EXP2),函数返回exp1和exp2中第一个不为空的值。
如果exp1为空则返回exp2,否则返回exp1。
注意:
如果exp1不是字符串,那么返回的数据类型和exp1的数据类型相同,否则返回的数据类型为varchar2型。
SQL>selectnvl('Thisisnotnull',7)Frist,nvl(null,'MyOracle')Secondfromdual;
FRISTSECOND
-------------------------------
ThisisnotnullMyOracle
三.自动汇总函数rollup,cube
Rollup:
表示的意思是:
除了分组的功能外,还进行累加的的,多了一个汇总。
如果是GROUPBYROLLUP(A,B,C)的话,GROUPBY顺序
(A、B、C)
(A、B)
(A)
最后对全表进行GROUPBY操作。
Cube提供了按照多字段汇总的功能。
如果是GROUPBYCUBE(A,B,C),GROUPBY顺序
(A、B、C)
(A、B)
(A、C)
(A),
(B、C)
(B)
(C),
最后对全表进行GROUPBY操作。
示例:
CREATETABLEstudentscore
(
student_namevarchar2(20),
subjectsvarchar2(20),
scorenumber
)
INSERTINTOstudentscoreVALUES('WBQ','ENGLISH',90);
INSERTINTOstudentscoreVALUES('WBQ','MATHS',95);
INSERTINTOstudentscoreVALUES('WBQ','CHINESE',88);
INSERTINTOstudentscoreVALUES('CZH','ENGLISH',80);
INSERTINTOstudentscoreVALUES('CZH','MATHS',90);
INSERTINTOstudentscoreVALUES('CZH','HISTORY',92);
INSERTINTOstudentscoreVALUES('CB','POLITICS',70);
INSERTINTOstudentscoreVALUES('CB','HISTORY',75);
INSERTINTOstudentscoreVALUES('LDH','POLITICS',80);
INSERTINTOstudentscoreVALUES('LDH','CHINESE',90);
INSERTINTOstudentscoreVALUES('LDH','HISTORY',95);
select*fromstudentscore;
/*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
SELECTstudent_name,subjects,SUM(score)
FROMstudentscore
GROUPBYCUBE(student_name,subjects)
ORDERBY1;
等同于以下标准SQL
/*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
SELECTNULL,subjects,SUM(score)
FROMstudentscore
GROUPBYsubjects
UNION
SELECTstudent_name,NULL,SUM(score)
FROMstudentscore
GROUPBYstudent_name
UNION
SELECTNULL,NULL,SUM(score)
FROMstudentscore
UNION
SELECTstudent_name,subjects,SUM(score)
FROMstudentscore
GROUPBYstudent_name,subjects
/*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
SELECTstudent_name,subjects,SUM(score)
FROMstudentscore
GROUPBYROLLUP(student_name,subjects);
/*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
SELECTstudent_name,NULL,SUM(score)
FROMstudentscore
GROUPBYstudent_name
UNION
SELECTNULL,NULL,SUM(score)
FROMstudentscore
UNION
SELECTstudent_name,subjects,SUM(score)
FROMstudentscore
GROUPBYstudent_name,subjects
/*Formattedon2009/11/0820:
35(FormatterPlusv4.8.8)*/
SELECTGROUPING(student_name),GROUPING(subjects),student_name,subjects,
SUM(score)
FROMstudentscore
GROUPBYCUBE(student_name,subjects)
ORDERBY1,2;
/*Formattedon2009/11/0820:
36(FormatterPlusv4.8.8)*/
SELECTGROUPING(student_name),GROUPING(subjects),student_name,subjects,
SUM(score)
FROMstudentscore
GROUPBYROLLUP(student_name,subjects)
ORDERBY1,2;
/*Formattedon2009/11/0820:
36(FormatterPlusv4.8.8)*/
SELECTGROUPING_ID(student_name,subjects),student_name,subjects,
SUM(score)
FROMstudentscore
GROUPBYCUBE(student_name,subjects)
ORDERBY1;
/*Formattedon2009/11/0820:
36(FormatterPlusv4.8.8)*/
SELECTGROUPING_ID(student_name,subjects),student_name,subjects,
SUM(score)
FROMstudentscore
GROUPBYROLLUP(student_name,subjects)
ORDERBY1;
/*Formattedon2009/11/0820:
36(FormatterPlusv4.8.8)*/
SELECTGROUPING(student_name),GROUPING(subjects),
CASE
WHENGROUPING(student_name)=0
ANDGROUPING(subjects)=1
THEN'学生成绩合计'
WHENGROUPING(student_name)=1
ANDGROUPING(subjects)=0
THEN'课目成绩合计'
WHENGROUPING(student_name)=1
ANDGROUPING(subjects)=1
THEN'总计'
ELSE''
ENDsummary,
student_name,subjects,SUM(score)
FROMstudentscore
GROUPBYCUBE(student_name,subjects)
ORDERBY1,2;
四.rank,dense_rank,row_number函数
Rank,Dense_rank,Row_number函数为每条记录产生一个从1开始至N的自然数,N的值可能小于等于记录的总数。
这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
②DENSE_RANK:
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
③RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
示例:
/*Formattedon2009/11/0820:
48(FormatterPlusv4.8.8)*/
SELECTename,deptno,sal,
RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)"RANK",
DENSE_RANK()OVER(PARTITIONBYdeptnoORDERBYsalDESC)"DENSE_RANK",
ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalDESC)"ROW_NUMBER"
FROMscott.emp
ENAME
DEPTNO
SAL
RANK
DENSE_RANK
ROW_NUMBER
KING
10
5,000
1
1
1
CLARK
10
2,450
2
2
2
MILLER
10
1,300
3
3
3
SCOTT
20
3,000
1
1
1
FORD
20
3,000
1
1
2
JONES
20
2,975
3
2
3
ADAMS
20
1,100
4
3
4
SMITH
20
800
5
4
5
BLAKE
30
2,850
1
1
1
ALLEN
30
1,600
2
2
2
TURNER
30
1,500
3
3
3
MARTIN
30
1,250
4
4
4
WARD
30
1,250
4
4
5
JAMES
30
950
6
5
6
五.lag,lead函数
lag(expression,
lead(expression,
其中,offset是正整数,默认为1.因组内第一个条记录没有之前的行,最后一行没有之后的行,它表示要取列第N行之前或者之后的值,default就是用于处理这样的信息,默认为空.它用于当之前或者之后第N行不存在时的值。
注意:
这2个函数必须指定orderBy字句.
/*Formattedon2009/11/0821:
09(FormatterPlusv4.8.8)*/
SELECTename,deptno,sal,LAG(sal)OVER(ORDERBYsal)LAG,
LEAD(sal)OVER(ORDERBYsal)LEAD
FROMscott.emp;
ENAME
DEPTNO
SAL
LAG
LEAD
SMITH
20
800
950
JAMES
30
950
800
1,100
ADAMS
20
1,100
950
1,250
WARD
30
1,250
1,100
1,250
MARTIN
30
1,250
1,250
1,300
MILLER
10
1,300
1,250
1,500
TURNER
30
1,500
1,300
1,600
ALLEN
30
1,600
1,500
2,450
CLARK
10
2,450
1,600
2,850
BLAKE
30
2,850
2,450
2,975
JONES
20
2,975
2,850
3,000
SCOTT
20
3,000
2,975
3,000
FORD
20
3,000
3,000
5,000
KING
10
5,000
3,000
/*Formattedon2009/11/0821:
11(FormatterPlusv4.8.8)*/
SELECTename,deptno,sal,LAG(sal,2,0)OVER(ORDERBYsal)LAG,
LEAD(sal,2,sal)OVER(ORDERBYsal)LEAD
FROMscott.emp;
ENAME
DEPTNO
SAL
LAG
LEAD
SMITH
20
800
0
1,100
JAMES
30
950
0
1,250
ADAMS
20
1,100
800
1,250
WARD
30
1,250
950
1,300
MARTIN
30
1,250
1,100
1,500
MILLER
10
1,300
1,250
1,600
TURNER
30
1,500
1,250
2,450
ALLEN
30
1,600
1,300
2,850
CLARK
10
2,450
1,500
2,975
BLAKE
30
2,850
1,600
3,000
JONES
20
2,975
2,450
3,000
SCOTT
20
3,000
2,850
5,000
FORD
20
3,000
2,975
3,000
KING
10
5,000
3,000
5,000
六.sum,avg,count,max函数
6.1SUM函数
/*Formattedon2009/11/0821:
25(FormatterPlusv4.8.8)*/
SELECTSUM(sal)"sum"
FROMscott.emp;
/*Formattedon2009/11/0821:
27(FormatterPlusv4.8.8)*/
SELECTename,deptno,sal,
SUM(sal)OVER(PARTITIONBYdeptnoORDERBYsalRANGEUNBOUNDEDPRECEDING)"SUM"
FROMscott.emp;
ENAME
DEPTNO
SAL
SUM
MILLER
10
1,300
1,300
CLARK
10
2,450
3,750
KING
10
5,000
8,750
SMITH
20
800
800
ADAMS
20
1,100
1,900
JONES
20
2,975
4,875
SCOTT
20
3,000
10,875
FORD
20
3,000
10,875
JAMES
30
950
950
MARTIN
30
1,250
3,450
WARD
30
1,250
3,450
TURNER
30
1,500
4,950
ALLEN
30
1,600
6,550
BLAKE
30
2,850
9,400
6.2AVG函数
/*Formattedon2009/11/0821:
29(FormatterPlusv4.8.8)*/
SELECTAVG(sal)"avg"
FROMscott.emp;
/*Formattedon2009/11/0821:
31(FormatterPlusv4.8.8)*/
SELECTename,deptno,sal,hiredate,
round((AVG(sal)OVER(PARTITIONBYdeptnoORDERBYhiredate
ROWSBETWEEN1PRECEDINGAND1FOLLOWING)),2)"c_mavg"
FROMscott.emp;
Round()取小数点后面2位
ENAMEDEPTNOSALHIREDATEc_mavg
CLARK1024501981-6-93725
KING1050001981-11-172916.67
MILLER1013001982-1-233150
SMITH208001980-12-171887.5
JONES2029751981-4-22258.33
FORD2030001981-12-32991.67
SCOTT2030001987-4-192366.67
ADAMS2011001987-5-232050
ALLEN3016001981-2-201425
WARD3012501981-2-221900
BLAKE3028501981-5-11866.67
TURNER3015001981-9-81866.67
MARTIN3012501981-9-281233.33
JAMES309501981-12-31100
6.3Count函数
/*Formattedon2009/11/0916:
50(FormatterPlusv4.8.8)*/
SELECTCOUNT(*)"Total"
FROMscott.emp;
/*Formattedon2009/11/1010:
42(FormatterPlusv4.8.8)*/
SELECTename,empno,deptno,sal,
COUNT(sal)OVER(PARTITIONBYdeptnoORDERBYsal
RANGEBETWEEN50PRECEDINGAND150FOLLOWING)
ASmov_countFROMscott.emp;
ENAME
EMPNO
DEPTNO
SAL
MOV_COUNT
MILLER
7,934
10
1,300
1
CLARK
7,782
10
2,450
1
KING
7,839
10
5,000
1
SMITH
7,369
20
800
1
ADAMS
7,876
20
1,100
1
JONES
7,566
20
2,975
3
SCOTT
7,788
20
3,000
3
FORD
7,902
20
3,000
3
JAMES
7,900
30
950
1
MARTIN
7,654
30
1,250
2
WARD
7,521
30
1,250
2
TURNER
7,844
30
1,500
2
ALLEN
7,499
30
1,600
1
BLAKE
7,698
30
2,850
1
6.4Max函数
/*Formattedon2009/11/1014:
45(FormatterPlusv4.8.8)*/
SELECTMAX(sal)MAX
FROMscott.emp;
/*Formatte
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- zgb1 函数