【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
系列文章目录
【SQL开发实战技巧】系列一:关于SQL不得不说的那些事
【SQL开发实战技巧】系列二简单单表查询
【SQL开发实战技巧】系列三SQL排序的那些事
【SQL开发实战技巧】系列四从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列五从执行计划看IN、EXISTS 和 INNER JOIN效率我们要分场景不要死记网上结论
【SQL开发实战技巧】系列六从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率记住内外关联条件不要乱放
【SQL开发实战技巧】系列七从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列八聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列九一个update误把其他列数据更新成空了Merge改写update给你五种删除重复数据的写法
【SQL开发实战技巧】系列十从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列十一拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列十二三问(如何对字符串字母去重后按字母顺序排列字符串如何识别哪些字符串中包含数字如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列十三讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列十四计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列十五查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列十六数据仓库中时间类型操作初级日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列十七数据仓库中时间类型操作初级确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列十八数据仓库中时间类型操作进阶INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列十九数据仓库中时间类型操作进阶如何一个SQL打印当月或一年的日历如何确定某月内第一个和最后—个周内某天的日期
【SQL开发实战技巧】系列二十数据仓库中时间类型操作进阶获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列二十一数据仓库中时间类型操作进阶识别重叠的日期范围按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列二十二数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列二十三数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
文章目录
前言
本篇文章讲解的主要内容是关于时间类型操作的进阶操作这些操作在数仓中也属于比较难一些的时间操作案例了如何一个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语句的作用之一。
总结
本章介绍的四个时间操作的案例还是有难度的如果会到这个程度感觉时间类型操作应该都能游刃有余了~