【書評:Oracle查詢優(yōu)化改寫】第五至十三章

【書評:Oracle查詢優(yōu)化改寫】第五至十三章

一.1 BLOG文檔結(jié)構(gòu)圖

一.2 前言部分

一.2.1 導(dǎo)讀

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

① 字符串的處理

② 常用分析函數(shù)

③ 用sql輸出九九乘法表

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

一.2.2 實(shí)驗(yàn)環(huán)境介紹

oracle 11g

一.2.3 相關(guān)參考文章鏈接

前4章的鏈接參考相關(guān)連接：

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

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

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

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

行列互轉(zhuǎn)內(nèi)容鏈接：http://blog.itpub.net/26736162/viewspace-1272538/

一.2.4 本文簡介

大家奇怪了，怎么不一章一章的寫了，直接跳躍了，小麥苗告訴大家，因?yàn)榈?到13章的內(nèi)容大多數(shù)是開發(fā)的內(nèi)容，和SQL調(diào)優(yōu)相差太遠(yuǎn)，這里列出這幾章的目錄，雖說是開發(fā)sql的內(nèi)容，但是很多實(shí)例還是比較實(shí)用的，比如對translate函數(shù)的應(yīng)用。

   第5章 處理字符串，包含translate函數(shù)和個(gè)別oracle的分析函數(shù)。

第6章 處理數(shù)字，介紹了分析函數(shù)

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

第9章仍然介紹分析函數(shù)

第10章的重點(diǎn)是結(jié)果集的分頁

第11章講述了行列轉(zhuǎn)換函數(shù)， 用UNPIVOT對UNION ALL做一定的優(yōu)化，還有ROLLUP及CUBE可以讓你少寫一些UNION ALL語句。

第12章講解樹形查詢

第13章選取了部分網(wǎng)友的需求案例，希望讀者能通過這些案例的啟發(fā)找到實(shí)現(xiàn)自己需求的思路。

第 5 章 使用字符串

5.1 遍歷字符串

5.2 字符串文字中包含引號 

5.3 計(jì)算字符在字符串中出現(xiàn)的次數(shù)

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

5.5 將字符和數(shù)字?jǐn)?shù)據(jù)分離

5.6 查詢只包含字母或數(shù)字型的數(shù)據(jù)

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

5.8 按字符串中的數(shù)值排序

5.9 根據(jù)表中的行創(chuàng)建一個(gè)分隔列表

5.10 提取第 n 個(gè)分隔的子串

5.11 分解 IP 地址

5.12 將分隔數(shù)據(jù)轉(zhuǎn)換為多值 IN 列表 

5.13 按字母順序排列字符串

5.14 判別可作為數(shù)值的字符串

第 6 章 使用數(shù)字

6.1 常用聚集函數(shù) 

6.2 生成累計(jì)和 

6.3 計(jì)算累計(jì)差

6.4 更改累計(jì)和的值

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

6.6 計(jì)算出現(xiàn)次數(shù)最多的值

6.7 返回最值所在行數(shù)據(jù) 

6.8 first_value

6.9 求總和的百分比

第 7 章 日期運(yùn)算

7.1 加減日、月、年

7.2 加減時(shí)、分、秒

7.3 日期間隔之時(shí)、分、秒

7.4 日期間隔之日、月、年

7.5 確定兩個(gè)日期之間的工作天數(shù)

7.6 計(jì)算一年中周內(nèi)各日期的次數(shù)

7.7 確定當(dāng)前記錄和下一條記錄之間相差的天數(shù)

第 8 章 日期操作

8.1 SYSDATE 能得到的信息

8.2 INTERVAL

8.3 EXTRACT

8.4 確定一年是否為閏年

8.5 周的計(jì)算

8.6 確定一年內(nèi)屬于周內(nèi)某一天的所有日期

8.7 確定某月內(nèi)第一個(gè)和最后一個(gè)“周內(nèi)某天”的日期

8.8 創(chuàng)建本月日歷

8.9 全年日歷

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

8.11 補(bǔ)充范圍內(nèi)丟失的值

8.12 按照給定的時(shí)間單位進(jìn)行查找

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

8.14 識別重疊的日期范圍

8.15 按指定間隔匯總數(shù)據(jù)

第 9 章 范圍處理

9.1 定位連續(xù)值的范圍

9.2 查找同一組或分區(qū)中行之間的差

