Oracle常用函数【建议收藏】

2401_84814254

于 2024-05-16 21:14:15 发布

阅读量400

点赞数 5

分类专栏：程序员文章标签： oracle 数据库

本文链接：https://blog.csdn.net/2401_84814254/article/details/138975324

版权

程序员专栏收录该内容

116 篇文章 0 订阅

订阅专栏

s2 要替换的字符串

SCOTT@ORCL> select replace(‘he love you’,‘he’,‘I’) string from dual;

STRING

I love you

ABS：返回指定值的绝对值

CEIL：返回大于或等于给出数字的最小整数

SCOTT@ORCL> select ceil(3.1415927) from dual;

CEIL(3.1415927)

FLOOR:对给定的数字取整数

SCOTT@ORCL> select floor(2345.67) from dual;

FLOOR(2345.67)

2345

MOD(n1,n2)

返回一个n1除以n2的余数

SCOTT@ORCL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3) MOD(3,3) MOD(2,3)

1 0 2

ROUND和TRUNC

按照指定的精度进行舍入

round(55.5) = 56

round(55.4) = 55

trunc(sysdate,‘yyyy’) --返回当年第一天

trunc(sysdate,‘mm’) --返回当月第一天

trunc(sysdate,‘day’) --返回当前星期的第一天（周日）

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89 (即取整)

TRUNC（89.985，-1）=80

日期函数

SQL> select to_char(add_months(to_date(‘202012’,‘yyyymm’),2),‘yyyymm’) datum from dual; --增加或减去月份

DATUM

202102

SQL> select last_day(sysdate) datum from dual; --返回本月的最后一天

DATUM

2020/12/31

SQL> select months_between(to_date(‘20000520’,‘yyyymmdd’),to_date(‘20050520’,‘yyyymmdd’)) mon_betw from dual; --月份差异

MON_BETW

-60

SQL> select next_day( sysdate, ‘MONDAY’) from dual; --当前日期的下个星期一

NEXT_DAY(SYSDATE,‘MONDAY’)

2020/12/21 6:52:42

GREATEST：返回一组表达式中的最大值,即比较字符的编码大小

LEAST：返回一组表达式中的最小值

STDDEV(distinct|all)

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)

1182.503223

pl/sql中的case语句

select (case when DUMMY=‘X’ then 0 else 1 end) as flag from dual;

1.case的第1种用法：

case col when ‘a’ then 1

when ‘b’ then 2

else 0 end

这种用法跟decode一样没什么区别

2.case的第2种用法：

case when score <60 then ‘d’

when score >=60 and score <70 then ‘c’

when score >=70 and score <80 then ‘b’

else ‘a’ end

NVL(expr1, expr2)

expr1为NULL，返回expr2，不为NULL，返回expr1，注意两者的类型要一致

NVL2 (expr1, expr2, expr3)

expr1不为NULL，返回expr2,为NULL,返回expr3,expr2和expr3类型不同的话，expr3会转换为expr2的类型

NULLIF (expr1, expr2)

相等返回NULL，不等返回expr1

to_char 中fm

有9的地方如果有数字就显示如果没有数字就不显示，有0的地方在没有数字的时候也会有０来占位

select to_char(9999.09556,‘fm99999.0900’),to_char(9999.09556,‘fm00099.0900’) from dual；

分析函数

分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行,常用的分析函数如下所列:

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 …)

二、分析函数

2.1 rank（等级函数，也指示排名函数）

ROW_NUMBER()

定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，

每一条数据加一个序号，一般多用于分页查询，

比如查询前10个查询10-100个学生。

实例：

对学生成绩排序

在这里插入图片描述

这里number就是每个学生的序号根据studentScore(分数)进行desc倒序

获取第二个同学的成绩信息

在这里插入图片描述

这里用到的思想就是分页查询的思想在原sql外再套一层select

where t.number>=1 and t.number<=10 是不是就是获取前十个学生的成绩信息纳。

RANK()

定义：RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，这里为什么和ROW_NUMBER()不一样那，ROW_NUMBER()是排序，

当存在相同成绩的学生时，ROW_NUMBER()会依次进行排序，他们序号不相同，而Rank()不一样出现相同的，他们的排名是一样的，看下面看例子:

