本篇文章讲解的主要内容是：如何确定两个日期之间的工作日有多少天、计算—年中每周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、确定两个日期之间的工作天数
现在有个需求：返回员工BLAKE与JONES聘用日期之间的工作天数。
先看一下初始数据：

SQL> select ename,hiredate from emp where ename in ('BLAKE','JONES');

ENAME HIREDATE
---------- -----------
BLAKE 1981-5-1
JONES 1981-4-2

接下来一步步分析这个需求怎么做！
第一步，先初始化个600条数据的临时表T，具体啥用待会给大家说

with t as (
select level as id from dual connect by level<=600
)

第二步，通过max,min与group by将上面初始数据转为一行，这个结果做第二个临时表T1：

SQL> with t as
2 (select level as id from dual connect by level <= 600),
3 t1 as
4 (select min(hiredate) as min_hd, max(hiredate) as max_hd
5 from emp
6 where ename in ('BLAKE', 'JONES'))
7 select * from t1;

MIN_HD MAX_HD
----------- -----------
1981-4-2 1981-5-1

第三步，枚举出来两个日期之间的间隔天数，不过日期相减需要+1，比如1~2天是2天，所以计算公式应该是(2-1)+1。

SQL> with t as
2 (select level as id from dual connect by level <= 600),
3 t1 as
4 (select min(hiredate) as min_hd, max(hiredate) as max_hd
5 from emp
6 where ename in ('BLAKE', 'JONES'))
7 select (max_hd-min_hd)+1 as 天数 from t1;

天数
----------
30

第四步，将T表与T1表做个笛卡尔积，枚举出来这30天的所有日期。

SQL> set pagesize 200;
SQL>
SQL> with t as
2 (select level as id from dual connect by level <= 600),
3 t1 as
4 (select min(hiredate) as min_hd, max(hiredate) as max_hd
5 from emp
6 where ename in ('BLAKE', 'JONES'))
7 select min_hd + (t.id - 1) as 日期
8 from t, t1
9 where t.id <= ((max_hd - min_hd) + 1);

日期
-----------
1981-4-2
1981-4-3
1981-4-4
1981-4-5
1981-4-6
1981-4-7
1981-4-8
1981-4-9
1981-4-10
1981-4-11
1981-4-12
1981-4-13
1981-4-14
1981-4-15
1981-4-16
1981-4-17
1981-4-18
1981-4-19
1981-4-20
1981-4-21
1981-4-22
1981-4-23
1981-4-24
1981-4-25
1981-4-26
1981-4-27
1981-4-28
1981-4-29
1981-4-30
1981-5-1

30 rows selected


第五步，根据这些日期得到对应的工作日信息

SQL>
SQL> with t as
2 (select level as id from dual connect by level <= 600),
3 t1 as
4 (select min(hiredate) as min_hd, max(hiredate) as max_hd
5 from emp
6 where ename in ('BLAKE', 'JONES')),
7 t2 as
8 (select min_hd + (t.id - 1) as 日期
9 from t, t1
10 where t.id <= ((max_hd - min_hd) + 1))
11 select 日期, to_char(日期, 'DY', 'NLS_DATE_LANGUAGE=American') as dy
12 from t2;

日期 DY
----------- ---------------------------------------------------------------------------
1981-4-2 THU
1981-4-3 FRI
1981-4-4 SAT
1981-4-5 SUN
1981-4-6 MON
1981-4-7 TUE
1981-4-8 WED
1981-4-9 THU
1981-4-10 FRI
1981-4-11 SAT
1981-4-12 SUN
1981-4-13 MON
1981-4-14 TUE
1981-4-15 WED
1981-4-16 THU
1981-4-17 FRI
1981-4-18 SAT
1981-4-19 SUN
1981-4-20 MON
1981-4-21 TUE
1981-4-22 WED
1981-4-23 THU
1981-4-24 FRI
1981-4-25 SAT
1981-4-26 SUN
1981-4-27 MON
1981-4-28 TUE
1981-4-29 WED
1981-4-30 THU
1981-5-1 FRI

30 rows selected


第六步，过滤，把得到的结果汇总就是工作天数。

SQL> with t as
2 (select level as id from dual connect by level <= 600),
3 t1 as
4 (select min(hiredate) as min_hd, max(hiredate) as max_hd
5 from emp
6 where ename in ('BLAKE', 'JONES')),
7 t2 as
8 (select min_hd + (t.id - 1) as 日期
9 from t, t1
10 where t.id <= ((max_hd - min_hd) + 1)),
11 t3 as
12 (select 日期, to_char(日期, 'DY', 'NLS_DATE_LANGUAGE=American') as dy
13 from t2)
14 select count(*) from t3 where dy not in ('SAT', 'SUN');

COUNT(*)
----------
22


二、计算—年中周内各日期的次数
比如,计算一年内有多少天是星期一,多少天是星期二等,这个问题需要以下几步。

取得当前年度信息。
计算一年有多少天。
生成日期列表。
转换为对应的星期标识。
汇总。
那么接下来看怎么做！

SQL> with t as
2 (select to_date('2023-01-01', 'yyyy-mm-dd') as 年初 from dual),
3 t1 as
4 (select 年初, add_months(年初, 12) as 下年初 from t),
5 t2 as
6 (select 年初, 下年初, 下年初 - 年初 as 天数 from t1),
7 t3 as/*生成列表*/
8 (select 年初 + (level - 1) as 日期 from t2 connect by level <= 天数),
9 t4 as/*对数据进行转换*/
10 (select 日期, to_char(日期, 'DY') as 星期 from t3)
11 select 星期, count(*) as 天数 from t4 group by 星期;

星期 天数
--------------------------------------------------------------------------- ----------
星期二 52
星期六 52
星期日 53
星期三 52
星期四 52
星期五 52
星期一 52

7 rows selected


三、确定当前记录和下一条记录之间相差的天数
首先需要把下一条记录的雇佣日期作为当前行,这需要用到lead()over()分析函数。

SQL> select deptno,
2 ename,
3 hiredate,
4 lead(hiredate) over(order by hiredate) next_hd
5 from emp
6 where deptno = 10;

DEPTNO ENAME HIREDATE NEXT_HD
------ ---------- ----------- -----------
10 CLARK 1981-6-9 1981-11-17
10 KING 1981-11-17 1982-1-23
10 MILLER 1982-1-23

当数据提取到同一行后,再计算就比较简单：

SQL> with t as (
2 select deptno,
3 ename,
4 hiredate,
5 lead(hiredate) over(order by hiredate) next_hd
6 from emp
7 where deptno = 10)
8 select ename,hiredate,next_hd-hiredate diff
9 from t;

ENAME HIREDATE DIFF
---------- ----------- ----------
CLARK 1981-6-9 161
KING 1981-11-17 67
MILLER 1982-1-23

和lead对应的就是lag函数,如果读者能记住两个函数的区别当然比较好,如果记不住,可直接实验。

SQL>
SQL> with t as (
2 select deptno,
3 ename,
4 hiredate,
5 lag(hiredate) over(order by hiredate) lag_hd,
6 lead(hiredate) over(order by hiredate) lead_hd
7 from emp
8 where deptno = 10)
9 select * from t;

DEPTNO ENAME HIREDATE LAG_HD LEAD_HD
------ ---------- ----------- ----------- -----------
10 CLARK 1981-6-9 1981-11-17
10 KING 1981-11-17 1981-6-9 1982-1-23
10 MILLER 1982-1-23 1981-11-17


总结
本章节的三个需求：确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
有些许难度，不过建议还是学会比较好。
————————————————

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs
同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack