Rollup Cube Grouping Set Usage Summary

The above three kinds are often used in data summary statistics, I try to summarize their usage and meaning in a concise and understandable way.

GROUP by ROLLUP (c1,c2,c3...cn) rating Rollup

1. Summary from left to right step down, the most granular summary data is based on the right column to get

2. According to C1 get all summary, and then according to C2 Subdivision summary->...-> according to the CN get the most fine summary.

Purchase stock list purchase (item ID, quantity, price, type, date, supplier )

SELECT MONTH (date), type, vendor, SUM (Price)

From Purchase

GROUP by ROLLUP (MONTH (date), type, supplier)

The summary conditions include 4 types:

(All),(month (date)), (month (date),type), (month (date), type, supplier)

Result set corresponding to the condition:

The total purchase amount is aggregated, the monthly purchase amount is collected according to the date, the individual summaries of the purchase amount of different types of goods in each month, and each type of commodity is supplied by the different suppliers.

GROUP by CUBE (c1,c2,c3 ... Cn) gets a superset of all aggregated values , in other words, the resulting set of results includes all possible aggregations (all dimensions )

SELECT MONTH (date), type, vendor, SUM (price)

From Purchase

GROUP by CUBE (MONTH (date), type, supplier)

The summary conditions include 7 types:

(All),(month (date)),(Type), (vendor), (month (date), type),

(MONTH (date), supplier), (type, supplier)
The condition corresponding result set includes:

Summary of total purchase amount, sum of purchases in each month, sum of purchase amount of various types of goods, total purchase amount of different suppliers, purchase volume of different types of goods per month, purchase amount of different suppliers per month, and purchase amount of different suppliers for each type of commodity.

GROUP by GROUPING Sets (c1,c2,c3...cn) specifies a different summary column, merging the results to a result set. Similar multiple summary results are combined with UNION all.

SELECT MONTH (date), type, vendor, SUM (Price)

From Purchase

GROUP by GROUPING Sets (MONTH (date), type, supplier)

() condition indicates that all totals. You can also combine multiple columns into one grouping condition, such as ((C1,C2), C3)

Summary conditions:

(MONTH (date)), (type), (vendor), get the results separately, and then merge into a return result set.

Significance:

A result set output, a combined result set of multiple grouped totals. Purchase amount of each month + purchase amount of various types of goods + purchase amount of each supplier.

This article is from "Joe TJ" blog, be sure to keep this source http://joetang.blog.51cto.com/2296191/1680382

Rollup Cube Grouping Set Usage Summary