【書評:Oracle查詢優化改寫】第五至十三章

【書評:Oracle查詢優化改寫】第五至十三章

一.1 BLOG文檔結構圖

一.2 前言部分

一.2.1 導讀

各位技術愛好者，看完本文后，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：

① 字符串的處理

② 常用分析函數

③ 用sql輸出九九乘法表

本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。

一.2.2 實驗環境介紹

oracle 11g

一.2.3 相關參考文章鏈接

前4章的鏈接參考相關連接：

【書評:Oracle查詢優化改寫】第一章 http://blog.itpub.net/26736162/viewspace-1652985/

【書評:Oracle查詢優化改寫】第二章 http://blog.itpub.net/26736162/viewspace-1654252/

【書評:Oracle查詢優化改寫】第三章 http://blog.itpub.net/26736162/viewspace-1660422/

【書評:Oracle查詢優化改寫】第四章 http://blog.itpub.net/26736162/viewspace-1661906/

行列互轉內容鏈接：http://blog.itpub.net/26736162/viewspace-1272538/

一.2.4 本文簡介

大家奇怪了，怎么不一章一章的寫了，直接跳躍了，小麥苗告訴大家，因為第5到13章的內容大多數是開發的內容，和SQL調優相差太遠，這里列出這幾章的目錄，雖說是開發sql的內容，但是很多實例還是比較實用的，比如對translate函數的應用。

   第5章 處理字符串，包含translate函數和個別oracle的分析函數。

第6章 處理數字，介紹了分析函數

第7、8章講了DATE類型的常見用法。

第9章仍然介紹分析函數

第10章的重點是結果集的分頁

第11章講述了行列轉換函數， 用UNPIVOT對UNION ALL做一定的優化，還有ROLLUP及CUBE可以讓你少寫一些UNION ALL語句。

第12章講解樹形查詢

第13章選取了部分網友的需求案例，希望讀者能通過這些案例的啟發找到實現自己需求的思路。

第 5 章 使用字符串

5.1 遍歷字符串

5.2 字符串文字中包含引號 

5.3 計算字符在字符串中出現的次數

5.4 從字符串中刪除不需要的字符

5.5 將字符和數字數據分離

5.6 查詢只包含字母或數字型的數據

5.7 提取姓名的大寫首字母縮寫

5.8 按字符串中的數值排序

5.9 根據表中的行創建一個分隔列表

5.10 提取第 n 個分隔的子串

5.11 分解 IP 地址

5.12 將分隔數據轉換為多值 IN 列表 

5.13 按字母順序排列字符串

5.14 判別可作為數值的字符串

第 6 章 使用數字

6.1 常用聚集函數 

6.2 生成累計和 

6.3 計算累計差

6.4 更改累計和的值

6.5 返回各部門工資排名前三位的員工

6.6 計算出現次數最多的值

6.7 返回最值所在行數據 

6.8 first_value

6.9 求總和的百分比

第 7 章 日期運算

7.1 加減日、月、年

7.2 加減時、分、秒

7.3 日期間隔之時、分、秒

7.4 日期間隔之日、月、年

7.5 確定兩個日期之間的工作天數

7.6 計算一年中周內各日期的次數

7.7 確定當前記錄和下一條記錄之間相差的天數

第 8 章 日期操作

8.1 SYSDATE 能得到的信息

8.2 INTERVAL

8.3 EXTRACT

8.4 確定一年是否為閏年

8.5 周的計算

8.6 確定一年內屬于周內某一天的所有日期

8.7 確定某月內第一個和最后一個“周內某天”的日期

8.8 創建本月日歷

8.9 全年日歷

8.10 確定指定年份季度的開始日期和結束日期

8.11 補充范圍內丟失的值

8.12 按照給定的時間單位進行查找

8.13 使用日期的特殊部分比較記錄

8.14 識別重疊的日期范圍

8.15 按指定間隔匯總數據

第 9 章 范圍處理

9.1 定位連續值的范圍

9.2 查找同一組或分區中行之間的差

9.3 定位連續值范圍的開始點和結束點

9.4 合并時間段

第 10 章 高級查找

10.1 給結果集分頁

10.2 重新生成房間號

10.3 跳過表中 n 行

10.4 排列組合去重

10.5 找到包含最大值和最小值的記錄

第 11 章 報表和數據倉庫運算

11.1 行轉列

