天天看點
傳回

SQL優化一(SQL使用技巧)

1、行列轉換

oracle 中的行列轉換是通過 decode 函數實作的：

decode(條件,值1,傳回值1,值2,傳回值2,...值n,傳回值n,預設值);

舉例：查詢emp表中的每個部門的人數？

SELECT sum(decode(deptno,10,1,0)) as 部門10,
            sum(decode(deptno,20,1,0)) as 部門20,
            sum(decode(deptno,30,1,0)) as 部門30 from emp
SQL優化一(SQL使用技巧)

做個對比

select deptno,count(deptno) from emp group by deptno
SQL優化一(SQL使用技巧)

2、遞歸查詢的優化 

# 解釋:字元不夠的時候向左或者向右填充。
lpad/rpad( string, padded_length, [ pad_string ] )      
# 輸出： xxxxxabcde
select lpad('abcde',10,'x') from dual;      
SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
FROM T_TREE   
START WITH FATHER_ID = 0   
CONNECT BY PRIOR ID = FATHER_ID;

解釋：CONNECT_BY_ISLEAF 判斷該行記錄是否為葉子節點，如果是傳回1，否則傳回0

          START WITH FATHER_ID = 0 規定哪一條記錄為根節點

          CONNECT BY PRIOR ID = FATHER_ID 判斷此節點的父節點是哪一條記錄

3、分析函數

分析函數是什麼？

分析函數是 Oracle 專門用于解決複雜報表統計需求的功能強大的函數，它可以在資料中進行分組然後計算基于組的某種統計值，并且每一組的每一行都可以傳回一個統計值。

分析函數和聚合函數的不同之處是什麼？

普通的聚合函數用 group by 分組，每個分組傳回一個統計值，傳回的字段名隻能是分組名。而分析函數采用partition by分組，并且每組每行都可以傳回一個統計值，傳回的字段名可以是每個字段，因為是對應到記錄的，是以沒有關系。

分析函數的形式

分析函數帶有一個開窗函數over()，包含三個分析子句:分組(partition by), 排序(order by), 視窗(rows) ，他們的使用形式如下：

over(partition by xxx order by yyy rows between zzz)

視窗就是分析函數分析時要處理的資料範圍，就拿 sum 來說,它是 sum 視窗中的記錄而不是整個分組中的記錄，是以我們在想得到某個欄位的累計值時，我們需要把視窗指定到該分組中的第一行資料到目前行, 如果你指定該視窗從該分組中的第一行到最後一行,那麼該組中的每一個sum值都會一樣，即整個組的總和。

OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)：目前組第一行到目前行的彙總
OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)：目前行到最後一行的彙總
OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)：目前行的上一行(rownum-1)到目前行的彙總
OVER(PARTITION BY DEPTNO ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)：目前行的上一行(rownum-1)到下兩行(rownum+2)的彙總 

tips：

 1、視窗子句不能單獨出現，必須有order by子句時才能出現。

 2、當省略視窗子句時

    a)  如果存在 order by 則預設的視窗是 unbounded preceding and current row  --目前組的第一行到目前行，即在目前組中，第一行到目前行，這裡強調一下，如果 partition by 字段和 order by 字段一樣的話，這個 order by不生效，相當于省略了 order by。

    b)  如果同時省略 order by 則預設的視窗是 unbounded preceding and unbounded following  --整個組

SQL 中 order by 和 開窗函數 order by 的執行時機？

分析函數（以及與其配合的開窗函數over()）是在整個sql查詢結束後（sql語句中的order by的執行比較特殊）再進行的操作, 也就是說 sql 語句中的 order by 也會影響分析函數的執行結果：

    a) 兩者一緻：如果sql語句中的order by滿足與分析函數配合的開窗函數 over() 分析時要求的排序，即sql語句中的order by子句裡的内容和開窗函數over() 中的order by子句裡的内容一樣，那麼sql語句中的排序将先執行,分析函數在分析時就不必再排序；

    b) 兩者不一緻：如果sql語句中的order by不滿足與分析函數配合的開窗函數over() 分析時要求的排序，即sql語句中的order by子句裡的内容和開窗函數over() 中的order by子句裡的内容不一樣，那麼sql語句中的排序将最後在分析函數分析結束後執行排序。

常用的分析函數：

1、row_number() over(partition by ... order by ...)  為每一條記錄傳回一個唯一的值。當碰到相同資料時，排名按照記錄集中記錄的順序依次遞增，現實情景為：個人在分組内的排名

2、rank() over(partition by ... order by ...)  得到每條記錄在資料中的排名，排名不跳躍

3、dense_rank() over(partition by ... order by ...) 得到每條記錄在資料中的排名，排名跳躍

4、count() over(partition by ... order by ...) 每個分組中，某個字段的統計

5、max() over(partition by ... order by ...)

6、min() over(partition by ... order by ...)

7、sum() over(partition by ... order by ...)

select ename,deptno,sal,sum(sal) over(partition by deptno order by ename) as A from emp
SQL優化一(SQL使用技巧)

