Oracle 分析函数（Analytic Functions） 说明

69a773g988

　　一.  Analytic Functions 说明
分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式.
 
官方对分析函数的说明如下：
Analytic functions compute an aggregate value based on a group of rows. They differ fromaggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates.
 
From：Analytic Functions
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174
 
分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。
聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。
 
Oracle 聚合函数（Aggregate Functions）说明
http://blog.csdn.net/tianlesoftware/article/details/7057249
 
分析函数的over()部分的分析字句有3部分构成，分区语句，排序语句和窗口语句。
        （1）分区语句（partition by）：将查询结果分为不同的组，功能类似于group by语句,是分析函数工作的基础。默认是将所有结果作为一个分组。
        （2）排序语句(order  by)：将每个分区进行排序。
        （3）窗口语句：定义当前窗口，具体说是对每一个分组，按照给定的排序规则排序后，从分组的顶部到底部依次迭代，每次针对当前的行可以定义一个包含若干行的窗口。如果省略了窗口语句，默认使用从分组第一行到当前行的分组。
 
Analyticfunctions are commonly used in data warehousing environments. In the list ofanalytic functions that follows, functions followed by an asterisk (*) allowthe full syntax, including the windowing_clause.
--分析函数通常在数据仓库环境下使用，下表列出了所有的分析函数，其中加星号的支持全部语法，包括开窗选项。

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MIN *
NTH_VALUE*
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
 
 
二． Analytic Functions 使用示例
下面主要介绍一下以下几个函数的使用方法
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函数
 
2.1 Over() 开窗函数
Over() 开窗函数是Oracle的分析函数，其语法如下：
函数名([ 参数]) over( [ 分区子句 ] [ 排序子句[ 滑动窗口子句 ] ])

分区子句类似于聚组函数所需要的group by，排序子句可看成是SQL语句中的orderby，只不过在此语句中还可指定null值排前（nullsfirst）还是排后（nulls last）。
 
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary）按照salary排序进行累计，orderby是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unboundedpreceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unboundedpreceding and unbounded following）
 
2.2 Nvl() 函数
NVL（EXP1,EXP2），函数返回exp1和exp2中第一个不为空的值。
 
如果exp1为空则返回exp2，否则返回exp1。
 
注意：如果exp1不是字符串，那么返回的数据类型和exp1的数据类型相同，否则返回的数据类型为varchar2型。
 
SQL> select nvl('This is not null',7) Frist, nvl(null, 'My Oracle')  Secondfrom dual;
 FRIST            SECOND
------------------     -------------
This is not null     My Oracle
 
2.3  自动汇总函数rollup,cube
Rollup:表示的意思是：除了分组的功能外，还进行累加的的，多了一个汇总。
如果是 GROUP BYROLLUP(A, B, C) 的话， GROUP BY 顺序
(A 、B 、 C)
(A 、B)
(A)
最后对全表进行 GROUP BY 操作。
 
Cube提供了按照多字段汇总的功能。
如果是 GROUP BY CUBE(A,B, C) ， GROUP BY 顺序
(A 、B 、 C)
(A 、B)
(A 、C)
(A) ，
(B 、C)
(B)
(C) ，
最后对全表进行 GROUP BY 操作。
 
示例：
 
view plain

• CREATE TABLE studentscore  
  
• (  
  
•  student_name varchar2(20),  
  
•  subjects varchar2(20),  
  
•  score number  
  
• )  
  
•    
  
•    
  
• INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);  
  
• INSERT INTO studentscore VALUES('WBQ','MATHS',95);  
  
• INSERT INTO studentscore VALUES('WBQ','CHINESE',88);  
  
• INSERT INTO studentscore VALUES('CZH','ENGLISH',80);  
  
• INSERT INTO studentscore VALUES('CZH','MATHS',90);  
  
• INSERT INTO studentscore VALUES('CZH','HISTORY',92);  
  
• INSERT INTO studentscore VALUES('CB','POLITICS',70);  
  
• INSERT INTO studentscore VALUES('CB','HISTORY',75);  
  
• INSERT INTO studentscore VALUES('LDH','POLITICS',80);  
  
• INSERT INTO studentscore VALUES('LDH','CHINESE',90);  
  