11.2 列轉行 

11.3 將結果集反向轉置為一列 

11.4 抑制結果集中的重復值 

11.5 利用“行轉列”進行計算

11.6 給數據分組

11.7 對數據分組

11.8 計算簡單的小計

11.9 判別非小計的行

11.10 計算所有表達式組合的小計

11.11 人員在工作間的分布

11.12 創建稀疏矩陣

11.13 對不同組/分區同時實現聚集

11.14 對移動范圍的值進行聚集

11.15 常用分析函數開窗講解

11.16 listagg 與小九九

第 12 章 分層查詢

12.1 簡單的樹形查詢

12.2 根節點、分支節點、葉子節點

12.3 sys_connect_by_path 

12.4 樹形查詢中的排序 

12.5 樹形查詢中的 WHERE

12.6 查詢樹形的一個分支

12.7 剪去一個分支

12.8 字段內 list 值去重 

第 13 章 應用案例實現

13.1 從不固定位置提取字符串的元素

13.2 搜索字母數字混合的字符串

13.3 把結果分級并轉為列

13.4 構建基礎數據的重要性

13.5 根據傳入條件返回不同列中的數據

13.6 拆分字符串進行連接

13.7 整理垃圾數據

13.8 用“行轉列”來得到隱含信息

13.9 用隱藏數據進行行轉列

13.10 用正則表達式提取 clob 里的文本格式記錄集

下邊我針對不同的章節，選取感興趣的部分內容分享給大家：

一.3 第五章部分內容  字符串的處理

一.3.1 遍歷字符串

create or replace view v as

select '天天向上' as 漢字 ,'TTXS' as 首拼  from dual;

select v.漢字 , v.首拼 ,level from v connect by level<=length(v.漢字);

SELECT v.漢字,

       v.首拼,

LEVEL,

substr(v.漢字, LEVEL, 1) AS 漢字拆分,

substr(v.首拼, LEVEL, 1) AS 首拼拆分,

