系列文章目錄
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（一）:關(guān)于SQL不得不說(shuō)的那些事
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二）：簡(jiǎn)單單表查詢(xún)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（四）：從執(zhí)行計(jì)劃討論UNION ALL與空字符串&UNION與OR的使用注意事項(xiàng)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（五）：從執(zhí)行計(jì)劃看IN、EXISTS 和 INNER JOIN效率，我們要分場(chǎng)景不要死記網(wǎng)上結(jié)論
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（六）：從執(zhí)行計(jì)劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關(guān)聯(lián)條件不要亂放
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（七）：從有重復(fù)數(shù)據(jù)前提下如何比較出兩個(gè)表中的差異數(shù)據(jù)及對(duì)應(yīng)條數(shù)聊起
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時(shí)比約束更靈活的限制數(shù)據(jù)插入以及怎么一個(gè)insert語(yǔ)句同時(shí)插入多張表
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（九）：一個(gè)update誤把其他列數(shù)據(jù)更新成空了？Merge改寫(xiě)update！給你五種刪除重復(fù)數(shù)據(jù)的寫(xiě)法！
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計(jì)字符串出現(xiàn)次數(shù)說(shuō)起
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十一）：拿幾個(gè)案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十二）：三問(wèn)(如何對(duì)字符串字母去重后按字母順序排列字符串？如何識(shí)別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉(zhuǎn)換為多值IN列表?)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過(guò)執(zhí)行計(jì)劃看sum()over()對(duì)員工工資進(jìn)行累加
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十四）：計(jì)算消費(fèi)后的余額&計(jì)算銀行流水累計(jì)和&計(jì)算各部門(mén)工資排名前三位的員工
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計(jì)算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（初級(jí)）日、月、年、時(shí)、分、秒之差及時(shí)間間隔計(jì)算
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（初級(jí)）確定兩個(gè)日期之間的工作天數(shù)、計(jì)算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（進(jìn)階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計(jì)算
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（進(jìn)階）如何一個(gè)SQL打印當(dāng)月或一年的日歷？如何確定某月內(nèi)第一個(gè)和最后—個(gè)周內(nèi)某天的日期？
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（進(jìn)階）獲取季度開(kāi)始結(jié)束時(shí)間以及如何統(tǒng)計(jì)非連續(xù)性時(shí)間的數(shù)據(jù)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（進(jìn)階）識(shí)別重疊的日期范圍，按指定10分鐘時(shí)間間隔匯總數(shù)據(jù)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十二）：數(shù)倉(cāng)報(bào)表場(chǎng)景? 從分析函數(shù)效率一定快嗎聊一聊結(jié)果集分頁(yè)和隔行抽樣實(shí)現(xiàn)方式
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十三）：數(shù)倉(cāng)報(bào)表場(chǎng)景? 如何對(duì)數(shù)據(jù)排列組合去重以及通過(guò)如何找到包含最大值和最小值的記錄這個(gè)問(wèn)題再次用執(zhí)行計(jì)劃給你證明分析函數(shù)性能不一定高
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十四）：數(shù)倉(cāng)報(bào)表場(chǎng)景?通過(guò)案例執(zhí)行計(jì)劃詳解”行轉(zhuǎn)列”,”列轉(zhuǎn)行”是如何實(shí)現(xiàn)的
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十五）：數(shù)倉(cāng)報(bào)表場(chǎng)景?結(jié)果集中的重復(fù)數(shù)據(jù)只顯示一次以及計(jì)算部門(mén)薪資差異高效的寫(xiě)法以及如何對(duì)數(shù)據(jù)進(jìn)行快速分組
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十六）：數(shù)倉(cāng)報(bào)表場(chǎng)景?聊聊ROLLUP、UNION ALL是如何分別做分組合計(jì)的以及如何識(shí)別哪些行是做匯總的結(jié)果行
前言
本篇文章講解的主要內(nèi)容是：如何識(shí)別重疊的日期范圍、日期出現(xiàn)次數(shù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】這一系列博主當(dāng)作復(fù)習(xí)舊知識(shí)來(lái)進(jìn)行寫(xiě)作，畢竟SQL開(kāi)發(fā)在數(shù)據(jù)分析場(chǎng)景非常重要且基礎(chǔ)，面試也會(huì)經(jīng)常問(wèn)SQL開(kāi)發(fā)和調(diào)優(yōu)經(jīng)驗(yàn)，相信當(dāng)我寫(xiě)完這一系列文章，也能再有所收獲，未來(lái)面對(duì)SQL面試也能游刃有余~。
一、識(shí)別重疊的日期范圍
下面是一個(gè)有關(guān)工程的明細(xì)數(shù)據(jù)：
create or replace view zyd as
SELECT 7782 empno,'CLARK' as ename,1 as proj_id,date'2023-06-16' proj_start,date'2023-06-18' proj_end from dual union all
SELECT 7782 empno,'CLARK' as ename,4 as proj_id,date'2023-06-19' proj_start,date'2023-06-24' proj_end from dual union all
SELECT 7782 empno,'CLARK' as ename,7 as proj_id,date'2023-06-22' proj_start,date'2023-06-25' proj_end from dual union all
SELECT 7782 empno,'CLARK' as ename,10 as proj_id,date'2023-06-25' proj_start,date'2023-06-28' proj_end from dual union all
SELECT 7782 empno,'CLARK' as ename,13 as proj_id,date'2023-06-28' proj_start,date'2023-07-02' proj_end from dual union all
SELECT 7839 empno,'KING' as ename,2 as proj_id,date'2023-06-17' proj_start,date'2023-06-21' proj_end from dual union all
SELECT 7839 empno,'KING' as ename,8 as proj_id,date'2023-06-23' proj_start,date'2023-06-25' proj_end from dual union all
SELECT 7839 empno,'KING' as ename,14 as proj_id,date'2023-06-29' proj_start,date'2023-06-30' proj_end from dual union all
SELECT 7839 empno,'KING' as ename,11 as proj_id,date'2023-06-26' proj_start,date'2023-06-27' proj_end from dual union all
SELECT 7839 empno,'KING' as ename,5 as proj_id,date'2023-06-20' proj_start,date'2023-06-24' proj_end from dual union all
SELECT 7934 empno,'MILLER' as ename,3 as proj_id,date'2023-06-18' proj_start,date'2023-06-22' proj_end from dual union all
SELECT 7934 empno,'MILLER' as ename,12 as proj_id,date'2023-06-27' proj_start,date'2023-06-28' proj_end from dual union all
SELECT 7934 empno,'MILLER' as ename,15 as proj_id,date'2023-06-30' proj_start,date'2023-07-03' proj_end from dual union all
SELECT 7934 empno,'MILLER' as ename,9 as proj_id,date'2023-06-24' proj_start,date'2023-06-27' proj_end from dual union all
SELECT 7934 empno,'MILLER' as ename,6 as 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 1 2023-6-16 2023-6-18
7782 CLARK 4 2023-6-19 2023-6-24
7782 CLARK 7 2023-6-22 2023-6-25
7782 CLARK 10 2023-6-25 2023-6-28
7782 CLARK 13 2023-6-28 2023-7-2
7839 KING 2 2023-6-17 2023-6-21
7839 KING 8 2023-6-23 2023-6-25
7839 KING 14 2023-6-29 2023-6-30
7839 KING 11 2023-6-26 2023-6-27
7839 KING 5 2023-6-20 2023-6-24
7934 MILLER 3 2023-6-18 2023-6-22
7934 MILLER 12 2023-6-27 2023-6-28
7934 MILLER 15 2023-6-30 2023-7-3
7934 MILLER 9 2023-6-24 2023-6-27
7934 MILLER 6 2023-6-21 2023-6-23
15 rows selected
通過(guò)數(shù)據(jù)可以看到,有很多員工在舊的工程結(jié)束之前就開(kāi)始了新的工程（如員工 7782 的工程4結(jié)束日期是6月24日,而工程7開(kāi)始日期是6月22日）,現(xiàn)要求返回這些工程時(shí)間重復(fù)的數(shù)據(jù)。
前面介紹了Oracle中有兩個(gè)分析函數(shù)LAG和LEAD,分別用于訪(fǎng)問(wèn)結(jié)果集中的前一行和后一行。我們可以用分析函數(shù)LAG取得員工各自的上一個(gè)工程的結(jié)束日期及工程號(hào),然后與當(dāng)前工程相比較。
- 1、取信息
SQL> select empno,
2 ename,
3 proj_id as 工程號(hào),
4 proj_start as 開(kāi)始日期,
5 proj_end as 結(jié)束日期,
6 lag(proj_end) over(partition by empno order by proj_start) as 上一工程結(jié)束日期,
7 lag(proj_id) over(partition by empno order by proj_start) as 上一工程號(hào)
8 from zyd;
EMPNO ENAME 工程號(hào) 開(kāi)始日期 結(jié)束日期 上一工程結(jié)束日期 上一工程號(hào)
---------- ------ ---------- ----------- ----------- ----------- ----------
7782 CLARK 1 2023-6-16 2023-6-18
7782 CLARK 4 2023-6-19 2023-6-24 2023-6-18 1
7782 CLARK 7 2023-6-22 2023-6-25 2023-6-24 4
7782 CLARK 10 2023-6-25 2023-6-28 2023-6-25 7
7782 CLARK 13 2023-6-28 2023-7-2 2023-6-28 10
7839 KING 2 2023-6-17 2023-6-21
7839 KING 5 2023-6-20 2023-6-24 2023-6-21 2
7839 KING 8 2023-6-23 2023-6-25 2023-6-24 5
7839 KING 11 2023-6-26 2023-6-27 2023-6-25 8
7839 KING 14 2023-6-29 2023-6-30 2023-6-27 11
7934 MILLER 3 2023-6-18 2023-6-22
7934 MILLER 6 2023-6-21 2023-6-23 2023-6-22 3
7934 MILLER 9 2023-6-24 2023-6-27 2023-6-23 6
7934 MILLER 12 2023-6-27 2023-6-28 2023-6-27 9
7934 MILLER 15 2023-6-30 2023-7-3 2023-6-28 12
15 rows selected
這里增加了partition by empno這樣就可以對(duì)數(shù)據(jù)分組進(jìn)行分析,不同的empno之間互不影響。
- 2、比較
SQL> with t as
2 (select empno,
3 ename,
4 proj_id as 工程號(hào),
5 proj_start as 開(kāi)始日期,
6 proj_end as 結(jié)束日期,
7 lag(proj_end) over(partition by empno order by proj_start) as 上一工程結(jié)束日期,
8 lag(proj_id) over(partition by empno order by proj_start) as 上一工程號(hào)
9 from zyd)
10 select t.empno,
11 t.ename,
12 t.工程號(hào),
13 t.開(kāi)始日期,
14 t.結(jié)束日期,
15 case
16 when 上一工程結(jié)束日期 >= 開(kāi)始日期 then
17 '工程' || lpad(工程號(hào), 2, '0') || '與工程' || lpad(上一工程號(hào), 2, '0') || '重復(fù)'
18 end as 描述
19 from t;
EMPNO ENAME 工程號(hào) 開(kāi)始日期 結(jié)束日期 描述
---------- ------ ---------- ----------- ----------- --------------------------------------------------------------------------------
7782 CLARK 1 2023-6-16 2023-6-18
7782 CLARK 4 2023-6-19 2023-6-24
7782 CLARK 7 2023-6-22 2023-6-25 工程07與工程04重復(fù)
7782 CLARK 10 2023-6-25 2023-6-28 工程10與工程07重復(fù)
7782 CLARK 13 2023-6-28 2023-7-2 工程13與工程10重復(fù)
7839 KING 2 2023-6-17 2023-6-21
7839 KING 5 2023-6-20 2023-6-24 工程05與工程02重復(fù)
7839 KING 8 2023-6-23 2023-6-25 工程08與工程05重復(fù)
7839 KING 11 2023-6-26 2023-6-27
7839 KING 14 2023-6-29 2023-6-30
7934 MILLER 3 2023-6-18 2023-6-22
7934 MILLER 6 2023-6-21 2023-6-23 工程06與工程03重復(fù)
7934 MILLER 9 2023-6-24 2023-6-27
7934 MILLER 12 2023-6-27 2023-6-28 工程12與工程09重復(fù)
7934 MILLER 15 2023-6-30 2023-7-3
15 rows selected
如果只想看重復(fù)數(shù)據(jù),直接加個(gè)過(guò)濾就行啦?。?！
二、按指定間隔匯總數(shù)據(jù)
現(xiàn)在有個(gè)需求：按指定的時(shí)間間隔(10分鐘）匯總數(shù)據(jù),分別匯總至0分、10分、20分、30分等。
我這里用v$sql表來(lái)當(dāng)作案例數(shù)據(jù)，先看下這里的部分?jǐn)?shù)據(jù)：
select a.LAST_ACTIVE_TIME from v$sql a where rownum<=5;
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
我們一步步來(lái)。
- 1、截取數(shù)據(jù)到分鐘，并提取分鐘信息
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-13 22:14:00 14
2、對(duì)14和10取余
SQL> select mod(14,10) from dual;
MOD(14,10)
----------
4
SQL>
- 2、對(duì)比上面結(jié)果，我們可以知道如果想計(jì)算整10分鐘的間隔，那就直接用MI-MOD(14,10)就算出來(lái)這個(gè)分鐘對(duì)應(yīng)在整十的哪個(gè)范圍了。
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-13 22:14:00 14 10
那么鋪墊做完了，這個(gè)需求的最終實(shí)現(xiàn)sql如下：文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-407218.html
SQL> with t as
2 (select sql_id,
3 trunc(a.LAST_ACTIVE_TIME, 'mi') -
4 mod(to_char(a.LAST_ACTIVE_TIME, 'mi'), 10) / 24 / 60 as new_tim
5 from v$sql a)
6 select * from (
7 select new_tim, count(*) from t group by new_tim order by new_tim desc nulls last
8 )where rownum<=10;
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 2
10 rows selected
SQL>
總結(jié)
本篇文章講解的主要內(nèi)容是：如何識(shí)別重疊的日期范圍、日期出現(xiàn)次數(shù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-407218.html
到了這里，關(guān)于【SQL開(kāi)發(fā)實(shí)戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉(cāng)庫(kù)中時(shí)間類(lèi)型操作（進(jìn)階）識(shí)別重疊的日期范圍，按指定10分鐘時(shí)間間隔匯總數(shù)據(jù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
