按匯總分組/多維數據集

時不時地，您會遇到一個使您達到SQL限制的要求。我們中的許多人可能會提早放棄并使用Java / [或您的語言]計算內容。相反，使用SQL可能是如此簡單快捷。如果您使用的是高級數據庫，例如DB2 ， Oracle ， SQL Server ， Sybase SQL Anywhere （以及本例中的MySQL ，它們支持WITH ROLLUP子句），則可以利用ROLLUP / CUBE / GROUPING SETS分組功能。

讓我們看一下我的虛構薪水與虛構的朋友相比，后者選擇了不同的職業道路（觀察2011年的薪水增長情況）：

select 'Lukas'      as employee, 'SoftSkills' as company, 80000        as salary, 2007         as year from dualunion all select 'Lukas', 'SoftSkills', 80000,  2008 from dualunion all select 'Lukas', 'SmartSoft',  90000,  2009 from dualunion all select 'Lukas', 'SmartSoft',  95000,  2010 from dualunion all select 'Lukas', 'jOOQ',       200000, 2011 from dualunion all select 'Lukas', 'jOOQ',       250000, 2012 from dualunion all select 'Tom',   'SoftSkills', 89000,  2007 from dualunion all select 'Tom',   'SoftSkills', 90000,  2008 from dualunion all select 'Tom',   'SoftSkills', 91000,  2009 from dualunion all select 'Tom',   'SmartSoft',  92000,  2010 from dualunion all select 'Tom',   'SmartSoft',  93000,  2011 from dualunion all select 'Tom',   'SmartSoft',  94000,  2012 from dual

現在，我們習慣于使用簡單的分組和簡單的聚合函數來收集統計信息。例如，讓我們計算一下盧卡斯和湯姆在過去幾年中的平均收入：

with data as ([above select])select employee, avg(salary)from datagroup by employee

這將表明Lukas賺了更多：

+--------+-----------+
|EMPLOYEE|AVG(SALARY)|
+--------+-----------+
|Lukas   |     132500|
|Tom     |      91500|
+--------+-----------+

因此，找出他們在哪家公司的平均收入可能很有趣：

with data as (...)select company, employee, avg(salary)from datagroup by company, employeeorder by company, employee

隨即，很明顯，大筆錢在哪里，湯姆做出了一個錯誤的決定。

+----------+--------+-----------+
|COMPANY   |EMPLOYEE|AVG(SALARY)|
+----------+--------+-----------+
|jOOQ      |Lukas   |     225000|
|SmartSoft |Lukas   |      92500|
|SmartSoft |Tom     |      93000|
|SoftSkills|Lukas   |      80000|
|SoftSkills|Tom     |      90000|
+----------+--------+-----------+

卷起

通過添加分組字段，我們“丟失”了一些聚合信息。在以上示例中，不再直接從結果中獲得每位員工的總體平均工資。考慮到分組算法，這很明顯。但是在美觀的報告中，我們通常也希望顯示這些分組標題。這是ROLLUP，CUBE（和GROUPING SETS）起作用的地方。考慮以下查詢：

with data as (...)select company, employee, avg(salary)from datagroup by rollup(company), employee

上面的匯總功能現在將向分組結果集中添加其他行，并保留有用的匯總值。在這種情況下，當我們“匯總公司的薪水”時，我們將獲得剩余分組字段的平均值，即每位員工的平均值：

+----------+--------+-----------+
|COMPANY   |EMPLOYEE|AVG(SALARY)|
+----------+--------+-----------+
|SmartSoft |Tom     |      93000|
|SoftSkills|Tom     |      90000|
|{null}    |Tom     |      91500|
|jOOQ      |Lukas   |     225000|
|SmartSoft |Lukas   |      92500|
|SoftSkills|Lukas   |      80000|
|{null}    |Lukas   |     132500|
+----------+--------+-----------+

請注意，這些行與第一個查詢（第一個查詢僅由員工分組）所保存的信息相同。當我們將更多分組字段放入匯總功能時，這將變得更加有趣：

with data as (...)select company, employee, avg(salary)from datagroup by rollup(employee, company)

如您所見，分組字段的順序在匯總功能中很重要。現在，此查詢的結果還將添加支付給所有公司中所有員工的總體平均工資

