文章目录

Oracle常用函数整理

ROW_NUMBER() OVER()函数用法详解

语法格式:row_number() over(partition by 分组列 order by 排序列 desc)

在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

测试用表:

create table TEST_ROW_NUMBER_OVER(
       id varchar(10) not null,
       name varchar(10) null,
       age varchar(10) null,
       salary int null
);
select * from TEST_ROW_NUMBER_OVER t;
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);
insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(5,'d2',17,1800);
-- 对查询结果进行排序(无分组)
select id,name,age,salary,row_number()over(order by salary desc) rn
from TEST_ROW_NUMBER_OVER t;
-- 根据id分组排序
select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t;
-- 找出每一组中序号为一的数据
select * from(select id,name,age,salary,row_number()over(partition by id order by salary desc) rank
from TEST_ROW_NUMBER_OVER t)
where rank <2;
-- 排序找出年龄在13岁到16岁数据,按salary排序
select id,name,age,salary,row_number()over(order by salary desc)  rank
from TEST_ROW_NUMBER_OVER t where age between '13' and '16';

更多例子:

-- 1.使用row_number()函数进行编号, 先按psd进行排序,排序完后,给每条数据进行编号
select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer;
-- 2.在订单中按价格的升序进行排序,并给每条记录进行排序代码如下:
select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order;
-- 统计出每一个各户的所有订单并按每一个客户下的订单的金额 升序排序,同时给每一个客户的订单进行编号
select ROW_NUMBER() over(partition by customerID  order by totalPrice) as rows,
customerID,totalPrice, DID from OP_Order;
-- 统计每一个客户最近下的订单是第几次下的订单:
with tabs as  
(  
select ROW_NUMBER() over(partition by customerID  order by totalPrice)
 as rows,customerID,totalPrice, DID from OP_Order  
 )  
select MAX(rows) as '下单次数',customerID from tabs 
group by customerID ;

-- 5.统计每一个客户所有的订单中购买的金额最小,而且并统计改订单中,客户是第几次购买的:
-- 思路:利用临时表来执行这一操作。
--         1.先按客户进行分组,然后按客户的下单的时间进行排序,并进行编号。
--         2.然后利用子查询查找出每一个客户购买时的最小价格。
--         3.根据查找出每一个客户的最小价格来查找相应的记录。
with tabs as  
(  
     select ROW_NUMBER() over(partition by customerID  order by insDT) 
    as rows,customerID,totalPrice, DID from OP_Order  
)  
select * from tabs  
where totalPrice in   
(  
    select MIN(totalPrice)from tabs group by customerID  
) ;
-- 6.筛选出客户第一次下的订单。
with tabs as  
(  
    select ROW_NUMBER() over(partition by customerID  order by insDT) as rows,* from OP_Order  
)  
select * from tabs where rows = 1 ;
-- 注意:在使用over等开窗函数时,over里头的分组及排序的执行晚于“where,group by,order by”的执行。
select   
ROW_NUMBER() over(partition by customerID  order by insDT) as rows,  
customerID,totalPrice, DID  
from OP_Order where insDT>'2011-07-22'

学习整理自:ROW_NUMBER() OVER()函数用法详解 (分组排序 例子多)

nvl null判断

SELECT
    nvl(null,0) count
FROM
  dual;

count 加条件

参考: https://blog.csdn.net/qq_32112175/article/details/89707327

SELECT
        en_station_id ,
        count( * )  in_count,
        COUNT( CASE UPLOADSTATUS WHEN 4 THEN 1 ELSE NULL END ) in_success,
        COUNT( CASE UPLOADSTATUS WHEN 4 THEN NULL ELSE 1 END ) in_fail
    FROM
        ZC_WEIGHT_LINE_IN_NEW t 
    WHERE
        check_time >= TO_DATE( '2020-10-20 00:00:00', 'yyyy-MM-dd HH24:mi:ss' ) 
        AND check_time <= TO_DATE( '2020-10-22 23:59:59', 'yyyy-MM-dd HH24:mi:ss' ) 
    GROUP BY
        en_station_id

时间函数

sysdate 获取系统当前时间

select sysdate  from dual;

年、季、月、周、日

select  to_char(sysdate, 'yyyy' )  from dual; --年 
select  to_char(sysdate, 'MM' )  from dual; --月 
select  to_char(sysdate, 'dd' )  from dual; --日 
select  to_char(sysdate, 'Q')  from dual; --季 
select  to_char(sysdate, 'iw')  from dual; --周--按日历上的那种,每年有52或者53周

TO_DATE、TO_CHAR时间格式化

SELECT
    TO_DATE( '2021-01-01 23:23:59', 'yyyy-MM-dd HH24:mi:ss') to_date,
    TO_CHAR(SYSDATE,'yyyy-MM-dd HH24:mi:ss') to_char
FROM
  dual;

日期操作

--当前时间减去7分钟的时间 
select   sysdate,sysdate - interval '7' MINUTE  from  dual; 
--当前时间减去7小时的时间 
select   sysdate - interval '7' hour  from dual; 
--当前时间减去7天的时间 
select   sysdate - interval '7' day  from dual; 
--当前时间减去7月的时间 
select   sysdate,sysdate - interval '7' month  from dual; 
--当前时间减去7年的时间 
select   sysdate,sysdate - interval '7' year  from dual; 
--时间间隔乘以一个数字 
select   sysdate,sysdate - 8*interval '7' hour  from dual;

常用的时间戳

--获取当年的一月一号
SELECT to_date(concat((select to_char(sysdate,'yyyy') from dual), '-01-01 00:00:00'),'yyyy-MM-dd HH24:mi:ss') FROM DUAL;
--获取这个月的一月一号
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1  FROM DUAL; 
SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)) + 1,'yyyy-mm-dd HH24:mi:ss')  FROM DUAL;

计算两个时间差

--有两个日期数据START_DATE,END_DATE,欲得到这两个日期的时间差(以天,小时,分钟,秒,毫秒):
--天:
ROUND(TO_NUMBER(END_DATE - START_DATE))
--小时:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
--分钟:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
--秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
--毫秒:
ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 60)
--系统时间相差某一个范围的时间值的记录的写法
select * from tableName where ROUND(TO_NUMBER(SYSDATE - date_column) * 24) between 72 and 96
--获得相隔时间的相差天数
SELECT CEIL((TO_DATE('2008-05-02 00:00:00', 'yyyy-mm-dd hh24-mi-ss') - TO_DATE('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss'))) 相差天数  FROM DUAL;
--相隔时间的相差小时数
SELECT CEIL((TO_DATE('2008-06-02 10:00:00', 'yyyy-mm-dd hh24-mi-ss') - TO_DATE('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) * 24) 相差小时数  FROM DUAL;
--相隔时间的相差分钟数
SELECT CEIL(((TO_DATE('2008-05-12 00:00:00', 'yyyy-mm-dd hh24-mi-ss') - TO_DATE('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60) 相差分钟数  FROM DUAL;
--相隔时间的相差秒数
SELECT CEIL((TO_DATE('2011-08-16 14:03:20', 'yyyy-mm-dd hh24-mi-ss') -  TO_DATE('2011-08-16 11:00:20', 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;