系列文章目錄
【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)技巧】系列（七）：從有重復(fù)數(shù)據(jù)前提下如何比較出兩個表中的差異數(shù)據(jù)及對應(yīng)條數(shù)聊起
【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表
【SQL開發(fā)實戰(zhàn)技巧】系列（九）：一個update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復(fù)數(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ù)、確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時間類型操作（進階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時間類型操作（進階）如何一個SQL打印當(dāng)月或一年的日歷？如何確定某月內(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é)果集中的重復(fù)數(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)
前言
本篇文章講解的主要內(nèi)容是：通過行轉(zhuǎn)列實現(xiàn)人員空間分布問題(工作顯示為一列,每位員工顯示一行)、連續(xù)行轉(zhuǎn)列應(yīng)該注意的問題、通過執(zhí)行計劃看對不同組、分區(qū)同時實現(xiàn)聚集需求：要求在員工表的明細(xì)數(shù)據(jù)里列出員工所在部門及職位的人數(shù)?。?/strong>
【SQL開發(fā)實戰(zhàn)技巧】這一系列博主當(dāng)作復(fù)習(xí)舊知識來進行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎(chǔ)，面試也會經(jīng)常問SQL開發(fā)和調(diào)優(yōu)經(jīng)驗，相信當(dāng)我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。
一、人員在工作空間的分布問題
現(xiàn)在有一個需求：要求每種工作顯示為一列,每位員工顯示一行,員工與工作對應(yīng)時顯示為是,不對應(yīng)則顯示為空！
那這個需求怎么做呢？
其實我們可以活用PIVOT函數(shù),按工作、員工分組,設(shè)對應(yīng)位置為是:
SQL> select * from (select ename,job from emp)
2 pivot(
3 max('是')
4 for job in(
5 'ANALYST' as ANALYST,
6 'CLERK' as CLERK,
7 'MANAGER' as MANAGER,
8 'PRESIDENT' as PRESIDENT,
9 'SALESMAN' as SALESMAN
10 )
11 );
ENAME ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ------- ----- ------- --------- --------
ADAMS 是
ALLEN 是
BLAKE 是
CLARK 是
FORD 是
JAMES 是
JONES 是
KING 是
MARTIN 是
MILLER 是
SCOTT 是
SMITH 是
TURNER 是
WARD 是
14 rows selected
這個語句相當(dāng)于group by ename,job。
二、創(chuàng)建稀疏矩陣
給上面問題的需求增加一下難度,現(xiàn)在的需求是：對應(yīng)位置直接顯示為員工姓名,且增加在部門間的分布,因未對數(shù)據(jù)進行匯總,所以仍可以用PIVOT來處理,查詢語句如下：
SQL>
SQL> select *
2 from (select empno, ename, ename as ename2, job, deptno from emp)
3 pivot(max(ename)
4 for deptno in(10 as d10, 20 as d20, 30 as d30))
5 pivot(max(ename2)
6 for job in('ANALYST' as ANALYST,
7 'CLERK' as CLERK,
8 'MANAGER' as MANAGER,
9 'PRESIDENT' as PRESIDENT,
10 'SALESMAN' as SALESMAN
11 ));
EMPNO D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7900 JAMES JAMES
7369 SMITH SMITH
7499 ALLEN ALLEN
7521 WARD WARD
7566 JONES JONES
7654 MARTIN MARTIN
7698 BLAKE BLAKE
7782 CLARK CLARK
7788 SCOTT SCOTT
7839 KING KING
7844 TURNER TURNER
7876 ADAMS ADAMS
7902 FORD FORD
7934 MILLER MILLER
14 rows selected
注意：如果對數(shù)據(jù)有匯總,就不要用這種有兩個PIOVT的方式。因為這種查詢實際上相當(dāng)于兩個PIVOT的子句嵌套。
在之前的文章中有一個count的case when語句,具體如下：
SQL>
SQL> select count(case
2 when deptno = 10 then
3 ename
4 end) as deptno_10,
5 count(case
6 when deptno = 20 then
7 ename
8 end) as deptno_20,
9 count(case
10 when deptno = 30 then
11 ename
12 end) as deptno_30,
13 count(case
14 when job = 'ANALYST' then
15 job
16 end) as ANALYST,
17 count(case
18 when job = 'CLERK' then
19 job
20 end) as CLERK,
21 count(case
22 when job = 'MANAGER' then
23 job
24 end) as MANAGER,
25 count(case
26 when job = 'PRESIDENT' then
27 job
28 end) as PRESIDENT,
29 count(case
30 when job = 'SALESMAN' then
31 job
32 end) as SALESMAN
33 from emp;
DEPTNO_10 DEPTNO_20 DEPTNO_30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3 5 6 2 4 3 1 4
我們嘗試用PIOVT來改寫,看會出現(xiàn)什么問題。原始PIOVT語句如下：
SQL>
SQL> select *
2 from (select ename, ename as ename2, job, deptno from emp)
3 pivot(count(ename)
4 for deptno in(10 as d10, 20 as d20, 30 as d30))
5 pivot(count(ename2)
6 for job in('ANALYST' as ANALYST,
7 'CLERK' as CLERK,
8 'MANAGER' as MANAGER,
9 'PRESIDENT' as PRESIDENT,
10 'SALESMAN' as SALESMAN
11 ));
D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 1 0 1 1 0 4
0 1 0 2 2 1 0 0
1 0 0 0 1 1 1 0
SQL>
可以看到數(shù)據(jù),與case when的結(jié)果不一致。下面改為嵌套的方式來分析。
嵌套第一步：
SQL> with t as (
2 select *
3 from (select ename, ename as ename2, job, deptno from emp)
4 pivot(count(ename)
5 for deptno in(10 as d10, 20 as d20, 30 as d30))
6 )
7 select * from t;
ENAME2 JOB D10 D20 D30
---------- --------- ---------- ---------- ----------
FORD ANALYST 0 1 0
KING PRESIDENT 1 0 0
WARD SALESMAN 0 0 1
ADAMS CLERK 0 1 0
ALLEN SALESMAN 0 0 1
BLAKE MANAGER 0 0 1
CLARK MANAGER 1 0 0
JAMES CLERK 0 0 1
JONES MANAGER 0 1 0
SCOTT ANALYST 0 1 0
SMITH CLERK 0 1 0
MARTIN SALESMAN 0 0 1
MILLER CLERK 1 0 0
TURNER SALESMAN 0 0 1
14 rows selected
第一步相當(dāng)于group by empno,job。
嵌套示例第二步：
SQL> with t as
2 (select *
3 from (select ename, ename as ename2, job, deptno from emp)
4 pivot(count(ename)
5 for deptno in(10 as d10, 20 as d20, 30 as d30)))
6 select *
7 from t
8 pivot (count(ename2) for job in('ANALYST' as ANALYST,
9 'CLERK' as CLERK,
10 'MANAGER' as MANAGER,
11 'PRESIDENT' as PRESIDENT,
12 'SALESMAN' as SALESMAN));
D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 1 0 1 1 0 4
0 1 0 2 2 1 0 0
1 0 0 0 1 1 1 0
SQL>
因第一步返回的列為(ENAME2,JOB,D10,D20,D30)去掉(ENAME2,JOB)后,剩余的是(D10,D20,D30)。所以第二
步相當(dāng)于group by D10,D20,D30。
但是我們想要的是對emp表根據(jù)job分組求count和根據(jù)部門分組求count，它們是對emp表兩個組合的統(tǒng)計合并，而不是在條件1的基礎(chǔ)上，再進行分組count。
三、對不同組、分區(qū)同時實現(xiàn)聚集
現(xiàn)在有個需求：要求在員工表的明細(xì)數(shù)據(jù)里列出員工所在部門及職位的人數(shù)。
沒用分析函數(shù)前,這種需求要用自關(guān)聯(lián)：
SQL> with t as
2 (select count(*) as cnt from emp),
3 t1 as
4 (select deptno, count(*) as dcnt from emp group by deptno),
5 t2 as
6 (select job, count(*) as jcnt from emp group by job)
7 select emp.ename,
8 emp.deptno,
9 t1.dcnt,
10 emp.job,
11 t2.jcnt,
12 (select * from t) as cnt
13 from emp
14 inner join t1
15 on (emp.deptno = t1.deptno)
16 inner join t2
17 on (emp.job = t2.job);
ENAME DEPTNO DCNT JOB JCNT CNT
---------- ------ ---------- --------- ---------- ----------
FORD 20 5 ANALYST 2 14
SCOTT 20 5 ANALYST 2 14
MILLER 10 3 CLERK 4 14
JAMES 30 6 CLERK 4 14
ADAMS 20 5 CLERK 4 14
SMITH 20 5 CLERK 4 14
CLARK 10 3 MANAGER 3 14
BLAKE 30 6 MANAGER 3 14
JONES 20 5 MANAGER 3 14
KING 10 3 PRESIDENT 1 14
TURNER 30 6 SALESMAN 4 14
MARTIN 30 6 SALESMAN 4 14
WARD 30 6 SALESMAN 4 14
ALLEN 30 6 SALESMAN 4 14
14 rows selected
SQL>
看一下執(zhí)行計劃：
Plan Hash Value :
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 868 | 12 | 00:00:01 |
| 1 | VIEW | | 1 | 13 | 1 | 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | | | |
| 3 | INDEX FULL SCAN | IDX_EMPNO | 15 | | 1 | 00:00:01 |
| * 4 | HASH JOIN | | 14 | 868 | 11 | 00:00:01 |
| * 5 | HASH JOIN | | 13 | 559 | 7 | 00:00:01 |
| 6 | VIEW | | 3 | 78 | 4 | 00:00:01 |
| 7 | SORT GROUP BY | | 3 | 9 | 4 | 00:00:01 |
| 8 | TABLE ACCESS FULL | EMP | 15 | 45 | 3 | 00:00:01 |
| * 9 | TABLE ACCESS FULL | EMP | 13 | 221 | 3 | 00:00:01 |
| 10 | VIEW | | 5 | 95 | 4 | 00:00:01 |
| 11 | SORT GROUP BY | | 5 | 40 | 4 | 00:00:01 |
| 12 | TABLE ACCESS FULL | EMP | 15 | 120 | 3 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("EMP"."JOB"="T2"."JOB")
* 5 - access("EMP"."DEPTNO"="T1"."DEPTNO")
* 9 - filter("EMP"."JOB" IS NOT NULL AND "EMP"."DEPTNO" IS NOT NULL)
這種寫法比較復(fù)雜,而且要對表emp訪問四次(因為我建了索引。所以有一個走了索引)。
如果改用分析函數(shù),語句就較簡單：
SQL> select emp.ename,
2 emp.deptno,
3 count(*) over(partition by deptno) dcnt,
4 emp.job,
5 count(*) over(partition by job) jcnt,
6 count(*) over() as cnt
7 from emp
8 ;
ENAME DEPTNO DCNT JOB JCNT CNT
---------- ------ ---------- --------- ---------- ----------
MILLER 10 3 CLERK 4 14
KING 10 3 PRESIDENT 1 14
CLARK 10 3 MANAGER 3 14
SMITH 20 5 CLERK 4 14
SCOTT 20 5 ANALYST 2 14
ADAMS 20 5 CLERK 4 14
FORD 20 5 ANALYST 2 14
JONES 20 5 MANAGER 3 14
WARD 30 6 SALESMAN 4 14
MARTIN 30 6 SALESMAN 4 14
TURNER 30 6 SALESMAN 4 14
ALLEN 30 6 SALESMAN 4 14
JAMES 30 6 CLERK 4 14
BLAKE 30 6 MANAGER 3 14
14 rows selected
看執(zhí)行計劃：
Plan Hash Value : 4086863039
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 255 | 5 | 00:00:01 |
| 1 | WINDOW SORT | | 15 | 255 | 5 | 00:00:01 |
| 2 | WINDOW SORT | | 15 | 255 | 5 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 15 | 255 | 3 | 00:00:01 |
----------------------------------------------------------------------
從執(zhí)行計劃看，就掃描了一次表。
但是我前面不是有兩篇文章不是一直在說用分析函數(shù)要慎重嘛？為什么我這里又推薦大家用了？
遇到這種多次訪問同一個表的情況時,可以嘗試看一下能否用分析函數(shù)改寫,以及改寫后的效率如何，如果像我現(xiàn)在這樣通過分析執(zhí)行計劃得到"性能提升很明顯"這個結(jié)論，
那你這個場景當(dāng)然就可以用啦，當(dāng)然還有最重要的一點是：改寫完了別忘了核對數(shù)據(jù)！這可是非常重要的一點。文章來源：http://www.zghlxwxcb.cn/news/detail-408188.html
總結(jié)
本篇文章講解的主要內(nèi)容是：通過行轉(zhuǎn)列實現(xiàn)人員空間分布問題(工作顯示為一列,每位員工顯示一行)、連續(xù)行轉(zhuǎn)列應(yīng)該注意的問題、通過執(zhí)行計劃看對不同組、分區(qū)同時實現(xiàn)聚集需求：要求在員工表的明細(xì)數(shù)據(jù)里列出員工所在部門及職位的人數(shù)??！文章來源地址http://www.zghlxwxcb.cn/news/detail-408188.html
到了這里，關(guān)于【SQL開發(fā)實戰(zhàn)技巧】系列（二十八）：數(shù)倉報表場景?人員分布問題以及不同組(分區(qū))同時聚集如何實現(xiàn)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
