grouping_id
function
Applies to: Databricks SQL Databricks Runtime
Returns the level of grouping for a set of columns.
Returns
A BIGINT.
The function combines the grouping function for several columns into one by assigning each column a bit in a bit vector.
The col1
is represented by the highest order bit.
A bit is set to 1 if the row computes a subtotal for the corresponding column.
Specifying no argument is equivalent to specifying all columns listed in the GROUPING SET
, CUBE
, or ROLLUP
.
Examples
> SELECT name, age, grouping_id(name, age),
conv(cast(grouping_id(name, age) AS STRING), 10, 2),
avg(height)
FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height)
GROUP BY cube(name, age)
Alice 2 0 0 165.0
Alice NULL 1 1 165.0
NULL 2 2 10 165.0
NULL NULL 3 11 172.5
Bob NULL 1 1 180.0
Bob 5 0 0 180.0
NULL 5 2 10 180.0