Oracle分析函数 sql分析函数

http://www.huomo.cn/database/article-377.html

Oracle 分析函数

更新: 2010-07-02字体: 【大 中 小】点击: 32

分析函数是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( [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ])

分区子句类似于聚组函数所需要的group by,排序子句可看成是SQL语句中的orderby,只不过在此语句中还可指定null值排前(nulls first)还是排后(nulls last)。

开窗函数指定了分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变化而变化,举例如下:
over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
over(partition by deptno)按照部门分区
over(order by salary range between 50 preceding and 150following)
每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150
over(order by salary rows between 50 preceding and 150following)
每行对应的数据窗口是之前50行,之后150行
over(order by salary rows between unbounded preceding and unboundedfollowing)
每行对应的数据窗口是从第一行到最后一行,等效:
over(order by salary range between unbounded preceding andunbounded following)

二.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') Second from dual;

FRISTSECOND

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

This is notnullMy Oracle

三. 自动汇总函数rollup,cube

Rollup:表示的意思是:除了分组的功能外,还进行累加的的,多了一个汇总。

如果是 GROUP BY ROLLUP(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 操作。

示例:

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;

SELECT student_name,subjects, SUM (score)

FROMstudentscore

GROUP BY CUBE (student_name, subjects)

ORDER BY 1;

等同于以下标准 SQL

SELECT NULL, subjects, SUM(score)

FROMstudentscore

GROUP BY subjects

UNION

SELECT student_name, NULL,SUM (score)

FROMstudentscore

GROUP BY student_name

UNION

SELECT NULL, NULL, SUM (score)

FROM studentscore

UNION

SELECT student_name,subjects, SUM (score)

FROMstudentscore

GROUP BY student_name, subjects

SELECT student_name,subjects, SUM (score)

FROMstudentscore

GROUP BY ROLLUP (student_name, subjects);

SELECT student_name, NULL,SUM (score)

FROMstudentscore

GROUP BY student_name

UNION

SELECT NULL, NULL, SUM (score)

FROM studentscore

UNION

SELECT student_name,subjects, SUM (score)

FROMstudentscore

GROUP BY student_name, subjects

SELECT GROUPING(student_name), GROUPING (subjects), student_name, subjects,

SUM (score)

FROMstudentscore

GROUP BY CUBE (student_name, subjects)

ORDER BY 1, 2;

SELECT GROUPING(student_name), GROUPING (subjects), student_name, subjects,

SUM (score)

FROMstudentscore

GROUP BY ROLLUP (student_name, subjects)

ORDER BY 1, 2;

SELECT GROUPING_ID(student_name, subjects), student_name, subjects,

SUM (score)

FROMstudentscore

GROUP BY CUBE (student_name, subjects)

ORDER BY 1;

SELECT GROUPING_ID(student_name, subjects), student_name, subjects,

SUM (score)

FROMstudentscore

GROUP BY ROLLUP (student_name, subjects)

ORDER BY 1;

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)

FROMstudentscore

GROUP BY CUBE (student_name, subjects)

ORDER BY 1, 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函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

示例:

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

五. lag,lead函数

lag(expression,<offset>,<default>)函数可以访问组内当前行之前的行,
lead(expression,<offset>,<default>)函数则正相反,可以反问组内当前行之后的行.
其中,offset是正整数,默认为1.因组内第一个条记录没有之前的行,最后一行没有之后的行,它表示要取列第N行之前或者之后的值,default就是用于处理这样的信息,默认为空.它用于当之前或者之后第N行不存在时的值。

注意:这2个函数必须指定 order By 字句.

SELECT ename, deptno, sal, LAG (sal) OVER (ORDER BY sal)LAG,

LEAD (sal) OVER (ORDER BY sal) LEAD

FROM scott.emp;

ENAME
Oracle分析函数 sql分析函数
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

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

六. sum,avg, count,max 函数

6.1 SUM 函数

SELECT SUM (sal) "sum"

FROM scott.emp;

SELECT ename, deptno, sal,

SUM (sal) OVER (PARTITION BY deptno ORDER BY sal RANGE UNBOUNDEDPRECEDING) "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

6.2 AVG 函数

SELECT AVG (sal) "avg"

FROM scott.emp;

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 102450 1981-6-93725

KING 105000 1981-11-17 2916.67

MILLER 1013001982-1-23 3150

SMITH 20800 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 309501981-12-3 1100

6.3 Count 函数

SELECT COUNT (*) "Total"

FROM scott.emp;

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

6.4 Max 函数

SELECT MAX (sal) MAX

FROM scott.emp;

SELECT ename, sal, deptno, MAX (sal) OVER (PARTITION BY deptno)AS MAX

FROM scott.emp;

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

七. 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)

SELECT ENAME, SAL, DEPTNO, RATIO_TO_REPORT (SAL) OVER () ASRR

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

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

八. First,Last,First_value,Last_value取基数的分析函数

