0
0

从一个SQL打印全年日历漫谈数据仓库中时间操作场景的重点写法

文章目录

前言

在本月工作及与网友互动的SQL开发问题中，大家经常会问到时间处理的问题，比如下面几个问题就是大家最常问问题：

  1. 我如何快速确定今年是否是闰年的😣
  2. 我如何从DATE类型数据获取年、月(月初&月末)、周、日、时、分、秒信息🤯
  3. 我如何快速查到本月月初第一周的周一和本月最后一周周一是在几号😑
  4. 我如何快速确定每个季度的开始和结束日期😫
  5. 领导让统计每个月招聘的人数，但是有的月份没招人也就没在数据库记录当月招聘信息，我怎么统计出来没有的月份😤
  6. 领导让每隔十分钟统计一次数据库登录人数，我该怎么统计啊🙄
  7. 突发奇想，大佬你能否用一条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 日
  5from dual;

年初        二月初      二月底      日
----------- ----------- ----------- ---------------------------------------------------------------------------2023-1-12023-2-12023-2-2828

从上面结果立马知道了，今年是平年,这么计算是不是超级简单？？？

二、 我如何从DATE类型数据获取年、月(月初&月末)、周、日、时、分、秒信息🤯

经常看到有人因为不熟悉日期操作,获取相应信息的时候,要写很复杂的语句。下面举一个简单的例子。

SQL>select hiredate,2         to_date(to_char(hiredate,'yyyy-mm')||'-1','yyyy-mm-dd')as yuechu
  3from emp
  4where rownum <=1;

HIREDATE    YUECHU
----------- -----------1980-12-171980-12-1SQL>

其实要获取这个数据,只需要一个简单的函数就可以做到,而根本不需要多次转换：

SQL>SELECT hiredate AS 雇佣日期, trunc(hiredate,'mm')AS 月初
  2from emp
  3where rownum <=1;

雇佣日期    月初
----------- -----------1980-12-171980-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'）月份
 20FROM(SELECT hiredate+30/24/60/60+20/24/60+5/24AS hiredate FROM emp WHERE ROWNUM<=1);

HIREDATE             时          分          秒          日          月          年      年内第几天 一天之始    周初        周初        月初        月未        下月初      年初        周几                                                                        周几                                                                        月份                                                                        月份
----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------1980-12-1752030171219803521980-12-171980-12-141980-12-141980-12-11980-12-311981-1-11980-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 最后一周的周一
  3from dual;

第一周周一  最后一周的周一
----------- -----------2023-2-62023-2-27

当我给他这个sql的时候，他很是震惊，因为他们团队的开发DBA给他的是枚举SQL，写的很长！！！

四、我如何快速确定每个季度的开始和结束日期😫

这个问题，是几个做报表的网友问我的，生成汇总报表时候时常要求按季度分类汇总这就需要通过给定年份提取对应的季度信息，那怎么快速获取某个季度的开始结束日期呢？
我是这样给他的：

SQL>with t as2(select to_char(sysdate,'yyyy')as 年,levelas sn
  3from dual
  4connectbylevel<=45)6select sn as 季度,7(sn -1)*3+1as 开始月份,8         add_months(trunc(to_date(年,'yyyy'),'y'),(sn -1)*3)as 开始日期,9         add_months(trunc(to_date(年,'yyyy'),'y'), sn *3)-1as 结束日期
 10from t;

        季度       开始月份 开始日期    结束日期
---------- ---------- ----------- -----------112023-1-12023-3-31242023-4-12023-6-30372023-7-12023-9-304102023-10-12023-12-31SQL>

其实只要想清楚计算公式和日期的计算方法，就可以快速计算出来了。

五、领导让统计每个月招聘的人数，但是有的月份没招人也就没在数据库记录当月招聘信息，我怎么统计出来没有的月份😤

有时业务数据并不是连续的，比如考勤、比如网友说的招聘信息，可能在某一天或则某一月并没有相关操作，数据库也就没相关记录，而领导却要展示报表时候，这些日期的信息项都要展示出来用于后续分析统计，那我们该怎么统计呢？接下来用一个案例来一步步解释！
现在有下面员工信息

SQL>select empno,hiredate from emp orderby2;

EMPNO HIREDATE
----- -----------73691980-12-1774991981-2-2075211981-2-2275661981-4-276981981-5-177821981-6-978441981-9-876541981-9-2878391981-11-1779001981-12-379021981-12-379341982-1-2377881987-4-1978761987-5-2310012021-10-9115rows selected

有的年份没有招聘员工，这时按年份查询招聘人数结果如下：

SQL>select to_char(hiredate,'yyyy')asyear,count(*)as cnt
  2from emp
  3groupby to_char(hiredate,'yyyy')4orderby1;YEAR                                                                               CNT
--------------------------------------------------------------------------- ----------19801198110198211987220211SQL>

为了分析数据一般需要把表中没有的年份（如1983年）内的人数统计为0,这时就需要先根据表中的信息生成一个年份的枚举列表。

