系列文章目录
【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开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【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面试也能游刃有余~。
一、识别重叠的日期范围
下面是一个有关工程的明细数据：
createorreplaceview zyd asSELECT7782 empno,'CLARK'as ename,1as proj_id,date'2023-06-16' proj_start,date'2023-06-18' proj_end from dual unionallSELECT7782 empno,'CLARK'as ename,4as proj_id,date'2023-06-19' proj_start,date'2023-06-24' proj_end from dual unionallSELECT7782 empno,'CLARK'as ename,7as proj_id,date'2023-06-22' proj_start,date'2023-06-25' proj_end from dual unionallSELECT7782 empno,'CLARK'as ename,10as proj_id,date'2023-06-25' proj_start,date'2023-06-28' proj_end from dual unionallSELECT7782 empno,'CLARK'as ename,13as proj_id,date'2023-06-28' proj_start,date'2023-07-02' proj_end from dual unionallSELECT7839 empno,'KING'as ename,2as proj_id,date'2023-06-17' proj_start,date'2023-06-21' proj_end from dual unionallSELECT7839 empno,'KING'as ename,8as proj_id,date'2023-06-23' proj_start,date'2023-06-25' proj_end from dual unionallSELECT7839 empno,'KING'as ename,14as proj_id,date'2023-06-29' proj_start,date'2023-06-30' proj_end from dual unionallSELECT7839 empno,'KING'as ename,11as proj_id,date'2023-06-26' proj_start,date'2023-06-27' proj_end from dual unionallSELECT7839 empno,'KING'as ename,5as proj_id,date'2023-06-20' proj_start,date'2023-06-24' proj_end from dual unionallSELECT7934 empno,'MILLER'as ename,3as proj_id,date'2023-06-18' proj_start,date'2023-06-22' proj_end from dual unionallSELECT7934 empno,'MILLER'as ename,12as proj_id,date'2023-06-27' proj_start,date'2023-06-28' proj_end from dual unionallSELECT7934 empno,'MILLER'as ename,15as proj_id,date'2023-06-30' proj_start,date'2023-07-03' proj_end from dual unionallSELECT7934 empno,'MILLER'as ename,9as proj_id,date'2023-06-24' proj_start,date'2023-06-27' proj_end from dual unionallSELECT7934 empno,'MILLER'as ename,6as proj_id,date'2023-06-21' proj_start,date'2023-06-23' proj_end from dual;select*from zyd;
EMPNO ENAME PROJ_ID PROJ_START PROJ_END
---------- ------ ---------- ----------- -----------7782 CLARK 12023-6-162023-6-187782 CLARK 42023-6-192023-6-247782 CLARK 72023-6-222023-6-257782 CLARK 102023-6-252023-6-287782 CLARK 132023-6-282023-7-27839 KING 22023-6-172023-6-217839 KING 82023-6-232023-6-257839 KING 142023-6-292023-6-307839 KING 112023-6-262023-6-277839 KING 52023-6-202023-6-247934 MILLER 32023-6-182023-6-227934 MILLER 122023-6-272023-6-287934 MILLER 152023-6-302023-7-37934 MILLER 92023-6-242023-6-277934 MILLER 62023-6-212023-6-2315rows selected
通过数据可以看到,有很多员工在旧的工程结束之前就开始了新的工程（如员工 7782 的工程4结束日期是6月24日,而工程7开始日期是6月22日）,现要求返回这些工程时间重复的数据。
前面介绍了Oracle中有两个分析函数
LAG
和
LEAD
,分别用于访问结果集中的前一行和后一行。我们可以用分析函数
LAG
取得员工各自的上一个工程的结束日期及工程号,然后与当前工程相比较。
- 1、取信息
SQL>select empno,2 ename,3 proj_id as 工程号,4 proj_start as 开始日期,5 proj_end as 结束日期,6 lag(proj_end)over(partitionby empno orderby proj_start)as 上一工程结束日期,7 lag(proj_id)over(partitionby empno orderby proj_start)as 上一工程号
8from zyd;
EMPNO ENAME 工程号 开始日期 结束日期 上一工程结束日期 上一工程号
---------- ------ ---------- ----------- ----------- ----------- ----------7782 CLARK 12023-6-162023-6-187782 CLARK 42023-6-192023-6-242023-6-1817782 CLARK 72023-6-222023-6-252023-6-2447782 CLARK 102023-6-252023-6-282023-6-2577782 CLARK 132023-6-282023-7-22023-6-28107839 KING 22023-6-172023-6-217839 KING 52023-6-202023-6-242023-6-2127839 KING 82023-6-232023-6-252023-6-2457839 KING 112023-6-262023-6-272023-6-2587839 KING 142023-6-292023-6-302023-6-27117934 MILLER 32023-6-182023-6-227934 MILLER 62023-6-212023-6-232023-6-2237934 MILLER 92023-6-242023-6-272023-6-2367934 MILLER 122023-6-272023-6-282023-6-2797934 MILLER 152023-6-302023-7-32023-6-281215rows selected
这里增加了partitionby empno这样就可以对数据分组进行分析,不同的empno之间互不影响。
- 2、比较
SQL>with t as2(select empno,3 ename,4 proj_id as 工程号,5 proj_start as 开始日期,6 proj_end as 结束日期,7 lag(proj_end)over(partitionby empno orderby proj_start)as 上一工程结束日期,8 lag(proj_id)over(partitionby empno orderby proj_start)as 上一工程号
9from zyd)10select t.empno,11 t.ename,12 t.工程号,13 t.开始日期,14 t.结束日期,15case16when 上一工程结束日期 >= 开始日期 then17'工程'|| lpad(工程号,2,'0')||'与工程'|| lpad(上一工程号,2,'0')||'重复'18endas 描述
19from t;
EMPNO ENAME 工程号 开始日期 结束日期 描述
---------- ------ ---------- ----------- ----------- --------------------------------------------------------------------------------7782 CLARK 12023-6-162023-6-187782 CLARK 42023-6-192023-6-247782 CLARK 72023-6-222023-6-25 工程07与工程04重复
7782 CLARK 102023-6-252023-6-28 工程10与工程07重复
7782 CLARK 132023-6-282023-7-2 工程13与工程10重复
7839 KING 22023-6-172023-6-217839 KING 52023-6-202023-6-24 工程05与工程02重复
7839 KING 82023-6-232023-6-25 工程08与工程05重复
7839 KING 112023-6-262023-6-277839 KING 142023-6-292023-6-307934 MILLER 32023-6-182023-6-227934 MILLER 62023-6-212023-6-23 工程06与工程03重复
7934 MILLER 92023-6-242023-6-277934 MILLER 122023-6-272023-6-28 工程12与工程09重复
7934 MILLER 152023-6-302023-7-315rows 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:00142、对14和10取余
SQL>selectmod(14,10)from dual;MOD(14,10)----------4SQL>
- 2、对比上面结果，我们可以知道如果想计算整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>
总结
本篇文章讲解的主要内容是：如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数
版权归原作者 赵延东的一亩三分地 所有， 如有侵权，请联系我们删除。