SQL優化一

来源:http://www.cnblogs.com/jmcui/archive/2017/05/14/6853769.html
-Advertisement-
Play Games

1、行列轉換： decode(條件,值1,返回值1,值2,返回值2,...值n,返回值n,預設值); select decode(sign(變數1-變數2),-1,變數1,變數2) from dual; --取較小值 sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1 例如: 變數1 ...


1、行列轉換：

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

 

select decode(sign(變數1-變數2),-1,變數1,變數2) from dual; --取較小值

 

　　sign()函數根據某個值是0、正數還是負數，分別返回0、1、-1

 

　　例如:

 

　　變數1=10，變數2=20

 

　　則sign(變數1-變數2)返回-1，decode解碼結果為“變數1”，達到了取較小值的目的。

 

        decode(欄位或欄位的運算，值1，值2，值3）

 

SELECT T.ZRZCODE,

     SUM(DECODE(T.LSBZ, 0, 1, 0)) YX,

     SUM(DECODE(T.LSBZ, 1, 1, 0)) LS

 FROM FW T

GROUP BY T.ZRZCODE;

 

2、遞歸查詢的優化

   lpad/rpad( string, padded_length, [ pad_string ] )

   解釋:字元不夠的時候向左或者向右填充。

   例如：SQL> select lpad('abcde',10,'x') from dual;

      LPAD('ABCDE',10,'X')

      xxxxxabcde

 

 

    

  1. SELECT ID, FATHER_ID, NAME, CONNECT_BY_ISLEAF LEAF   
  2. FROM T_TREE   
  3. START WITH FATHER_ID = 0   
  4. CONNECT BY PRIOR ID = FATHER_ID;  

 

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

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

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

 

 

例子：  SELECT LPAD(MENU_NAME,             LENGTHB(MENU_NAME) + LEVEL,             DECODE(CONNECT_BY_ISLEAF, 1, ' | ', '+'))   FROM MENU  START WITH PID = '0'  CONNECT BY PRIOR ID = PID;

 

3、利用分析函數排序和去重

http://blog.csdn.net/haiross/article/details/15336313#comments

 

分析函數是什麼？
分析函數是Oracle專門用於解決複雜報表統計需求的功能強大的函數，它可以在數據中進行分組然後計算基於組的某種統計值，並且每一組的每一行都可以返回一個統計值。

          

分析函數和聚合函數的不同之處是什麼？
普通的聚合函數用group by分組，每個分組返回一個統計值，返回的欄位名只能是分組名。而分析函數採用partition by分組，並且每組每行都可以返回一個統計值，返回的欄位名可以是每個欄位，因為是對應到記錄的，所以沒有關係。

              

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

 

 開窗函數over（）包含三個分析子句：分組子句(partition by)， 排序子句(order by)， 視窗子句(rows)
      視窗就是分析函數分析時要處理的數據範圍，就拿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)的彙總
 

而無論是否省略分組子句，如下結論都是成立的：

 

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

 2、當省略視窗子句時:
    a) 如果存在order by則預設的視窗是unbounded preceding and current row  --當前組的第一行到當前行，即在當前組中，第一行到當前行
    b) 如果同時省略order by則預設的視窗是unbounded preceding and unbounded following  --整個組

 

兩個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 ...)
  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行的數據

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

 

 

lag(arg1,arg2,arg3)
第一個參數是列名，
第二個參數是偏移的offset，
第三個參數是超出記錄視窗時的預設值。

select id,name,lag(name,1,0) over(order by id) from kkk; 

 

 

 

 

去重：

  1、利用rowid的唯一性查詢或刪除重覆數據

   SELECT * FROM T_FWXX a WHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM T_FWXX b WHERE a. FWZL =b. FWZL);

   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   聚合函數(within group語法): SELECT RANK(50000234, 38) WITHIN GROUP(ORDER BY ZRZ.ZRZCODE, FW.ZZMJ DESC) PM      FROM FW, ZRZ     WHERE FW.ZRZCODE = ZRZ.ZRZCODE     AND ZRZ.ZRZCODE = 50000234;--給定面積在幢中排名  

RANK(50000234,38)即:當zrz.zrzcode=50000234和fw.zzmj=38，插入到資料庫後的排名是多少？

如果存在一條記錄，這條記錄的salary欄位值為1500。
那麼將該條記錄插入employees表中後，按照salary欄位降序排列後，該條記錄的序號為多少？

 

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

 

 

 4、求占比、小計和總計

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

SELECT ZRZ.ZRZCODE,        ZRZ.ZH,        FW.FWZL,        FW.ZZMJ,        ROUND(RATIO_TO_REPORT(FW.ZZMJ) OVER(PARTITION BY ZRZ.ZRZCODE) * 100,3) SCALE   FROM FW, ZRZ  WHERE FW.ZRZCODE = ZRZ.ZRZCODE    AND ZRZ.ZRZCODE = 50000234;--房屋面積占幢中面積比例   select deptno,ename,empno,round(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno)*100,1) 百分比 from emp2   

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


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

 

