oracle常用函数

1、row_number over
ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。

那么比如对于一个数据框我们希望按某一列的某个值去去分组，并在组内进行排序。就可以采用row_number(),over去进行，比如

需求：根据部门分组，显示每个部门的工资等级
预期结果：

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

如果要求每个部门只显示两行的话，可以采用

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee where rk<=2;

2、to_date()，to_char()
to_date:将字符串转换为日期
to_char:将日期格式转换为字符串
yy 两位年 显示值:07
yyy 三位年 显示值:007
yyyy 四位年 显示值:2007
Month:
mm 两位月 显示值:11
mon 字符集表示 显示值:11月,若是英文版,显示nov
month 字符集表示 显示值:11月,若是英文版,显示november
Day:
dd number 当月第几天 显示值:02
ddd number 当年第几天 显示值:02
dy abbreviated 当周第几天简写 显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写 显示值:星期五,若是英文版,显示friday
Hour ：
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13
Minute:
mi two digits 60进制 显示值:45
Second:
ss two digits 60进制 显示值:25

日期以2019-08-20 16：49：23为例
例1：to_char
ect to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串  
显示：2019-08-20 16：49：23
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年  
显示：2019
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月  
显示：08
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日  
显示：20
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时  
显示：16
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分  
显示：49
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒1
显示：23

例2：to_date
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual
显示：2004/05/07 13：23：44
select to_date('2004-05-07 ','yyyy-mm-dd')    from dual
2004/5/7
select to_date('2004-05-07','yyyymmdd')    from dual

例3：算日期天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;  
显示：4533

floor(向下取整)

3、时间戳转换函数
hive_sql
from_unixtime():将时间戳转换为时间
unix_timestamp():获取当前时间戳

oracle：
因为oracle中没有时间戳转换函数，所以需要自己编写
时间戳是按照从格林威治时间1970年1月1日期计算的一个秒数

时间戳转时间
select TO_DATE('19700101','yyyymmdd') + 1235728935/86400 +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24 from dual，其中1235728935就是unix/linux时间戳，转换完之后就表示为 2009-2-27 18:02:15

时间戳转日期
select (to_date('2009-2-27 18:02:15','yyyy-mm-dd hh24:mi:ss') - to_date('1970-1-1','yyyy-mm-dd'))*86400- TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 from dual  

3、rollup函数
rollup()是group by的一个扩展函数，初步的感觉是，可以多个列进行group by，然后分别进行统计。

1. 日志表，按操作类型统计：
   SELECT l.operate_type,count(*) FROM quick_do_log l group by rollup(l.operate_type);
   
2. 日志表，按号码归属地和操作类型：
   SELECT l.number_location,l.operate_type,count(*) FROM quick_do_log l group by rollup(l.number_location,l.operate_type);
   
   rollup为统计函数
   4、decode函数
   decode函数为字符串判断函数
   主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；

SELECT DECODE（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）
FROM talbename
WHERE…
DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)的理解如下：

if （条件==值1）
then　　　　
return(翻译值1)
elif （条件==值2）
then　　　　
return(翻译值2)　　　　
......
elif （条件==值n）
 then　　　　
return(翻译值n)
else　　　　
return(缺省值)
end if

其中columnname为要选择的table中所定义的column

现定义一table名为output，其中定义两个column分别为monthid（var型）
和sale（number型），若sale值=1000时翻译为D，
=2000时翻译为C，=3000时翻译为B，
=4000时翻译为A，
如是其他值则翻译为Other；

SQL如下：

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output

5、add_months函数
add_months 函数主要是对日期函数进行操作
add_months 有两个参数，第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)

select add_months(sysdate,3) from dual;  
ADD_MONTHS(SYSDATE,3)
>> 2012-5-16 下午 02:30:47

6、pivot函数
pivot函数是经典的行转列函数，是将数据表中的行按字段分割变为列的，如图中所示，我想讲其中的假牌汽车、套牌汽车、加套牌摩托车按月份转为列。
设表为t1

则将数据按月份切分为07、08可以用如下代码

select * from t1
pivot(
sum(jp_qc) jp_qc,sum(tp_qc) tp_qc,sum(jtp_mtp) jtp_mtc --要分的数值
for yf in ('07' as n07,'08' as n08) --对月份中的字段进行重命名
)
#注意合成后对列中字段的命名会自动和前面的字段进行命令拼接

7、over()

设t1为这个，如果我们想在swrs后面添加一列，swrs的总数，可以利用over（）函数，相当于在swrs后面添加了一列常数,对所有常数为1的进行求和
select swrs,sum(swrs) over() swrs汇总 from t1

8、row_number、rank、dense_rank的区别
row_number:与over()函数一起使用，查询时为每一行生成一个记录，不会重复
rank:同样为排序的行生成序号，所不同的是，rank会考虑值相同的情况，也就是说当两个值相同时，生成的序号一样，到下一个时序号加相同的列数
dense_rank:与上面的相同，相同的序号生成的序号相同，向下计数时序号+1。下面我们来看具体的例子
假设现在有一张学生表student，学生表中有姓名、分数、课程编号。

-row_number() 顺序排序
select name,course,row_number() over(partition by course order by score desc) rank from student;

②

--rank() 跳跃排序，如果有两个第一级别时，接下来是第三级别
select name,course,rank() over(partition by course order by score desc) rank from student;

③

--dense_rank() 连续排序，如果有两个第一级别时，接下来是第二级别 
select name,course,dense_rank() over(partition by course order by score desc) rank from student;

总结一般来说，对于取名次这三者的就有比较显著的区别了

--每门课程第一名只取一个： 
select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有： 
select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
--每门课程第一名取所有：
select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;

9、lag、lead 滞后函数
lag与lead函数是跟偏移量相关的两个分析函数，通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接。
over()表示 lag()与lead()操作的数据都在over()的范围内，他里面可以使用partition by 语句（用于分组） order by 语句（用于排序）。partition by a order by b表示以a字段进行分组，再 以b字段进行排序，对数据进行查询。
用法：
（1）lead(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。
（2）lag(field, num, defaultvalue) field需要查找的字段，num往后查找的num行的数据，defaultvalue没有符合条件的默认值。

二、表初始化数据为：

a、获取当前记录的id，以及下一条记录的id

select t.id id ,
       lead(t.id, 1, null) over (order by t.id)  next_record_id, t.cphm
from tb_test t       
  order by t.id asc

b、获取当前记录的id，以及上一条记录的id

select t.id id ,
       lag(t.id, 1, null) over (order by t.id)  next_record_id, t.cphm
from tb_test t       
  order by t.id asc

c、获取号牌号码相同的，当前记录的id与，下一条记录的id（使用partition by）

select t.id id, 
       lead(t.id, 1, null) over(partition by cphm order by t.id) next_same_cphm_id, t.cphm
from tb_test t
     order by t.id asc 

10、between… and…
oracle 种between…and…会包含两个端点的值，比如
BETWEEN value1 AND value2
就是在[VALUE1,VALUE2]之间。
如between 3 and 5
那么选取的数就是3 4 5