大连网站建设价格低,wordpress返回顶部图标,阿里云服务器做网站,专业网站开发哪家专业文章目录前言一、我如何快速确定今年是否是闰年的#x1f623;二、 我如何从DATE类型数据获取年、月(月初月末)、周、日、时、分、秒信息#x1f92f;三、我如何快速查到本月月初第一周的周一和本月最后一周周一是在几号#x1f611;四、我如何快速确定每个季度的开始和…
文章目录前言一、我如何快速确定今年是否是闰年的二、 我如何从DATE类型数据获取年、月(月初月末)、周、日、时、分、秒信息三、我如何快速查到本月月初第一周的周一和本月最后一周周一是在几号四、我如何快速确定每个季度的开始和结束日期五、领导让统计每个月招聘的人数但是有的月份没招人也就没在数据库记录当月招聘信息我怎么统计出来没有的月份六、领导让每隔十分钟统计一次数据库登录人数我该怎么统计啊七、突发奇想大佬你能否用一条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 hiredate30/24/60/6020/24/605/24 AS hiredate FROM emp WHERE ROWNUM1);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号因为这个月12号是上个月最后一周的周六、周日
其次是计算本月最后一个周一无非也是两种情况
最后一个周一恰好是月末最后一天最后一个周一是月末的前几天但是肯定是当月最后一天的前七天内
上面两个分析逻辑总结一句话 其实计算当月内第一个星期一与最后一个星期一,我们只需要分别找上月末及当月末之前七天的下一周周一即可。
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 empno1001)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 rownum5;
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_idgcsnqzu9q0004
SQL_ID TIM MI
gcsnqzu9q0004 2023-2-13 22:14:00 142、对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 rownum10;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 5318 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 5321 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语句的作用之一。 总结
提示这里对文章进行总结 例如以上就是今天要讲的内容本文仅仅简单介绍了pandas的使用而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。