8、avg() over(partition by ... order by ...)

9、first_value() over(partition by ... order by ...) 得到第一個記錄值

10、last_value() over(partition by ... order by ...) 得到最後一個記錄值

11、lag() over(partition by ... order by ...) lag函數可以在一次查詢中取出同一字段的前n行的資料

# lag(arg1,arg2,arg3)
# 第一個參數是列名，
# 第二個參數是偏移的offset，
# 第三個參數是超出記錄視窗時的預設值。
select id,name,lag(name,1,0) over(order by id) from kkk;

12、lead() over(partition by ... order by ...) lead函數可以在一次查詢中取出同一字段的後n行的值

4、去重

1、利用rowid的唯一性查詢或删除重複資料

select ROWNUM,ROWID,d1.* from dept2 d1 where d1.rowid=(select min(d2.rowid) from dept2 d2 where d2.deptno=d1.deptno);

2、給定重複行序号并去重

SELECT FWZL
    FROM (SELECT FWZL,
                 ROW_NUMBER() OVER(PARTITION BY FWZL ORDER BY ID DESC) RN
            FROM T_FWXX) F
   WHERE F.RN = 1

5、求占比、小計和總計

分析函數 RATIO_TO_REPORT 用來計算目前記錄的名額 expr 占開窗函數 over 中包含記錄的所有同一名額的百分比， 這裡如果開窗函數的統計結果為 null 或者為 0，就是說占用比率的被除數為 0 或者為 null，則得到的結果也為 0。 開窗條件 query_partition_clause 決定被除數的值，如果使用者忽略了這個條件，則計算查詢結果中所有記錄的彙總值。使用者不能使用其他分析函數或者 ratio_to_report 作為分析函數 ratio_to_report 的參數 expr，也就是說這個函數。

 百分比 

select deptno,ename,empno,round(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno)*100,1) 百分比 from emp
SQL優化一(SQL使用技巧)

rollup() 與cube()：排列組合分組 

ROLLUP，是GROUP BY子句的一種擴充，可以為每個分組傳回小計記錄以及為所有分組傳回總計記錄。

CUBE，也是GROUP BY子句的一種擴充，可以傳回每一個列組合的小計記錄，同時在末尾加上總計記錄。

group by rollup(a, b, c)

  1. 首先會對(a、b、c)進行group by，
  2. 然後再對(a、b)進行group by，
  3. 其後再對(a)進行group by，
  4. 最後對全表進行彙總操作。

group by cube(a, b, c)：

  1. 則首先會對(a、b、c)進行group by，
  2. 然後依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，

求每個分組的總計

select deptno,sal,sum(sal) from emp group by rollup(deptno,sal)
SQL優化一(SQL使用技巧)

求每一個列的組合的小計

select deptno,sum(sal) from emp group by cube(deptno,sal)
SQL優化一(SQL使用技巧)

6、單條記錄插入多表

原理：利用一個insert all 文法：insert all when .. then

INSERT ALL WHEN LOCALE = 1 THEN INTO EMPLOYEE1(ID, LOCALE, NAME, AGE, GENDER, CODE) VALUES (ID, LOCALE, NAME, AGE, GENDER, CODE)
           WHEN LOCALE = 32 THEN INTO EMPLOYEE2(ID, LOCALE, NAME, AGE, GENDER, CODE) VALUES(ID, LOCALE, NAME, AGE, GENDER, CODE)      
insert all into table values()
           into  table values()

7、Merge 的使用

解釋:DML語句,适用于批量處理

MERGE INTO table_name alias1 
　　USING (table|view|sub_query) alias2 
　　ON (join condition) 
　　WHEN MATCHED THEN 
　　UPDATE table_name 
　　SET col1 = col_val1, 
　　col2 = col2_val where 條件
　　WHEN NOT MATCHED THEN 
　　INSERT (column_list) VALUES (column_values) where 條件;      
MERGE INTO EMPLOYEE E

USING (SELECT * FROM EMPLOYEE1) E1
ON (E.NAME = E1.NAME)
WHEN MATCHED THEN
    UPDATE SET E.CODE = E1.CODE, E.AGE = E1.AGE
WHEN NOT MATCHED THEN
   INSERT  (E.ID, E.LOCALE, E.NAME, E.AGE, E.GENDER, E.CODE) VALUES(E1.ID, E1.LOCALE, E1.NAME, E1.AGE, E1.GENDER, E1.CODE);

備注：對兩張表的兩個字段相比對，如果比對上了就做更新操作，否則就做插入操作。

8、KEEP的使用

keep是Oracle下的另一個分析函數，他的用法不同于通過over關鍵字指定的分析函數，可以用于這樣一種場合下：取同一個分組下以某個字段排序後，對指定字段取最小或最大的那個值。   

MIN [ MAX ] (A) KEEP(DENSE_RANK FIRST [ LAST ] ORDER BY B)

這裡引用别人說的明的解釋一下：

DENSE_RANK

