【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？

系列文章目录

【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开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值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开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

系列文章目录
前言
一、确定一年内属于周内某一天的所有日期
二、确定某月内第一个和最后—个周内某天的日期
三、创建本月日历
四、全年日历
总结

前言

本篇文章讲解的主要内容是关于时间类型操作的进阶操作，这些操作在数仓中也属于比较难一些的时间操作案例了：如何一个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

四、全年日历

前面介绍了一个月的日历怎么写。

如果是全年呢？方式大差不离！！！枚举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

这种数据需要用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

于是全年日历可查询为：

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

通过本例可以看到,使用with语句可以让你的思路及代码展示得非常清晰,你可以很方便地检查t,t1,t2,t3各步是否达到了预期目的,这就是with语句的作用之一。