SQL>with t as2(select extract(yearfrommin(hiredate))as 开始年份,3           extract(yearfrommax(hiredate))as 结束年份
  4from emp where empno<>1001)5select 开始年份 +(level-1)as 年份
  6from t
  7connectbylevel<=((结束年份 - 开始年份)+1);

        年份
----------198019811982198319841985198619878rows selected

通过这个列表关联查询就可以得到所有年份的数据。

SQL>with t as2(select extract(yearfrommin(hiredate))as 开始年份,3           extract(yearfrommax(hiredate))as 结束年份
  4from emp
  5where empno <>1001),6  t1 as7(select 开始年份 +(level-1)as 年份
  8from t
  9connectbylevel<=((结束年份 - 开始年份)+1))10select t1.年份,count(emp.empno)as 聘用人数
 11from emp
 12rightjoin t1
 13on(extract(yearfrom emp.hiredate)= t1.年份)14groupby 年份
 15orderby1;

        年份       聘用人数
---------- ----------198011981101982119830198401985019860198728rows 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-1322:14:272023-2-153:00:592023-2-71:05:292023-2-81:05:302023-2-1515: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-1322:14:0014

2、对14和10取余

SQL>selectmod(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_mi
  from v$sql a
 where sql_id ='gcsnqzu9q0004';
SQL_ID    TIM    MI    NEW_MI
gcsnqzu9q0004    2023-2-1322:14:001410

那么铺垫做完了，这个需求的最终实现sql如下：

SQL>with t as2(select sql_id,3           trunc(a.LAST_ACTIVE_TIME,'mi')-4mod(to_char(a.LAST_ACTIVE_TIME,'mi'),10)/24/60as new_tim
  5from v$sql a)6select*from(7select new_tim,count(*)from t groupby new_tim  orderby new_tim desc nulls last8)where rownum<=10;

NEW_TIM       COUNT(*)----------- ----------2023-2-1519:00:004762023-2-1518:50:00442023-2-1518:40:00202023-2-1518:30:00212023-2-1518:20:00522023-2-1518:10:0042023-2-1518:00:0072023-2-1517:50:0022023-2-1517:40:0072023-2-1517:30:00210rows selected

SQL>

七、突发奇想，大佬你能否用一条sql打印一年的日历出来，我懵逼了😫😭😤🤯

大家看到这个需求会不会有点懵逼？用SQL竟然能打印一张全年的日历？？
其实我们可以枚举所有月份所有的日期,并转换为对应的月、周信息,再按所在周做一次“行转列”即可。
我们先来了解一下Oracle的日期转换函数的使用小案例：

SQL>WITH x AS2(SELECT to_date('2013-12-27','yyyy-mm-dd')+(LEVEL-1)AS d
  3FROM dual
  4CONNECTBYLEVEL<=5)5SELECT d, to_char(d,'day')ASDAY, to_char(d,'iw')AS iw FROM x;

D           DAY                                                                         IW
----------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------2013-12-27  星期五                                                                      522013-12-28  星期六                                                                      522013-12-29  星期日                                                                      522013-12-30  星期一                                                                      012013-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 d
  3FROM dual
  4CONNECTBYLEVEL<=5),5  x1 as6(SELECT d,7           to_char(d,'day')ASDAY,8           to_char(d,'mm')AS mm,9           to_char(d,'iw')AS iw
 10FROM x)11select d,12day,13         mm,14         iw,15case16when mm =12and iw ='01'then17'53'18else19            iw
 20endas new_iw
 21from x1;

D           DAY                                                                         MM                                                                          IW                                                                          NEW_IW
----------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------2013-12-27  星期五                                                                      1252522013-12-28  星期六                                                                      1252522013-12-29  星期日                                                                      1252522013-12-30  星期一                                                                      1201532013-12-31  星期二                                                                      120153SQL>

于是全年日历可查询为：

SQL>with t as2(select trunc(sysdate,'y')as 本年年初,3           add_months(trunc(sysdate,'y'),12)as 下年初
  4from dual),5  t1 as6(select 本年年初 +(level-1)as 日期
  7from t
  8connectbylevel<= 下年初 - 本年年初),9  t2 as10(select 日期,11           to_char(日期,'mm')as 月份,12           to_char(日期,'iw') 所在周,13           to_number(to_char(日期,'d'))as 周几
 14from t1),15  t3 as16(select 日期,17           月份,18case19when 月份 =12and 所在周 ='01'then20'53'21else22              所在周
 23endas 所在周,24           周几
 25from t2)26selectcase27when lag(月份)over(orderby 所在周)= 月份 then28null29else30            月份
 31endas 月份,32         所在周,33max(case 周几
 34when2then35                日期
 36end) 周一,37max(case 周几
 38when3then39                日期
 40end) 周二,41max(case 周几
 42when4then43                日期
 44end) 周三,45max(case 周几
 46when5then47                日期
 48end) 周四,49max(case 周几
 50when6then51                日期
 52end) 周五,53max(case 周几
 54when7then55                日期
 56end) 周六,57max(case 周几
 58when1then59                日期
 60end) 周天
 61from t3
 62groupby 月份, 所在周
 63orderby2;