功能描述：根據ORDER BY子句中表達式的值，從查詢傳回的每一行，計算它們與其它行的相對位置。組内的資料按ORDER BY子句排序，然後給每一行賦一個号，進而形成一個序列，該序列從1開始，往後累加。每次ORDER BY表達式的值發生變化時，該序列也随之增加。有同樣值的行得到同樣的數字序号（認為null時相等的）。密集的序列傳回的時沒有間隔的數。

FIRST

功能描述：從DENSE_RANK傳回的集合中取出排在最前面的一個值的行（可能多行，因為值可能相等），是以完整的文法需要在開始處加上一個集合函數以從中取出記錄。

LAST

功能描述：從DENSE_RANK傳回的集合中取出排在最後面的一個值的行（可能多行，因為值可能相等），是以完整的文法需要在開始處加上一個集合函數以從中取出記錄。

是以預設排序下，FIRST可以了解是取小值，LAST取大值。而前面的MIN或者MAX則是在KEEP的結果集中取某一字段的最大值或最小值。

keep和普通分析函數的差別：

  • 普通的分析函數隻是列出分組後的記錄，而對每一個組的記錄進行統計分析。
  • keep對分組内的函數通過order by和max(),min()選取某個字段的值。可以了解成這裡的keep就是sum() groud by deptno 前的sum()那樣。

執行個體：取出各個部門薪資最高的員工編号

select deptno,empno,sal,max(empno) keep(dense_rank first order by sal desc) over(partition by deptno) from emp2

select deptno,max(empno) keep(dense_rank first order by sal desc) from emp2 group by deptno

解釋：按deptno分組，再對分組中的sal降序，取出第一個sal的員工号

SQL優化一(SQL使用技巧)

9、SQL查詢正規表達式的使用

ORACLE中的支援正規表達式的函數主要有下面四個：

1，REGEXP_LIKE ：與LIKE的功能相似

select * from emp where regexp_like(empno,'7[0-9]{2}9');

2，REGEXP_INSTR ：與INSTR的功能相似

REGEXP_INSTR 有 6 個參數

第一個是輸入的字元串

第二個是正規表達式

第三個是辨別從第幾個字元開始正規表達式比對。（預設為1）

第四個是辨別第幾個比對組。（預設為1）

第五個是指定傳回值的類型，如果該參數為0，則傳回值為比對位置的第一個字元，如果該值為非0則傳回比對值的最後一個位置。

第六個是是取值範圍：

i：大小寫不敏感；

c：大小寫敏感；

n：點号 . 不比對換行符号；

m：多行模式；

x：擴充模式，忽略正規表達式中的空白字元；

SELECT REGEXP_INSTR(a,'[0-9]+') AS A FROM test_reg_substr; 

3，REGEXP_SUBSTR 

function REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)

srcstr：需要進行正則處理的字元串

pattern：進行比對的正規表達式

position：起始位置，從第幾個字元開始正規表達式比對（預設為1）

occurrence：辨別第幾個比對組，預設為1

modifie：模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。預設為'c'。）

查詢使用正則分割後的第一個值，也就是34  

SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL

查詢使用正則分割後的最後一個值，也就是23    

SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL

4，REGEXP_REPLACE ：與REPLACE的功能相似

10、常見函數

TRUNC：截取函數

EXTRACT：用于從一個date或者interval類型中截取到特定的部分

NVL：

DECODE：行列轉換

SOUNDEX：傳回由四個字元組成的代碼 (SOUNDEX) 以評估兩個字元串的相似性

MONTHS_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

ROUND：函數用于把數值字段舍入為指定的小數位數

WITHIN GROUP：

select rank(1500) within group (order by sal desc) "rank of 1500" from emp;

解釋：如果存在一條記錄，這條記錄的salary字段值為1500。那麼将該條記錄插入emp表中後，按照sal字段降序排列後，該條記錄的序号為多少？

可以使用 within group 關鍵字的函數有 rank，dense_rank，PERCENT_RANK，PERCENTILE_CONT，PERCENTILE_DISC等  

SQL優化一(SQL使用技巧)

11、分頁函數

-- 普通寫法

SELECT AA.FWZL, AA.FWTYBH
  FROM (SELECT A.FWZL, A.FWTYBH, ROWNUM RN
          FROM (SELECT F.FWZL, F.FWTYBH FROM FW F ORDER BY F.FWTYBH DESC) A
         WHERE ROWNUM <= 120020) AA
 WHERE AA.RN > 120000;

-- rowid寫法

SELECT /*+ ROWID(FW) */ FW.FWZL, FW.FWTYBH
   FROM FW FW,
        (SELECT AA.RID, AA.RN
           FROM (SELECT A.RID, ROWNUM RN
                  FROM (SELECT /*+ index(F IDX_FW_FWTYBH) */
                         ROWID RID
                          FROM FW F
                          ORDER BY F.FWTYBH DESC) A
                 WHERE ROWNUM <= 120020) AA
          WHERE AA.RN > 120000) B
  WHERE FW.ROWID = B.RID;
上一篇: 今天是我們和中國一汽的大喜日子
下一篇: NodeJS安裝第一個工程.