本篇主要说明一下几个函数rank(),to_date(),pivot(),listagg()等以及函数的使用。
1、rank() —— 分组排序
通过定义一个rank等级字段来为各行进行分组排序
rank、dense_rank:分组
partition、order:排序
select 科目,成绩,rank() over(order by 成绩) as 名次
from sc ;
dense_rank() over(order by ###) as 名次 --简写
--注意partition by之后不能加DESC
rank() over(partition by ##1 order by ###) as 名次
dense_rank() over(partition by ##1 order by ###) as 名次
结果:
科目 | 成绩 | 名次 |
数学 | 140 | 1 |
语文 | 140 | 1 |
数学 | 140 | 1 |
数学 | 130 | 4 |
语文 | 120 | 5 |
科目 | 成绩 | 名次 |
数学 | 140 | 1 |
语文 | 140 | 1 |
数学 | 140 | 1 |
数学 | 130 | 2 |
语文 | 120 | 3 |
科目 | 成绩 | 名次 |
数学 | 140 | 1 |
数学 | 140 | 1 |
数学 | 130 | 3 |
语文 | 140 | 1 |
语文 | 120 | 2 |
科目 | 成绩 | 名次 |
数学 | 140 | 1 |
数学 | 140 | 1 |
数学 | 130 | 2 |
语文 | 140 | 1 |
语文 | 120 | 2 |
总结:rank()不连续等级、dense_rank()连续等级
partition by大项排序、order by小项排序
2、to_date() —— 格式转化
将字符串数据按格式改为时间数据
还有to_char()、to_number()等, 主要讲to_date(),顺带上to_char()
(好处是便于对时间的限制)
select to_date(char_time,'yyyy-MM-dd', 'NLS_DATE_LANGUAGE = American') from dual;
--注意年月日等的写法;可设置语言
to_char(date_time,'yy','NLS_DATE_LANGUAGE = American')
--select省略
各种时间制写法:
分类 | 格式 | 含义 | 显示值 |
YEAR | yyyy | 四位年 | 2023 |
yyy | 三位年 | 023 | |
yy | 两位年 | 23 | |
MONTH | mm | 两位月 | 07 |
mon | 字符集表示 | 7月或jul | |
month | 字符全集表示 | 7月或jully | |
DAY | dd | 当月第几日(两位日) | 11 |
ddd | 当年第几日 | 192 | |
dy | 当周第几天简 | 星期二或tues | |
day | 当周第几天全 | 星期二或tuesday | |
HOUR | hh | 12小时进制 | 05 |
hh24 | 24小时进制 | 17 | |
MINUTE | mi | 60进制(一般的分钟表示) | 39 |
SECOND | ss | 60进制(一般的秒表示) | 46 |
WEEK | w | 当月第几周 | 2 |
ww | 当年第几周 | 28 | |
季度 | q | 当年第几季度 | 3 |
常见用法:
--(1)、求自定义某时间的具体信息[to_char()]
to_char(date_time,'yy') --''内容为所求值
select to_char(to_date('2023-6-23','yyyy-mm-dd'),'q') from dual;
--结果:3
--(2)、求两天之间的天数[1-2]
floor(date_time1-date_time2) --floor()是求小于该值的最大整数
select floor(sysdate-to_date('2023-4-3','yyyy-mm-dd')) from dual;
--结果:99
--更改时间显示语言还可以
alter session set nls_date_language='AMERICAN';
--可看一些配置属性
select * from nls_session_parameters
--(3)、求月份之差[months_between()]
months_between(date_time1,date_time2)
select months_between(sysdate,to_date('2023-4-3','yyyy-mm-dd')) from dual;
--结果:3.27191382915173
--(4)、next_day()用法
next_day(date_time,day) --day为当周第几天
select to_char(sysdate,'mm/dd' ) 星期二,
to_char(next_day(sysdate,1),'mm/dd') 周日,
to_char(next_day(sysdate,2),'mm/dd') 周一,
to_char(next_day(sysdate,3),'mm/dd') 周二,
to_char(next_day(sysdate,4),'mm/dd') 周三,
to_char(next_day(sysdate,5),'mm/dd') 周四,
to_char(next_day(sysdate,6),'mm/dd') 周五,
to_char(next_day(sysdate,7),'mm/dd') 周六
from dual;
--结果:
--星期二 周日 周一 周二 周三 周四 周五 周六
--07/11 07/16 07/17 07/18 07/12 07/13 07/14 07/15
再解释一下next_day():就是从第二天算起的一整周,通过1-7找周日-六
3、pivot() —— 行转列
将某列的部分值,按其余列值聚合的方式,以字段名出现于表头
uppivot(),列转行,主要讲pivot()
pivot(func(c1) for c2 in(v1,v2,v3)) --func()需为聚合函数
select 学号,语文,数学
from sc pivot(sum(成绩) for 科目 in ('语文' 语文,'数学' 数学))
order by 学号;
学号 | 语文 | 数学 |
001 | 140 | 130 |
002 | 120 | 140 |
003 | (null) | 140 |
聚合函数在不同情况下的选择不同:
max使用于大部分场景,min容易与else起冲突,avg和数值起冲突,sum和字符起冲突
总结:搞清楚按什么分组,对谁进行行列转换。
4、listagg() —— 合并内容
当其余字段内容一致时,将某一字段的内容按条件合并
group(),确定连接值的顺序
listagg(col,'')within group(order by col)
select 科目,listagg(成绩,',')within group(order by 成绩 DESC) 成绩序列
from sc
group by 科目; --需要加group by
科目 | 成绩序列 |
语文 | 140,120 |
数学 | 140,140,120 |
总结:分清要连接内容和连接先后由哪一列决定
5、常用数据处理函数
floor()对某数值(向下取整)取小于该值的最大整数,还有ceil(),向上取整,主要讲floor()
round()对数据精度值规定,可实现四舍五入,还有trunc(),不能四舍五入,主要讲round()
nvl(exp1,exp2)对空值数据处理,当exp1值为null,则赋值exp2,
还有nvl2(exp1,exp2,exp3),当exp1值为null,则赋值exp2,否则赋值exp3,主要讲nvl2()
--(1)、求整
select floor(3.1415926) from dual;
--结果:3
--(2)、规定精度
select round(3.1415926,3) from dual;
--结果:3.142
--(3)、空值处理
select id,name,nvl2(name,'未录入信息','已录入信息') as 录入情况 from Persons;
--结果如下图
总结:floor,ceil取整,round,trunc求精,nvl,nvl2空处理
下期见。