GROUPING & GROUPING_ID & GROUP_ID & GROUPING SETS

1. Grouping ()

The function must accept one column and can only accept one column as its argument. The parameter column value returns 1 if the parameter column value is empty, and 0 if the parameter column value is not empty.

Second, grouping_id () function

GROUPING_ID can receive multiple columns, return values in parameter order, use the grouping () function for each parameter in turn, and then string the resulting values into a string of binary numbers and then convert them into decimal values.

For example: grouping (A) = 0; grouping (B) = 1

Then: grouping_id (A _ maeb) = (01) = 1

Grouping_id (BBME A) = (10) = 2

Example:

SELECT DIVISION_ID

JOB_ID

GROUPING (DIVISION_ID) AS DIV_GRP

GROUPING (JOB_ID) AS JOB_GRP

GROUPING_ID (DIVISION_ID, JOB_ID) AS GRP_ID

SUM (SALARY) FROM EMPLOYEES2

GROUP BY CUBE (DIVISION_ID, JOB_ID)

ORDER BY DIVISION_ID, JOB_ID; III, GROUP_ID () function

The GROUP_ID function can be used to eliminate duplicate records returned by the GROUP BY clause. GROUP_ID () does not accept any parameters. If a particular packet is repeated n times, GROUP_ID () returns an integer from 0 to NMMI 1.

Example:

SELECT DIVISION_ID, JOB_ID, GROUP_ID (), SUM (SALARY)

FROM EMPLOYEES

GROUP BY DIVISION_ID, ROLLUP (DIVISION_ID, JOB_ID); IV. GROUPING SETS

Grouping sets is equivalent to combining the grouping conditions of multiple group by, and the empty grouping condition is the sum of all the data.

Example:

SELECT GROUPING_ID (S.PRODUCT, S.COUNTRY)

GROUPING (PRODUCT)

GROUPING (COUNTRY)

PRODUCT

COUNTRY

SUM (SALES)

FROM PLCH_SALES S

GROUP BY GROUPING SETS ((S.PRODUCT), (S.COUNTRY), ())