Purpose
The GROUPING_ID function calculates the combination value of a set of expressions and returns a number that represents the GROUP BY level of the row.
The GROUPING_ID function creates a bit vector by concatenating the 1 and 0 sequences generated when the GROUPING function is applied to each attribute. This bit vector is treated as a binary number, and the GROUPING_ID function then returns its corresponding decimal value.
For example, when using CUBE(a, b) as the grouping expression, the possible GROUPING_ID values are as follows:
| Aggregation Level | Bit Vector | GROUPING_ID |
|---|---|---|
| a, b | 00 | 0 |
| a | 01 | 1 |
| b | 10 | 2 |
| Total | 11 | 3 |
Limitations and considerations
- If an expression is included in the grouping set, the value returned by
GROUPING_IDincreases; if not, it remains unchanged.
Syntax
GROUPING_ID(attributeList)
Parameters
| Parameter | Description |
|---|---|
attributeList |
The list of expressions for which the combination value is to be calculated. |
Examples
Create an orders table to store order data, including order ID (order_id), year (year), quarter (quarter), and order amount (amount).
obclient [SYS]> CREATE TABLE orders (
-> order_id INT,
-> year INT,
-> quarter INT,
-> amount DECIMAL(10, 2)
-> );
Next, insert some sample data and use GROUPING_ID to analyze this data.
obclient [SYS]> INSERT INTO orders (order_id, year, quarter, amount) VALUES
-> (1, 2021, 1, 1000.00),
-> (2, 2021, 1, 1500.00),
-> (3, 2021, 2, 2000.00),
-> (4, 2021, 2, 2500.00),
-> (5, 2021, 3, 3000.00),
-> (6, 2021, 4, 3500.00),
-> (7, 2021, 4, 4000.00),
-> (8, 2022, 1, 4500.00),
-> (9, 2022, 2, 5000.00),
-> (10, 2022, 3, 5500.00),
-> (11, 2022, 4, 6000.00);
This query generates a result set that shows all possible aggregation combinations for different years and quarters, along with the total order amount for each combination. GROUPING_ID provides a unique number for each combination to indicate the aggregation level.
In this example, GROUPING_ID(year, quarter) will produce the following values:
- When both
yearandquarterare notNULL(most specific grouping), the value ofGROUPING_IDis 0. - When only
yearis notNULL(total by year), the value ofGROUPING_IDis 1. - When both
yearandquarterareNULL(total for all orders), the value ofGROUPING_IDis 2.
obclient [SYS]> SELECT
-> year,
-> quarter,
-> GROUPING_ID(year, quarter) AS grouping_level,
-> SUM(amount) AS total_amount
-> FROM orders
-> GROUP BY ROLLUP(year, quarter)
-> ORDER BY year, quarter;
The result is as follows:
+------+---------+----------------+--------------+
| YEAR | QUARTER | GROUPING_LEVEL | TOTAL_AMOUNT |
+------+---------+----------------+--------------+
| 2021 | 1 | 0 | 2500 |
| 2021 | 2 | 0 | 4500 |
| 2021 | 3 | 0 | 3000 |
| 2021 | 4 | 0 | 7500 |
| 2021 | NULL | 1 | 17500 |
| 2022 | 1 | 0 | 4500 |
| 2022 | 2 | 0 | 5000 |
| 2022 | 3 | 0 | 5500 |
| 2022 | 4 | 0 | 6000 |
| 2022 | NULL | 1 | 21000 |
| NULL | NULL | 3 | 38500 |
+------+---------+----------------+--------------+
11 rows in set