oracle-数据库03

本篇主要说明一下几个函数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 名次

结果：

table 1

科目	成绩	名次
数学	140	1
语文	140	1
数学	140	1
数学	130	4
语文	120	5

table 2

科目	成绩	名次
数学	140	1
语文	140	1
数学	140	1
数学	130	2
语文	120	3

table 3

科目	成绩	名次
数学	140	1
数学	140	1
数学	130	3
语文	140	1
语文	120	2

table 4

科目	成绩	名次
数学	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空处理

---

下期见。