Oracle Reporting 3 - Aggregation Level

To determine the aggregation level in a report, Oracle provides grouping_id and group_id functions.
Grouping_ID:GROUPING_ID returns a single number that enables you to determine the exact GROUP BY level. For each row, GROUPING_ID takes the set of 1's and 0's that would be generated if you used the appropriate GROUPING functions and concatenates them, forming a bit vector. The bit vector is treated as a binary number, and the number's base-10 value is
returned by the GROUPING_ID function. For instance, if you group with the expression CUBE(a, b) the possible values are as follows:

select deptno, job, sum(sal), grouping_id(deptno, job)
from emp
group by cube(deptno, job)
order by deptno nulls last; 


Group_ID: The GROUP_ID function lets you distinguish among duplicate groupings. If there are multiple sets of rows calculated for a given level, GROUP_ID assigns the value of 0 to all the rows in the first set. All other sets of duplicate rows for a particular grouping are assigned higher values, starting with 1.

SELECT country_iso_code, SUBSTR(cust_state_province,1,12), SUM(amount_sold),
GROUPING_ID(country_iso_code, cust_state_province) GROUPING_ID, GROUP_ID()
FROM sales, customers, times, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id
AND customers.country_id=countries.country_id AND times.time_id= '30-OCT-00'
AND country_iso_code IN ('FR', 'ES')
GROUP BY GROUPING SETS (country_iso_code,
ROLLUP(country_iso_code, cust_state_province));

Groupings: (country_id, cust_state_province), (country_id), (country_id), and (). 
You can filter out duplicate (region) groupings from the previous example by adding a HAVING clause condition GROUP_ID()=0 to the query.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/638844/viewspace-1061063/，如需轉載，請註明出處，否則將追究法律責任。