• INSERT INTO studentscore VALUES('LDH','HISTORY',95);  
  
•    
  
• select * from studentscore;  
  
•    
  
• /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */  
  
• SELECT  student_name, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY CUBE (student_name, subjects)  
  
• ORDER BY 1;  
  
•    
  
• <span style="color:#ff0000;">等同于以下标准 SQL</span>  
  
•    
  
• /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */  
  
• SELECT  NULL, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY subjects  
  
• UNION  
  
• SELECT  student_name, NULL, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY student_name  
  
• UNION  
  
• SELECT NULL, NULL, SUM (score)  
  
•  FROM studentscore  
  
• UNION  
  
• SELECT  student_name, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY student_name, subjects  
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */  
  
• SELECT  student_name, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY ROLLUP (student_name, subjects);  
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */  
  
• SELECT  student_name, NULL, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY student_name  
  
• UNION  
  
• SELECT NULL, NULL, SUM (score)  
  
•  FROM studentscore  
  
• UNION  
  
• SELECT  student_name, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY student_name, subjects  
  
•    
  
•    
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:35 (Formatter Plus v4.8.8) */  
  
• SELECT  GROUPING (student_name), GROUPING (subjects), student_name, subjects,  
  
•          SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY CUBE (student_name, subjects)  
  
• ORDER BY 1, 2;  
  
•    
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
  
• SELECT  GROUPING (student_name), GROUPING (subjects), student_name, subjects,  
  
•          SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY ROLLUP (student_name, subjects)  
  
• ORDER BY 1, 2;  
  
•    
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
  
• SELECT  GROUPING_ID (student_name, subjects), student_name, subjects,  
  
•          SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY CUBE (student_name, subjects)  
  
• ORDER BY 1;  
  
•    
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
  
• SELECT  GROUPING_ID (student_name, subjects), student_name, subjects,  
  
•          SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY ROLLUP (student_name, subjects)  
  
• ORDER BY 1;  
  
•    
  
•    
  
• /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
  
• SELECT  GROUPING (student_name), GROUPING (subjects),  
  
•          CASE  
  
•             WHEN GROUPING (student_name) = 0  
  
•                  AND GROUPING (subjects) = 1  
  
•                THEN ' 学生成绩合计 '  
  
•             WHEN GROUPING (student_name) = 1  
  
•                  AND GROUPING (subjects) = 0  
  
•                THEN ' 课目成绩合计 '  
  
•             WHEN GROUPING (student_name) = 1  
  
•                  AND GROUPING (subjects) = 1  
  
•                THEN ' 总                 计 '  
  
•             ELSE ''  
  
•          END summary,  
  
•          student_name, subjects, SUM (score)  
  
•     FROM studentscore  
  
• GROUP BY CUBE (student_name, subjects)  
  
• ORDER BY 1, 2;  
  

 
2.4． rank, dense_rank,row_number函数
Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
 （1） ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
 （2）DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
 （3） RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
 
示例：

view plain

• /* Formatted on 2009/11/08 20:48 (Formatter Plusv4.8.8) */  
  
• SELECT ename, deptno, sal,  
  
•        RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK",  
  
•        DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) "DENSE_RANK",  
  
•        ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) "ROW_NUMBER"  
  
•  FROM scott.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

 
2.5 lag,lead函数
lag(expression,<offset>,<default>)  函数可以访问组内当前行之前的行，
lead(expression,<offset>,<default>) 函数则正相反，可以反问组内当前行之后的行．
其中，offset是正整数，默认为1．因组内第一个条记录没有之前的行，最后一行没有之后的行，它表示要取列第N行之前或者之后的值，default就是用于处理这样的信息，默认为空．它用于当之前或者之后第N行不存在时的值。
 
注意：这2个函数必须指定 order By 字句．
 
view plain

• /* Formattedon 2009/11/08 21:09 (Formatter Plus v4.8.8) */  
  
• SELECT ename, deptno, sal, LAG (sal) OVER (ORDER BY sal) LAG,  
  
•        LEAD (sal) OVER (ORDER BY sal) LEAD  
  
•   FROM scott.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

 

view plain

• /* Formattedon 2009/11/08 21:11 (Formatter Plus v4.8.8) */  
  