'substr(''' || v.漢字 || ''',' || LEVEL || ',1)' AS fun

FROM   v

CONNECT BY LEVEL <= length(v.漢字);

一.3.2 計算字符在字符串中出現的次數

create or replace view v as

select 'CLARK,KING,MILLER' as str from dual;

---可以有多種辦法：

select REGEXP_COUNT(str,',')+1 as cnt from v;

select length(REGEXP_replace(str,'[^,]'))+1 as cnt from v;

select length(translate(str,','||str,','))+1 as cnt from v;

create or replace view v as

select 'CLARK$#KING$#MILLER' as str from dual;

select REGEXP_COUNT(str,'\$#')+1 as cnt from v;

select length(translate(str,'$#'||str,'$#'))/length('$#')+1 as cnt from v;

另外也可以自己編寫函數，：

FUNCTION fun_getSpecharcounts_lhr(p_string IN VARCHAR2) RETURN NUMBER AS

        v_count    NUMBER := 0;

        v_position NUMBER := 0; --特殊字符的位置

        v_Spechar  VARCHAR2(5) := '/';

BEGIN

LOOP

--找到特殊字符的位置

SELECT instr(p_string, v_Spechar, v_position+1)

INTO   v_position

FROM   dual;

IF v_position = 0 OR (v_position >= length(p_string)) THEN

EXIT;

END IF;

            v_count := v_count + 1;

END LOOP;

RETURN v_count;

END fun_getSpecharcounts_lhr;

一.3.3 根據表中的行創建一個分隔列表

SELECT a.deptno,

SUM(a.sal) AS total_sal,

       listagg(a.ename, ',') within GROUP(ORDER BY ename) AS total_ename,

wmsys.wm_concat(a.ename) ,

to_char(wmsys.wm_concat(a.ename))

FROM   scott.emp a

GROUP BY a.deptno;

一.3.4 分解IP地址

SELECT regexp_substr(v.ip, '[^.]+', 1, 1) a,

       regexp_substr(v.ip, '[^.]+', 1, 2) b,

       regexp_substr(v.ip, '[^.]+', 1, 3) c,

       regexp_substr(v.ip, '[^.]+', 1, 4) d

FROM (SELECT '192.168.59.130' ip FROM DUAL) v;

一.4 第六章部分內容  數字處理

一.4.1 生成累積和

SELECT manager_id,

       last_name,

       salary,

SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary,employee_id ) l_csum,

SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary,employee_id RANGE UNBOUNDED PRECEDING) l_csum,

SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary,employee_id RANGE between UNBOUNDED PRECEDING and current row ) l_csum,

SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary,employee_id rows between UNBOUNDED PRECEDING and current row ) l_csum  ,

(SELECT listagg(b.salary, '+') within GROUP(ORDER BY salary,employee_id)

FROM   hr.employees b

WHERE  b.manager_id IN (101, 103, 108)

AND    b.manager_id = t.manager_id 

AND    b.salary <=  t.salary  )

FROM   hr.employees t

WHERE  manager_id IN (101, 103, 108);

一.4.2 求總和的百分比 RATIO_TO_REPORT

create table T_salary(F_depart varchar2(20),F_EMP varchar2(20),F_salary integer );

truncate table t_salary;

--

插入測試數據

insert into t_salary(f_depart, f_emp, f_salary)

select '信息管理部','張三',10000 from dual union all

select '信息管理部','李四',2000 from dual union all

select '人力資源部','王五',3000 from dual union all

select '人力資源部','趙六',10000 from dual;

commit;

select * from t_salary;

--查詢每個員工占所在部門的工資比例

SELECT f_depart,

       f_emp,

       f_salary,

SUM(f_salary) over(PARTITION BY f_depart) sum_salary,

       ratio_to_report(f_salary) over(PARTITION BY f_depart) ratio_salary

FROM   t_salary;

--遞歸查詢員工占所在部門的百分比, 以及部門所占公司的工資比例.

SELECT f_depart,

       f_emp,

       f_salary,

       g1,

SUM(f_salary) over(PARTITION BY decode(g1, 0, f_depart, NULL), g1) sum_salary,

       ratio_to_report(f_salary) over(PARTITION BY decode(g1, 0, f_depart, NULL), g1) r_salary

FROM (SELECT f_depart,

               f_emp,

SUM(f_salary) f_salary,

GROUPING(f_depart) + GROUPING(F_emp) g1

FROM   t_salary

GROUP BY ROLLUP(f_depart, f_emp)) t;

    由于分析函數可以使用普通函數的結果作為expr參數, 所以上面的代碼又可以整合為下述方式.

SELECT f_depart,

       f_emp,

SUM(f_salary) f_salary,

SUM(SUM(f_salary)) over(PARTITION BY decode(GROUPING(f_depart) + GROUPING(F_emp), 0, f_depart, NULL), GROUPING(f_depart) + GROUPING(F_emp)) sum_salary,

       ratio_to_report(SUM(f_salary)) over(PARTITION BY decode(GROUPING(f_depart) + GROUPING(F_emp), 0, f_depart, NULL), GROUPING(f_depart) + GROUPING(F_emp)) r_salary,

GROUPING(f_depart) + GROUPING(F_emp) g1

FROM   t_salary

GROUP BY ROLLUP(f_depart, f_emp);

一.5 第七、八、九、十章 日期處理、分析函數

這個第7、8章就是所有的日期函數的處理，基礎內容，沒啥分享的，第9和10章是繼續分析函數。

一.6 第11章 行列互轉

關于該章我之前分析過我整理的內容，參考：http://blog.itpub.net/26736162/viewspace-1272538/

這里我再增加一些內容，就是關于pivot和unpivot分析函數的應用。

一.6.1 pivot 函數

SELECT *

FROM (SELECT e.job,

               e.sal,

               e.deptno

FROM   scott.emp e)

pivot(SUM(sal) AS s

FOR    deptno IN(10 AS d10, 20, 30 AS d30))

ORDER BY 1;

---增加一個

SELECT *

FROM (SELECT e.job,

               e.sal,

               e.deptno ,

comm

FROM   scott.emp e)

pivot(SUM(sal) AS s,sum(comm) as c

FOR    deptno IN(10 AS d10, 20 as d20, 30 AS d30))

ORDER BY 1;

一.6.2 unpivot函數

drop table test purge;

create table test as

SELECT *

FROM (SELECT e.deptno,

               e.sal

FROM   scott.emp e)

pivot(COUNT(*) AS cnt, SUM(sal) AS s

FOR    deptno IN(10 AS d10, 20 AS d20, 30 AS d30))

ORDER BY 1;

SELECT * FROM test();

SELECT * FROM test unpivot(人次 FOR deptno IN(d10_cnt, d20_cnt, d30_cnt));

SELECT deptno AS 部門編碼,

       人次,

       工資

FROM test a

unpivot include nulls (人次 FOR deptno IN(d10_cnt as 10, d20_cnt as 20, d30_cnt as 30))

unpivot include nulls (工資 FOR deptno2 IN(d10_s as 10, d20_s as 20, d30_s as 30))

where  deptno= deptno2

;

一.7 第12、13章

一.7.1 用sql輸出九九乘法表

這個只能給個鏈接了，因為這個帖子上的太經典了：http://www.itpub.net/thread-762215-1-1.html

這里截取幾個例子：

select r1 || '*' || 1 || '=' || r1 * 1 A,

decode(r2, '', '', r2 || '*' || 2 || '=' || r2 * 2) b,

decode(r3, '', '', r3 || '*' || 3 || '=' || r3 * 3) C,

decode(r4, '', '', r4 || '*' || 4 || '=' || r4 * 4) D,

decode(r5, '', '', r5 || '*' || 5 || '=' || r5 * 5) E,

decode(r6, '', '', r6 || '*' || 6 || '=' || r6 * 6) F,

decode(r7, '', '', r7 || '*' || 7 || '=' || r7 * 7) G,

decode(r8, '', '', r8 || '*' || 8 || '=' || r8 * 8) H,

decode(r9, '', '', r9 || '*' || 9 || '=' || r9 * 9) I

from (select level r1,

lag(level+1, 1) over(order by level) r2,

lag(level+2, 2) over(order by level) r3,

lag(level+3, 3) over(order by level) r4,

lag(level+4, 4) over(order by level) r5,

lag(level+5, 5) over(order by level) r6,

lag(level+6, 6) over(order by level) r7,

lag(level+7, 7) over(order by level) r8,

lag(level+8, 8) over(order by level) r9

from dual

connect by level < 10);

SELECT rn,

       ltrim(MAX(sys_connect_by_path(product, '  ')), '  ') product

FROM   (SELECT rn,

               product,

               MIN(product) over(PARTITION BY rn) product_min,

               (row_number() over(ORDER BY rn, product)) +

               (dense_rank() over(ORDER BY rn)) numId

        FROM   (SELECT b.rn,

                       a.rn || '*' || b.rn || '=' || a.rn * b.rn product

                FROM   (SELECT rownum rn FROM all_objects WHERE rownum <= 9) a,

                       (SELECT rownum rn FROM all_objects WHERE rownum <= 9) b

                WHERE  a.rn <= b.rn

                ORDER  BY b.rn,

                          product))

START  WITH product = product_min

CONNECT BY numId - 1 = PRIOR numId

GROUP  BY rn

ORDER  BY rn;

select replace(reverse(sys_connect_by_path(reverse(rownum || '*' || lv || '=' || rpad(rownum * lv, 2)),'/ ')),'/')

   from (select level lv from dual connect by level < 10)

where lv = 1

connect by lv + 1 = prior lv;

select ltrim(sys_connect_by_path

    (rownum - rn1+1||'*'||rownum || '=' || rpad(rownum * (rownum - rn1+1), 2) ,'   '))

    from

    (select rownum rn1 from dual connect by rownum <=9)

    where rn1 = 1

    connect by rn1+1 = prior rn1;

with t as (select level as n from dual connect by level <=9)

   select max(substr(sys_connect_by_path(b.n || '*' || a.n || '=' || a.n * b.n, ', '),3)) as val

     from t a, t b

    where a.n >= b.n

     start with b.n=1

    connect by a.n=prior a.n and b.n=prior b.n+1

    group by a.n

order by val

;

一.8 總結

到此SQL查詢優化改寫第5-13章基本over，重點是對分析函數的領悟和掌握，希望對做SQL優化的童鞋有所幫助。

一.9 about me

...........................................................................................................................................................................................

本文作者：小麥苗，只專注于數據庫的技術，更注重技術的運用

ITPUB BLOG：http://blog.itpub.net/26736162

本文地址：http://blog.itpub.net/26736162/viewspace-1665934/

本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d

QQ：642808185 若加QQ請注明你所正在讀的文章標題

創作時間地點：2015-05-21 09:00~ 2015-05-21 18:00 于×××交易中心

<版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任!>

...........................................................................................................................................................................................