本文介绍GROUPING函数在SQL中如何帮助处理ROLLUP和CUBE操作产生的NULL值，通过实例展示如何使用该函数标识子总计行及聚合级别，并结合DECODE函数提高结果集的可读性。
70. Which two statements are true about the GROUPING function? (Choose two.)
A. It is used to find the groups forming the subtotal in a row.
B. It is used to identify the NULL value in the aggregate functions.
C. It is used to form the group sets involved in generating the totals and
subtotals.
D. It can only be used with ROLLUP and CUBE operators specified in the GROUP
BY clause.
Answer: AD
GROUPING Functions
Two challenges arise with the use of ROLLUP and CUBE. First, how can you programmatically determine which result set rows are subtotals, and how do you find the exact level of aggregation for a given subtotal? You often need to use subtotals in calculations such as percent-of-totals, so you need an easy way to determine which rows are the subtotals. Second, what happens if query results contain both stored NULL values and "NULL" values created by a ROLLUP or CUBE? How can you differentiate between the two? See Oracle Database SQL Reference for syntax and restrictions.
GROUPING Function
GROUPING handles these problems. Using a single column as its argument, GROUPING returns 1 when it encounters a NULL value created by a ROLLUP or CUBEoperation. That is, if the NULL indicates the row is a subtotal, GROUPING returns a 1. Any other type of value, including a stored NULL, returns a 0.
GROUPING appears in the selection list portion of a SELECT statement. Its form is:
SELECT … [GROUPING(dimension_column)…] …
GROUP BY … {CUBE | ROLLUP| GROUPING SETS} (dimension_column)
Example 20-6 GROUPING to Mask Columns
This example uses GROUPING to create a set of mask columns for the result set shown in Example 20-3. The mask columns are easy to analyze programmatically.
SELECT channel_desc, calendar_month_desc, country_iso_code,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) AS Ch,
GROUPING(calendar_month_desc) AS Mo, GROUPING(country_iso_code) AS Co
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND countries.country_iso_code IN ('GB', 'US')
GROUP BY ROLLUP(channel_desc, calendar_month_desc, countries.country_iso_code);
CHANNEL_DESC CALENDAR CO SALES$ CH MO CO
-------------------- -------- -- -------------- ---------- ---------- ----------
Internet 2000-09 GB 16,569 0 0 0
Internet 2000-09 US 124,224 0 0 0
Internet 2000-09 140,793 0 0 1
Internet 2000-10 GB 14,539 0 0 0
Internet 2000-10 US 137,054 0 0 0
Internet 2000-10 151,593 0 0 1
Internet 292,387 0 1 1
Direct Sales 2000-09 GB 85,223 0 0 0
Direct Sales 2000-09 US 638,201 0 0 0
Direct Sales 2000-09 723,424 0 0 1
Direct Sales 2000-10 GB 91,925 0 0 0
Direct Sales 2000-10 US 682,297 0 0 0
Direct Sales 2000-10 774,222 0 0 1
Direct Sales 1,497,646 0 1 1
1,790,032 1 1 1
A program can easily identify the detail rows by a mask of "0 0 0" on the T, R, and D columns. The first level subtotal rows have a mask of "0 0 1", the second level subtotal rows have a mask of "0 1 1", and the overall total row has a mask of "1 1 1".
You can improve the readability of result sets by using the GROUPING and DECODE functions as shown in Example 20-7.
Example 20-7 GROUPING For Readability
SELECT DECODE(GROUPING(channel_desc), 1, 'Multi-channel sum', channel_desc) AS
Channel, DECODE (GROUPING (country_iso_code), 1, 'Multi-country sum',
country_iso_code) AS Country, TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc= '2000-09'
AND country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, country_iso_code);
CHANNEL COUNTRY SALES$
-------------------- ----------------- --------------
Multi-channel sum Multi-country sum 864,217
Multi-channel sum GB 101,792
Multi-channel sum US 762,425
Internet Multi-country sum 140,793
Internet GB 16,569
Internet US 124,224
Direct Sales Multi-country sum 723,424
Direct Sales GB 85,223
Direct Sales US 638,201
To understand the previous statement, note its first column specification, which handles the channel_desc column. Consider the first line of the previous statement:
SELECT DECODE(GROUPING(channel_desc), 1, 'All Channels', channel_desc)AS Channel
In this, the channel_desc value is determined with a DECODE function that contains a GROUPING function. The GROUPING function returns a 1 if a row value is an aggregate created by ROLLUP or CUBE, otherwise it returns a 0. The DECODE function then operates on the GROUPING function's results. It returns the text "All Channels" if it receives a 1 and the channel_desc value from the database if it receives a 0. Values from the database will be either a real value such as "Internet" or a stored NULL. The second column specification, displaying country_id, works the same way.
When to Use GROUPING
The GROUPING function is not only useful for identifying NULLs, it also enables sorting subtotal rows and filtering results. In Example 20-8, you retrieve a subset of the subtotals created by a CUBE and none of the base-level aggregations. The HAVING clause constrains columns that use GROUPING functions.
Example 20-8 GROUPING Combined with HAVING
SELECT channel_desc, calendar_month_desc, country_iso_code, TO_CHAR(
SUM(amount_sold), '9,999,999,999') SALES$, GROUPING(channel_desc) CH, GROUPING
(calendar_month_desc) MO, GROUPING(country_iso_code) CO
FROM sales, customers, times, channels, countries
WHERE sales.time_id=times.time_id AND sales.cust_id=customers.cust_id
AND customers.country_id = countries.country_id
AND sales.channel_id= channels.channel_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND country_iso_code IN ('GB', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, country_iso_code)
HAVING (GROUPING(channel_desc)=1 AND GROUPING(calendar_month_desc)= 1
AND GROUPING(country_iso_code)=1) OR (GROUPING(channel_desc)=1
AND GROUPING (calendar_month_desc)= 1) OR (GROUPING(country_iso_code)=1
AND GROUPING(calendar_month_desc)= 1);
CHANNEL_DESC C CO SALES$ CH MO CO
-------------------- - -- -------------- ---------- ---------- ----------
US 1,581,775 1 1 0
GB 208,257 1 1 0
Direct Sales 1,497,646 0 1 1
Internet 292,387 0 1 1
1,790,032 1 1 1
Compare the result set of Example 20-8 with that in Example 20-3 to see how Example 20-8 is a precisely specified group: it contains only the yearly totals, regional totals aggregated over time and department, and the grand total.
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
