系列文章目錄
【SQL開發(fā)實戰(zhàn)技巧】系列（一）:關(guān)于SQL不得不說的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（二）：簡單單表查詢
【SQL開發(fā)實戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（四）：從執(zhí)行計劃討論UNION ALL與空字符串&UNION與OR的使用注意事項
【SQL開發(fā)實戰(zhàn)技巧】系列（五）：從執(zhí)行計劃看IN、EXISTS 和 INNER JOIN效率，我們要分場景不要死記網(wǎng)上結(jié)論
【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關(guān)聯(lián)條件不要亂放
【SQL開發(fā)實戰(zhàn)技巧】系列（七）：從有重復數(shù)據(jù)前提下如何比較出兩個表中的差異數(shù)據(jù)及對應條數(shù)聊起
【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表
【SQL開發(fā)實戰(zhàn)技巧】系列（九）：一個update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復數(shù)據(jù)的寫法！
【SQL開發(fā)實戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計字符串出現(xiàn)次數(shù)說起
【SQL開發(fā)實戰(zhàn)技巧】系列（十一）：拿幾個案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十二）：三問(如何對字符串字母去重后按字母順序排列字符串？如何識別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表?)
【SQL開發(fā)實戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過執(zhí)行計劃看sum()over()對員工工資進行累加
【SQL開發(fā)實戰(zhàn)技巧】系列（十四）：計算消費后的余額&計算銀行流水累計和&計算各部門工資排名前三位的員工
【SQL開發(fā)實戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開發(fā)實戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉庫中時間類型操作（初級）日、月、年、時、分、秒之差及時間間隔計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉庫中時間類型操作（初級）確定兩個日期之間的工作天數(shù)、計算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時間類型操作（進階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時間類型操作（進階）如何一個SQL打印當月或一年的日歷？如何確定某月內(nèi)第一個和最后—個周內(nèi)某天的日期？
【SQL開發(fā)實戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉庫中時間類型操作（進階）獲取季度開始結(jié)束時間以及如何統(tǒng)計非連續(xù)性時間的數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉庫中時間類型操作（進階）識別重疊的日期范圍，按指定10分鐘時間間隔匯總數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十二）：數(shù)倉報表場景? 從分析函數(shù)效率一定快嗎聊一聊結(jié)果集分頁和隔行抽樣實現(xiàn)方式
【SQL開發(fā)實戰(zhàn)技巧】系列（二十三）：數(shù)倉報表場景? 如何對數(shù)據(jù)排列組合去重以及通過如何找到包含最大值和最小值的記錄這個問題再次用執(zhí)行計劃給你證明分析函數(shù)性能不一定高
【SQL開發(fā)實戰(zhàn)技巧】系列（二十四）：數(shù)倉報表場景?通過案例執(zhí)行計劃詳解”行轉(zhuǎn)列”,”列轉(zhuǎn)行”是如何實現(xiàn)的
【SQL開發(fā)實戰(zhàn)技巧】系列（二十五）：數(shù)倉報表場景?結(jié)果集中的重復數(shù)據(jù)只顯示一次以及計算部門薪資差異高效的寫法以及如何對數(shù)據(jù)進行快速分組
【SQL開發(fā)實戰(zhàn)技巧】系列（二十六）：數(shù)倉報表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計的以及如何識別哪些行是做匯總的結(jié)果行
【SQL開發(fā)實戰(zhàn)技巧】系列（二十七）：數(shù)倉報表場景?通過對移動范圍進行聚集來詳解分析函數(shù)開窗原理以及如何一個SQL打印九九乘法表
【SQL開發(fā)實戰(zhàn)技巧】系列（二十八）：數(shù)倉報表場景?人員分布問題以及不同組(分區(qū))同時聚集如何實現(xiàn)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十九）：數(shù)倉報表場景?簡單的樹形(分層)查詢以及如何確定根節(jié)點、分支節(jié)點和葉子節(jié)點
【SQL開發(fā)實戰(zhàn)技巧】系列（三十）：數(shù)倉報表場景?樹形(分層)查詢?nèi)绾闻判?？以及如何在樹形查詢中正確的使用where條件
【SQL開發(fā)實戰(zhàn)技巧】系列（三十一）：數(shù)倉報表場景?分層查詢?nèi)绾沃徊樵儤湫谓Y(jié)構(gòu)某一個分支？如何剪掉一個分支？
【SQL開發(fā)實戰(zhàn)技巧】系列（三十二）：數(shù)倉報表場景?對表中某個字段內(nèi)的值去重
【SQL開發(fā)實戰(zhàn)技巧】系列（三十三）：數(shù)倉報表場景?從不固定位置提取字符串的元素以及搜索滿足字母在前數(shù)字在后等條件的數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（三十四）：數(shù)倉報表場景?如何對數(shù)據(jù)分級并行轉(zhuǎn)為列
【SQL開發(fā)實戰(zhàn)技巧】系列（三十五）：數(shù)倉報表場景?根據(jù)條件返回不同列的數(shù)據(jù)以及Left /Full Join注意事項
【SQL開發(fā)實戰(zhàn)技巧】系列（三十六）：數(shù)倉報表場景?整理垃圾數(shù)據(jù)：查找數(shù)據(jù)的連續(xù)性時間和重疊時間的關(guān)系，初始化開始結(jié)束時間
前言
本篇文章講解的主要內(nèi)容是：從整理人員簽到信息記錄表的垃圾數(shù)據(jù)來再次體會一下隱藏數(shù)據(jù)列信息以及查找數(shù)據(jù)的連續(xù)性時間和重疊時間的實戰(zhàn)案例
【SQL開發(fā)實戰(zhàn)技巧】這一系列博主當作復習舊知識來進行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎(chǔ)，面試也會經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗，相信當我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。
一、整理垃圾數(shù)據(jù)：查找數(shù)據(jù)的連續(xù)性時間和重疊時間的關(guān)系，初始化開始結(jié)束時間
現(xiàn)在有下面一堆臟數(shù)據(jù)，是人員簽到信息記錄表，如下：
CREATE OR REPLACE VIEW people(人員編號,開始時間,結(jié)束時間,類型,數(shù)值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;
人員編號 開始時間 結(jié)束時間 類型 數(shù)值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
上面的數(shù)據(jù)是雜亂的，要求清洗上面數(shù)據(jù)，得到如下數(shù)據(jù)：
人員編號 區(qū)間
---------- -----------
11 201312--NULL
22 201305--201306
22 201308--201309
22 201403--201404
33 201305--201305
33 201307--201307
33 201312--NULL
由于上面數(shù)據(jù)質(zhì)量不高，現(xiàn)在提出數(shù)據(jù)清洗規(guī)則需求如下：
1、當類型為"-1"時,數(shù)據(jù)丟棄。
2、當類型為"-1",且其前一行"結(jié)束時間"為空值時,"開始時間-1"當作其前一行的結(jié)束時間。
3、如果后面的時間比前面的時間早,則覆蓋前面的時間,不能覆蓋的時間要保留。
4、時段重疊的要合并為一行。
數(shù)據(jù)亂,需求也就復雜。
首先要取出對應的數(shù)據(jù),剛開始寫語句時可能不知道從何下手,沒關(guān)系，我們一步步分析：
select 人員編號,
with t as (
select 人員編號,
開始時間,
min(開始時間) over(partition by 人員編號 order by 數(shù)值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 數(shù)值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 數(shù)值id rows between 1
following and unbounded following),
開始時間 + 1) as min_開始時間,
結(jié)束時間 as tmp_結(jié)束時間,
case
when 結(jié)束時間 is null and
(lead(類型) over(partition by 人員編號 order by 數(shù)值id)) = -1 then
add_months(lead(開始時間) over(partition by 人員編號 order by 數(shù)值id),
-1)
else
結(jié)束時間
end as 結(jié)束時間,
類型,
數(shù)值ID,
max(數(shù)值id) over(partition by 人員編號) as max_id
from people
）
select * from t;
人員編號 開始時間 TMP開始時間 TMP_MIN開始時間 MIN_開始時間 TMP_結(jié)束時間 結(jié)束時間 類型 數(shù)值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
上面標識了"開始時間"（見第三條）與"結(jié)束時間"（見第二條）分別處理的過程。
with t as (
select 人員編號,
開始時間,
min(開始時間) over(partition by 人員編號 order by 數(shù)值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 數(shù)值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 數(shù)值id rows between 1
following and unbounded following),
開始時間 + 1) as min_開始時間,--當前人員的最小日期，覆蓋用
結(jié)束時間 as tmp_結(jié)束時間,
case
when 結(jié)束時間 is null and
(lead(類型) over(partition by 人員編號 order by 數(shù)值id)) = -1 then
add_months(lead(開始時間) over(partition by 人員編號 order by 數(shù)值id),
-1)
else
結(jié)束時間
end as 結(jié)束時間,
類型,
數(shù)值ID,
max(數(shù)值id) over(partition by 人員編號) as max_id
from people
）
select 人員編號,
開始時間,
min_開始時間,
結(jié)束時間,
類型,
數(shù)值ID,
max_id,
case--生成區(qū)間是否重疊的標識，合并時段時用
when (lag(結(jié)束時間) over(partition by 人員編號 order by 數(shù)值id)) <
add_months(開始時間, -1) then
1
when (lag(類型) over(partition by 人員編號 order by 數(shù)值id)) = 1 then
null
else
1
end as so
from t;
人員編號 開始時間 MIN_開始時間 結(jié)束時間 類型 數(shù)值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
生成分組標識,如果后錄入的數(shù)據(jù)開始時間更早,就說明前面錄入的是無用的數(shù)據(jù),要丟棄,如果范圍重疊,就修正前面的結(jié)束時間。
with t as (
select 人員編號,
開始時間,
min(開始時間) over(partition by 人員編號 order by 數(shù)值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 數(shù)值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 數(shù)值id rows between 1
following and unbounded following),
開始時間 + 1) as min_開始時間,--當前人員的最小日期，覆蓋用
結(jié)束時間 as tmp_結(jié)束時間,
case
when 結(jié)束時間 is null and
(lead(類型) over(partition by 人員編號 order by 數(shù)值id)) = -1 then
add_months(lead(開始時間) over(partition by 人員編號 order by 數(shù)值id),
-1)
else
結(jié)束時間
end as 結(jié)束時間,
類型,
數(shù)值ID,
max(數(shù)值id) over(partition by 人員編號) as max_id
from people
),
t1 as (
select 人員編號,
開始時間,
min_開始時間,
結(jié)束時間,
類型,
數(shù)值ID,
max_id,
case--生成區(qū)間是否重疊的標識，合并時段時用
when (lag(結(jié)束時間) over(partition by 人員編號 order by 數(shù)值id)) <
add_months(開始時間, -1) then
1
when (lag(類型) over(partition by 人員編號 order by 數(shù)值id)) = 1 then
null
else
1
end as so
from t)
select 人員編號,
數(shù)值ID,
max_id,
類型,
sum(so) over(partition by 人員編號 order by 數(shù)值id) as so,/*累加標識,生成分組合并依據(jù)*/
開始時間,
min_開始時間,
case/*根據(jù)最前面生成的時間覆蓋對應的時段*/
when min_開始時間 < 結(jié)束時間 and min_開始時間 >= 開始時間 then
min_開始時間
else
結(jié)束時間
end as 結(jié)束時間
from t1
where 類型 = 1
/*如果開始時間比這還小,就丟棄吧*/
and 開始時間 <= min_開始時間;
人員編號 數(shù)值ID MAX_ID 類型 SO 開始時間 MIN_開始時間 結(jié)束時間
---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------
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
合并數(shù)據(jù),語句如下：
/*人員編號 NUMBER Y
開始時間 DATE Y
結(jié)束時間 DATE Y
類型 NUMBER Y
數(shù)值ID NUMBER Y
1、當類型為"-l"時,數(shù)據(jù)丟棄。
2、當類型為"-1",且其前一行"結(jié)束時間"為空值時,"開始時間-1"當作其前一行的結(jié)束時間。
3、如果后面的時間比前面的時間早,則覆蓋前面的時間,不能覆蓋的時間要保留。
5、時段重疊的要合并為一行。
*/
with t as (
select 人員編號,
開始時間,
min(開始時間) over(partition by 人員編號 order by 數(shù)值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 數(shù)值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 數(shù)值id rows between 1
following and unbounded following),
開始時間 + 1) as min_開始時間,--當前人員的最小日期，覆蓋用
結(jié)束時間 as tmp_結(jié)束時間,
case
when 結(jié)束時間 is null and
(lead(類型) over(partition by 人員編號 order by 數(shù)值id)) = -1 then
add_months(lead(開始時間) over(partition by 人員編號 order by 數(shù)值id),
-1)
else
結(jié)束時間
end as 結(jié)束時間,
類型,
數(shù)值ID,
max(數(shù)值id) over(partition by 人員編號) as max_id
from people
),
t1 as (
select 人員編號,
開始時間,
min_開始時間,
結(jié)束時間,
類型,
數(shù)值ID,
max_id,
case--生成區(qū)間是否重疊的標識，合并時段時用
when (lag(結(jié)束時間) over(partition by 人員編號 order by 數(shù)值id)) <
add_months(開始時間, -1) then
1
when (lag(類型) over(partition by 人員編號 order by 數(shù)值id)) = 1 then
null
else
1
end as so
from t),
t2 as (
select 人員編號,
數(shù)值ID,
max_id,
類型,
sum(so) over(partition by 人員編號 order by 數(shù)值id) as so,/*累加標識,生成分組合并依據(jù)*/
開始時間,
min_開始時間,
case/*根據(jù)最前面生成的時間覆蓋對應的時段*/
when min_開始時間 < 結(jié)束時間 and min_開始時間 >= 開始時間 then
min_開始時間
else
結(jié)束時間
end as 結(jié)束時間
from t1
where 類型 = 1
/*如果開始時間比這還小,就丟棄吧*/
and 開始時間 <= min_開始時間)
select 人員編號,
max_id,
max(數(shù)值ID) as max_id2,
sum(類型) as 類型,
min(開始時間) keep(dense_rank first order by 數(shù)值id) as 開始時間,
max(開始時間) keep(dense_rank last order by 數(shù)值id) as 結(jié)束時間
from t2
group by 人員編號, so, max_id;
人員編號 MAX_ID MAX_ID2 類型 開始時間 結(jié)束時間
---------- ---------- ---------- ---------- ----------- -----------
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 數(shù)值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 數(shù)值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 數(shù)值id rows between 1
following and unbounded following),
開始時間 + 1) as min_開始時間,--當前人員的最小日期，覆蓋用
結(jié)束時間 as tmp_結(jié)束時間,
case
when 結(jié)束時間 is null and
(lead(類型) over(partition by 人員編號 order by 數(shù)值id)) = -1 then
add_months(lead(開始時間) over(partition by 人員編號 order by 數(shù)值id),
-1)
else
結(jié)束時間
end as 結(jié)束時間,
類型,
數(shù)值ID,
max(數(shù)值id) over(partition by 人員編號) as max_id
from people
),
t1 as (
select 人員編號,
開始時間,
min_開始時間,
結(jié)束時間,
類型,
數(shù)值ID,
max_id,
case--生成區(qū)間是否重疊的標識，合并時段時用
when (lag(結(jié)束時間) over(partition by 人員編號 order by 數(shù)值id)) <
add_months(開始時間, -1) then
1
when (lag(類型) over(partition by 人員編號 order by 數(shù)值id)) = 1 then
null
else
1
end as so
from t),
t2 as (
select 人員編號,
數(shù)值ID,
max_id,
類型,
sum(so) over(partition by 人員編號 order by 數(shù)值id) as so,/*累加標識,生成分組合并依據(jù)*/
開始時間,
min_開始時間,
case/*根據(jù)最前面生成的時間覆蓋對應的時段*/
when min_開始時間 < 結(jié)束時間 and min_開始時間 >= 開始時間 then
min_開始時間
else
結(jié)束時間
end as 結(jié)束時間
from t1
where 類型 = 1
/*如果開始時間比這還小,就丟棄吧*/
and 開始時間 <= min_開始時間),
t3 as (
select 人員編號,
max_id,
max(數(shù)值ID) as max_id2,
sum(類型) as 類型,
min(開始時間) keep(dense_rank first order by 數(shù)值id) as 開始時間,
max(開始時間) keep(dense_rank last order by 數(shù)值id) as 結(jié)束時間
from t2
group by 人員編號, so, max_id)
select 人員編號,to_char(開始時間,'yyyymm')||'--'||to_char(結(jié)束時間,'yyyymm') as str
from t3
where (max_id=max_id2 or 開始時間<=結(jié)束時間)
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
到這一步就可以了。整理數(shù)據(jù)是最考驗耐心的工作,特別是需求還不確定的時候。文章來源：http://www.zghlxwxcb.cn/news/detail-438058.html
總結(jié)
本篇文章講解的主要內(nèi)容是：從整理人員簽到信息記錄表的垃圾數(shù)據(jù)來再次體會一下隱藏數(shù)據(jù)列信息以及查找數(shù)據(jù)的連續(xù)性時間和重疊時間的實戰(zhàn)案例文章來源地址http://www.zghlxwxcb.cn/news/detail-438058.html
到了這里，關(guān)于【SQL開發(fā)實戰(zhàn)技巧】系列（三十六）：數(shù)倉報表場景?整理垃圾數(shù)據(jù)：查找數(shù)據(jù)的連續(xù)性時間和重疊時間的關(guān)系，初始化開始結(jié)束時間的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