9.3 定位連續(xù)值范圍的開始點(diǎn)和結(jié)束點(diǎn)

9.4 合并時(shí)間段

第 10 章 高級查找

10.1 給結(jié)果集分頁

10.2 重新生成房間號

10.3 跳過表中 n 行

10.4 排列組合去重

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

第 11 章 報(bào)表和數(shù)據(jù)倉庫運(yùn)算

11.1 行轉(zhuǎn)列

11.2 列轉(zhuǎn)行 

11.3 將結(jié)果集反向轉(zhuǎn)置為一列 

11.4 抑制結(jié)果集中的重復(fù)值 

11.5 利用“行轉(zhuǎn)列”進(jìn)行計(jì)算

11.6 給數(shù)據(jù)分組

11.7 對數(shù)據(jù)分組

11.8 計(jì)算簡單的小計(jì)

11.9 判別非小計(jì)的行

11.10 計(jì)算所有表達(dá)式組合的小計(jì)

11.11 人員在工作間的分布

11.12 創(chuàng)建稀疏矩陣

11.13 對不同組/分區(qū)同時(shí)實(shí)現(xiàn)聚集

11.14 對移動(dòng)范圍的值進(jìn)行聚集

11.15 常用分析函數(shù)開窗講解

11.16 listagg 與小九九

第 12 章 分層查詢

12.1 簡單的樹形查詢

12.2 根節(jié)點(diǎn)、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)

12.3 sys_connect_by_path 

12.4 樹形查詢中的排序 

12.5 樹形查詢中的 WHERE

12.6 查詢樹形的一個(gè)分支

12.7 剪去一個(gè)分支

12.8 字段內(nèi) list 值去重 

第 13 章 應(yīng)用案例實(shí)現(xiàn)

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

13.2 搜索字母數(shù)字混合的字符串

13.3 把結(jié)果分級并轉(zhuǎn)為列

13.4 構(gòu)建基礎(chǔ)數(shù)據(jù)的重要性

13.5 根據(jù)傳入條件返回不同列中的數(shù)據(jù)

13.6 拆分字符串進(jìn)行連接

13.7 整理垃圾數(shù)據(jù)

13.8 用“行轉(zhuǎn)列”來得到隱含信息

13.9 用隱藏?cái)?shù)據(jù)進(jìn)行行轉(zhuǎn)列

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

下邊我針對不同的章節(jié)，選取感興趣的部分內(nèi)容分享給大家：

一.3 第五章部分內(nèi)容  字符串的處理

一.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 計(jì)算字符在字符串中出現(xiàn)的次數(shù)

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;

另外也可以自己編寫函數(shù)，：

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 根據(jù)表中的行創(chuàng)建一個(gè)分隔列表

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 第六章部分內(nèi)容  數(shù)字處理

一.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;

--

插入測試數(shù)據(jù)

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;

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

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;

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

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 第七、八、九、十章 日期處理、分析函數(shù)

這個(gè)第7、8章就是所有的日期函數(shù)的處理，基礎(chǔ)內(nèi)容，沒啥分享的，第9和10章是繼續(xù)分析函數(shù)。

一.6 第11章 行列互轉(zhuǎn)

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

這里我再增加一些內(nèi)容，就是關(guān)于pivot和unpivot分析函數(shù)的應(yīng)用。

一.6.1 pivot 函數(shù)

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;

---增加一個(gè)

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函數(shù)

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輸出九九乘法表

這個(gè)只能給個(gè)鏈接了，因?yàn)檫@個(gè)帖子上的太經(jīng)典了：http://www.itpub.net/thread-762215-1-1.html

這里截取幾個(gè)例子：

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 總結(jié)

到此SQL查詢優(yōu)化改寫第5-13章基本over，重點(diǎn)是對分析函數(shù)的領(lǐng)悟和掌握，希望對做SQL優(yōu)化的童鞋有所幫助。

一.9 about me

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

本文作者：小麥苗，只專注于數(shù)據(jù)庫的技術(shù)，更注重技術(shù)的運(yùn)用

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

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

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

QQ：642808185 若加QQ請注明你所正在讀的文章標(biāo)題

創(chuàng)作時(shí)間地點(diǎn)：2015-05-21 09:00~ 2015-05-21 18:00 于×××交易中心

<版權(quán)所有，文章允許轉(zhuǎn)載，但須以鏈接方式注明源地址，否則追究法律責(zé)任!>

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