• SELECT ename, deptno, sal, LAG (sal, 2, 0) OVER (ORDER BY sal) LAG,  
  
•        LEAD (sal, 2, sal) OVER (ORDER BY sal) LEAD  
  
•   FROM scott.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

 
 
2.6  sum,avg, count，max 函数
2.6.1  SUM 函数
 

view plain

• /* Formattedon 2009/11/08 21:25 (Formatter Plus v4.8.8) */  
  
• SELECT SUM (sal) "sum"  
  
•   FROM scott.emp;  
  
•   
  
• /* Formattedon 2009/11/08 21:27 (Formatter Plus v4.8.8) */  
  
• SELECT ename, deptno, sal,  
  
•        SUM (sal) OVER (PARTITION BY deptno ORDER BY sal RANGE UNBOUNDED PRECEDING) "SUM"  
  
•   FROM scott.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

 
2.6.2  AVG 函数
 
view plain

• /* Formattedon 2009/11/08 21:29 (Formatter Plus v4.8.8) */  
  
• SELECT AVG (sal) "avg"  
  
•   FROM scott.emp;  
  
•    
  
• /* Formattedon 2009/11/08 21:31 (Formatter Plus v4.8.8) */  
  
• SELECT ename, deptno, sal,hiredate,  
  
•        round((AVG (sal) OVER (PARTITION BY deptno ORDER BY hiredate  
  
•        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)),2) "c_mavg"  
  
•   FROM scott.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  10   1300   1982-1-23   3150
SMITH 20 800   1980-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
 
2.6.3  Count 函数

view plain

• /* Formattedon 2009/11/09 16:50 (Formatter Plus v4.8.8) */  
  
• SELECT COUNT (*) "Total"  
  
•   FROM scott.emp;  
  
•    
  
• /* Formattedon 2009/11/10 10:42 (Formatter Plus v4.8.8) */  
  
• SELECT ename, empno, deptno, sal,  
  
•        COUNT (sal) OVER (PARTITION BY deptno ORDER BY sal  
  
• RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)  
  
• AS mov_count  FROM scott.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

 
 
view plain

• </pre><p></p><h3><span style="color:#ff0000;">2.6.4 Max 函数</span></h3><p> </p><pre name="code" class="sql">/* Formattedon 2009/11/10 14:45 (Formatter Plus v4.8.8) */  
  
• SELECT MAX (sal) MAX  
  
•  FROM scott.emp;  
  
•   
  
• /* Formattedon 2009/11/10 14:46 (Formatter Plus v4.8.8) */  
  
• SELECT ename, sal, deptno, MAX (sal) OVER (PARTITION BY deptno) AS MAX  
  
•  FROM scott.emp;  
  
•    
  
• /* Formattedon 2009/11/10 14:47 (Formatter Plus v4.8.8) */  
  
• SELECT ename, sal, deptno  
  
•  FROM (SELECT ename, sal, deptno, MAX (sal) OVER (PARTITION BY deptno) sal_max  
  
•           FROM scott.emp)  
  
•  WHERE sal = sal_max;  
  

 
 

ENAME	SAL	DEPTNO
KING	5,000	10
FORD	3,000	20
SCOTT	3,000	20
BLAKE	2,850	30

 
 
2.7．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)
 

view plain

• /* FORMATTEDON 2009/11/10 20:24 (FORMATTER PLUS V4.8.8) */  
  
• SELECT ENAME, SAL, DEPTNO, RATIO_TO_REPORT (SAL) OVER () AS RR  
  
•  FROM SCOTT.EMP  
  
•  WHERE DEPTNO = 10;  
  

 
 

ENAME	SAL	DEPTNO	RR
CLARK	2,450	10	0.28
KING	5,000	10	0.571428571428571
MILLER	1,300	10	0.148571428571429

 

view plain

• /* FORMATTEDON 2009/11/10 20:36 (FORMATTER PLUS V4.8.8) */  
  
• SELECT ENAME, SAL, DEPTNO,  
  
•        RATIO_TO_REPORT (SAL) OVER (PARTITION BY DEPTNO) AREA_PCT  
  
•  FROM SCOTT.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

 
 
2.8  First,Last，First_value，Last_value取基数的分析函数

    First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
    Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
     FIRST_VALUE、LAST_VALUE：返回结果集中排在第一位和最后一位的值。
     语法是：FIRST_VALUE (expr) OVER ( analytic_clause)



