首頁 > 開發者 > Oracle

oracle rollup和cube分析

來源：互聯網
上載者：User
創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

rollup是對group by的擴充，會進行小計和合計，而cube包含rollup，是粒度更精細的小計和合計。當只有一個欄位時，rollup和cube是一樣的。

    可用grouping對rollup和cube進行測試：0看；1不看[看錶示列有顯示，不看錶示沒有顯示]

             ① rollup
                  ㈠ 什麼都不看
                  ㈡ 從左往右  ┌ 先看第1個
                                        │ 再看前2個
                                        └ 後看前3個
             ② cube：0、1全排列

   rollup

    rollup後面指定的列以逗號分隔，rollup的計算結果和其後面指定的列的順序有關，因為rollup的分組具有方向性。若指定n列，則有n+1種分組方式。可改變列的順序，達到不同的業務需求。一定要牢記列的順序對結果的影響！

    例子：

[sql]
view plaincopyprint?
  1. 20:32:51 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job) 
  2. 20:33:06   2    from dept a,emp b 
  3. 20:33:06   3   where a.deptno=b.deptno 
  4. 20:33:06   4   group by
    rollup(a.dname,b.job) 
  5. 20:33:08   5  / 
  6.  
  7. DNAME      JOB           SUM_SAL GROUPING(A.DNAME)
    GROUPING(B.JOB) 
  8. ---------- ---------- ---------- ----------------- --------------- 
  9. SALES      CLERK             950                 0               0 
  10. SALES      MANAGER          2850                 0               0 
  11. SALES      SALESMAN         5600                 0               0 
  12. SALES                       9400                 0               1 
  13. RESEARCH   CLERK            1200                 0               0 
  14. RESEARCH   ANALYST          7000                 0               0 
  15. RESEARCH   MANAGER          2975                 0               0 
  16. RESEARCH                   11175                 0               1 
  17. ACCOUNTING CLERK            1300                 0               0 
  18. ACCOUNTING MANAGER          2450                 0               0 
  19. ACCOUNTING PRESIDENT        5000                 0               0 
  20. ACCOUNTING                  8750                 0               1 
  21.                            29325                 1               1 
  22.  
  23. 已選擇13行。  
20:32:51 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job)20:33:06   2    from dept a,emp b20:33:06   3   where a.deptno=b.deptno20:33:06   4   group by rollup(a.dname,b.job)20:33:08   5  /DNAME      JOB           SUM_SAL GROUPING(A.DNAME) GROUPING(B.JOB)---------- ---------- ---------- ----------------- ---------------SALES      CLERK             950                 0               0SALES      MANAGER          2850                 0               0SALES      SALESMAN         5600                 0               0SALES                       9400                 0               1RESEARCH   CLERK            1200                 0               0RESEARCH   ANALYST          7000                 0               0RESEARCH   MANAGER          2975                 0               0RESEARCH                   11175                 0               1ACCOUNTING CLERK            1300                 0               0ACCOUNTING MANAGER          2450                 0               0ACCOUNTING PRESIDENT        5000                 0               0ACCOUNTING                  8750                 0               1                           29325                 1               1已選擇13行。

    解釋：什麼都不看（1,1）；從左往右，先看第一個（0,1）

    可以將不需要進行小計和合計的列移出rollup，要小計的留在rollup裡面。

    比如：

[sql]
view plaincopyprint?
  1. 20:48:37 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job) 
  2. 20:57:43   2    from dept a,emp b 
  3. 20:57:43   3   where a.deptno=b.deptno 
  4. 20:57:43   4   group by a.dname,rollup(b.job) 
  5. 20:57:45   5  / 
  6.  
  7. DNAME                        JOB                   SUM_SAL
    GROUPING(A.DNAME) GROUPING(B.JOB) 
  8. ---------------------------- ------------------ ---------- ----------------- --------------- 
  9. SALES                        CLERK                     950                 0               0 
  10. SALES                        MANAGER                  2850                 0               0 
  11. SALES                        SALESMAN                 5600                 0               0 
  12. SALES                                                 9400                 0               1 
  13. RESEARCH                     CLERK                    1200                 0               0 
  14. RESEARCH                     ANALYST                  7000                 0               0 
  15. RESEARCH                     MANAGER                  2975                 0               0 
  16. RESEARCH                                             11175                 0               1 
  17. ACCOUNTING                   CLERK                    1300                 0               0 
  18. ACCOUNTING                   MANAGER                  2450                 0               0 
  19. ACCOUNTING                   PRESIDENT                5000                 0               0 
  20. ACCOUNTING                                            8750                 0               1 
  21.  
  22. 已選擇12行。  
20:48:37 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job)20:57:43   2    from dept a,emp b20:57:43   3   where a.deptno=b.deptno20:57:43   4   group by a.dname,rollup(b.job)20:57:45   5  /DNAME                        JOB                   SUM_SAL GROUPING(A.DNAME) GROUPING(B.JOB)---------------------------- ------------------ ---------- ----------------- ---------------SALES                        CLERK                     950                 0               0SALES                        MANAGER                  2850                 0               0SALES                        SALESMAN                 5600                 0               0SALES                                                 9400                 0               1RESEARCH                     CLERK                    1200                 0               0RESEARCH                     ANALYST                  7000                 0               0RESEARCH                     MANAGER                  2975                 0               0RESEARCH                                             11175                 0               1ACCOUNTING                   CLERK                    1300                 0               0ACCOUNTING                   MANAGER                  2450                 0               0ACCOUNTING                   PRESIDENT                5000                 0               0ACCOUNTING                                            8750                 0               1已選擇12行。

  

   cube

    和rollup不同，cube的計算結果和順序無關。若n列，則分組方式有2的n次方種。

