算术函数
round(d[,n])
:
对数字进行四舍五入操作。
d:代表操作的数字,n代表保留的小数位,如果省略n则直接保留整数
ceil(d)
:
向上取整,获取比当前的数字大的最小整数
floor(d)
:
向下取整,获取比当前数字小的最大整数。
trunc(d[,n])
:
对数字进行截取操作,不会进行四舍五入,直接舍去。
sqrt(d)
:
返回指定的数字的平方根。
power(x,y)
:
计算x的y次幂
mod(x,y)
:
计算x除以y的余数
abs(b)
:
返回指定数字的绝对值
字符串函数
ascii( c )
:
将指定的字符转换成对应的ASCII码值。
chr(d)
:
将对应的ASCII转换成字符
concat(s1,s2)
:
将字符串s1和s1拼接到一起。
instr(s,s1[,i,j])
:
在字符串s中查找字符串s1出现的位置,i 代表开始查找的位置(i可以为负数,代表从后往前查找), j代表出现的次数。 如果省略 i 和 j ,代表从第一位开始查找,第1次出现的位置。
initcap(s)
:
将字符串中的每一个单词的首字母变成大写
length(s)
:
返回字符串中字符的个数。
lower(s)
:
将字符串中的所有的字母变成小写
upper(s)
:
将字符串中的所有的字母变成大写
trim('字符' from '字符串')
:
去除字符串左右两边的指定的字符
ltrim(字符串,'字符')
:
去除字符串左边的指定的字符
rtrim(字符串,'字符')
:
去除字符串右边的指定的字符
reverse(s)
:
将字符串中的内容逆向输出。
replace(s,s1[,s2])
:
将字符串s中的s1字符串替换为s2,如果省略掉s2则代表将s1字符串从s中去掉。
substr(s,i[,j])
:
将字符串s从第i位置开始截取,截取长度为j,如果 j 省略则代表截取到末尾
时间类函数(重要):
sysdate
:
获取当前的系统时间
last_day(d)
:
获取指定日期月份的最后一天
next_day(d,n)
:
返回指定日期的下一个星期几。【n:代表一个数字,从1开始,1代表星期天,2代表星期一依此类推】
add_months(d,n)
:
在指定的日期上加上n个月后的日期【注意:在Oracle中日期对象可以直接进行加减运算的,一般日期加上1则就代表1天,如果小时就除以24】
months_between(d1,d2)
:
返回两个日期对象之间相差多少个月。
extract(属性 from date|timestamp)
:
截取日期中具体的年,月,日,时,分,秒…【 注意:如果要提取的时候年月日,则直接可以使用date类型的对象,如果要提取时分秒,则必须要使用timestamp类型对象。】
trunc(d,参数)
:
时间截取函数,可以将日期定位到指定的参数的开始位置。
转换函数:
to_date(s,格式)
:
将一个时间字符串转换成日期对象。
to_char(d,格式)
:
将一个日期对象转换成字符串
cast(数据 as 目标数据类型)
::
将指定的数据转换成目标数据类型。
分析函数:
分支函数:
nvl(字段,值1)
:
如果字段等于null,则返回值1,否则返回他自己。
nvl2(字段,值1,值2)
:
如果字段的值不为空则返回值1,否则返回值2.
decode
:
语法一:
decode(字段,条件值,值1,值2)
:当字段的值等于条件值的时候返回值1,否则返回值2.
语法二:
decode(字段,条件1,值1,条件2,值2,条件3,值3,.........,值n)
:如果字段的值等于条件1则返回值1,否则如果等于条件2则返回值2,…如果都不满足则返回值n.
case.. when
:
- 语法一:
-
case 字段 when 值1 then 执行内容1 when 值2 then 执行内容2 ........ else 执行的内容n end;
【功能类似decode,只能做等值匹配,如果字段等于值1,则执行内容1,如果等于值2,则执行内容2,…如果都不相等则执行内容n】
- 语法二:
case when 条件判断1 then 执行内容1
when 条件判断2 then 执行内容2
......
else 执行内容n end
当条件判断1成立则执行内容1,如果条件判断2成立则执行内容2,…,如果条件都不成立,则执行内容n
开窗函数(重要)
相当在原来的表后面添加了一些列用来显示汇总之后的结果,不会影响原来表中的数据。
- 语法:
函数([参数]) over([partition by 分组字段,... order by 排序字段 desc|asc][定位框架])
开窗函数中可以使用的函数有以下:
1、聚合函数:count()、 sum() 、avg() 、max() 、min()
【注意:如果在使用聚合函数的时候,在over中指定了order by,则进行汇总数据的时候按照指定的范围进行汇总,但不是汇总所有的数据,默认范围从第一行到当前行进行汇总。】
定位框架:
语法:order by 排序字段 desc|asc range|rows between 边界规则的下限 and 边界规则的上限
;
边界规则:
unbounded preceding:一直向上直到第一行
n preceding:前n行
current row:当前行
n following:后n行
unbounded following:一直往下到最后一行
- range和rows区别:
range是按照值的范围进行累计操作
rows:按照行的范围进行累计操作
默认情况 range between unbounded preceding and current row;
2.排名函数:
- row_number():忽略相同的值,也就是相同的值是不同的名次,排名是连续的。
- rank():相同值名次是一样的,但是排名是跳跃的。
- dense_rank():相同的值名次是一样的,但是排名是连续的。
使用场景:
1、如果要对表中的数据进行排名,可以使用开窗函数。
2.如果要对表中每一个组内的数据进行排名,获取前几名或者第几名到第几名
3.计算连续性的问题也可以使用排名函数。
【思路】:将数据按照指定的列进行分组然后再组内进行排名,在使用时间列减去名次,如果相减的结果是一样的,则代表连续。
3.移动函数:
lag(字段,n[,为空的时候填充的内容])
:下移函数,将指定的列向下移动n行
lead(字段,n[,为空的时候填充的内容])
:上移函数,将指定的列向上移动n行
【使用场景】:经常用来进行环比和同比的计算:
环比:当月的数据和上月的数据进行对比
同比:今年本月数据和去年当月的数据进行对比
开窗分组:partition by
partition by分组汇总之后将汇总的结果显示在表后面添加的列中,不影响原来的数据,但是group by分组之后将组内的数据最终汇总为一行,并不能显示原来的详细信息。
其他函数:
listagg()
:
将数据进行分组,然后将该组中的某一列数据按照指定的拼接符,拼接到一个列中。
语法:listagg(列名,'拼接符') within group (order by 列名)
;
【注意】:使用该函数则必须要使用group by分组
rollup()
:
对分组统计之后的数据在进行小计。
语法:select 分组字段,聚合函数,... from 表 group by rollup(分组字段)
层级关系函数
:
在Oracle中层级关系主要使用connect by来实现
语法: select * from 表 [where 条件] start with 字段条件 connect by prior 字段名=子节点字段名
;
层级关系中的相关参数:
1.prior 字段
:代表上级节点的字段。
2.connect_by_root 字段
:获取层级关系中最高层指定字段的值。
3.connect_by_isleaf
:用来判断是否是叶子节点,0代表不是叶子,1代表是叶子结点
4.sys_connect_by_path(字段,'分隔符')
:用来显示完整的层级关系
5.level
:返回等级,数字越小代表等级越高。