月份                                                                        所在周                                                                      周一        周二        周三        周四        周五        周六        周天
--------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------01012023-1-22023-1-32023-1-42023-1-52023-1-62023-1-72023-1-8022023-1-92023-1-102023-1-112023-1-122023-1-132023-1-142023-1-15032023-1-162023-1-172023-1-182023-1-192023-1-202023-1-212023-1-22042023-1-232023-1-242023-1-252023-1-262023-1-272023-1-282023-1-29052023-1-302023-1-3102052023-2-12023-2-22023-2-32023-2-42023-2-5062023-2-62023-2-72023-2-82023-2-92023-2-102023-2-112023-2-12072023-2-132023-2-142023-2-152023-2-162023-2-172023-2-182023-2-19082023-2-202023-2-212023-2-222023-2-232023-2-242023-2-252023-2-26092023-2-272023-2-2803092023-3-12023-3-22023-3-32023-3-42023-3-5102023-3-62023-3-72023-3-82023-3-92023-3-102023-3-112023-3-12112023-3-132023-3-142023-3-152023-3-162023-3-172023-3-182023-3-19122023-3-202023-3-212023-3-222023-3-232023-3-242023-3-252023-3-26132023-3-272023-3-282023-3-292023-3-302023-3-3104132023-4-12023-4-2142023-4-32023-4-42023-4-52023-4-62023-4-72023-4-82023-4-9152023-4-102023-4-112023-4-122023-4-132023-4-142023-4-152023-4-16162023-4-172023-4-182023-4-192023-4-202023-4-212023-4-222023-4-23172023-4-242023-4-252023-4-262023-4-272023-4-282023-4-292023-4-3005182023-5-12023-5-22023-5-32023-5-42023-5-52023-5-62023-5-7192023-5-82023-5-92023-5-102023-5-112023-5-122023-5-132023-5-14202023-5-152023-5-162023-5-172023-5-182023-5-192023-5-202023-5-21212023-5-222023-5-232023-5-242023-5-252023-5-262023-5-272023-5-28222023-5-292023-5-302023-5-3106222023-6-12023-6-22023-6-32023-6-4232023-6-52023-6-62023-6-72023-6-82023-6-92023-6-102023-6-11242023-6-122023-6-132023-6-142023-6-152023-6-162023-6-172023-6-18252023-6-192023-6-202023-6-212023-6-222023-6-232023-6-242023-6-25262023-6-262023-6-272023-6-282023-6-292023-6-3007262023-7-12023-7-2272023-7-32023-7-42023-7-52023-7-62023-7-72023-7-82023-7-9282023-7-102023-7-112023-7-122023-7-132023-7-142023-7-152023-7-16292023-7-172023-7-182023-7-192023-7-202023-7-212023-7-222023-7-23302023-7-242023-7-252023-7-262023-7-272023-7-282023-7-292023-7-30312023-7-3108312023-8-12023-8-22023-8-32023-8-42023-8-52023-8-6322023-8-72023-8-82023-8-92023-8-102023-8-112023-8-122023-8-13332023-8-142023-8-152023-8-162023-8-172023-8-182023-8-192023-8-20342023-8-212023-8-222023-8-232023-8-242023-8-252023-8-262023-8-27352023-8-282023-8-292023-8-302023-8-3109352023-9-12023-9-22023-9-3362023-9-42023-9-52023-9-62023-9-72023-9-82023-9-92023-9-10372023-9-112023-9-122023-9-132023-9-142023-9-152023-9-162023-9-17382023-9-182023-9-192023-9-202023-9-212023-9-222023-9-232023-9-24392023-9-252023-9-262023-9-272023-9-282023-9-292023-9-3010392023-10-1402023-10-22023-10-32023-10-42023-10-52023-10-62023-10-72023-10-8412023-10-92023-10-102023-10-112023-10-122023-10-132023-10-142023-10-15422023-10-162023-10-172023-10-182023-10-192023-10-202023-10-212023-10-22432023-10-232023-10-242023-10-252023-10-262023-10-272023-10-282023-10-29442023-10-302023-10-3111442023-11-12023-11-22023-11-32023-11-42023-11-5452023-11-62023-11-72023-11-82023-11-92023-11-102023-11-112023-11-12462023-11-132023-11-142023-11-152023-11-162023-11-172023-11-182023-11-19472023-11-202023-11-212023-11-222023-11-232023-11-242023-11-252023-11-26482023-11-272023-11-282023-11-292023-11-3012482023-12-12023-12-22023-12-3492023-12-42023-12-52023-12-62023-12-72023-12-82023-12-92023-12-10502023-12-112023-12-122023-12-132023-12-142023-12-152023-12-162023-12-17512023-12-182023-12-192023-12-202023-12-212023-12-222023-12-232023-12-2401522023-1-112522023-12-252023-12-262023-12-272023-12-282023-12-292023-12-302023-12-3163rows selected

SQL>

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

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

标签： 数据仓库 时间连续性 闰年快速判断
本文转载自: https://blog.csdn.net/qq_28356739/article/details/129176883
版权归原作者 赵延东的一亩三分地 所有， 如有侵权，请联系我们删除。
登录后发布评论

“从一个SQL打印全年日历漫谈数据仓库中时间操作场景的重点写法”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航