First功能描述:从DENSE_RANK返回的集合中取出排在最前面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
Last功能描述:从DENSE_RANK返回的集合中取出排在最后面的一个值的行(可能多行,因为值可能相等),因此完整的语法需要在开始处加上一个集合函数以从中取出记录。

FIRST_VALUE、LAST_VALUE:返回结果集中排在第一位和最后一位的值。

语法是:FIRST_VALUE (expr) OVER ( analytic_clause)

SELECT ENAME, DEPTNO, SAL,

MIN (SAL)KEEP (DENSE_RANK FIRST ORDER BY DEPTNO) OVER (PARTITION BYDEPTNO) "Worst",

MAX (SAL)KEEP (DENSE_RANK LAST ORDER BY DEPTNO) OVER (PARTITION BYDEPTNO) "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

SELECT ENAME, DEPTNO, SAL,

FIRST_VALUE (ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL ASC) ASLOWEST_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

九. Greatest, Least 函数

Greatest函数:取一个一维数组中的最大值。

Least函数:取一个一维数组中的最小值。

SELECT GREATEST (1, 2, 3, 4, 5, 6) MAX

FROM DUAL;

SELECT LEAST (1, 2, 3, 4, 5, 6) MIN

FROM DUAL;

十. Trunc, round, decode,substr函数

全角的数字/字母/标点符号转半角to_single_byte

10.1 Trunc 函数

Trunc实际上是truncate函数,字面意思是截断,截尾。函数的功能是将数字进行截断。tranc()并不四舍五入。

SELECT TRUNC (1234.5678, 2) rs

FROM DUAL;

----------

1234.56

SELECT TRUNC (1234.5678, 0) rs

FROM DUAL;

----------

1234

SELECT TRUNC (1234.5678, -2) rs

FROM DUAL;

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

1200

10.2 Round 函数

Round 函数: 返回按指定位数进行四舍五入的数值。

语法: Round(expression[, numdecimalplaces])

expression 必选。 数值表达式 被四舍五入。

numdecimalplaces 可选。数字表明小数点右边有多少位进行四舍五入。如果省略,则 Round 函数返回整数。

SELECT ROUND (12.45, 1) rs

FROM DUAL;

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

12.5

10.3 Decode 函数

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算,值1,值2,值3)
SELECTDECODE (value, <if this value>,<return this value>) FROM table
这个函数运行的结果是,当字段或字段的运算的值等于值1时,该函数返回值2,否则返回值3
当然值1,值2,值3也可以是表达式,这个函数使得某些sql语句简单了许多

SELECT DECODE (NAME, 'Dave', 'I Love BL', 'NoLove') rs

FROM (SELECT 'Dave' NAME

FROM DUAL);

---------

I Love BL

SELECT DECODE (NAME, 'BL', 'I love Dave', 'NoLove') rs

FROM (SELECT 'Dave' NAME

FROM DUAL);

------

NoLove

10.4 SubStr 函数

SUBSTR(string,start,count) : 取子字符串,从start开始,取count个。

SELECT SUBSTR ('I Love You', 3, 4) rs

FROM DUAL;

----

Love

  

爱华网本文地址 » http://www.413yy.cn/a/25101010/39734.html

更多阅读

oracle decode函数使用方法 oracle 分区使用函数

oracle decode函数使用方法——简介decode()函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能。oracle decode函数使用方法——工具/原料·软件环境:  1、Wi

初学者怎样学习编程?

对于刚刚接触编程的人来说,怎样学习编程?学习编程要注意哪些方面?学习编程要从哪些方面着手才能更快进入编程世界?怎样做才是掌握了编程的要义呢?这一系列的问题都是一名编程初学者最关心的问题,今天,笔者从7个方面分析如何学习编程。初学

Oracle中的NVL函数 oracle decode函数

Oracle中函数以前介绍的字符串处理,日期函数,数学函数,以及转换函数等等,还有一类函数是通用函数。主要有:NVL,NVL2,NULLIF,COALESCE,这几个函数用在各个类型上都可以。下面简单介绍一下几个函数的用法。在介绍

matlab函数_连通区域转载 matlab 连通区域分析

matlab函数_连通区域1、 matlab函数bwareaopen──删除小面积对象格式:BW2 = bwareaopen(BW,P,conn)作用:删除二值图像BW中面积小于P的对象,默认情况下使用8邻域。算法:(1)Determine the connected components. L = bwlabeln(BW, conn)

Datedif函数全面解析及BUG分析 datedif函数在哪里

以前发在EHblog的老博文,现在关闭了,准备陆陆续续搬迁一点过来。时间久了,有些文章也不一定保值了。DATEDIF函数是一个隐藏的日期函数,继承于Lotus1-2-3。这个函数用于计算两个时间点之间的间隔,并且可以以“日”、“月”、“年”为单位

声明:《Oracle分析函数 sql分析函数》为网友二十四桥夜月明分享!如侵犯到您的合法权益请联系我们删除