view plain

• /* FORMATTEDON 2009/11/10 20:51 (FORMATTER PLUS V4.8.8) */  
  
• SELECT ENAME, DEPTNO, SAL,  
  
•        MIN (SAL)KEEP (DENSE_RANK FIRST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO)  "Worst",  
  
•        MAX (SAL)KEEP (DENSE_RANK LAST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO) "Best"  
  
•   FROM SCOTT.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

 


view plain

• /* Formatted on 2009/11/10 20:55 (Formatter Plusv4.8.8) */  
  
• SELECT ENAME, DEPTNO, SAL,  
  
•        FIRST_VALUE (ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL ASC) AS LOWEST_SAL  
  
•   FROM SCOTT.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

 
2.9 Greatest， Least 函数
Greatest函数：取一个一维数组中的最大值。
Least函数：取一个一维数组中的最小值。

view plain

• /* Formattedon 2009/11/10 20:59 (Formatter Plus v4.8.8) */  
  
• SELECT GREATEST (1, 2, 3, 4, 5, 6) MAX  
  
•  FROM DUAL;  
  
•    
  
• /* Formattedon 2009/11/10 20:59 (Formatter Plus v4.8.8) */  
  
• SELECT LEAST (1, 2, 3, 4, 5, 6) MIN  
  
•   FROM DUAL;  
  

 
2.10  Trunc, round,decode，substr函数
全角的数字/字母/标点符号转半角to_single_byte
2.10.1 Trunc 函数
Trunc实际上是truncate函数，字面意思是截断，截尾。函数的功能是将数字进行截断。tranc()并不四舍五入。
 
view plain

• /* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */  
  
• SELECT TRUNC (1234.5678, 2) rs  
  
•   FROM DUAL;  
  
•    
  
•         RS  
  
• ----------  
  
•    1234.56  
  
•    
  
• /* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */  
  
• SELECT TRUNC (1234.5678, 0) rs  
  
•   FROM DUAL;  
  
•    
  
•          RS  
  
• ----------  
  
•       1234  
  
•    
  
• /* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */  
  
• SELECT TRUNC (1234.5678, -2) rs  
  
•   FROM DUAL;  
  
•        RS  
  
• ------------  
  
•      1200  
  

 
2.10.2 Round 函数
 
Round 函数: 返回按指定位数进行四舍五入的数值。
语法: Round(expression[,numdecimalplaces])
 
expression 必选。数值表达式 被四舍五入。
numdecimalplaces 可选。数字表明小数点右边有多少位进行四舍五入。如果省略，则 Round 函数返回整数。
 
/* Formatted on 2009/11/10 21:23 (Formatter Plus v4.8.8) */
SELECT ROUND (12.45, 1) rs
  FROM DUAL;
 
       RS
---------------
     12.5 
 
2.10.3 Decode 函数
   decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
    decode(字段或字段的运算，值1，值2，值3）
    SELECT DECODE (value, <if thisvalue>, <return this value>) FROM table
    这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
   当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
 
 
view plain

• /* Formatted on 2009/11/10 21:28 (Formatter Plus v4.8.8) */  
  
• SELECT DECODE (NAME, 'Dave', 'I Love BL', 'NoLove') rs  
  
•   FROM (SELECT 'Dave' NAME  
  
•           FROM DUAL);  
  
• RS  
  
• ---------  
  
• I Love BL  
  
•    
  
• /* Formatted on 2009/11/10 21:29 (Formatter Plus v4.8.8) */  
  
• SELECT DECODE (NAME, 'BL', 'I love Dave', 'NoLove') rs  
  
•   FROM (SELECT 'Dave' NAME  
  
•           FROM DUAL);  
  
• RS  
  
• ------  
  
• NoLove  
  

 
 
2.10.4 SubStr 函数
SUBSTR(string,start,count) ： 取子字符串,从start开始,取count个。
 
 
view plain

• /* Formatted on 2009/11/10 21:37 (Formatter Plus v4.8.8) */  
  
• SELECT SUBSTR ('I Love You', 3, 4) rs  
  
•   FROM DUAL;  
  
•    
  
• RS  
  
• ----  
  
• Love