基本group by用法
create table test_table
(
a varchar(20),
b varchar(20),
c varchar(20)
)
insert into test_tablevalues(1,'a','甲')
insert into test_tablevalues(1,'a','甲')
insert into test_tablevalues(1,'a','甲')
insert into test_tablevalues(1,'a','甲')
insert into test_tablevalues(1,'a','乙')
insert into test_tablevalues(1,'b','乙')
insert into test_tablevalues(1,'b','乙')
insert into test_tablevalues(1,'b','乙')
select b,count(c) sumc from test_table group by b
其中select后面跟的列必须是 group by跟的条件或者聚合函数 count sum avg 等
select c,sum(a) couna from test_table where a > 6 group by c having sum(a) >1
其中 where是在分组前，对数据行的条件过滤，having sum，avg，count实在分组后，对组的过滤。
其中可以加入join ，使用join的目的是实现搜素想要数据，实现数据扩充。
理解group by 语句的扩展使用
在SQL的开发中我们会经常使用group by语句对数据进行分组统计，然而在一些复杂的BI报表开发中会常遇到更
复杂的分组需求，单单使用group by 就不能解决我们的问题了，这时我们就需要学习了解一下在group by 语句上的一些扩展使用，下面我们就来学习一下rollup、cube、grouping sets语句的使用。
1.group by
SQL> select a.dname,b.job,sum(b.sal) sum_sal 2 from dept a,emp b 3 where a.deptno=b.deptno 4 group by a.dname,b.job; DNAME JOB SUM_SAL -------------- --------- ---------- SALES MANAGER 2850 SALES CLERK 950 ACCOUNTING MANAGER 2450 ACCOUNTING PRESIDENT 5000 ACCOUNTING CLERK 1300 RESEARCH MANAGER 2975 SALES SALESMAN 5600 RESEARCH ANALYST 6000 RESEARCH CLERK 1900 9 rows selected
可以看到以上数据中我们按照部门名和职位名进行了分组，然后求出每组内的工资数。
假如现在我们有一个需求，需要在这个查询中增加一个不同部门间的工资总和，那么这个SQL该怎么写：
我们是不是会这样写：
SQL> select * from (
2 SELECT a.dname,b.job,SUM(b.sal) sum_sal
3 FROM dept a,emp b
4 WHERE a.deptno = b.deptno
5 GROUP BY a.dname,b.job
6 UNION ALL
7 --部门的小计
8 SELECT a.dname,NULL, SUM(b.sal) sum_sal
9 FROM dept a,emp b
10 WHERE a.deptno = b.deptno
11 GROUP BY a.dname
12 UNION ALL
13 --所有部门总的合计
14 SELECT NULL,NULL, SUM(b.sal) sum_sal
15 FROM dept a,emp b
16 WHERE a.deptno = b.deptno)
17 order by dname;
DNAME JOB SUM_SAL
-------------- --------- ----------
ACCOUNTING 8750
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK 1300
RESEARCH CLERK 1900
RESEARCH MANAGER 2975
RESEARCH 10875
RESEARCH ANALYST 6000
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
29025
13 rows selected
可以看到上面的SQL写法实现了之前的需求，但是执行效率将非常低，dept表和emp表将会被多次扫描，
能否一次扫描就能搞定这个事哪，答案的肯定的，使用rollup函数。
2.group by rollup
SQL> select a.dname,b.job,sum(b.sal) sum_sal
2 from dept a,emp b
3 where a.deptno=b.deptno
4 group by rollup(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
RESEARCH 10875
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
29025
13 rows selected
通过上面的查询可以看到使用rollup函数我们一次就实现了上面的需求，而且dept表和emp表将只会被扫描一次，可以通过查询执行计划要来验证(略)。
group by rollup(a.dname,b.job)
分组是这样的：
1.首先对a.dname,b.job进行联合分组，求出sum
2.其次对a.dname进行分组，求出sum
3.最后按全表进行分组求出sum
可能有些人对于上面union all的写法还能接受，决定实现起来不难而且很好理解，但是我要说的是如果是需求再改变，在增加求出对雇佣年份的统计，是不是又要union all了，这就显得sql很冗肿了，效率下降，你还能接受吗？如果现在我们的需求又改变了，我们(a.dname,b.job)在前面的基础上增加对 b.job进行分组求和，这时该怎么做那，。。。。这时就需要我们使用cube函数。
3.group by cube
SQL> select a.dname,b.job,sum(b.sal) sum_sal
2 from dept a,emp b
3 where a.deptno=b.deptno
4 group by cube(a.dname,b.job);
DNAME JOB SUM_SAL
-------------- --------- ----------
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
SALES 9400
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 5600
RESEARCH 10875
RESEARCH CLERK 1900
RESEARCH ANALYST 6000
RESEARCH MANAGER 2975
ACCOUNTING 8750
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
18 rows selected
通过上面的sql查询我们发现cube函数是rollup函数基础上更细化的分组，在rollup的基础上又增加了对job的分组，是不是这样的那??? 通过上面的查询发现使用rollup函数有13条数据，相同数据的情况下使用cube函数有18条数据，那么多出的5条数据就是对job的分组，查询一下job有种：
SQL> select distinct job from emp; JOB --------- CLERK SALESMAN PRESIDENT MANAGER ANALYST
可以看到正好有5种job，验证了上面的问题。
group by cube(a.dname,b.job)
分组是这样的：
1.首先按照a.dname,b.job进行分组，求聚合函数的值
2.其次按照a.dname进行分组,求聚合函数的值
3.再次按照b.job进行分组，求聚合函数的值
4.最后对全表进行分组，求聚合函数的值
如果是三列数据那：
group by cube(a.dname,b.job,b.hiredate)
分组是这样的：
1.首先按照a.dname,b.job,b.hiredate进行分组，求聚合函数的值
2.然后按照a.dname,b.job进行分组，求聚合函数的值
3.然后按照a.dname,b.hiredate进行分组，求聚合函数的值
4.然后按照b.job,b.hiredate进行分组，求聚合函数的值
5.然后按照a.dname进行分组，求聚合函数的值
6.然后按照b.job进行分组，求聚合函数的值
7.然后按照b.hiredate进行分组，求聚合函数的值
8.最后按照全表进行分组，求聚合函数的值
例如：
SQL> select a.dname,b.job,b.hiredate,sum(b.sal) sum_sal
2 from dept a,emp b
3 where a.deptno=b.deptno
4 group by cube(a.dname,b.job,b.hiredate);
DNAME JOB HIREDATE SUM_SAL
-------------- --------- ----------- ----------
29025
1980/12/17 800
1981/2/20 1600
1981/2/22 1250
1981/4/2 2975
1981/5/1 2850
1981/6/9 2450
1981/9/8 1500
1981/9/28 1250
1981/11/17 5000
1981/12/3 3950
1982/1/23 1300
1987/4/19 3000
1987/5/23 1100
CLERK 4150
CLERK 1980/12/17 800
CLERK 1981/12/3 950
CLERK 1982/1/23 1300
CLERK 1987/5/23 1100
ANALYST 6000
ANALYST 1981/12/3 3000
ANALYST 1987/4/19 3000
MANAGER 8275
MANAGER 1981/4/2 2975
MANAGER 1981/5/1 2850
MANAGER 1981/6/9 2450
SALESMAN 5600
SALESMAN 1981/2/20 1600
SALESMAN 1981/2/22 1250
SALESMAN 1981/9/8 1500
SALESMAN 1981/9/28 1250
PRESIDENT 5000
PRESIDENT 1981/11/17 5000
SALES 9400
SALES 1981/2/20 1600
SALES 1981/2/22 1250
SALES 1981/5/1 2850
SALES 1981/9/8 1500
SALES 1981/9/28 1250
SALES 1981/12/3 950
SALES CLERK 950
SALES CLERK 1981/12/3 950
SALES MANAGER 2850
SALES MANAGER 1981/5/1 2850
SALES SALESMAN 5600
SALES SALESMAN 1981/2/20 1600
SALES SALESMAN 1981/2/22 1250
SALES SALESMAN 1981/9/8 1500
SALES SALESMAN 1981/9/28 1250
RESEARCH 10875
RESEARCH 1980/12/17 800
RESEARCH 1981/4/2 2975
RESEARCH 1981/12/3 3000
RESEARCH 1987/4/19 3000
RESEARCH 1987/5/23 1100
RESEARCH CLERK 1900
RESEARCH CLERK 1980/12/17 800
RESEARCH CLERK 1987/5/23 1100
RESEARCH ANALYST 6000
RESEARCH ANALYST 1981/12/3 3000
RESEARCH ANALYST 1987/4/19 3000
RESEARCH MANAGER 2975
RESEARCH MANAGER 1981/4/2 2975
ACCOUNTING 8750
ACCOUNTING 1981/6/9 2450
ACCOUNTING 1981/11/17 5000
ACCOUNTING 1982/1/23 1300
ACCOUNTING CLERK 1300
ACCOUNTING CLERK 1982/1/23 1300
ACCOUNTING MANAGER 2450
ACCOUNTING MANAGER 1981/6/9 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING PRESIDENT 1981/11/17 5000
73 rows selected
如果分别按照a.dname,b.job,b.hiredate进行分组求和，我们是不是可以放在一个SQL中实现，
答案的可以的，我们可以利用grouping sets函数
4.group by grouping sets
SQL> select a.dname,b.job,b.hiredate,sum(b.sal) sum_sal
2 from dept a,emp b
3 where a.deptno=b.deptno
4 group by grouping sets (a.dname,b.job,b.hiredate);
DNAME JOB HIREDATE SUM_SAL
-------------- --------- ----------- ----------
1987/5/23 1100
1981/11/17 5000
1982/1/23 1300
1981/12/3 3950
1981/2/22 1250
1981/6/9 2450
1980/12/17 800
1981/4/2 2975
1987/4/19 3000
1981/2/20 1600
1981/9/8 1500
1981/5/1 2850
1981/9/28 1250
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
ACCOUNTING 8750
RESEARCH 10875
SALES 9400
21 rows selected
通过上面的查询我们就很一目了然看到他们的分组规律。
group by grouping sets (a.dname,b.job,b.hiredate)
分组是这样的：
1.首先按照a.dname进行分组，求聚合函数的值
2.其次按照b.job进行分组，求聚合函数的值
1.最后按照b.hiredate进行分组，求聚合函数的值
5.总结归类下
Rollup是在group by的基础上再进行分级的汇总，例如：Rollup(A,B,C)的分组顺序是：
(A,B,C)
(A,B)
(A)
最后对全表进行group by 分组。
Cube是在Rollup的基础上再进行更加细粒度的汇总，例如：cube(A,B,C)它的分组顺序是：
(A,B,C)
(A,B)
(A,C)
(A)
(B,C)
(B)
(C)
最后对全表进行group by 分组。
Grouping sets与rollup和cube不同，它只是对单列进行分组，例如grouping sets(A,B,C)的分组顺序是:
(A)
(B)
(C)
ORACLE grouping
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
1、在ROLLUP中对单列使用GROUPING()
SQL> select division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
DIV SUM(SALARY)
--- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
8881000
加上GROUPING来看看
SQL> select grouping(division_id),division_id,sum(salary)
2 from employees2
3 group by rollup(division_id)
4 order by division_id;
GROUPING(DIVISION_ID) DIV SUM(SALARY)
--------------------- --- -----------
0 BUS 1610000
0 OPE 1320000
0 SAL 4936000
0 SUP 1015000
1 8881000
可以看到，为空的地方返回1，非空的地方返回0。
2、使用CASE转换GROUPING()的返回值
可能你会觉得前面的0和1太枯燥了，代表不了任何意义，说白了就是不够人性化，呵呵。这个时候我们可以使用CASE来转换为一些有意义的值。
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 sum(salary)
7 from employees2
8 group by rollup(division_id)
9 order by division_id;
DIV SUM(SALARY)
------------- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
all divisions 8881000
3、使用CASE和GROUPING()转换多个列的值
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum(salary)
11 from employees2
12 group by rollup(division_id,job_id)
13 order by division_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
DIV JOB SUM(SALARY)
------------- -------- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions all jobs 8881000
16 rows selected.
4、CUBE与GROUPING()结合使用
SQL> select
2 case grouping(division_id)
3 when 1 then 'all divisions'
4 else division_id
5 end as div,
6 case grouping(job_id)
7 when 1 then 'all jobs'
8 else job_id
9 end as job,
10 sum(salary)
11 from employees2
12 group by cube(division_id,job_id)
13 order by division_id,job_id;
DIV JOB SUM(SALARY)
------------- -------- -----------
BUS MGR 530000
BUS PRE 800000
BUS WOR 280000
BUS all jobs 1610000
OPE ENG 245000
OPE MGR 805000
OPE WOR 270000
OPE all jobs 1320000
SAL MGR 4446000
SAL WOR 490000
SAL all jobs 4936000
DIV JOB SUM(SALARY)
------------- -------- -----------
SUP MGR 465000
SUP TEC 115000
SUP WOR 435000
SUP all jobs 1015000
all divisions ENG 245000
all divisions MGR 6246000
all divisions PRE 800000
all divisions TEC 115000
all divisions WOR 1475000
all divisions all jobs 8881000
21 rows selected.
5、使用GROUPING SETS子句
使用GROUPING SETS子句可以只返回小计记录。
SQL> select division_id,job_id,sum(salary)
2 from employees2
3 group by grouping sets(division_id,job_id)
4 order by division_id,job_id;
DIV JOB SUM(SALARY)
--- --- -----------
BUS 1610000
OPE 1320000
SAL 4936000
SUP 1015000
ENG 245000
MGR 6246000
PRE 800000
TEC 115000
WOR 1475000
---------------------
个人实践grouping
select case grouping(b) when 0 then b else 'all' end as b,count(c) countc from test_table group by rollup (b)
扫一扫
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
