当前位置： > 视频教程 > 网页制作视频教程 > 文章内容

Oracle特有的函数(oracle函数大全及举例)

http://www.itjxue.com 2023-01-25 00:03 来源:未知 点击次数:

oracle数据包含有什么函数

Oracle 函数有很多

有字符处理的函数 , 有数学处理的函数, 有日期函数, 有比较函数, 有统计(聚集)函数, 有分析函数, 有窗口函数.

全部列出来的话, 基本上是一本书了.

要查具体的函数, 还是去找找 Oracle 的文档去。

Oracle特有的函数(oracle函数大全及举例)

如何在SAS中直接使用Oracle特有函数

在使用sas进行行转列的字符串合并时，发现sas中并没有合适的方法，而oracle在10g之后包含了WMSYS.WM_CONCAT函数，可以轻松的解决这类问题：

select t.rank, t.Name from t_menu_item t;

10 CLARK

10 KING

10 MILLER

20 ADAMS

20 FORD

20 JONES

20 SCOTT

20 SMITH

30 ALLEN

30 BLAKE

30 JAMES

30 MARTIN

30 TURNER

30 WARD

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

我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成行转列的效果

select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

DEPTNO ENAME

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

10 CLARK, KING, MILLER

20 ADAMS, FORD, JONES, SCOTT, SMITH

30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

例子如下：

SQL create table idtable (id number,name varchar2(30));

Table created

SQL insert into idtable values(10,'ab');

1 row inserted

SQL insert into idtable values(10,'bc');

1 row inserted

SQL insert into idtable values(10,'cd');

1 row inserted

SQL insert into idtable values(20,'hi');

1 row inserted

SQL insert into idtable values(20,'ij');

1 row inserted

SQL insert into idtable values(20,'mn');

1 row inserted

SQL select * from idtable;

ID NAME

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

10 ab

10 bc

10 cd

20 hi

20 ij

20 mn

6 rows selected

SQL select id,wmsys.wm_concat(name) name from idtable

2 group by id;

ID NAME

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

10 ab,bc,cd

20 hi,ij,mn

SQL select id,wmsys.wm_concat(name) over (order by id) name from idtable;

ID NAME

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

10 ab,bc,cd

20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

ID NAME

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

10 ab

10 ab,bc

10 ab,bc,cd

20 ab,bc,cd,hi

20 ab,bc,cd,hi,ij

20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

ID NAME

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

10 ab,bc,cd

20 hi,ij,mn

6 rows selected

SQL select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

ID NAME

oracle的分析函数over(Partition by...)

Sql代码

over（Partition by…）一个超级牛皮的ORACLE特有函数

最近工作中才接触到这个功能强大而灵活的函数

oracle的分析函数over 及开窗函数

一分析函数over

Oracle从开始提供分析函数分析函数用于计算基于组的某种聚合值它和聚合函数的不同之处是对于每个组返回多行而聚合函数对于每个组只返回一行

下面通过几个例子来说明其应用

:统计某商店的营业额

date?????? sale

??????????

规则按天统计每天都统计前面几天的总额

得到的结果

DATE?? SALE?????? SUM

????? ??????? ?????????? 天

????? ??????? ?????????? 天+ 天

????? ??????? ?????????? 天+ 天+ 天

????? ??????? ???????????

:统计各班成绩第一名的同学信息

NAME?? CLASS S

fda??? ?????

ffd??? ?????

dss??? ?????

cfe??? ?????

gds??? ?????

gf???? ?????

ddd??? ?????

adf??? ?????

asdf?? ?????

dd??? ?????

通过

select * from

（

select name class s rank（）over（partition by class order by s desc） mm from t

）

where mm=

得到结果

NAME?? CLASS S?????????????????????? MM

dss??? ????? ?????????????????????

gds??? ????? ?????????????????????

gf???? ????? ?????????????????????

ddd??? ????? ?????????????????????

注意

在求第一名成绩的时候不能用row_number（）因为如果同班有两个并列第一 row_number（）只返回一个结果

rank（）和dense_rank（）的区别是

rank（）是跳跃排序有两个第二名时接下来就是第四名

dense_rank（）l是连续排序有两个第二名时仍然跟着第三名

分类统计（并显示信息）

A?? B?? C

m?? a??

n?? a??

m?? a??

n?? b??

x?? b??

h?? b??

select a c sum（c）over（partition by a） from t

得到结果

A?? B?? C??????? SUM（C）OVER（PARTITIONBYA）

h?? b?? ???????

m?? a?? ???????

n?? a?? ???????

n?? b?? ???????

x?? b?? ???????

如果用sum group by 则只能得到

