【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第五至十三章

站在用戶(hù)的角度思考問(wèn)題，與客戶(hù)深入溝通，找到九江網(wǎng)站設(shè)計(jì)與九江網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶(hù)體驗(yàn)好的作品，建站類(lèi)型包括：網(wǎng)站設(shè)計(jì)制作、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、域名注冊(cè)、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋九江地區(qū)。

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

一.2 前言部分

一.2.1 導(dǎo)讀

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

① 字符串的處理

② 常用分析函數(shù)

③ 用sql輸出九九乘法表

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

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

oracle 11g

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

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

【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第一章 http://blog.itpub.net/26736162/viewspace-1652985/

【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第二章 http://blog.itpub.net/26736162/viewspace-1654252/

【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第三章 http://blog.itpub.net/26736162/viewspace-1660422/

【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第四章 http://blog.itpub.net/26736162/viewspace-1661906/

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

一.2.4 本文簡(jiǎn)介

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

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

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

第7、8章講了DATE類(lèi)型的常見(jiàn)用法。

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

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

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

第12章講解樹(shù)形查詢(xún)

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

第 5 章 使用字符串

5.1 遍歷字符串

5.2 字符串文字中包含引號(hào) 

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

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

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

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

5.7 提取姓名的大寫(xiě)首字母縮寫(xiě)

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 返回各部門(mén)工資排名前三位的員工

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 確定指定年份季度的開(kāi)始日期和結(jié)束日期

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

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

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

8.14 識(shí)別重疊的日期范圍

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

第 9 章 范圍處理

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

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

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

9.4 合并時(shí)間段

第 10 章 高級(jí)查找

10.1 給結(jié)果集分頁(yè)

10.2 重新生成房間號(hào)

10.3 跳過(guò)表中 n 行

10.4 排列組合去重

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

第 11 章 報(bào)表和數(shù)據(jù)倉(cāng)庫(kù)運(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 對(duì)數(shù)據(jù)分組

11.8 計(jì)算簡(jiǎn)單的小計(jì)

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

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

11.11 人員在工作間的分布

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

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

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

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

11.16 listagg 與小九九

第 12 章 分層查詢(xún)

12.1 簡(jiǎn)單的樹(shù)形查詢(xún)

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

12.3 sys_connect_by_path 

12.4 樹(shù)形查詢(xún)中的排序 

12.5 樹(shù)形查詢(xún)中的 WHERE

12.6 查詢(xún)樹(shù)形的一個(gè)分支

12.7 剪去一個(gè)分支

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

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

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

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

13.3 把結(jié)果分級(jí)并轉(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)列”來(lái)得到隱含信息

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

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

下邊我針對(duì)不同的章節(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,',')+1as cnt from v;

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

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

create or replace view v as

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

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

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

另外也可以自己編寫(xiě)函數(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 =0OR (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;

--

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

insert into t_salary(f_depart,f_emp,f_salary)

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

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

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

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

commit;

select *from t_salary;

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

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;

--遞歸查詢(xún)員工占所在部門(mén)的百分比, 以及部門(mén)所占公司的工資比例.

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)容，沒(méi)啥分享的，第9和10章是繼續(xù)分析函數(shù)。

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

關(guān)于該章我之前分析過(guò)我整理的內(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(10AS d10,20,30AS 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(10AS d10,20as d20,30AS 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(10AS d10,20AS d20,30AS d30))

ORDER BY 1;

SELECT *FROM test();

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

SELECT deptno AS 部門(mén)編碼,

       人次,

       工資

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查詢(xún)優(yōu)化改寫(xiě)第5-13章基本over，重點(diǎn)是對(duì)分析函數(shù)的領(lǐng)悟和掌握，希望對(duì)做SQL優(yōu)化的童鞋有所幫助。

一.9 about me

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

本文作者：小麥苗，只專(zhuān)注于數(shù)據(jù)庫(kù)的技術(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請(qǐng)注明你所正在讀的文章標(biāo)題

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

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

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

名稱(chēng)欄目：【書(shū)評(píng):Oracle查詢(xún)優(yōu)化改寫(xiě)】第五至十三章
鏈接地址：http://jinyejixie.com/article0/gdpoio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護(hù)、網(wǎng)站策劃、品牌網(wǎng)站設(shè)計(jì)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、標(biāo)簽優(yōu)化、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話(huà)：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)