Oracle is a practical implementation method for summary reports! Grouping_id and rollup usage

In the financial system or other report statistics function, I want to have a lot of cases similar to the following report. With the grouping_id function, it will be easy to implement, and it will be very expensive.

 

Serial number	Item	Last year		This year		Increase or decrease
		Average daily balance	Cost Rate	Average daily balance	Cost Rate	Average daily balance	Cost Rate
	Total
I	Bank Loan
1	Used for commercial ticket quota
	China Development Bank
2	Other credit lines
	China Development Bank
	Bank of America
II	Group split
3.	Headquarters split

 

Resolution:This table is a three-level summary. The bank summarizes the quota (Commercial quota, other quota), various quotas are summarized to the split-in method (bank loan, group split-in, Headquarters split-in), and the split-in method is summarized to the total. Grouping_id can achieve N-level Summary

Usage:

Case

Select grouping_id (loan. nborrowtype,
Loan. nfinancetype,
Loan. ncounterpartyid) groupid,
Sum (BAL. mbalance)/365 currentyearavg,
From billbalance Trans
Where 1 = 1
Group by rollup (loan. nborrowtype, loan. nfinancetype, loan. ncounterpartyid)
Order by loan. nborrowtype DESC,
Loan. nfinancetype DESC,
Loan. ncounterpartyid DESC

Resolution:Grouping_id (filed1, field2, field3. ..) To differentiate fields. The function uses the group of the first field as a level-1 display, that is, the split type in the table above. And so on, the second field is the second-level display, that is, the split mode ....

Group by rollup (filed1, field2, field3. ..), group field, same as above

Order by loan. nborrowtype DESC, loan. nfinancetype DESC, loan. ncounterpartyid DESC. Here, DESC indicates that the summary row is displayed on the display row. If you want to display the summary row below, change it to ASC ..

PS: The above three parts are indispensable