系列文章目录
【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开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点
【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件
【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？
【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重
【SQL开发实战技巧】系列（三十三）：数仓报表场景☞从不固定位置提取字符串的元素以及搜索满足字母在前数字在后等条件的数据
【SQL开发实战技巧】系列（三十四）：数仓报表场景☞如何对数据分级并行转为列
【SQL开发实战技巧】系列（三十五）：数仓报表场景☞根据条件返回不同列的数据以及Left /Full Join注意事项
【SQL开发实战技巧】系列（三十六）：数仓报表场景☞整理垃圾数据：查找数据的连续性时间和重叠时间的关系，初始化开始结束时间
前言
本篇文章讲解的主要内容是：从整理人员签到信息记录表的垃圾数据来再次体会一下隐藏数据列信息以及查找数据的连续性时间和重叠时间的实战案例
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、整理垃圾数据：查找数据的连续性时间和重叠时间的关系，初始化开始结束时间
现在有下面一堆脏数据，是人员签到信息记录表，如下：
CREATE OR REPLACE VIEW people(人员编号,开始时间,结束时间,类型,数值id)AS
SELECT 11, to_date('201305', 'yyyymm'), to_date('201308', 'yyyymm'), 1, 1
FROM dual
UNION ALL
SELECT 11, to_date('201307', 'yyyymm'), NULL, 1, 2
FROM dual
UNION ALL
SELECT 11, to_date('201301', 'yyyymm'), NULL, -1, 3
FROM dual
UNION ALL
SELECT 11, to_date('201312', 'yyyymm'), NULL, 1, 4
FROM dual
UNION ALL
SELECT 22, to_date('201305', 'yyyymm'), to_date('201306', 'yyyymm'), 1, 1
FROM dual
UNION ALL
SELECT 22, to_date('201308', 'yyyymm'), to_date('201309', 'yyyymm'), 1, 2
FROM dual
UNION ALL
SELECT 22, to_date('201312', 'yyyymm'), to_date('201312', 'yyyymm'), -1, 3
FROM dual
UNION ALL
SELECT 22, to_date('201403', 'yyyymm'), NULL, 1, 4
FROM dual
UNION ALL
SELECT 22, to_date('201405', 'yyyymm'), NULL, -1, 4
FROM dual
UNION ALL
SELECT 33, to_date('201305', 'yyyymm'), to_date('201305', 'yyyymm'), 1, 1
FROM dual
UNION ALL
SELECT 33, to_date('201307', 'yyyymm'), to_date('201307', 'yyyymm'), 1, 2
from dual
union all
SELECT 33, to_date('201310', 'yyyymm'), NULL, -1, 3
FROM dual
UNION ALL
SELECT 33, to_date('201312', 'yyyymm'), NULL, 1, 4
FROM dual;
SQL> select *from people;
人员编号 开始时间 结束时间 类型 数值ID
---------- ----------- ----------- ---------- ----------
11 2013-5-1 2013-8-1 1 1
11 2013-7-1 1 2
11 2013-1-1 -1 3
11 2013-12-1 1 4
22 2013-5-1 2013-6-1 1 1
22 2013-8-1 2013-9-1 1 2
22 2013-12-1 2013-12-1 -1 3
22 2014-3-1 1 4
22 2014-5-1 -1 4
33 2013-5-1 2013-5-1 1 1
33 2013-7-1 2013-7-1 1 2
33 2013-10-1 -1 3
33 2013-12-1 1 4
13 rows selected
上面的数据是杂乱的，要求清洗上面数据，得到如下数据：
人员编号 区间
---------- -----------
11 201312--NULL
22 201305--201306
22 201308--201309
22 201403--201404
33 201305--201305
33 201307--201307
33 201312--NULL
由于上面数据质量不高，现在提出数据清洗规则需求如下：
1、当类型为"-1"时,数据丢弃。
2、当类型为"-1",且其前一行"结束时间"为空值时,"开始时间-1"当作其前一行的结束时间。
3、如果后面的时间比前面的时间早,则覆盖前面的时间,不能覆盖的时间要保留。
4、时段重叠的要合并为一行。
数据乱,需求也就复杂。
首先要取出对应的数据,刚开始写语句时可能不知道从何下手,没关系，我们一步步分析：
select 人员编号,
with t as (
select 人员编号,
开始时间,
min(开始时间) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp开始时间,
min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp_min开始时间,
coalesce(min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end)
over(partition by 人员编号 order by 数值id rows between 1
following and unbounded following),
开始时间 + 1) as min_开始时间,
结束时间 as tmp_结束时间,
case
when 结束时间 is null and
(lead(类型) over(partition by 人员编号 order by 数值id)) = -1 then
add_months(lead(开始时间) over(partition by 人员编号 order by 数值id),
-1)
else
结束时间
end as 结束时间,
类型,
数值ID,
max(数值id) over(partition by 人员编号) as max_id
from people
）
select * from t;
人员编号 开始时间 TMP开始时间 TMP_MIN开始时间 MIN_开始时间 TMP_结束时间 结束时间 类型 数值ID MAX_ID
---------- ----------- ----------- ----------- ----------- ----------- ----------- ---------- ---------- ----------
11 2013-5-1 2013-1-1 2012-12-1 2012-12-1 2013-8-1 2013-8-1 1 1 4
11 2013-7-1 2013-1-1 2012-12-1 2012-12-1 2012-12-1 1 2 4
11 2013-1-1 2013-12-1 2013-12-1 2013-12-1 -1 3 4
11 2013-12-1 2013-12-2 1 4 4
22 2013-5-1 2013-8-1 2013-8-1 2013-8-1 2013-6-1 2013-6-1 1 1 4
22 2013-8-1 2013-12-1 2013-11-1 2013-11-1 2013-9-1 2013-9-1 1 2 4
22 2013-12-1 2014-3-1 2014-3-1 2014-3-1 2013-12-1 2013-12-1 -1 3 4
22 2014-3-1 2014-5-1 2014-4-1 2014-4-1 2014-4-1 1 4 4
22 2014-5-1 2014-5-2 -1 4 4
33 2013-5-1 2013-7-1 2013-7-1 2013-7-1 2013-5-1 2013-5-1 1 1 4
33 2013-7-1 2013-10-1 2013-9-1 2013-9-1 2013-7-1 2013-7-1 1 2 4
33 2013-10-1 2013-12-1 2013-12-1 2013-12-1 -1 3 4
33 2013-12-1 2013-12-2 1 4 4
13 rows selected
上面标识了"开始时间"（见第三条）与"结束时间"（见第二条）分别处理的过程。
with t as (
select 人员编号,
开始时间,
min(开始时间) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp开始时间,
min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp_min开始时间,
coalesce(min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end)
over(partition by 人员编号 order by 数值id rows between 1
following and unbounded following),
开始时间 + 1) as min_开始时间,--当前人员的最小日期，覆盖用
结束时间 as tmp_结束时间,
case
when 结束时间 is null and
(lead(类型) over(partition by 人员编号 order by 数值id)) = -1 then
add_months(lead(开始时间) over(partition by 人员编号 order by 数值id),
-1)
else
结束时间
end as 结束时间,
类型,
数值ID,
max(数值id) over(partition by 人员编号) as max_id
from people
）
select 人员编号,
开始时间,
min_开始时间,
结束时间,
类型,
数值ID,
max_id,
case--生成区间是否重叠的标识，合并时段时用
when (lag(结束时间) over(partition by 人员编号 order by 数值id)) <
add_months(开始时间, -1) then
1
when (lag(类型) over(partition by 人员编号 order by 数值id)) = 1 then
null
else
1
end as so
from t;
人员编号 开始时间 MIN_开始时间 结束时间 类型 数值ID MAX_ID SO
---------- ----------- ----------- ----------- ---------- ---------- ---------- ----------
11 2013-5-1 2012-12-1 2013-8-1 1 1 4 1
11 2013-7-1 2012-12-1 2012-12-1 1 2 4
11 2013-1-1 2013-12-1 -1 3 4
11 2013-12-1 2013-12-2 1 4 4 1
22 2013-5-1 2013-8-1 2013-6-1 1 1 4 1
22 2013-8-1 2013-11-1 2013-9-1 1 2 4 1
22 2013-12-1 2014-3-1 2013-12-1 -1 3 4 1
22 2014-3-1 2014-4-1 2014-4-1 1 4 4 1
22 2014-5-1 2014-5-2 -1 4 4
33 2013-5-1 2013-7-1 2013-5-1 1 1 4 1
33 2013-7-1 2013-9-1 2013-7-1 1 2 4 1
33 2013-10-1 2013-12-1 -1 3 4 1
33 2013-12-1 2013-12-2 1 4 4 1
13 rows selected
生成分组标识,如果后录入的数据开始时间更早,就说明前面录入的是无用的数据,要丢弃,如果范围重叠,就修正前面的结束时间。
with t as (
select 人员编号,
开始时间,
min(开始时间) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp开始时间,
min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp_min开始时间,
coalesce(min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end)
over(partition by 人员编号 order by 数值id rows between 1
following and unbounded following),
开始时间 + 1) as min_开始时间,--当前人员的最小日期，覆盖用
结束时间 as tmp_结束时间,
case
when 结束时间 is null and
(lead(类型) over(partition by 人员编号 order by 数值id)) = -1 then
add_months(lead(开始时间) over(partition by 人员编号 order by 数值id),
-1)
else
结束时间
end as 结束时间,
类型,
数值ID,
max(数值id) over(partition by 人员编号) as max_id
from people
),
t1 as (
select 人员编号,
开始时间,
min_开始时间,
结束时间,
类型,
数值ID,
max_id,
case--生成区间是否重叠的标识，合并时段时用
when (lag(结束时间) over(partition by 人员编号 order by 数值id)) <
add_months(开始时间, -1) then
1
when (lag(类型) over(partition by 人员编号 order by 数值id)) = 1 then
null
else
1
end as so
from t)
select 人员编号,
数值ID,
max_id,
类型,
sum(so) over(partition by 人员编号 order by 数值id) as so,/*累加标识,生成分组合并依据*/
开始时间,
min_开始时间,
case/*根据最前面生成的时间覆盖对应的时段*/
when min_开始时间 < 结束时间 and min_开始时间 >= 开始时间 then
min_开始时间
else
结束时间
end as 结束时间
from t1
where 类型 = 1
/*如果开始时间比这还小,就丢弃吧*/
and 开始时间 <= min_开始时间;
人员编号 数值ID MAX_ID 类型 SO 开始时间 MIN_开始时间 结束时间
---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------
11 4 4 1 1 2013-12-1 2013-12-2
22 1 4 1 1 2013-5-1 2013-8-1 2013-6-1
22 2 4 1 2 2013-8-1 2013-11-1 2013-9-1
22 4 4 1 3 2014-3-1 2014-4-1 2014-4-1
33 1 4 1 1 2013-5-1 2013-7-1 2013-5-1
33 2 4 1 2 2013-7-1 2013-9-1 2013-7-1
33 4 4 1 3 2013-12-1 2013-12-2
7 rows selected
合并数据,语句如下：
/*人员编号 NUMBER Y
开始时间 DATE Y
结束时间 DATE Y
类型 NUMBER Y
数值ID NUMBER Y
1、当类型为"-l"时,数据丢弃。
2、当类型为"-1",且其前一行"结束时间"为空值时,"开始时间-1"当作其前一行的结束时间。
3、如果后面的时间比前面的时间早,则覆盖前面的时间,不能覆盖的时间要保留。
5、时段重叠的要合并为一行。
*/
with t as (
select 人员编号,
开始时间,
min(开始时间) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp开始时间,
min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp_min开始时间,
coalesce(min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end)
over(partition by 人员编号 order by 数值id rows between 1
following and unbounded following),
开始时间 + 1) as min_开始时间,--当前人员的最小日期，覆盖用
结束时间 as tmp_结束时间,
case
when 结束时间 is null and
(lead(类型) over(partition by 人员编号 order by 数值id)) = -1 then
add_months(lead(开始时间) over(partition by 人员编号 order by 数值id),
-1)
else
结束时间
end as 结束时间,
类型,
数值ID,
max(数值id) over(partition by 人员编号) as max_id
from people
),
t1 as (
select 人员编号,
开始时间,
min_开始时间,
结束时间,
类型,
数值ID,
max_id,
case--生成区间是否重叠的标识，合并时段时用
when (lag(结束时间) over(partition by 人员编号 order by 数值id)) <
add_months(开始时间, -1) then
1
when (lag(类型) over(partition by 人员编号 order by 数值id)) = 1 then
null
else
1
end as so
from t),
t2 as (
select 人员编号,
数值ID,
max_id,
类型,
sum(so) over(partition by 人员编号 order by 数值id) as so,/*累加标识,生成分组合并依据*/
开始时间,
min_开始时间,
case/*根据最前面生成的时间覆盖对应的时段*/
when min_开始时间 < 结束时间 and min_开始时间 >= 开始时间 then
min_开始时间
else
结束时间
end as 结束时间
from t1
where 类型 = 1
/*如果开始时间比这还小,就丢弃吧*/
and 开始时间 <= min_开始时间)
select 人员编号,
max_id,
max(数值ID) as max_id2,
sum(类型) as 类型,
min(开始时间) keep(dense_rank first order by 数值id) as 开始时间,
max(开始时间) keep(dense_rank last order by 数值id) as 结束时间
from t2
group by 人员编号, so, max_id;
人员编号 MAX_ID MAX_ID2 类型 开始时间 结束时间
---------- ---------- ---------- ---------- ----------- -----------
11 4 4 1 2013-12-1 2013-12-1
22 4 1 1 2013-5-1 2013-5-1
22 4 2 1 2013-8-1 2013-8-1
22 4 4 1 2014-3-1 2014-3-1
33 4 1 1 2013-5-1 2013-5-1
33 4 2 1 2013-7-1 2013-7-1
33 4 4 1 2013-12-1 2013-12-1
7 rows selected
最后一步是过滤,语句如下：
with t as (
select 人员编号,
开始时间,
min(开始时间) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp开始时间,
min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end) over(partition by 人员编号 order by 数值id rows between 1 following and unbounded following) as tmp_min开始时间,
coalesce(min(case
when 类型 = -1 then
add_months(开始时间, -1)
else
开始时间
end)
over(partition by 人员编号 order by 数值id rows between 1
following and unbounded following),
开始时间 + 1) as min_开始时间,--当前人员的最小日期，覆盖用
结束时间 as tmp_结束时间,
case
when 结束时间 is null and
(lead(类型) over(partition by 人员编号 order by 数值id)) = -1 then
add_months(lead(开始时间) over(partition by 人员编号 order by 数值id),
-1)
else
结束时间
end as 结束时间,
类型,
数值ID,
max(数值id) over(partition by 人员编号) as max_id
from people
),
t1 as (
select 人员编号,
开始时间,
min_开始时间,
结束时间,
类型,
数值ID,
max_id,
case--生成区间是否重叠的标识，合并时段时用
when (lag(结束时间) over(partition by 人员编号 order by 数值id)) <
add_months(开始时间, -1) then
1
when (lag(类型) over(partition by 人员编号 order by 数值id)) = 1 then
null
else
1
end as so
from t),
t2 as (
select 人员编号,
数值ID,
max_id,
类型,
sum(so) over(partition by 人员编号 order by 数值id) as so,/*累加标识,生成分组合并依据*/
开始时间,
min_开始时间,
case/*根据最前面生成的时间覆盖对应的时段*/
when min_开始时间 < 结束时间 and min_开始时间 >= 开始时间 then
min_开始时间
else
结束时间
end as 结束时间
from t1
where 类型 = 1
/*如果开始时间比这还小,就丢弃吧*/
and 开始时间 <= min_开始时间),
t3 as (
select 人员编号,
max_id,
max(数值ID) as max_id2,
sum(类型) as 类型,
min(开始时间) keep(dense_rank first order by 数值id) as 开始时间,
max(开始时间) keep(dense_rank last order by 数值id) as 结束时间
from t2
group by 人员编号, so, max_id)
select 人员编号,to_char(开始时间,'yyyymm')||'--'||to_char(结束时间,'yyyymm') as str
from t3
where (max_id=max_id2 or 开始时间<=结束时间)
and 类型>-1;
人员编号 STR
---------- --------------------------------------------------------------------------------
11 201312--201312
22 201305--201305
22 201308--201308
22 201403--201403
33 201305--201305
33 201307--201307
33 201312--201312
7 rows selected
到这一步就可以了。整理数据是最考验耐心的工作,特别是需求还不确定的时候。文章来源：https://www.toymoban.com/news/detail-438058.html
总结
本篇文章讲解的主要内容是：从整理人员签到信息记录表的垃圾数据来再次体会一下隐藏数据列信息以及查找数据的连续性时间和重叠时间的实战案例文章来源地址https://www.toymoban.com/news/detail-438058.html
到了这里，关于【SQL开发实战技巧】系列（三十六）：数仓报表场景☞整理垃圾数据：查找数据的连续性时间和重叠时间的关系，初始化开始结束时间的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
