Oracle函数

Oracle的常见函数我有整理成表格形式，没有用法，只有函数的功能，可通过搜索该函数去网上找相应的用法。列举一些常用的，不过我会持续更新。

4.1 数值型函数
数值函数

近似函数，五舍四入：round(x,2)

向下取整函数：floor(x)

向上取整函数：ceil(x)

取随机函数：DBMS_RANDOM.VALUE(a,b)

幂运算函数：power(x,n)

平方根函数：sqrt(x)

绝对值函数：abs(x)

取余函数：mod(x,y)

4.2 字符型函数
字符串函数

截取字符串的一部分：substr(字符串, 开始位置, 连续取值的长度)

两个字符串的拼接：concat(x1, x2)

字符串替换：replace(x, old, new)

字符查找函数:instr(源字符串, 目标字符串, 起始位置, 匹配序号)

转大写字母:upper()

转小写字母:lower()

返回一个字符串的长度:length()

4.3 时间日期函数
日期函数

返回当前日期和时间：SYSDATE

从一个date类型中截取year,month,day：extract()

计算日期的月份偏移 正数时间往后推移，负数时间往前推移：add_months(x, m)

计算日期的最后一天：last_day(x)

计算两个日期之间的月份差：months_between(x1, x2)

例：

select extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

select months_between(date'2020-9-2',date'2019-9-1') from dual;

4.4 聚合分组函数
聚合函数

SUM：计算一组数值的总和。

AVG：计算一组数值的平均值。

MAX：返回一组数值中的最大值。

MIN：返回一组数值中的最小值。

COUNT：计算一组数值的个数。

4.5 开窗函数
4.5.1 用法
计算函数部分() over(partition by 列名 order by 列名 asc|desc)

--计算函数部分：sum,count,max,min,avg,row_number(),rank()等，只能是一个函数

--over() 关键字

--partition by ：分组，根据分区表达式的条件逻辑将单个结果集分成N组，不是必选项

--order by：对分区中的数据排序,不是必选项

例：

--计算工资与部门最高工资的差

select

emp.*,max(sal) over(partition by deptno) maxsal,

sal-max(sal) over(partition by deptno) c

from emp ;



4.5.2 排名函数
1.row_number()：根据某个列，按照顺序进行排序 1 2 3 4

2.rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，会跳过占用的名次 1 2 2 4

3.dense_rank()：根据某个列，按照顺序进行排序，如果值相同，会出现并列的名次，不会跳过名次 1 2 2 3

4.5.3 平移函数
在平常业务中一般用来实现数据的同比和环比的计算。

同比：今年的7月和去年的7月相比，在两个时间范围内，取相同时间节点的数据进行比较

环比：今年的7月和今年的6月相比，在同一个时间范围内，取相邻的时间节点的数据进行比较

lag()：将数据往下平移一行 + 开窗函数

lead()：将数据往上平移一行 + 开窗函数

例：

表及数据：

create table sale_info(
y number,
m number,
amount number(7,2)
);
insert into sale_info values(2018,1,2342);
insert into sale_info values(2018,2,1234);
insert into sale_info values(2018,3,3545);
insert into sale_info values(2018,4,3456);
insert into sale_info values(2018,5,2342);
insert into sale_info values(2018,6,4534);
insert into sale_info values(2018,7,3453);
insert into sale_info values(2018,8,2342);
insert into sale_info values(2018,9,4352);
insert into sale_info values(2018,10,1312);
insert into sale_info values(2018,11,3453);
insert into sale_info values(2018,12,1235);
insert into sale_info values(2019,1,3453);
insert into sale_info values(2019,2,1233);
insert into sale_info values(2019,3,3445);
insert into sale_info values(2019,4,1233);
insert into sale_info values(2019,5,1231);
insert into sale_info values(2019,6,4234);
insert into sale_info values(2019,7,1231);
insert into sale_info values(2019,8,2131);
insert into sale_info values(2019,9,1231);
insert into sale_info values(2019,10,3421);
insert into sale_info values(2019,11,1231);
insert into sale_info values(2019,12,1231);
commit;

需求1：计算2019年每一个月和2018年同月份的数据增长率。(同比)

需求2：计算2019年的每一个月，比上个月增长了百分之多少？(环比)

需求1：

select
b.*,c/lo,
round(c/lo*100,2)||'%' 增长率
from
(select
a.*,lag(amount) over(partition by m order by y) lo,
amount-lag(amount) over(partition by m order by y) c
from sale_info a) b
where y=2019;


注：这里可以看到同比增长率显示有问题，-0.08%不能显示。这是Oracle的显示问题，对于小于1的小数，小数点前面的0是不显示的。

解决方案：对该数值转为字符类型。

to_char(round(c/lo*100,2),'fm99990.0099')||'%' 增长率

需求2：

select
b.*,amount-lo c,
to_char(round((amount-lo)/lo*100,2),'fm99990.0099')||'%' 环比增长率
from
(select a.*,lag(amount) over(order by m) lo from sale_info a where y=2019) b;


当然，同环比的计算方法也可以用多表连接来处理，在方法上都不难。

4.6 转换函数
转换函数最常用的就4个：

转换成日期类型：to_date()
转换成字符串类型：to_char()
转换成数值类型：to_number()
将某种数据类型的表达式显式转换为另一种数据类型：cast(表达式 as 数据类型)
其他还有进制类等的相互转换，因也没用过，就不说明了。

4.7其他函数
其他函数

rownum：取行号函数

rowid：数据去重，表里面每一行数据，在数据库中的唯一编号，不会出现重复的数据

pivot：行列转换

case when：条件取值函数

decode：功能同case when差不多，也是条件取值

unpivot：列转行函数

nvl(列名, 如果这列为空时设置的默认值)

nvl2(列名, 如果不为空显示的内容, 如果为空显示的内容)

sign()：是用来判断数据是负数、0、正数分别返回-1、0、1


————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/weixin_63021300/article/details/132267190