在这里插入图片描述

注：当出现两个学生成绩相同是里面出现变化。

RANK()是 1 2 2，而ROW_NUMBER()则还是1 2 3，这就是RANK()和ROW_NUMBER()的区别了

DENSE_RANK()

定义：DENSE_RANK()函数也是排名函数，和RANK()功能相似，也是对字段进行排名，那它和RANK()到底有什么不同那？看例子：

在这里插入图片描述

DENSE_RANK()密集的排名他和RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃的排名，所以一般情况下用的排名函数就DENSE_RANK()

NTILE()

定义：NTILE()函数是将有序分区中的行分发到指定数目的组中，就是按序号分组的意思，各个组有编号，编号从1开始，就像我们说的分区一样，分为几个区，一个区会有多少个。

在这里插入图片描述

这里查询了3次,第一次分为1个区,所以查询结果number全是1，

第二次分为2个区，查询结果为 1 1 2，意思就是第一个区为 1 1 （两个编号的数据），第二个区只有2这个数据。

总结：

select row_number() over(order by a.deptno desc) rak,a.* from emp a --排序，用于分页查询

select rank() over(order by a.deptno desc) rak,a.* from emp a --跳跃的排名

select dense_rank() over(order by a.deptno desc) rak,a.* from emp a --排名连续

select NTILE(4) over(order by a.deptno desc) rak,a.* from emp a --分几个组

2.2 windowing函数

Order By 子句

select ename,sal,avg(sal) over() from emp; --所有值的平均值放在每行

在这里插入图片描述

select ename,sal,avg(sal) over(order by ename) from emp; --累积平均

在这里插入图片描述

注：在没有Order by子句时，在全部组上计算平均值，每一行给一个同样的值。

在用带有Order by的AVG（）时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数）

select ename,

deptno,

sum(sal) over(order by ename, deptno) sum_ename_deptno, --但它仅仅在分区内对行进行排序，逻辑计算

sum(sal) over(order by deptno, ename) sum_deptno_ename

from emp

order by ename, deptno --只是排序，不影响逻辑计算

在这里插入图片描述

ROW 窗口

注：Windowing子句给出了一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作，

在一组内基于任意变化或固定的窗口中，可以用该子句来让分析函数计算它的值，

ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的列子作为ROW分区

select deptno,ename,sal,

sum(sal) over(

partition by deptno

order by ename

rows 2 preceding

)sliding_total

from emp

order by deptno,ename

这将在一组内创建一个变化的窗口，并计算那一组中当前行的SAL列加上前两行SAL列的总和，请注意，要使用窗口，必须使用ORDER BY 子句

Range 窗口

select ename,

hiredate,

sal,

avg(sal) over(order by hiredate asc range 100 preceding) avg_sal_100_days_before,

avg(sal) over(order by hiredate desc range 100 preceding) avg_sal_100_days_after

from emp

order by hiredate asc

如果有“range 5 preceding”，将产生一个滑动的窗口，它在组中拥有所有当前行以前5行的集合

range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内

rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）

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

over（order by salary rows between 50 preceding and 150 following） --每行对应的数据窗口是之前50行，之后150行

FIRST_VALUE：返回组中的第一个值

LAST_VALUE：返回组中的最后一个值

LAG：上一行

LEAD：下一行

KEEP函数

功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录

SELECT last_name, department_id, salary,

MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)

OVER (PARTITION BY department_id) “Worst”,

MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)

OVER (PARTITION BY department_id) “Best”

FROM employees

WHERE department_id in (20,80)

ORDER BY department_id, salary;

最后

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，不论你是刚入门Java开发的新手，还是希望在技术上不断提升的资深开发者，这些资料都将为你打开新的学习之门！

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！
多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。**

[外链图片转存中…(img-4tzKwpHG-1715865237615)]

[外链图片转存中…(img-nKzi2tgj-1715865237615)]

[外链图片转存中…(img-2XtwuGBG-1715865237616)]

如果你觉得这些内容对你有帮助，需要这份全套学习资料的朋友可以戳我获取！！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

2401_84814254

关注

5
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Oracle常用函数【建议收藏】

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
复制链接

扫一扫