(7/14) oracle查询优化的改写笔记第七章日期运算

超、凢脫俗 2023-02-21 04:56 41阅读 0赞

--7.1 日期类型
    select current_date, current_timestamp from dual;
    
    create view v7_1 (diff1, diff2, diff3) as
    select sysdate-to_date('2018-01-24 05:30:11', 'yyyy-mm-dd hh24:mi:ss'),
    	systimestamp - to_timestamp('2018-01-25 11:30:11.66', 'yyyy-mm-dd hh24:mi:ssxff'),
    	systimestamp-to_date('2018-01-24 05:30:11','yyyy-mm-dd hh24:mi:ss')
    from dual;
    
    desc v7_1;
    
    select * from  v7_1;
    
    create view v7_2 as
    select sysdate +1 as d1,
    	systimestamp+1 as d2
    from dual;
    
    select * from v7_2;
    
    -- 7.2 日期计算
    select hiredate,
    	hiredate+1 as next_day,
    	hiredate+1/24 as next_hour,
    	hiredate+5/24/60/60 as next_second
    from emp
    where rownum<=1;
    
    -- 7.3 时间间隔类型
    select interval '2' year as "year",
    	interval '50' month as "month",
    	interval '99' day as "day",
    	interval '80' hour as "hour",
    	interval '90' minute as "minute",
    	interval '3.15' second as "second",
    	interval '2 12:30:59' day to second as "day to second",
    	interval '13-3' year to month as "year to month"
    from dual;
    
    select systimestamp + interval '1' day from dual; 
    select sysdate + interval '1' day from dual; 
    select sysdate + interval '1.5' second from dual; 
    
    -- 7.4 日期计算函数
    select hiredate,
    	add_months(hiredate, -1) as last_month,
    	add_months(hiredate, 1*12) as next_year
    from emp
    where rownum<=1;
    
    
    -- 当计算上月同期时可能会重复计算28号的数据
    select d,add_months(d, -1) as last_month
    from (select date'2017-03-27' + level as d from  dual 
    connect by level <= 4);
    
    -- 遇到闰年的情况
    select add_months(date'2016-02-28', 12) as last_month from dual
    union all
    select add_months(date'2016-02-29',12) as last_month from dual;
    
    -- 月份加减不能使用interval函数
    select date'2018-01-01'+interval '1' month from dual;
    
    select date'2018-01-29'+interval '1' month from dual;
    
    -- 7.5 间隔月份
    select months_between(date'2018-03-01', date'2018-01-01') from dual;
    
    -- 注意2月份的28号是否影响需求
    select months_between(date'2018-03-31', date'2018-02-28' ) from dual
    union all
    select months_between(date'2018-03-30', date'2018-02-28' ) from dual
    union all
    select months_between(date'2018-03-29', date'2018-02-28' ) from dual
    union all
    select months_between(date'2018-03-28', date'2018-02-28' ) from dual;
    
    -- 7.6 获取记录间的间隔时间
    with v1 as
    	(select  
    	   rownum as rn, 
    		ename, 
    		empno, 
    		hiredate 
    	from (
    		select ename,
    			empno,
    			hiredate
    		from emp
    		where deptno=20
    		order by hiredate
    	) 
    ) 
    select * from v1;
    
    with v1 as
    (select rownum as rn, ename, empno, hiredate
    	from ( select ename,
    				empno,
    				hiredate
    			from emp
    			where deptno=20
    			order by hiredate))
    select a.rn,
    	a.ename,
    	a.empno,
    	a.hiredate,
    	b.hiredate as last_hiredate,
    	a.hiredate -b.hiredate as interval_day
    from v1 a,
    	v1 b
    where b.rn=a.rn-1;
    
    -- lag函数
    select deptno,
    	ename,
    	hiredate,
    	lag(hiredate) over (order by hiredate) last_hd
    from emp
    where deptno=20;
    
    select ename,
    	hiredate,
    	last_hd,
    	hiredate-last_hd as interval_day
    from (
    	select deptno,
    		ename,
    		hiredate,
    		lag(hiredate) over (order by hiredate) last_hd
    	from emp
    	where deptno=20
    );
    
    select deptno,
    	ename,
    	lead(hiredate) over(order by hiredate) lead_hd,
    	hiredate,
    	lag(hiredate) over(order by hiredate) lag_hd
    from emp
    where deptno=20;