Shulou(Shulou.com)05/31 Report--

Editor to share with you what is the use of the ROLLUP,CUBE,GROUPING SETS,grouping_id () function, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1.ROLLUP

Group by rollup can be understood as group by one column less at a time from right to left.

For example, group by rollup is grouped in the order of group by (1Magazine 3)-> group by (1Magne2)-> group by (1)-> group by null (final summary).

Equivalent to:

Select A ~ ~ B ~ C ~ ~ sum (E) from test group by A ~ ~ B ~ C

Union all

Select A ~ ~ B ~ sum (E) from test group by ~ B

Union all

Select Areco nullpaper nullrem 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), the permutations and combinations of each column need to be group by

For example, group by cube is grouped in the order of group by (1mag2)-> (1mag2)-> (1mag3)-> (2p3)-> (2)-> (3)-> group by null (final summary).

Equivalent to:

Select A ~ ~ B ~ C ~ ~ sum (E) from test group by A ~ ~ B ~ C

Union all

Select A ~ ~ B ~ sum (E) from test group by ~ B

Union all

Select A ~ C ~ ~ C ~ (E) from test group by ~ C

Union all

Select Areco nullpaper nullrem sum (E) from test group by A

Union all

Select null,B,C,sum (E) from test group by B Magi 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

Custom grouping scheme

Group by GROUPING SETS (1 group by 2) = (1), (2), (3) respectively

Group by grouping sets ((1) 2), 3) = (1) 2), (3) group by respectively

4. Combined application

Group by Amam rollup (Amae B)

Cartesian product will be applied to all sets after group by

So the order is: (a, (A)), (A), (A), (A) null) = (A), (A), (A)

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

Select A ~ ~ B ~ ~ sum (E) from test1 group by ~ ~ B

Union all

Select A _ paper _ null _ sum (E) from test1 group by A

Union all

Select A _ paper _ sum _ (E) from test1 group by A

5.GROUPING_ID ()

That is, the GROUPING function is used to distinguish between normal rows and aggregate rows after grouping. If it is an aggregate row, 1 is returned, and vice versa, 0.

GROUPING_ID is an enhanced version of GROUPING, and unlike GROUPING, which can have only one expression, it can take multiple expressions.

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.

These are all the contents of the article "what's the use of the ROLLUP,CUBE,GROUPING SETS,grouping_id () function?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.