[sql]
view plaincopyprint?
  1. 20:57:46 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job) 
  2. 21:27:03   2    from dept a,emp b 
  3. 21:27:03   3   where a.deptno=b.deptno 
  4. 21:27:03   4   group by
    cube(a.dname,b.job) 
  5. 21:27:04   5  / 
  6.  
  7. DNAME                        JOB                   SUM_SAL
    GROUPING(A.DNAME) GROUPING(B.JOB) 
  8. ---------------------------- ------------------ ---------- ----------------- --------------- 
  9.                                                      29325                 1               1 
  10.                              CLERK                    3450                 1               0 
  11.                              ANALYST                  7000                 1               0 
  12.                              MANAGER                  8275                 1               0 
  13.                              SALESMAN                 5600                 1               0 
  14.                              PRESIDENT                5000                 1               0 
  15. SALES                                                 9400                 0               1 
  16. SALES                        CLERK                     950                 0               0 
  17. SALES                        MANAGER                  2850                 0               0 
  18. SALES                        SALESMAN                 5600                 0               0 
  19. RESEARCH                                             11175                 0               1 
  20. RESEARCH                     CLERK                    1200                 0               0 
  21. RESEARCH                     ANALYST                  7000                 0               0 
  22. RESEARCH                     MANAGER                  2975                 0               0 
  23. ACCOUNTING                                            8750                 0               1 
  24. ACCOUNTING                   CLERK                    1300                 0               0 
  25. ACCOUNTING                   MANAGER                  2450                 0               0 
  26. ACCOUNTING                   PRESIDENT                5000                 0               0 
  27.  
  28. 已選擇18行。  
20:57:46 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job)21:27:03   2    from dept a,emp b21:27:03   3   where a.deptno=b.deptno21:27:03   4   group by cube(a.dname,b.job)21:27:04   5  /DNAME                        JOB                   SUM_SAL GROUPING(A.DNAME) GROUPING(B.JOB)---------------------------- ------------------ ---------- ----------------- ---------------                                                     29325                 1               1                             CLERK                    3450                 1               0                             ANALYST                  7000                 1               0                             MANAGER                  8275                 1               0                             SALESMAN                 5600                 1               0                             PRESIDENT                5000                 1               0SALES                                                 9400                 0               1SALES                        CLERK                     950                 0               0SALES                        MANAGER                  2850                 0               0SALES                        SALESMAN                 5600                 0               0RESEARCH                                             11175                 0               1RESEARCH                     CLERK                    1200                 0               0RESEARCH                     ANALYST                  7000                 0               0RESEARCH                     MANAGER                  2975                 0               0ACCOUNTING                                            8750                 0               1ACCOUNTING                   CLERK                    1300                 0               0ACCOUNTING                   MANAGER                  2450                 0               0ACCOUNTING                   PRESIDENT                5000                 0               0已選擇18行。

    注釋：和rollup的結果相比，cube的所有可能的分組都走一遍。

    可以去掉合計和某些不需要的小計，通過部分cube實現。部分cube比部分rollup來得有用多了。

[sql]
view plaincopyprint?
  1. 21:27:06 scott@ORCL (^ω^) select a.dname,b.job,sum(b.sal) sum_sal,grouping(a.dname),grouping(b.job) 
  2. 21:32:20   2    from dept a,emp b 
  3. 21:32:20   3   where a.deptno=b.deptno 
  4. 21:32:20   4   group by a.dname,cube(b.job) 
  5. 21:32:21   5  / 
  6.  
  7. DNAME                        JOB                   SUM_SAL
    GROUPING(A.DNAME) GROUPING(B.JOB) 
  8. ---------------------------- ------------------ ---------- ----------------- --------------- 
  9. SALES                                                 9400                 0               1 
  10. SALES                        CLERK                     950                 0               0 
  11. SALES                        MANAGER                  2850                 0               0 
  12. SALES                        SALESMAN                 5600                 0               0 
  13. RESEARCH                                             11175                 0               1 
  14. RESEARCH                     CLERK                    1200                 0               0 
  15. RESEARCH                     ANALYST                  7000                 0               0 
  16. RESEARCH                     MANAGER                  2975                 0               0 
  17. ACCOUNTING                                            8750                 0               1 
  18. ACCOUNTING                   CLERK                    1300                 0               0 
  19. ACCOUNTING                   MANAGER                  2450                 0               0 
  20. ACCOUNTING                   PRESIDENT                5000                 0               0 
  21.  
  22. 已選擇12行。 

原文地址：http://blog.csdn.net/linwaterbin/article/details/7985717

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

聯繫我們

該頁面正文內容均來源於網絡整理，並不代表阿里雲官方的觀點，該頁面所提到的產品和服務也與阿里云無關，如果該頁面內容對您造成了困擾，歡迎寫郵件給我們，收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容，歡迎發送郵件至： info-contact@alibabacloud.com 進行舉報並提供相關證據，工作人員會在 5 個工作天內聯絡您，一經查實，本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More