2)、group by cube(a, b, c)：
則首先會對(a、b、c)進行group by，
然後依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最後對全表進行彙總操作。

 

 

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

 

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

 

select deptno,sum(sal) from emp2 group by rollup(deptno)   SELECT E.LOCALE, E.GENDER, COUNT(1)   FROM EMPLOYEE E  GROUP BY ROLLUP(E.LOCALE, E.GENDER)  ORDER BY E.LOCALE, E.GENDER;--小計，總計   SELECT E.LOCALE, E.GENDER, COUNT(1)   FROM EMPLOYEE E  GROUP BY CUBE(E.LOCALE, E.GENDER)  ORDER BY E.LOCALE, E.GENDER;--X,Y軸同時求小計

 

 5、單條記錄插入多表

      原理：利用一個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()

 

 

 6、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);

 

 7、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()那樣

 

  實例：取出各個部門薪資最高的員工編號

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

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

 

8、SQL查詢正則表達式的使用

 

  ORACLE中的支持正則表達式的函數主要有下麵四個：
1，REGEXP_LIKE ：與LIKE的功能相似

    select * from emp2 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 ：與SUBSTR的功能相似

   

REGEXP_SUBSTR函數格式如下：
   function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr     ：需要進行正則處理的字元串
__pattern    ：進行匹配的正則表達式
__position   ：起始位置，從第幾個字元開始正則表達式匹配（預設為1）
__occurrence ：標識第幾個匹配組，預設為1
__modifier   ：模式（'i'不區分大小寫進行檢索；'c'區分大小寫進行檢索。預設為'c'。）
  1. --1、查詢使用正則分割後的第一個值，也就是34  
  2. SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,1,'i') AS STR FROM DUAL;  
  3. --結果是：34   
  4. --2、查詢使用正則分割後的最後一個值，也就是-23  
  5. SELECT REGEXP_SUBSTR('34,56,-23','[^,]+',1,3,'i') AS STR FROM DUAL;  
  6. --結果是：-23  


4，REGEXP_REPLACE ：與REPLACE的功能相似

 

 

 

 

    

9、常見函數

TRUNC:截取函數 EXTRACT：用於從一個date或者interval類型中截取到特定的部分 NVL DECODE

length:字元長度

lengthb:位元組長度

ASCII INITCAP：首字母大寫   SOUNDEX：返回由四個字元組成的代碼 (SOUNDEX) 以評估兩個字元串的相似性 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND：函數用於把數值欄位舍入為指定的小數位數

 

10、分頁函數

 

--普通寫法

 

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;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 用Anko和Kotlin實現Android上的對話框和警告提示（KAD 24）
    原作者介紹 Anko與Kotlin 怎樣使提示框和對話框編寫變得更加簡單。 ...
  • [device tree] How to decompile a compiled .dtb (device tree blog) into .dts (device tree source).
    Usage: dtc [options] <input file + I, in format <arg Input formats are: dts device tree source text dtb device tree blob fs /proc/device tree st ...
  • Android學習筆記-EditText(輸入框)(一)
    文章參考自：http://www.runoob.com/w3cnote/android-tutorial-edittext.html 1.設置預設提示文本 預設提示文本的兩個屬性如下： 前者設置提示的文本內容，後者設置提示文本的顏色！ 2.獲得焦點後全選組件內所有文本內容 當我們點擊想當我們的輸入框 ...
  • Android開發筆記（8）——調用子Activity
    轉載請註明：http://www.cnblogs.com/igoslly/p/6853730.html 調用子Activity 需要子Activity返回值 MainActivity使用startActivityForResult方法 MainActivity使用startActivityForRe ...
  • MySQL5.5多實例編譯安裝——多配置文件
    MySQL多實例簡單的說就是在一臺伺服器上安裝一套MySQL程式，通過不同的埠對外提供訪問，多實例不僅節省物理主機成本，還有效提升了單台物理主機的CPU、磁碟I/O使用效率，而且還可以在多實例之間做部署資料庫HA方案，根據不同的配置文件和啟動文件配置多實例，這種方法邏輯和配置簡單，但是不方便管理。 ...
  • Oracle學習筆記
    一下是我學習Oracle時的基礎練習，涵蓋了編程中的重點知識！特別適合用來複習！ 轉載記得標註！！！ ...
  • 用一條SQL語句查出每門課都大於80分的學生的姓名
    用一條SQL語句查出每門課都大於80分的學生的姓名，數據表結構如下： 建表SQL如下： 查詢每門課都大於80分的同學的姓名： 查詢平均分大於80的學生的姓名： ...
  • Flume Spooldir 源的一些問題
    （ 自己寫的插件，數據序列化，格式化拋出的異常都會導致flume停止，不能繼續取數據，異常可以自己處理 ） 最近在用Flume做數據的收集。用到了裡面的Spooldir的源在使用中有如下的問題： 如果文件的某一行有亂碼，不符合指定的編碼規範，那麼flume會拋出一個exception，然後就停在那兒 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類