+----------+--------+-----------+
|COMPANY   |EMPLOYEE|AVG(SALARY)|
+----------+--------+-----------+
|SmartSoft |Tom     |      93000|
|SoftSkills|Tom     |      90000|
|{null}    |Tom     |      91500|
|jOOQ      |Lukas   |     225000|
|SmartSoft |Lukas   |      92500|
|SoftSkills|Lukas   |      80000|
|{null}    |Lukas   |     132500|
|{null}    |{null}  |     112000|
+----------+--------+-----------+

為了標識要報告的總計行，可以在DB2，Oracle，SQL Server和Sybase SQL Anywhere中使用GROUPING（）函數。在Oracle和SQL Server中，還有更有用的GROUPING_ID（）函數：

with data as (...)select grouping_id(employee, company) id, company, employee, avg(salary)from datagroup by rollup(employee, company)

它記錄了當前行產生在匯總功能的哪個“分組級別”上：

+----+----------+--------+-----------+
|  ID|COMPANY   |EMPLOYEE|AVG(SALARY)|
+----+----------+--------+-----------+
|   0|SmartSoft |Tom     |      93000|
|   0|SoftSkills|Tom     |      90000|
|   1|{null}    |Tom     |      91500|
|   0|jOOQ      |Lukas   |     225000|
|   0|SmartSoft |Lukas   |      92500|
|   0|SoftSkills|Lukas   |      80000|
|   1|{null}    |Lukas   |     132500|
|   3|{null}    |{null}  |     112000|
+----+----------+--------+-----------+

立方體

多維數據集功能的工作原理類似，但多維數據集分組字段的順序變得無關緊要，因為所有分組組合都已合并。說起來有點棘手，所以讓它付諸實踐：

with data as (...)select grouping_id(employee, company) id, company, employee, avg(salary)from datagroup by cube(employee, company)

在以下結果中，您將獲得：

GROUPING_ID（）= 0：每個公司和每個員工的平均值。這是正常的分組結果
GROUPING_ID（）= 1：每位員工的平均值
GROUPING_ID（）= 2：每個公司的平均值
GROUPING_ID（）= 3：總體平均

+----+----------+--------+-----------+
|  ID|COMPANY   |EMPLOYEE|AVG(SALARY)|
+----+----------+--------+-----------+
|   3|{null}    |{null}  |     112000|
|   2|jOOQ      |{null}  |     225000|
|   2|SmartSoft |{null}  |      92800|
|   2|SoftSkills|{null}  |      86000|
|   1|{null}    |Tom     |      91500|
|   0|SmartSoft |Tom     |      93000|
|   0|SoftSkills|Tom     |      90000|
|   1|{null}    |Lukas   |     132500|
|   0|jOOQ      |Lukas   |     225000|
|   0|SmartSoft |Lukas   |      92500|
|   0|SoftSkills|Lukas   |      80000|
+----+----------+--------+-----------+

換句話說，使用CUBE（）函數，您將獲得提供給CUBE（）函數的分組字段的每種可能組合的分組結果，這將為n個“立方”分組字段產生2 ^ n GROUPING_ID（）

jOOQ中的支持

jOOQ 2.0引入了對這些功能的支持。如果要將最后一個選擇轉換為jOOQ，則可以大致得到以下Java代碼：

// assuming that DATA is an actual table...create.select(groupingId(DATA.EMPLOYEE, DATA.COMPANY).as("id"),DATA.COMPANY, DATA.EMPLOYEE, avg(SALARY)).from(DATA).groupBy(cube(DATA.EMPLOYEE, DATA.COMPANY));

有了這個功能強大的工具，您就可以準備好所有這些精美的報告和數據概述。有關更多詳細信息，請在SQL Server文檔頁面上繼續閱讀有關ROLLUP（），CUBE（）和GROUPING SETS（）函數的內容，其中對此進行了很好的解釋：
http://msdn.microsoft.com/en-us/library/bb522495.aspx

參考：來自JAVA，SQL和JOOQ博客的JCG合作伙伴 Lukas Eder的GROUP BY ROLLUP / CUBE 。

相關文章：

Java中的數據庫架構導航
ORM問題
SQL或NOSQL：這是問題嗎？
什么是NoSQL？

翻譯自: https://www.javacodegeeks.com/2011/12/group-by-rollup-cube.html

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/373836.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/373836.shtml
英文地址，請注明出處：http://en.pswp.cn/news/373836.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！