GROUPING_ID function-PolarDB(PolarDB)-阿里云帮助中心

GROUPING_ID encodes the subtotal level of each row in a ROLLUP, CUBE, or GROUPING SETS result set as a single integer. This eliminates the need to call GROUPING separately on each column and then interpret the individual flags.

How it works

GROUPING accepts one column expression and returns 0 (grouped) or 1 (aggregated). To identify the subtotal level across multiple grouping columns, you would need one GROUPING call per column and then read the flags together.

GROUPING_ID combines that process into one step. It takes the same column expressions as parameters, arranges their GROUPING results into a bit vector (leftmost parameter = most significant bit), and returns the base-10 integer equivalent of that bit vector.

Parameter order matters. GROUPING_ID(a, b) and GROUPING_ID(b, a) produce different integers for the same row because the bit positions are swapped. Always match the parameter order to the order in which you want to interpret the grouping levels.

Syntax

SELECT [ expr ...,]
  GROUPING_ID( col_expr_1 [, col_expr_2 ] ... )
  [, expr ] ...
FROM ...
GROUP BY [...,]
  { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr_1
  [, col_expr_2 ] [, ...] ) [, ...]

Each parameter must be a dimension column expression that appears in the ROLLUP, CUBE, or GROUPING SETS list of the GROUP BY clause.

Bit vector to integer mapping

For two grouping columns, the possible GROUPING_ID values are:

Columns aggregated	Bit vector (gf_col1 gf_col2)	GROUPING_ID value
col1, col2 (no subtotal)	0 0	0
col1 only	0 1	1
col2 only	1 0	2
Grand total	1 1	3

Examples

Identify grouping levels in a CUBE query

The following query uses GROUPING_ID(loc, dname) alongside individual GROUPING calls to show how the integer value relates to each flag:

SELECT loc, dname, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc", GROUPING(dname) AS "gf_dname",
  GROUPING_ID(loc, dname) AS "gid"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname)
ORDER BY 6, 1, 2;

Output:

   loc    |   dname    | employees | gf_loc | gf_dname | gid
----------+------------+-----------+--------+----------+-----
 BOSTON   | OPERATIONS |         3 |      0 |        0 |   0
 BOSTON   | RESEARCH   |         5 |      0 |        0 |   0
 CHICAGO  | SALES      |         6 |      0 |        0 |   0
 NEW YORK | ACCOUNTING |         3 |      0 |        0 |   0
 BOSTON   |            |         8 |      0 |        1 |   1
 CHICAGO  |            |         6 |      0 |        1 |   1
 NEW YORK |            |         3 |      0 |        1 |   1
          | ACCOUNTING |         3 |      1 |        0 |   2
          | OPERATIONS |         3 |      1 |        0 |   2
          | RESEARCH   |         5 |      1 |        0 |   2
          | SALES      |         6 |      1 |        0 |   2
          |            |        17 |      1 |        1 |   3
(12 rows)

The gid column is the base-10 value of the bit vector formed by gf_loc and gf_dname:

loc	dname	Bit vector (gf_loc gf_dname)	Calculation	gid
BOSTON	OPERATIONS	0 0	0 x 2^1 + 0 x 2^0	0
BOSTON	null	0 1	0 x 2^1 + 1 x 2^0	1
null	ACCOUNTING	1 0	1 x 2^1 + 0 x 2^0	2
null	null	1 1	1 x 2^1 + 1 x 2^0	3

Filter results by subtotal level using HAVING

To return only the subtotals aggregated by dname (where gid = 2), add a HAVING clause:

SELECT loc, dname, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc", GROUPING(dname) AS "gf_dname",
  GROUPING_ID(loc, dname) AS "gid"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname)
HAVING GROUPING_ID(loc, dname) = 2
ORDER BY 6, 1, 2;

Output:

 loc |   dname    | employees | gf_loc | gf_dname | gid
-----+------------+-----------+--------+----------+-----
     | ACCOUNTING |         3 |      1 |        0 |   2
     | OPERATIONS |         3 |      1 |        0 |   2
     | RESEARCH   |         5 |      1 |        0 |   2
     | SALES      |         6 |      1 |        0 |   2
(4 rows)

Usage notes

The NULL values in loc and dname in the output above are produced by the CUBE aggregation, not from NULL data in the source tables. Use GROUPING_ID to distinguish aggregation-produced NULLs from actual NULL values in the data.
GROUPING_ID parameters must exactly match the dimension column expressions used in the ROLLUP, CUBE, or GROUPING SETS clause.
Parameter order directly affects the returned integer. GROUPING_ID(loc, dname) and GROUPING_ID(dname, loc) return different values for the same row.