从一个SQL打印全年日历漫谈数据仓库中时间操作场景的重点写法
文章目录
前言
在本月工作及与网友互动的SQL开发问题中，大家经常会问到时间处理的问题，比如下面几个问题就是大家最常问问题：
- 我如何快速确定今年是否是闰年的😣
- 我如何从DATE类型数据获取年、月(月初&月末)、周、日、时、分、秒信息🤯
- 我如何快速查到本月月初第一周的周一和本月最后一周周一是在几号😑
- 我如何快速确定每个季度的开始和结束日期😫
- 领导让统计每个月招聘的人数，但是有的月份没招人也就没在数据库记录当月招聘信息，我怎么统计出来没有的月份😤
- 领导让每隔十分钟统计一次数据库登录人数，我该怎么统计啊🙄
- 突发奇想，大佬你能否用一条sql打印一年的日历出来，我懵逼了😫😭😤🤯
就上面这些问题我统一给大家通过一些案例去讲解如何去实现这些需求。
一、我如何快速确定今年是否是闰年的😣
闰年(Leap Year)共有366天（1-12月分别为31天，29天，31天，30天，31天，30天，31天，31天，30天，31天，30天，31天），是为了弥补因人为历法规定造成的年度天数与地球实际公转周期的时间差而设立的，闰年又分为普通闰年和世纪闰年：
- 普通年判断方法：能被4整除且不能被100整除的为闰年（如2004年就是闰年，1999年不是闰年）。
- 世纪年判断方法：能被400整除的是闰年（如2000年是闰年，1900年不是闰年）。
看到上面的介绍大家应该对闰年有了一定了解，那怎么在数仓中用SQL快速判断某一年是否是闰年呢？？？
其实若要判断一年是否为闰年,只需要看二月的月末是哪一天就可以啦！！！
SQL> select trunc(sysdate, 'y') as 年初,2 add_months(trunc(sysdate, 'y'), 1) as 二月初,3 last_day(add_months(trunc(sysdate, 'y'), 1)) as 二月底,4 to_char(last_day(add_months(trunc(sysdate, 'y'), 1)), 'dd') as 日5 from dual;年初 二月初 二月底 日
----------- ----------- ----------- ---------------------------------------------------------------------------
2023-1-1 2023-2-1 2023-2-28 28从上面结果立马知道了，今年是平年,这么计算是不是超级简单？？？
二、 我如何从DATE类型数据获取年、月(月初&月末)、周、日、时、分、秒信息🤯
经常看到有人因为不熟悉日期操作,获取相应信息的时候,要写很复杂的语句。下面举一个简单的例子。
SQL> select hiredate,2 to_date(to_char(hiredate, 'yyyy-mm') || '-1', 'yyyy-mm-dd') as yuechu3 from emp4 where rownum <= 1;HIREDATE YUECHU
----------- -----------
1980-12-17 1980-12-1SQL> 其实要获取这个数据,只需要一个简单的函数就可以做到,而根本不需要多次转换：
SQL> SELECT hiredate AS 雇佣日期, trunc(hiredate, 'mm') AS 月初2 from emp3 where rownum <= 1;雇佣日期 月初
----------- -----------
1980-12-17 1980-12-1SQL> 下面列举几个常用的取值方式,希望对大家有用。
SQL> SELECT hiredate,2 to_number(to_char(hiredate,'hh24')）时,3 to_number(to_char(hiredate,'mi'))分,4 to_number(to_char(hiredate,'ss')）秒,5 to_number(to_char(hiredate,'dd'))日,6 to_number(to_char(hiredate,'mm'))月,7 to_number(to_char(hiredate,'yyyy'))年,8 to_number(to_char(hiredate,'ddd'))年内第几天,9 trunc(hiredate,'dd')一天之始,10 trunc(hiredate,'day')周初,11 trunc(hiredate,'dy')周初,12 trunc(hiredate,'mm')月初,13 last_day(hiredate)月未,14 add_months(trunc(hiredate,'mm'),1)下月初,15 trunc(hiredate,'yy')年初,16 to_char(hiredate,'day')周几,17 to_char(hiredate,'dy')周几,18 to_char(hiredate,'month'）月份,19 to_char(hiredate,'mm'）月份20 FROM(SELECT hiredate+30/24/60/60+20/24/60+5/24 AS hiredate FROM emp WHERE ROWNUM<=1);HIREDATE 时 分 秒 日 月 年 年内第几天 一天之始 周初 周初 月初 月未 下月初 年初 周几 周几 月份 月份
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------
1980-12-17 5 20 30 17 12 1980 352 1980-12-17 1980-12-14 1980-12-14 1980-12-1 1980-12-31 1981-1-1 1980-1-1 星期三 星期三 12月 12SQL> 三、我如何快速查到本月月初第一周的周一和本月最后一周周一是在几号😑
这个问题是一个后端JAVA开发网友问我的，我先给大家分析一下我的思路：
首先分析计算月初第一个周一，无非是两种情况，拿本月(2月)来做案例：
- 本月的第一个周一恰好是本月1号；
- 本月的第一个周一可能是在3号，因为这个月1，2号是上个月最后一周的周六、周日；
其次是计算本月最后一个周一，无非也是两种情况：
- 最后一个周一恰好是月末最后一天；
- 最后一个周一是月末的前几天，但是肯定是当月最后一天的前七天内；
上面两个分析逻辑，总结一句话：
其实计算当月内第一个星期一与最后一个星期一,我们只需要分别找上月末及当月末之前七天的下一周周一即可。
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的时候，他很是震惊，因为他们团队的开发DBA给他的是枚举SQL，写的很长！！！
四、我如何快速确定每个季度的开始和结束日期😫
这个问题，是几个做报表的网友问我的，生成汇总报表时候时常要求按季度分类汇总这就需要通过给定年份提取对应的季度信息，那怎么快速获取某个季度的开始结束日期呢？
我是这样给他的：
SQL> with t as2 (select to_char(sysdate, 'yyyy') as 年, level as sn3 from dual4 connect by level <= 45 )6 select sn as 季度,7 (sn - 1) * 3 + 1 as 开始月份,8 add_months(trunc(to_date(年, 'yyyy'), 'y'), (sn - 1) * 3) as 开始日期,9 add_months(trunc(to_date(年, 'yyyy'), 'y'), sn * 3) - 1 as 结束日期10 from t;季度 开始月份 开始日期 结束日期
---------- ---------- ----------- -----------1 1 2023-1-1 2023-3-312 4 2023-4-1 2023-6-303 7 2023-7-1 2023-9-304 10 2023-10-1 2023-12-31SQL> 其实只要想清楚计算公式和日期的计算方法，就可以快速计算出来了。
五、领导让统计每个月招聘的人数，但是有的月份没招人也就没在数据库记录当月招聘信息，我怎么统计出来没有的月份😤
有时业务数据并不是连续的，比如考勤、比如网友说的招聘信息，可能在某一天或则某一月并没有相关操作，数据库也就没相关记录，而领导却要展示报表时候，这些日期的信息项都要展示出来用于后续分析统计，那我们该怎么统计呢？接下来用一个案例来一步步解释！
现在有下面员工信息
SQL> select empno,hiredate from emp order by 2;EMPNO HIREDATE
----- -----------7369 1980-12-177499 1981-2-207521 1981-2-227566 1981-4-27698 1981-5-17782 1981-6-97844 1981-9-87654 1981-9-287839 1981-11-177900 1981-12-37902 1981-12-37934 1982-1-237788 1987-4-197876 1987-5-231001 2021-10-9 115 rows selected有的年份没有招聘员工，这时按年份查询招聘人数结果如下：
SQL> select to_char(hiredate,'yyyy') as year, count(*) as cnt2 from emp3 group by to_char(hiredate,'yyyy')4 order by 1;YEAR CNT
--------------------------------------------------------------------------- ----------
1980 1
1981 10
1982 1
1987 2
2021 1SQL> 为了分析数据一般需要把表中没有的年份（如1983年）内的人数统计为0,这时就需要先根据表中的信息生成一个年份的枚举列表。
SQL> with t as2 (select extract(year from min(hiredate)) as 开始年份,3 extract(year from max(hiredate)) as 结束年份4 from emp where empno<>1001)5 select 开始年份 + (level - 1) as 年份6 from t7 connect by level <= ((结束年份 - 开始年份) + 1);年份
----------198019811982198319841985198619878 rows selected通过这个列表关联查询就可以得到所有年份的数据。
SQL> with t as2 (select extract(year from min(hiredate)) as 开始年份,3 extract(year from max(hiredate)) as 结束年份4 from emp5 where empno <> 1001),6 t1 as7 (select 开始年份 + (level - 1) as 年份8 from t9 connect by level <= ((结束年份 - 开始年份) + 1))10 select t1.年份, count(emp.empno) as 聘用人数11 from emp12 right join t113 on (extract(year from emp.hiredate) = t1.年份)14 group by 年份15 order by 1;年份 聘用人数
---------- ----------1980 11981 101982 11983 01984 01985 01986 01987 28 rows selected上面这种枚举关联处理方式，适用于很多场景。
六、领导让每隔十分钟统计一次数据库登录人数，我该怎么统计啊🙄
这也是一个做数据分析的网友请教的问题，他提的问题是：按指定的时间间隔(10分钟）汇总数据,分别汇总0分、10分、20分、30分等。
我这里用v$sql表来当作案例数据，先看下这里的部分数据：
select a.LAST_ACTIVE_TIME from v$sql a where rownum<=5;
LAST_ACTIVE_TIME
----------------
2023-2-13 22:14:27
2023-2-15 3:00:59
2023-2-7 1:05:29
2023-2-8 1:05:30
2023-2-15 15:59:03
我们一步步来。
1、截取数据到分钟，并提取分钟信息
select sql_id,trunc(a.LAST_ACTIVE_TIME,'mi') as tim,to_char(a.LAST_ACTIVE_TIME,'mi') as mi from v$sql a where sql_id='gcsnqzu9q0004'
SQL_ID TIM MI
gcsnqzu9q0004 2023-2-13 22:14:00 14
2、对14和10取余
SQL> select mod(14,10) from dual;MOD(14,10)
----------4SQL>
3、对比上面结果，我们可以知道如果想计算整10分钟的间隔，那就直接用MI-MOD(14,10)就算出来这个分钟对应在整十的哪个范围了。
select sql_id,trunc(a.LAST_ACTIVE_TIME, 'mi') as tim,to_char(a.LAST_ACTIVE_TIME, 'mi') as mi,to_char(a.LAST_ACTIVE_TIME, 'mi') -mod(to_char(a.LAST_ACTIVE_TIME, 'mi'), 10) as new_mifrom v$sql awhere sql_id = 'gcsnqzu9q0004';
SQL_ID TIM MI NEW_MI
gcsnqzu9q0004 2023-2-13 22:14:00 14 10那么铺垫做完了，这个需求的最终实现sql如下：
SQL> with t as2 (select sql_id,3 trunc(a.LAST_ACTIVE_TIME, 'mi') -4 mod(to_char(a.LAST_ACTIVE_TIME, 'mi'), 10) / 24 / 60 as new_tim5 from v$sql a)6 select * from (7 select new_tim, count(*) from t group by new_tim order by new_tim desc nulls last8 )where rownum<=10;NEW_TIM COUNT(*)
----------- ----------
2023-2-15 19:00:00 476
2023-2-15 18:50:00 44
2023-2-15 18:40:00 20
2023-2-15 18:30:00 21
2023-2-15 18:20:00 52
2023-2-15 18:10:00 4
2023-2-15 18:00:00 7
2023-2-15 17:50:00 2
2023-2-15 17:40:00 7
2023-2-15 17:30:00 210 rows selectedSQL> 七、突发奇想，大佬你能否用一条sql打印一年的日历出来，我懵逼了😫😭😤🤯
大家看到这个需求会不会有点懵逼？用SQL竟然能打印一张全年的日历？？
其实我们可以枚举所有月份所有的日期,并转换为对应的月、周信息,再按所在周做一次“行转列”即可。
我们先来了解一下Oracle的日期转换函数的使用小案例：
SQL> WITH x AS2 (SELECT to_date('2013-12-27', 'yyyy-mm-dd') + (LEVEL - 1) AS d3 FROM dual4 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 星期二 01SQL>
我们从上面获得了周信息与周对应年中属于第几周，这里有一个小问题，2013-12-30那一周应该是第53周的，却被算到了第二年的第一周。
这种数据需要用case when来处理。
SQL>
SQL> WITH x AS2 (SELECT to_date('2013-12-27', 'yyyy-mm-dd') + (LEVEL - 1) AS d3 FROM dual4 CONNECT BY LEVEL <= 5),5 x1 as6 (SELECT d,7 to_char(d, 'day') AS DAY,8 to_char(d, 'mm') AS mm,9 to_char(d, 'iw') AS iw10 FROM x)11 select d,12 day,13 mm,14 iw,15 case16 when mm = 12 and iw = '01' then17 '53'18 else19 iw20 end as new_iw21 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 53SQL>
于是全年日历可查询为：
SQL> with t as2 (select trunc(sysdate, 'y') as 本年年初,3 add_months(trunc(sysdate, 'y'), 12) as 下年初4 from dual),5 t1 as6 (select 本年年初 + (level - 1) as 日期7 from t8 connect by level <= 下年初 - 本年年初),9 t2 as10 (select 日期,11 to_char(日期, 'mm') as 月份,12 to_char(日期, 'iw') 所在周,13 to_number(to_char(日期, 'd')) as 周几14 from t1),15 t3 as16 (select 日期,17 月份,18 case19 when 月份 = 12 and 所在周 = '01' then20 '53'21 else22 所在周23 end as 所在周,24 周几25 from t2)26 select case27 when lag(月份) over(order by 所在周) = 月份 then28 null29 else30 月份31 end as 月份,32 所在周,33 max(case 周几34 when 2 then35 日期36 end) 周一,37 max(case 周几38 when 3 then39 日期40 end) 周二,41 max(case 周几42 when 4 then43 日期44 end) 周三,45 max(case 周几46 when 5 then47 日期48 end) 周四,49 max(case 周几50 when 6 then51 日期52 end) 周五,53 max(case 周几54 when 7 then55 日期56 end) 周六,57 max(case 周几58 when 1 then59 日期60 end) 周天61 from t362 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-802 2023-1-9 2023-1-10 2023-1-11 2023-1-12 2023-1-13 2023-1-14 2023-1-1503 2023-1-16 2023-1-17 2023-1-18 2023-1-19 2023-1-20 2023-1-21 2023-1-2204 2023-1-23 2023-1-24 2023-1-25 2023-1-26 2023-1-27 2023-1-28 2023-1-2905 2023-1-30 2023-1-31
02 05 2023-2-1 2023-2-2 2023-2-3 2023-2-4 2023-2-506 2023-2-6 2023-2-7 2023-2-8 2023-2-9 2023-2-10 2023-2-11 2023-2-1207 2023-2-13 2023-2-14 2023-2-15 2023-2-16 2023-2-17 2023-2-18 2023-2-1908 2023-2-20 2023-2-21 2023-2-22 2023-2-23 2023-2-24 2023-2-25 2023-2-2609 2023-2-27 2023-2-28
03 09 2023-3-1 2023-3-2 2023-3-3 2023-3-4 2023-3-510 2023-3-6 2023-3-7 2023-3-8 2023-3-9 2023-3-10 2023-3-11 2023-3-1211 2023-3-13 2023-3-14 2023-3-15 2023-3-16 2023-3-17 2023-3-18 2023-3-1912 2023-3-20 2023-3-21 2023-3-22 2023-3-23 2023-3-24 2023-3-25 2023-3-2613 2023-3-27 2023-3-28 2023-3-29 2023-3-30 2023-3-31
04 13 2023-4-1 2023-4-214 2023-4-3 2023-4-4 2023-4-5 2023-4-6 2023-4-7 2023-4-8 2023-4-915 2023-4-10 2023-4-11 2023-4-12 2023-4-13 2023-4-14 2023-4-15 2023-4-1616 2023-4-17 2023-4-18 2023-4-19 2023-4-20 2023-4-21 2023-4-22 2023-4-2317 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-719 2023-5-8 2023-5-9 2023-5-10 2023-5-11 2023-5-12 2023-5-13 2023-5-1420 2023-5-15 2023-5-16 2023-5-17 2023-5-18 2023-5-19 2023-5-20 2023-5-2121 2023-5-22 2023-5-23 2023-5-24 2023-5-25 2023-5-26 2023-5-27 2023-5-2822 2023-5-29 2023-5-30 2023-5-31
06 22 2023-6-1 2023-6-2 2023-6-3 2023-6-423 2023-6-5 2023-6-6 2023-6-7 2023-6-8 2023-6-9 2023-6-10 2023-6-1124 2023-6-12 2023-6-13 2023-6-14 2023-6-15 2023-6-16 2023-6-17 2023-6-1825 2023-6-19 2023-6-20 2023-6-21 2023-6-22 2023-6-23 2023-6-24 2023-6-2526 2023-6-26 2023-6-27 2023-6-28 2023-6-29 2023-6-30
07 26 2023-7-1 2023-7-227 2023-7-3 2023-7-4 2023-7-5 2023-7-6 2023-7-7 2023-7-8 2023-7-928 2023-7-10 2023-7-11 2023-7-12 2023-7-13 2023-7-14 2023-7-15 2023-7-1629 2023-7-17 2023-7-18 2023-7-19 2023-7-20 2023-7-21 2023-7-22 2023-7-2330 2023-7-24 2023-7-25 2023-7-26 2023-7-27 2023-7-28 2023-7-29 2023-7-3031 2023-7-31
08 31 2023-8-1 2023-8-2 2023-8-3 2023-8-4 2023-8-5 2023-8-632 2023-8-7 2023-8-8 2023-8-9 2023-8-10 2023-8-11 2023-8-12 2023-8-1333 2023-8-14 2023-8-15 2023-8-16 2023-8-17 2023-8-18 2023-8-19 2023-8-2034 2023-8-21 2023-8-22 2023-8-23 2023-8-24 2023-8-25 2023-8-26 2023-8-2735 2023-8-28 2023-8-29 2023-8-30 2023-8-31
09 35 2023-9-1 2023-9-2 2023-9-336 2023-9-4 2023-9-5 2023-9-6 2023-9-7 2023-9-8 2023-9-9 2023-9-1037 2023-9-11 2023-9-12 2023-9-13 2023-9-14 2023-9-15 2023-9-16 2023-9-1738 2023-9-18 2023-9-19 2023-9-20 2023-9-21 2023-9-22 2023-9-23 2023-9-2439 2023-9-25 2023-9-26 2023-9-27 2023-9-28 2023-9-29 2023-9-30
10 39 2023-10-140 2023-10-2 2023-10-3 2023-10-4 2023-10-5 2023-10-6 2023-10-7 2023-10-841 2023-10-9 2023-10-10 2023-10-11 2023-10-12 2023-10-13 2023-10-14 2023-10-1542 2023-10-16 2023-10-17 2023-10-18 2023-10-19 2023-10-20 2023-10-21 2023-10-2243 2023-10-23 2023-10-24 2023-10-25 2023-10-26 2023-10-27 2023-10-28 2023-10-2944 2023-10-30 2023-10-31
11 44 2023-11-1 2023-11-2 2023-11-3 2023-11-4 2023-11-545 2023-11-6 2023-11-7 2023-11-8 2023-11-9 2023-11-10 2023-11-11 2023-11-1246 2023-11-13 2023-11-14 2023-11-15 2023-11-16 2023-11-17 2023-11-18 2023-11-1947 2023-11-20 2023-11-21 2023-11-22 2023-11-23 2023-11-24 2023-11-25 2023-11-2648 2023-11-27 2023-11-28 2023-11-29 2023-11-30
12 48 2023-12-1 2023-12-2 2023-12-349 2023-12-4 2023-12-5 2023-12-6 2023-12-7 2023-12-8 2023-12-9 2023-12-1050 2023-12-11 2023-12-12 2023-12-13 2023-12-14 2023-12-15 2023-12-16 2023-12-1751 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-3163 rows selectedSQL>
通过本例可以看到,使用with语句可以让你的思路及代码展示得非常清晰,你可以很方便地检查t,t1,t2,t3各步是否达到了预期目的,这就是with语句的作用之一。
总结
以上就是2月份广大网友或同事咨询的关于时间操作相关的问题总结！
