【書評: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版：提取碼：af2d

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

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

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

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

一.1 BLOG文件結構圖

一.2 前言部分

一.2.1 導讀

一.2.2 實驗環境介紹

一.2.3 相關參考文章連結

一.2.4 本文簡介

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

一.3.1 遍歷字串

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

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

一.3.4 分解IP地址

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

一.4.1 生成累積和

一.4.2 求總和的百分比 RATIO_TO_REPORT

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

一.6 第11章 行列互轉

一.6.1 pivot 函式

一.6.2 unpivot函式

一.7 第12、13章

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

一.8 總結

一.9 about me

相關文章

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

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

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

一.6 第11章行列互轉