Oracle PL/SQL/Analytical Functions/GROUPING ID
Содержание
- 1 An Example Query that Illustrates the Use of GROUPING_ID()
- 2 Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
- 3 One useful application of GROUPING_ID() is to filter rows using a HAVING clause
- 4 You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause
An Example Query that Illustrates the Use of GROUPING_ID()
<source lang="sql">
SQL> CREATE TABLE employee(
2 employee_id INTEGER, 3 division_id CHAR(3), 4 job_id CHAR(3), 5 first_name VARCHAR2(10) NOT NULL, 6 last_name VARCHAR2(10) NOT NULL, 7 salary NUMBER(6, 0) 8 );
Table created. SQL> SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(1, "BUS","PRE","James","Smith","800000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(5, "SAL","WOR","Rob","Green","350000");
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY
--- --- ---------- ---------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
SQL> SQL> SQL> SQL> SQL> --An Example Query that Illustrates the Use of GROUPING_ID() SQL> SQL> --The following example passes division_id and job_id to GROUPING_ID(). Notice the output from the GROUPING_ID() function agrees with the expected returned values documented in the previous section: SQL> SQL> SELECT
2 division_id, job_id, 3 GROUPING(division_id) AS DIV_GRP, 4 GROUPING(job_id) AS JOB_GRP, 5 GROUPING_ID(division_id, job_id) AS grp_id, 6 SUM(salary) 7 FROM employee 8 GROUP BY CUBE(division_id, job_id);
DIV JOB DIV_GRP JOB_GRP GRP_ID SUM(SALARY) --- --- ---------- ---------- ---------- -----------
1 1 3 1840000 MGR 1 0 2 550000 PRE 1 0 2 800000 WOR 1 0 2 490000
BUS 0 1 1 800000 BUS PRE 0 0 0 800000 SAL 0 1 1 840000 SAL MGR 0 0 0 350000 SAL WOR 0 0 0 490000 SUP 0 1 1 200000 SUP MGR 0 0 0 200000 11 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL>
</source>
Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0
<source lang="sql">
SQL> CREATE TABLE employee(
2 employee_id INTEGER, 3 division_id CHAR(3), 4 job_id CHAR(3), 5 first_name VARCHAR2(10) NOT NULL, 6 last_name VARCHAR2(10) NOT NULL, 7 salary NUMBER(6, 0) 8 );
Table created. SQL> SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(1, "BUS","PRE","James","Smith","800000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(5, "SAL","WOR","Rob","Green","350000");
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY
--- --- ---------- ---------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
SQL> SQL> SQL> --Eliminate duplicate rows using a HAVING clause that only allows rows whose GROUP_ID() is 0 SQL> SQL> SELECT division_id, job_id, GROUP_ID(), SUM(salary)
2 FROM employee 3 GROUP BY division_id, ROLLUP(division_id, job_id) 4 HAVING GROUP_ID() = 0;
DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS PRE 0 800000 SAL MGR 0 350000 SAL WOR 0 490000 SUP MGR 0 200000 BUS 0 800000 SAL 0 840000 SUP 0 200000 7 rows selected. SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL>
</source>
One useful application of GROUPING_ID() is to filter rows using a HAVING clause
<source lang="sql">
SQL> CREATE TABLE employee(
2 employee_id INTEGER, 3 division_id CHAR(3), 4 job_id CHAR(3), 5 first_name VARCHAR2(10) NOT NULL, 6 last_name VARCHAR2(10) NOT NULL, 7 salary NUMBER(6, 0) 8 );
Table created. SQL> SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(1, "BUS","PRE","James","Smith","800000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(5, "SAL","WOR","Rob","Green","350000");
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY
--- --- ---------- ---------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
SQL> SQL> SQL> SQL> SQL> --A Useful Application of GROUPING_ID() SQL> SQL> --One useful application of GROUPING_ID() is to filter rows using a HAVING clause. Your HAVING clause can exclude rows that don�t contain a subtotal or total by simply checking if GROUPING_ID() returns a value greater than 0. For example: SQL> SQL> SELECT
2 division_id, job_id, 3 GROUPING_ID(division_id, job_id) AS grp_id, 4 SUM(salary) 5 FROM employee 6 GROUP BY CUBE(division_id, job_id) 7 HAVING GROUPING_ID(division_id, job_id) > 0;
DIV JOB GRP_ID SUM(SALARY) --- --- ---------- -----------
3 1840000 MGR 2 550000 PRE 2 800000 WOR 2 490000
BUS 1 800000 SAL 1 840000 SUP 1 200000 7 rows selected. SQL> SQL> SQL> SQL> SQL> drop table employee;
</source>
You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause
<source lang="sql">
SQL> CREATE TABLE employee(
2 employee_id INTEGER, 3 division_id CHAR(3), 4 job_id CHAR(3), 5 first_name VARCHAR2(10) NOT NULL, 6 last_name VARCHAR2(10) NOT NULL, 7 salary NUMBER(6, 0) 8 );
Table created. SQL> SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(1, "BUS","PRE","James","Smith","800000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(2, "SAL","MGR","Ron","Johnson","350000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(3, "SAL","WOR","Fred","Hobbs","140000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(4, "SUP","MGR","Susan","Jones","200000");
1 row created. SQL> insert into employee (EMPLOYEE_ID,division_id,JOB_ID,FIRST_NAME,LAST_NAME,SALARY)
2 values(5, "SAL","WOR","Rob","Green","350000");
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID DIV JOB FIRST_NAME LAST_NAME SALARY
--- --- ---------- ---------- ----------
1 BUS PRE James Smith 800000
2 SAL MGR Ron Johnson 350000
3 SAL WOR Fred Hobbs 140000
4 SUP MGR Susan Jones 200000
5 SAL WOR Rob Green 350000
SQL> SQL> --Using the GROUP_ID() Function SQL> SQL> --You can use the GROUP_ID() function to remove duplicate rows returned by a GROUP BY clause. GROUP_ID() doesn�t accept any parameters. If n duplicates exist for a particular grouping, GROUP_ID returns numbers in the range 0 to n-1. SQL> SQL> SQL> SELECT division_id, job_id, GROUP_ID(), SUM(salary)
2 FROM employee 3 GROUP BY division_id, ROLLUP(division_id, job_id);
DIV JOB GROUP_ID() SUM(SALARY) --- --- ---------- ----------- BUS PRE 0 800000 SAL MGR 0 350000 SAL WOR 0 490000 SUP MGR 0 200000 BUS 0 800000 SAL 0 840000 SUP 0 200000 BUS 1 800000 SAL 1 840000 SUP 1 200000 10 rows selected. SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL>
</source>
