GROUPING

功能描述

GROUPING函数用于区分分组后的普通行和聚合行。如果是聚合行，则返回1，反之，则是0。

注意事项

该函数仅适用于select语句并包含GROUP BY的情况。

语法格式

GROUPING( col_expr )

参数说明

col_expr

列名。

示例

1、创建测试表并插入数据。

CREATE TABLE group_test (group_id int, job varchar2(10), name varchar2(10), salary int);
INSERT INTO group_test VALUES (10,'Coding', 'Bruce',1000);
INSERT INTO group_test VALUES (10,'Programmer','Clair',1000);
INSERT INTO group_test VALUES (10,'Architect', 'Gideon',1000);
INSERT INTO group_test VALUES (10,'Director', 'Hill',1000);
INSERT INTO group_test VALUES (20,'Coding', 'Jason',2000);
INSERT INTO group_test VALUES (20,'Programmer','Joey',2000);
INSERT INTO group_test VALUES (20,'Architect', 'Martin',2000);
INSERT INTO group_test VALUES (20,'Director', 'Michael',2000);
INSERT INTO group_test VALUES (30,'Coding', 'Rebecca',3000);
INSERT INTO group_test VALUES (30,'Programmer','Rex',3000);
INSERT INTO group_test VALUES (30,'Architect', 'Richard',3000);
INSERT INTO group_test VALUES (30,'Director', 'Sabrina',3000);
INSERT INTO group_test VALUES (40,'Coding', 'Samuel',4000);
INSERT INTO group_test VALUES (40,'Programmer','Susy',4000);
INSERT INTO group_test VALUES (40,'Architect', 'Tina',4000);
INSERT INTO group_test VALUES (40,'Director', 'Wendy',4000);

2、使用grouping函数：传入两个expr参数，使用GROUP BY ROLLUP生成分组集。

SELECT group_id,job,grouping(group_id),grouping(job),grouping(group_id,job),sum(salary) FROM group_test GROUP BY rollup(group_id, job);

返回结果为：

 group_id |    job     | grouping | grouping | grouping |  sum
----------+------------+----------+----------+----------+-------
       10 | Architect  |        0 |        0 |        0 |  1000
       10 | Coding     |        0 |        0 |        0 |  1000
       10 | Director   |        0 |        0 |        0 |  1000
       10 | Programmer |        0 |        0 |        0 |  1000
       10 |            |        0 |        1 |        1 |  4000
       20 | Architect  |        0 |        0 |        0 |  2000
       20 | Coding     |        0 |        0 |        0 |  2000
       20 | Director   |        0 |        0 |        0 |  2000
       20 | Programmer |        0 |        0 |        0 |  2000
       20 |            |        0 |        1 |        1 |  8000
       30 | Architect  |        0 |        0 |        0 |  3000
       30 | Coding     |        0 |        0 |        0 |  3000
       30 | Director   |        0 |        0 |        0 |  3000
       30 | Programmer |        0 |        0 |        0 |  3000
       30 |            |        0 |        1 |        1 | 12000
       40 | Architect  |        0 |        0 |        0 |  4000
       40 | Coding     |        0 |        0 |        0 |  4000
       40 | Director   |        0 |        0 |        0 |  4000
       40 | Programmer |        0 |        0 |        0 |  4000
       40 |            |        0 |        1 |        1 | 16000
          |            |        1 |        1 |        3 | 40000
(21 rows)

3、清理测试表。

DROP TABLE group_test;