- 浏览: 1215907 次
-
文章分类
最新评论
-
netkongjian：
欢迎加入程序员网址导航[deyi]
准备做一个技术网址导航，请大家分享自己喜欢的技术网站 -
jinshi：
漫画阅读器代码呢？
Android 值得收藏的Demo -
javaboy2010：
非常不错!
老程序员从头开始学JQuery的读书笔记 -
hzfeibao：
很佩服这位仁兄的学习精神和良好的学习习惯，我自己学东西从来都是 ...
老程序员从头开始学JQuery的读书笔记 -
paradigm：
agree
我几年的工作心得，一个菜鸟DBA的心得
Oracle 分析函数
分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.
下面主要介绍一下以下几个函数的使用方法
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,<offset>,<default>)函数可以访问组内当前行之前的行，
lead(expression,<offset>,<default>)函数则正相反，可以反问组内当前行之后的行．
其中，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位
ENAME DEPTNO SAL HIREDATE c_mavg
CLARK 10 2450 1981-6-9 3725
KING 10 5000 1981-11-17 2916.67
MILLER 101300 1982-1-23 3150
SMITH 20 8001980-12-17 1887.5
JONES 20 2975 1981-4-2 2258.33
FORD 20 3000 1981-12-3 2991.67
SCOTT 20 3000 1987-4-19 2366.67
ADAMS 20 1100 1987-5-23 2050
ALLEN 30 1600 1981-2-20 1425
WARD 30 1250 1981-2-22 1900
BLAKE 30 2850 1981-5-1 1866.67
TURNER 30 1500 1981-9-8 1866.67
MARTIN 30 1250 1981-9-28 1233.33
JAMES 30 950 1981-12-3 1100
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;
/*Formattedon2009/11/1014:46(FormatterPlusv4.8.8)*/
SELECTename,sal,deptno,MAX(sal)OVER(PARTITIONBYdeptno)ASMAX
FROMscott.emp;
/*Formattedon2009/11/1014:47(FormatterPlusv4.8.8)*/
SELECTename,sal,deptno
FROM(SELECTename,sal,deptno,MAX(sal)OVER(PARTITIONBYdeptno)sal_max
FROMscott.emp)
WHEREsal=sal_max;
|
ENAME |
SAL |
DEPTNO |
|
KING |
5,000 |
10 |
|
FORD |
3,000 |
20 |
|
SCOTT |
3,000 |
20 |
|
BLAKE |
2,850 |
30 |
七．ratio_to_report报表处理函数
除报告详细数据外，许多报告中还包括每行总数的百分比。例如，每名客户的订单相对于总订单的百分比，或每位销售代表的销售额相对于总销售额的百分比。
传统上，Oracle计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。
分析函数RATIO_TO_REPORT用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比.这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null,则得到的结果也为0.
开窗条件query_partition_clause决定被除数的值,如果用户忽略了这个条件,则计算查询结果中所有记录的汇总值.
用户不能使用其他分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr,也就是说这个函数不能循环使用.但我们可以使用其他普通函数作为这个分析函数的查询结果.
RATIO_TO_REPORT解析函数使得这种类型的查询更容易编码。它的格式如下：
RATIO_TO_REPORT(expr)OVER(query_partition_clause)
/*FORMATTEDON2009/11/1020:24(FORMATTERPLUSV4.8.8)*/
SELECTENAME,SAL,DEPTNO,RATIO_TO_REPORT(SAL)OVER()ASRR
FROMSCOTT.EMP
WHEREDEPTNO=10;
|
ENAME |
SAL |
DEPTNO |
RR |
|
CLARK |
2,450 |
10 |
0.28 |
|
KING |
5,000 |
10 |
0.571428571428571 |
|
MILLER |
1,300 |
10 |
0.148571428571429 |
/*FORMATTEDON2009/11/1020:36(FORMATTERPLUSV4.8.8)*/
SELECTENAME,SAL,DEPTNO,
RATIO_TO_REPORT(SAL)OVER(PARTITIONBYDEPTNO)AREA_PCT
FROMSCOTT.EMP;
|
ENAME |
SAL |
DEPTNO |
AREA_PCT |
|
CLARK |
2,450.0000000000 |
10 |
0.2800000000 |
|
KING |
5,000.0000000000 |
10 |
0.5714285714 |
|
MILLER |
1,300.0000000000 |
10 |
0.1485714286 |
|
JONES |
2,975.0000000000 |
20 |
0.2735632184 |
|
FORD |
3,000.0000000000 |
20 |
0.2758620690 |
|
ADAMS |
1,100.0000000000 |
20 |
0.1011494253 |
|
SMITH |
800.0000000000 |
20 |
0.0735632184 |
|
SCOTT |
3,000.0000000000 |
20 |
0.2758620690 |
|
WARD |
1,250.0000000000 |
30 |
0.1329787234 |
|
TURNER |
1,500.0000000000 |
30 |
0.1595744681 |
|
ALLEN |
1,600.0000000000 |
30 |
0.1702127660 |
|
JAMES |
950.0000000000 |
30 |
0.1010638298 |
|
BLAKE |
2,850.0000000000 |
30 |
0.3031914894 |
|
MARTIN |
1,250.0000000000 |
30 |
0.1329787234 |
八．First,Last，First_value，Last_value取基数的分析函数
First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
FIRST_VALUE、LAST_VALUE：返回结果集中排在第一位和最后一位的值。
语法是：FIRST_VALUE(expr)OVER(analytic_clause)
/*FORMATTEDON2009/11/1020:51(FORMATTERPLUSV4.8.8)*/
SELECTENAME,DEPTNO,SAL,
MIN(SAL)KEEP(DENSE_RANKFIRSTORDERBYDEPTNO)OVER(PARTITIONBYDEPTNO)"Worst",
MAX(SAL)KEEP(DENSE_RANKLASTORDERBYDEPTNO)OVER(PARTITIONBYDEPTNO)"Best"
FROMSCOTT.EMP;
|
ENAME |
DEPTNO |
SAL |
Worst |
Best |
|
CLARK |
10 |
2,450 |
1,300 |
5,000 |
|
KING |
10 |
5,000 |
1,300 |
5,000 |
|
MILLER |
10 |
1,300 |
1,300 |
5,000 |
|
JONES |
20 |
2,975 |
800 |
3,000 |
|
FORD |
20 |
3,000 |
800 |
3,000 |
|
ADAMS |
20 |
1,100 |
800 |
3,000 |
|
SMITH |
20 |
800 |
800 |
3,000 |
|
SCOTT |
20 |
3,000 |
800 |
3,000 |
|
WARD |
30 |
1,250 |
950 |
2,850 |
|
TURNER |
30 |
1,500 |
950 |
2,850 |
|
ALLEN |
30 |
1,600 |
950 |
2,850 |
|
JAMES |
30 |
950 |
950 |
2,850 |
|
BLAKE |
30 |
2,850 |
950 |
2,850 |
|
MARTIN |
30 |
1,250 |
950 |
2,850 |
/*Formattedon2009/11/1020:55(FormatterPlusv4.8.8)*/
SELECTENAME,DEPTNO,SAL,
FIRST_VALUE(ENAME)OVER(PARTITIONBYDEPTNOORDERBYSALASC)ASLOWEST_SAL
FROMSCOTT.EMP;
|
ENAME |
DEPTNO |
SAL |
LOWEST_SAL |
|
MILLER |
10 |
1,300 |
MILLER |
|
CLARK |
10 |
2,450 |
MILLER |
|
KING |
10 |
5,000 |
MILLER |
|
SMITH |
20 |
800 |
SMITH |
|
ADAMS |
20 |
1,100 |
SMITH |
|
JONES |
20 |
2,975 |
SMITH |
|
FORD |
20 |
3,000 |
SMITH |
|
SCOTT |
20 |
3,000 |
SMITH |
|
JAMES |
30 |
950 |
JAMES |
|
MARTIN |
30 |
1,250 |
JAMES |
|
WARD |
30 |
1,250 |
JAMES |
|
TURNER |
30 |
1,500 |
JAMES |
|
ALLEN |
30 |
1,600 |
JAMES |
|
BLAKE |
30 |
2,850 |
JAMES |
九．Greatest，Least函数
Greatest函数：取一个一维数组中的最大值。
Least函数：取一个一维数组中的最小值。
/*Formattedon2009/11/1020:59(FormatterPlusv4.8.8)*/
SELECTGREATEST(1,2,3,4,5,6)MAX
FROMDUAL;
/*Formattedon2009/11/1020:59(FormatterPlusv4.8.8)*/
SELECTLEAST(1,2,3,4,5,6)MIN
FROMDUAL;
十．Trunc,round,decode，substr函数
全角的数字/字母/标点符号转半角to_single_byte
10.1Trunc函数
Trunc实际上是truncate函数，字面意思是截断，截尾。函数的功能是将数字进行截断。tranc()并不四舍五入。
/*Formattedon2009/11/1021:19(FormatterPlusv4.8.8)*/
SELECTTRUNC(1234.5678,2)rs
FROMDUAL;
RS
----------
1234.56
/*Formattedon2009/11/1021:19(FormatterPlusv4.8.8)*/
SELECTTRUNC(1234.5678,0)rs
FROMDUAL;
RS
----------
1234
/*Formattedon2009/11/1021:19(FormatterPlusv4.8.8)*/
SELECTTRUNC(1234.5678,-2)rs
FROMDUAL;
RS
------------
1200
10.2Round函数
Round函数:返回按指定位数进行四舍五入的数值。
语法:Round(expression[,numdecimalplaces])
expression必选。数值表达式被四舍五入。
numdecimalplaces可选。数字表明小数点右边有多少位进行四舍五入。如果省略，则Round函数返回整数。
/*Formattedon2009/11/1021:23(FormatterPlusv4.8.8)*/
SELECTROUND(12.45,1)rs
FROMDUAL;
RS
---------------
12.5
10.3Decode函数
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算，值1，值2，值3）
SELECTDECODE(value,<ifthisvalue>,<returnthisvalue>)FROMtable
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
/*Formattedon2009/11/1021:28(FormatterPlusv4.8.8)*/
SELECTDECODE(NAME,'Dave','ILoveBL','NoLove')rs
FROM(SELECT'Dave'NAME
FROMDUAL);
RS
---------
ILoveBL
/*Formattedon2009/11/1021:29(FormatterPlusv4.8.8)*/
SELECTDECODE(NAME,'BL','IloveDave','NoLove')rs
FROM(SELECT'Dave'NAME
FROMDUAL);
RS
------
NoLove
10.4SubStr函数
SUBSTR(string,start,count)：取子字符串,从start开始,取count个。
/*Formattedon2009/11/1021:37(FormatterPlusv4.8.8)*/
SELECTSUBSTR('ILoveYou',3,4)rs
FROMDUAL;
RS
----
Love
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：63306533; 聊天 群：40132017
发表评论
相关推荐
ORACLE 分析函数大全,包含很多关于ORACLE的分析函数，内置函数
关于ORACLE分析函数的教程，教程描述描述清晰
oracle 分析函数 开发必备 数据库开发工程师
ORACLE分析函数.pdf
oracle分析函数（用法+实例）,这属于oracle的高级应用。
Oracle分析函数.doc
Oracle分析函数，常用分析函数应有尽有
Oracle 分析函数详解 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数
文档详细介绍了oracle的分析函数，包括功能说明、sql示例等。分析函数功能强大，在报表或数据迁移的时候可能会使用到。分析函数用法看上去有点复杂，最好使用的时候，参考文档
Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结
分析函数是oracle中强大的功能,附件是分析函数学习笔记
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
oracle分析函数
Oracle分析函数，常见的基本统计函数里面都有，大家可以通过安装Oracle的示例数据库来一步一步跟进学习!
oracle分析函数及开窗函数的使用，包括over等分析函数
Oracle分析函数RANK(),ROW_NUMBER(),LAG()等的使用方法 Oracle分析函数参考手册 开窗函数(over)详解
oracle分析函数，窗口函数，报表函数 分析函数(OVER) 分析函数2(Rank, Dense_rank, row_number) 分析函数3(Top/Bottom N、First/Last、NTile)