A?? SUM（C）

h??

m??

n??

x??

无法得到B列值

=====

select * from test

数据

A B C

将B栏位值相同的对应的C 栏位值加总

select a b c SUM（C） OVER （PARTITION BY B） C_Sum

from test

A B C C_SUM

如果不需要已某个栏位的值分割那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a b c SUM（C） OVER （PARTITION BY null） C_Sum

from test

A B C C_SUM

求个人工资占部门工资的百分比

SQL select * from salary;

NAME DEPT SAL

SQL select name dept sal sal* /sum（sal） over（partition by dept） percent from salary;

NAME DEPT SAL PERCENT

二开窗函数

开窗函数指定了分析函数工作的数据窗口大小这个数据窗口大小可能会随着行的变化而变化举例如下

over（order by salary）按照salary排序进行累计 order by是个默认的开窗函数

over（partition by deptno）按照部门分区

over（order by salary range beeen preceding and following）

每行对应的数据窗口是之前行幅度值不超过之后行幅度值不超过

例如对于以下列

sum（aa）over（order by aa range beeen preceding and following）

得出的结果是

AA?????????????????????? SUM

??????????????????????

对于aa= 来说 sum= + + + + + = ???? ;

又如对于aa= =aa= + 只有一个数所以sum= ??? ;

:其它

over（order by salary rows beeen preceding and following）

每行对应的数据窗口是之前行之后行

:下面三条语句等效

over（order by salary rows beeen unbounded preceding and unbounded following）

每行对应的数据窗口是从第一行到最后一行等效

over（order by salary range beeen unbounded preceding and unbounded following）

等效

over（partition by null）

常用的分析函数如下所列

row_number（） over（partition by … order by …）

rank（） over（partition by … order by …）

dense_rank（） over（partition by … order by …）

count（） over（partition by … order by …）

max（） over（partition by … order by …）

min（） over（partition by … order by …）

sum（） over（partition by … order by …）

avg（） over（partition by … order by …）

first_value（） over（partition by … order by …）

last_value（） over（partition by … order by …）

lag（） over（partition by … order by …）

lead（） over（partition by … order by …）

示例

SQL select type qty from test;

TYPE QTY

SQL select type qty to_char（row_number（） over（partition by type order by qty））|| / ||to_char（count（*） over（partition by type）） as cnt from test;

TYPE QTY CNT

SQL select * from test;

SQL select t id mc to_char（b rn）|| / ||t id）e

from test t

（select rownum rn from （select max（to_number（id）） mid from test） connect by rownum =mid ））L

where b rn=to_number（t id）

order by id

ID MC TO_CHAR（B RN）|| / ||T ID

/ /

/ CNOUG /

rows selected

*******************************************************************

关于partition by

这些都是分析函数好像是以后才有的 row_number（）和rownum差不多功能更强一点（可以在各个分组内从开时排序） rank（）是跳跃排序有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank（）l是连续排序有两个第二名时仍然跟着第三名相比之下row_number是没有重复值的 lag（arg arg arg ） arg 是从其他行返回的表达式 arg 是希望检索的当前行分区的偏移量是一个正的偏移量时一个往回检索以前的行的数目 arg 是在arg 表示的数目超出了分组的范围时返回的值

select deptno row_number（） over（partition by deptno order by sal） from emp order by deptno;

select deptno rank（） over （partition by deptno order by sal） from emp order by deptno;

select deptno dense_rank（） over（partition by deptno order by sal） from emp order by deptno;

select deptno ename sal lag（ename null） over（partition by deptno order by ename） from emp ord er by deptno;

select deptno ename sal lag（ename example ） over（partition by deptno order by ename） from em p

order by deptno;

select deptno sal sum（sal） over（partition by deptno） from emp; 每行记录后都有总计值? select deptno sum（sal） from emp group by deptno;

求每个部门的平均工资以及每个人与所在部门的工资差额

select deptno ename sal

round（avg（sal） over（partition by deptno）） as dept_avg_sal

round（sal avg（sal） over（partition by deptno）） as dept_sal_diff

lishixinzhi/Article/program/Oracle/201311/18056

(责任编辑：IT教学网)

复制链接发给好友收藏本文关闭此页

上一篇：btbowl的简单介绍

下一篇：动态表情图(微信动态表情图)

Oracle特有的函数(oracle函数大全及举例)

oracle数据包含有什么函数

如何在SAS中直接使用Oracle特有函数

oracle的分析函数over(Partition by...)

(责任编辑：IT教学网)

相关网页制作视频教程文章

阅读排行

专题教程

推荐网页制作视频教程文章

最新更新网页制作视频教程