ROLLUP,CUBE,GROUPING SETS,grouping

142次閱讀

共計(jì) 3269 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 ROLLUP,CUBE,GROUPING SETS,grouping_id() 函數(shù)有什么用，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

1.ROLLUP

group by rollup(1,2,3), 可以理解為從右到左以一次少一列的方式依次進(jìn)行 group by。

例如: group by rollup(1,2,3) 則以 group by(1,2,3) – group by(1,2) – group by(1) – group by null(最終匯總) 的順序進(jìn)行分組

相當(dāng)于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,null,null,sum(E) from test;

2.CUBE

group by cube(1,2,3), 需要對每一列的排列組合進(jìn)行 group by

例如: group by cube(1,2,3) 則以 group by(1,2,3) – (1,2) – (1,3) – (2,3) – (2) – (3) – group by null(最終匯總) 的順序進(jìn)行分組

相當(dāng)于:

Select A,B,C,sum(E) from test group by A,B,C
union all
Select A,B,null,sum(E) from test group by A,B
union all
Select A,null,C,sum(E) from test group by A,C
union all
Select A,null,null,sum(E) from test group by A
union all
Select null,B,C,sum(E) from test group by B,C
union all
Select null,B,null,sum(E) from test group by B
union all
Select null,null,C,sum(E) from test group by C
union all
Select null,null,null,sum(E) from test;

3.GROUPING SETS

自定義分組方案

group by GROUPING SETS(1,2,3) = (1),(2),(3) 分別 group by

group by grouping sets((1,2),3) = (1,2),(3) 分別 group by

4. 組合應(yīng)用

group by A,rollup(A,B)

將對所有 group by 后面的集合進(jìn)行笛卡爾積

因此順序?yàn)? (A,(A,B)),(A,A),(A,NULL) = (A,B),(A),(A)

Select A,B,sum(E) from test1 group by A, rollup(A,B);

Select A,B,sum(E) from test1 group by A,B
Union all
Select A,null,sum(E) from test1 group by A
Union all
Select A,null,sum(E) from test1 group by A;

5.GROUPING_ID()

即 GROUPING 函數(shù)用于區(qū)分分組后的普通行和聚合行。如果是聚合行，則返回 1，反之，則是 0。

GROUPING_ID 是 GROUPING 的增強(qiáng)版，與 GROUPING 只能帶一個(gè)表達(dá)式不同，它能帶多個(gè)表達(dá)式。

SELECT TO_CHAR (log_date, YYYY) year,
TO_CHAR (log_date, Q) quarter,
TO_CHAR (log_date, MM) month,
employee_id,
MIN (old_salary),
MIN (new_salary),
GROUPING_ID (TO_CHAR (log_date, YYYY),
TO_CHAR (log_date, Q),
TO_CHAR (log_date, MM))
gid
FROM plch_emp_log
GROUP BY ROLLUP (TO_CHAR (log_date, YYYY),
TO_CHAR (log_date, Q),
TO_CHAR (log_date, MM)),
employee_id;

YEAR QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY) GID
——– — —- ———– ————— ————— ———-
2010 1 01 100 1000 1800 0
2010 1 100 1000 1800 1
2010 2 04 100 1800 1900 0
2010 2 100 1800 1900 1
2010 3 09 100 1900 1500 0
2010 3 100 1900 1500 1
2010 100 1000 1500 3
2011 1 01 100 1500 2500 0
2011 1 100 1500 2500 1
2011 2 04 100 2500 2200 0
2011 2 100 2500 2200 1

YEAR QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY) GID
——– — —- ———– ————— ————— ———-
2011 100 1500 2200 3
100 1000 1500 7
2010 1 01 200 1000 1600 0
2010 1 03 200 1600 2500 0
2010 1 200 1000 1600 1
2010 2 05 200 2500 2300 0
2010 2 200 2500 2300 1
2010 3 09 200 2300 3000 0
2010 3 200 2300 3000 1
2010 200 1000 1600 3
2011 1 02 200 3000 2000 0

YEAR QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY) GID
——– — —- ———– ————— ————— ———-
2011 1 200 3000 2000 1
2011 3 07 200 2000 2800 0
2011 3 200 2000 2800 1
2011 200 2000 2000 3
200 1000 1600 7
2010 2 04 300 1000 2000 0
2010 2 05 300 2000 3000 0
2010 2 300 1000 2000 1
2010 4 10 300 3000 2700 0
2010 4 300 3000 2700 1
2010 300 1000 2000 3

YEAR QU MONT EMPLOYEE_ID MIN(OLD_SALARY) MIN(NEW_SALARY) GID
——– — —- ———– ————— ————— ———-
2011 1 02 300 2700 2500 0
2011 1 300 2700 2500 1
2011 3 09 300 2500 2900 0
2011 3 300 2500 2900 1
2011 300 2500 2500 3
300 1000 2000 7

39 rows selected.

以上是“ROLLUP,CUBE,GROUPING SETS,grouping_id() 函數(shù)有什么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完