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

简介: 如何一个SQL打印出当月日历或当年日历???如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期???【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问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语句的作用之一。


总结

本章介绍的四个时间操作的案例还是有难度的,如果会到这个程度,感觉时间类型操作应该都能游刃有余了~

相关文章
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
22天前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
在Web开发中,安全至关重要,尤其要警惕SQL注入和XSS攻击。SQL注入通过在数据库查询中插入恶意代码来窃取或篡改数据,而XSS攻击则通过注入恶意脚本来窃取用户敏感信息。本文将带你深入了解这两种威胁,并提供Python实战技巧,包括使用参数化查询和ORM框架防御SQL注入,以及利用模板引擎自动转义和内容安全策略(CSP)防范XSS攻击。通过掌握这些方法,你将能够更加自信地应对Web安全挑战,确保应用程序的安全性。
55 3
|
2月前
|
SQL NoSQL 数据库
开发效率与灵活性:SQL vs NoSQL
【8月更文第24天】随着大数据和实时应用的兴起,数据库技术也在不断发展以适应新的需求。传统的SQL(结构化查询语言)数据库因其成熟的数据管理机制而被广泛使用,而NoSQL(Not Only SQL)数据库则以其灵活性和扩展性赢得了众多开发者的青睐。本文将从开发者的视角出发,探讨这两种数据库类型的优缺点,并通过具体的代码示例来说明它们在实际开发中的应用。
55 1
|
1月前
|
SQL 分布式计算 大数据
大数据开发SQL代码编码原则和规范
这段SQL编码原则强调代码的功能完整性、清晰度、执行效率及可读性,通过统一关键词大小写、缩进量以及禁止使用模糊操作如select *等手段提升代码质量。此外,SQL编码规范还详细规定了代码头部信息、字段与子句排列、运算符前后间隔、CASE语句编写、查询嵌套、表别名定义以及SQL注释的具体要求,确保代码的一致性和维护性。
40 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
176 0
|
3月前
|
Cloud Native 关系型数据库 OLAP
云原生数据仓库操作报错合集之遇到“table does not exist”错误,该怎么办
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
3月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库操作报错合集之遇到报错“DDL forbidden because backupTask is doing snapshot”如何处理
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
3月前
|
JSON Cloud Native 关系型数据库
云原生数据仓库操作报错合集之遇到报错 "ERROR: out of shared memory" ,该怎么办
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
3月前
|
SQL Cloud Native 关系型数据库
云原生数据仓库使用问题之控制JDBC方式请求的SQL大小限制的参数是什么
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
17天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")

热门文章

最新文章