oracle常用函数

冷不防 2021-10-30 04:54 561阅读 0赞

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记录返回一个序号。  
![在这里插入图片描述][20190820152638466.png]  
那么比如对于一个数据框我们希望按某一列的某个值去去分组，并在组内进行排序。就可以采用row\_number(),over去进行，比如  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70]  
需求：根据部门分组，显示每个部门的工资等级  
预期结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 1]

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);  
    ![在这里插入图片描述][20190823190522258.png]
2.  日志表，按号码归属地和操作类型：  
    SELECT l.number\_location,l.operate\_type,count(\*) FROM quick\_do\_log l group by rollup(l.number\_location,l.operate\_type);  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 2]  
    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  
![在这里插入图片描述][20190911143432191.png]

则将数据按月份切分为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) --对月份中的字段进行重命名
    )
    #注意合成后对列中字段的命名会自动和前面的字段进行命令拼接

![在这里插入图片描述][20190911143949318.png]  
7、over()  
![在这里插入图片描述][2019091115551573.png]  
设t1为这个，如果我们想在swrs后面添加一列，swrs的总数，可以利用over（）函数，相当于在swrs后面添加了一列常数,对所有常数为1的进行求和  
select swrs,sum(swrs) over() swrs汇总 from t1  
![在这里插入图片描述][20190911161107933.png]  
8、row\_number、rank、dense\_rank的区别  
row\_number:与over()函数一起使用，查询时为每一行生成一个记录，不会重复  
rank:同样为排序的行生成序号，所不同的是，rank会考虑值相同的情况，也就是说当两个值相同时，生成的序号一样，到下一个时序号加相同的列数  
dense\_rank:与上面的相同，相同的序号生成的序号相同，向下计数时序号+1。下面我们来看具体的例子  
假设现在有一张学生表student，学生表中有姓名、分数、课程编号。  
![在这里插入图片描述][2019092110551468.png]

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

![在这里插入图片描述][20190921105630743.png]  
②

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

![在这里插入图片描述][20190921105716116.png]  
③

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

![在这里插入图片描述][20190921105756436.png]  
总结一般来说，对于取名次这三者的就有比较显著的区别了

--每门课程第一名只取一个： 
    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没有符合条件的默认值。

二、表初始化数据为：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 3]  
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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 4]  
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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 5]  
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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 6]  
10、between… and…  
oracle 种between…and…会包含两个端点的值，比如  
BETWEEN value1 AND value2  
就是在\[VALUE1,VALUE2\]之间。  
如between 3 and 5  
那么选取的数就是3 4 5

[20190820152638466.png]: /images/20211029/504368bfa12947f38073d29023af1d6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70]: /images/20211029/099c94f68def454c96dea688c228473e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 1]: /images/20211029/c11f872a16d04602bd9f2ea21ba759ad.png
[20190823190522258.png]: /images/20211029/a0e5aa680e8f4d9fb03dab961ccd8c5c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 2]: /images/20211029/ba625003e80b47f9bbaa2db49653e87a.png
[20190911143432191.png]: /images/20211029/665a1259c6f44524aa8c9d0ef7df44dd.png
[20190911143949318.png]: /images/20211029/65f432693275456eb5d75e2ae7810521.png
[2019091115551573.png]: /images/20211029/006cd563746e49a6883a26607e7b585f.png
[20190911161107933.png]: /images/20211029/d2042fa7694c4561a87f26824d22b73c.png
[2019092110551468.png]: /images/20211029/7fd5e2e1c8b042b89f296c0235d760bb.png
[20190921105630743.png]: /images/20211029/d480dbdd7cef44cf8d75b00b7a7a0c48.png
[20190921105716116.png]: /images/20211029/66f4485b0002410c980e217f451207a2.png
[20190921105756436.png]: /images/20211029/d23aef56aac540c09788f3c5683dfb88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 3]: /images/20211029/16a403b81e014c6a87a0b84fa8432cad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 4]: /images/20211029/50e0fdf1db1b4a69bd01ed01f07bc629.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 5]: /images/20211029/a7db9c8158214e0192ff58a9deab3507.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MTYxMTA0NQ_size_16_color_FFFFFF_t_70 6]: /images/20211029/121ae5e65e4341ecaa7961e108a4ccc9.png