前言
本篇文章讲解的主要内容是关于时间类型操作的进阶操作，这些操作在数仓中也属于比较难一些的时间操作案例了：如何一个SQL打印出当月日历或当年日历？？？如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期？？？
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、确定一年内属于周内某一天的所有日期
本例要求返回指定年份内的所有周五,用前面介绍的知识枚举全年信息,然后再过滤就可以。
SQL> with t as
2 (select trunc(sysdate, 'y') + (level - 1) as dy
3 from dual
4 connect by level <=
5 (add_months(trunc(sysdate, 'y'), 12) - trunc(sysdate, 'y')))
6 select dy, to_char(dy, 'day') as 周五 from t where to_char(dy, 'd') = 6;
DY 周五
----------- ---------------------------------------------------------------------------
2023-1-6 星期五
2023-1-13 星期五
2023-1-20 星期五
2023-1-27 星期五
2023-2-3 星期五
2023-2-10 星期五
2023-2-17 星期五
2023-2-24 星期五
2023-3-3 星期五
2023-3-10 星期五
2023-3-17 星期五
2023-3-24 星期五
2023-3-31 星期五
2023-4-7 星期五
2023-4-14 星期五
2023-4-21 星期五
2023-4-28 星期五
2023-5-5 星期五
2023-5-12 星期五
2023-5-19 星期五
2023-5-26 星期五
2023-6-2 星期五
2023-6-9 星期五
2023-6-16 星期五
2023-6-23 星期五
2023-6-30 星期五
2023-7-7 星期五
2023-7-14 星期五
2023-7-21 星期五
2023-7-28 星期五
2023-8-4 星期五
2023-8-11 星期五
2023-8-18 星期五
2023-8-25 星期五
2023-9-1 星期五
2023-9-8 星期五
2023-9-15 星期五
2023-9-22 星期五
2023-9-29 星期五
2023-10-6 星期五
2023-10-13 星期五
2023-10-20 星期五
2023-10-27 星期五
2023-11-3 星期五
2023-11-10 星期五
2023-11-17 星期五
2023-11-24 星期五
2023-12-1 星期五
2023-12-8 星期五
2023-12-15 星期五
2023-12-22 星期五
2023-12-29 星期五
52 rows selected本例的要点是使用to_char(dy, 'd')来判断,这样可以避免不同客户端设置的影响,如：
SQL> select to_char(sysdate,'day')as day,to_char(sysdate,'d') as d from dual;
DAY D
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
星期三 4
SQL> alter session set nls_language=american;
Session altered
SQL> select to_char(sysdate,'day')as day,to_char(sysdate,'d') as d from dual;
DAY D
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
wednesday 4
SQL> 可以看到,当使用参数"day"时,不同字符集返回的结果不一样,但"d"不受影响。
二、确定某月内第一个和最后—个周内某天的日期
本例要求返回当月内第一个星期一与最后一个星期一,我们分别找上月末及当月末之前七天的下一周周一即可。
SQL> select next_day(trunc(sysdate,'mm')-1,2) as 第一周周一,
2 next_day(last_day(trunc(sysdate,'mm'))-7,2) as 最后一周的周一
3 from dual;
第一周周一 最后一周的周一
----------- -----------
2023-2-6 2023-2-27三、创建本月日历
现在有个需求：要求你写一个sql打印出当月日历信息
看到这个需求你是不是有点懵逼？
怎么实现呢？？？
其实我们可以枚举指定月份所有的日期,并转换为对应的周信息,再按所在周做一次“行转列”即可，我这里会给大家展示一种行列互换的方式，因为未介绍pivot/unpivot，所以就用case when做。
SQL> with t as
2 (select trunc(sysdate, 'mm') + (level - 1) as dy
3 from dual
4 connect by level <=
5 (add_months(trunc(sysdate, 'mm'), 1) - trunc(sysdate, 'mm'))),
6 t1 as
7 (select to_char(dy, 'iw') as 所在周,
8 to_char(dy, 'dd') as 日期,
9 to_number(to_char(dy, 'd')) 周几
10 from t)
11 select max(case 周几
12 when 2 then
13 日期
14 end) 周一,
15 max(case 周几
16 when 3 then
17 日期
18 end) 周二,
19 max(case 周几
20 when 4 then
21 日期
22 end) 周三,
23 max(case 周几
24 when 5 then
25 日期
26 end) 周四,
27 max(case 周几
28 when 6 then
29 日期
30 end) 周五,
31 max(case 周几
32 when 7 then
33 日期
34 end) 周六,
35 max(case 周几
36 when 1 then
37 日期
38 end) 周天
39 from t1
40 group by 所在周
41 order by 所在周;
周一 周二 周三 周四 周五 周六 周天
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28
SQL> 四、全年日历
前面介绍了一个月的日历怎么写。
如果是全年呢？方式大差不离！！！枚举365天就可以。
这里有一个小问题,第53周的数据to_char(日期,'iw')返回值有错,返回了第1周。
SQL> WITH x AS
2 (SELECT to_date('2013-12-27', 'yyyy-mm-dd') + (LEVEL - 1) AS d
3 FROM dual
4 CONNECT BY LEVEL <= 5)
5 SELECT d, to_char(d, 'day') AS DAY, to_char(d, 'iw') AS iw FROM x;
D DAY IW
----------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2013-12-27 星期五 52
2013-12-28 星期六 52
2013-12-29 星期日 52
2013-12-30 星期一 01
2013-12-31 星期二 01
SQL> 这种数据需要用case when来处理。
SQL>
SQL> WITH x AS
2 (SELECT to_date('2013-12-27', 'yyyy-mm-dd') + (LEVEL - 1) AS d
3 FROM dual
4 CONNECT BY LEVEL <= 5),
5 x1 as
6 (SELECT d,
7 to_char(d, 'day') AS DAY,
8 to_char(d, 'mm') AS mm,
9 to_char(d, 'iw') AS iw
10 FROM x)
11 select d,
12 day,
13 mm,
14 iw,
15 case
16 when mm = 12 and iw = '01' then
17 '53'
18 else
19 iw
20 end as new_iw
21 from x1;
D DAY MM IW NEW_IW
----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
2013-12-27 星期五 12 52 52
2013-12-28 星期六 12 52 52
2013-12-29 星期日 12 52 52
2013-12-30 星期一 12 01 53
2013-12-31 星期二 12 01 53
SQL> 于是全年日历可查询为：
SQL> with t as
2 (select trunc(sysdate, 'y') as 本年年初,
3 add_months(trunc(sysdate, 'y'), 12) as 下年初
4 from dual),
5 t1 as
6 (select 本年年初 + (level - 1) as 日期
7 from t
8 connect by level <= 下年初 - 本年年初),
9 t2 as
10 (select 日期,
11 to_char(日期, 'mm') as 月份,
12 to_char(日期, 'iw') 所在周,
13 to_number(to_char(日期, 'd')) as 周几
14 from t1),
15 t3 as
16 (select 日期,
17 月份,
18 case
19 when 月份 = 12 and 所在周 = '01' then
20 '53'
21 else
22 所在周
23 end as 所在周,
24 周几
25 from t2)
26 select case
27 when lag(月份) over(order by 所在周) = 月份 then
28 null
29 else
30 月份
31 end as 月份,
32 所在周,
33 max(case 周几
34 when 2 then
35 日期
36 end) 周一,
37 max(case 周几
38 when 3 then
39 日期
40 end) 周二,
41 max(case 周几
42 when 4 then
43 日期
44 end) 周三,
45 max(case 周几
46 when 5 then
47 日期
48 end) 周四,
49 max(case 周几
50 when 6 then
51 日期
52 end) 周五,
53 max(case 周几
54 when 7 then
55 日期
56 end) 周六,
57 max(case 周几
58 when 1 then
59 日期
60 end) 周天
61 from t3
62 group by 月份, 所在周
63 order by 2;
月份 所在周 周一 周二 周三 周四 周五 周六 周天
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
01 01 2023-1-2 2023-1-3 2023-1-4 2023-1-5 2023-1-6 2023-1-7 2023-1-8
02 2023-1-9 2023-1-10 2023-1-11 2023-1-12 2023-1-13 2023-1-14 2023-1-15
03 2023-1-16 2023-1-17 2023-1-18 2023-1-19 2023-1-20 2023-1-21 2023-1-22
04 2023-1-23 2023-1-24 2023-1-25 2023-1-26 2023-1-27 2023-1-28 2023-1-29
05 2023-1-30 2023-1-31
02 05 2023-2-1 2023-2-2 2023-2-3 2023-2-4 2023-2-5
06 2023-2-6 2023-2-7 2023-2-8 2023-2-9 2023-2-10 2023-2-11 2023-2-12
07 2023-2-13 2023-2-14 2023-2-15 2023-2-16 2023-2-17 2023-2-18 2023-2-19
08 2023-2-20 2023-2-21 2023-2-22 2023-2-23 2023-2-24 2023-2-25 2023-2-26
09 2023-2-27 2023-2-28
03 09 2023-3-1 2023-3-2 2023-3-3 2023-3-4 2023-3-5
10 2023-3-6 2023-3-7 2023-3-8 2023-3-9 2023-3-10 2023-3-11 2023-3-12
11 2023-3-13 2023-3-14 2023-3-15 2023-3-16 2023-3-17 2023-3-18 2023-3-19
12 2023-3-20 2023-3-21 2023-3-22 2023-3-23 2023-3-24 2023-3-25 2023-3-26
13 2023-3-27 2023-3-28 2023-3-29 2023-3-30 2023-3-31
04 13 2023-4-1 2023-4-2
14 2023-4-3 2023-4-4 2023-4-5 2023-4-6 2023-4-7 2023-4-8 2023-4-9
15 2023-4-10 2023-4-11 2023-4-12 2023-4-13 2023-4-14 2023-4-15 2023-4-16
16 2023-4-17 2023-4-18 2023-4-19 2023-4-20 2023-4-21 2023-4-22 2023-4-23
17 2023-4-24 2023-4-25 2023-4-26 2023-4-27 2023-4-28 2023-4-29 2023-4-30
05 18 2023-5-1 2023-5-2 2023-5-3 2023-5-4 2023-5-5 2023-5-6 2023-5-7
19 2023-5-8 2023-5-9 2023-5-10 2023-5-11 2023-5-12 2023-5-13 2023-5-14
20 2023-5-15 2023-5-16 2023-5-17 2023-5-18 2023-5-19 2023-5-20 2023-5-21
21 2023-5-22 2023-5-23 2023-5-24 2023-5-25 2023-5-26 2023-5-27 2023-5-28
22 2023-5-29 2023-5-30 2023-5-31
06 22 2023-6-1 2023-6-2 2023-6-3 2023-6-4
23 2023-6-5 2023-6-6 2023-6-7 2023-6-8 2023-6-9 2023-6-10 2023-6-11
24 2023-6-12 2023-6-13 2023-6-14 2023-6-15 2023-6-16 2023-6-17 2023-6-18
25 2023-6-19 2023-6-20 2023-6-21 2023-6-22 2023-6-23 2023-6-24 2023-6-25
26 2023-6-26 2023-6-27 2023-6-28 2023-6-29 2023-6-30
07 26 2023-7-1 2023-7-2
27 2023-7-3 2023-7-4 2023-7-5 2023-7-6 2023-7-7 2023-7-8 2023-7-9
28 2023-7-10 2023-7-11 2023-7-12 2023-7-13 2023-7-14 2023-7-15 2023-7-16
29 2023-7-17 2023-7-18 2023-7-19 2023-7-20 2023-7-21 2023-7-22 2023-7-23
30 2023-7-24 2023-7-25 2023-7-26 2023-7-27 2023-7-28 2023-7-29 2023-7-30
31 2023-7-31
08 31 2023-8-1 2023-8-2 2023-8-3 2023-8-4 2023-8-5 2023-8-6
32 2023-8-7 2023-8-8 2023-8-9 2023-8-10 2023-8-11 2023-8-12 2023-8-13
33 2023-8-14 2023-8-15 2023-8-16 2023-8-17 2023-8-18 2023-8-19 2023-8-20
34 2023-8-21 2023-8-22 2023-8-23 2023-8-24 2023-8-25 2023-8-26 2023-8-27
35 2023-8-28 2023-8-29 2023-8-30 2023-8-31
09 35 2023-9-1 2023-9-2 2023-9-3
36 2023-9-4 2023-9-5 2023-9-6 2023-9-7 2023-9-8 2023-9-9 2023-9-10
37 2023-9-11 2023-9-12 2023-9-13 2023-9-14 2023-9-15 2023-9-16 2023-9-17
38 2023-9-18 2023-9-19 2023-9-20 2023-9-21 2023-9-22 2023-9-23 2023-9-24
39 2023-9-25 2023-9-26 2023-9-27 2023-9-28 2023-9-29 2023-9-30
10 39 2023-10-1
40 2023-10-2 2023-10-3 2023-10-4 2023-10-5 2023-10-6 2023-10-7 2023-10-8
41 2023-10-9 2023-10-10 2023-10-11 2023-10-12 2023-10-13 2023-10-14 2023-10-15
42 2023-10-16 2023-10-17 2023-10-18 2023-10-19 2023-10-20 2023-10-21 2023-10-22
43 2023-10-23 2023-10-24 2023-10-25 2023-10-26 2023-10-27 2023-10-28 2023-10-29
44 2023-10-30 2023-10-31
11 44 2023-11-1 2023-11-2 2023-11-3 2023-11-4 2023-11-5
45 2023-11-6 2023-11-7 2023-11-8 2023-11-9 2023-11-10 2023-11-11 2023-11-12
46 2023-11-13 2023-11-14 2023-11-15 2023-11-16 2023-11-17 2023-11-18 2023-11-19
47 2023-11-20 2023-11-21 2023-11-22 2023-11-23 2023-11-24 2023-11-25 2023-11-26
48 2023-11-27 2023-11-28 2023-11-29 2023-11-30
12 48 2023-12-1 2023-12-2 2023-12-3
49 2023-12-4 2023-12-5 2023-12-6 2023-12-7 2023-12-8 2023-12-9 2023-12-10
50 2023-12-11 2023-12-12 2023-12-13 2023-12-14 2023-12-15 2023-12-16 2023-12-17
51 2023-12-18 2023-12-19 2023-12-20 2023-12-21 2023-12-22 2023-12-23 2023-12-24
01 52 2023-1-1
12 52 2023-12-25 2023-12-26 2023-12-27 2023-12-28 2023-12-29 2023-12-30 2023-12-31
63 rows selected
SQL> 通过本例可以看到,使用with语句可以让你的思路及代码展示得非常清晰,你可以很方便地检查t,t1,t2,t3各步是否达到了预期目的,这就是with语句的作用之一。
总结
本章介绍的四个时间操作的案例还是有难度的，如果会到这个程度，感觉时间类型操作应该都能游刃有余了~
