GROUPING_ID

Syntax

[画像:Description of grouping_id.gif follows]
Description of the illustration ''grouping_id.gif''

Purpose

GROUPING_ID returns a number corresponding to the GROUPING bit vector associated with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function. In queries with many GROUP BY expressions, determining the GROUP BY level of a particular row requires many GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is useful in these cases.

GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros). By using GROUPING_ID you can avoid the need for multiple GROUPING functions and make row filtering conditions easier to express. Row filtering is easier with GROUPING_ID because the desired rows can be identified with a single condition of GROUPING_ID = n. The function is especially useful when storing multiple levels of aggregation in a single table.

Examples

The following example shows how to extract grouping IDs from a query of the sample table sh.sales:

SELECT channel_id, promo_id, sum(amount_sold) s_sales,
 GROUPING(channel_id) gc,
 GROUPING(promo_id) gp,
 GROUPING_ID(channel_id, promo_id) gcp,
 GROUPING_ID(promo_id, channel_id) gpc
 FROM sales
 WHERE promo_id > 496
 GROUP BY CUBE(channel_id, promo_id)
 ORDER BY channel_id, promo_id, s_sales, gc;
 
CHANNEL_ID PROMO_ID S_SALES GC GP GCP GPC
---------- ---------- ---------- ---------- ---------- ---------- ----------
 2 999 25797563.2 0 0 0 0
 2 25797563.2 0 1 1 2
 3 999 55336945.1 0 0 0 0
 3 55336945.1 0 1 1 2
 4 999 13370012.5 0 0 0 0
 4 13370012.5 0 1 1 2
 999 94504520.8 1 0 2 1
 94504520.8 1 1 3 3