27.报表高级分组函数
--group函数复习
hr@ORCLPDB01 2023-02-26 19:55:19> select avg(salary),stddev(salary),count(commission_pct),max(hire_date)
2 from employees
3 where job_id like 'SA%';
AVG(SALARY) STDDEV(SALARY) COUNT(COMMISSION_PCT) MAX(HIRE_DATE)
----------- -------------- --------------------- ---------------------------------------------------------------------------
8900 2030.64754 35 21-APR-08 12.00.00.000000 AM
Elapsed: 00:00:00.01
--group by复习
hr@ORCLPDB01 2023-02-26 20:09:20> select department_id,job_id,sum(salary),count(employee_id)
2 from employees
3 group by department_id,job_id;
DEPARTMENT_ID JOB_ID SUM(SALARY) COUNT(EMPLOYEE_ID)
------------- ---------- ----------- ------------------
90 AD_VP 34000 2
100 FI_MGR 12008 1
80 SA_REP 243500 29
SA_REP 7000 1
90 AD_PRES 24000 1
20 MK_REP 6000 1
110 AC_MGR 12008 1
60 IT_PROG 28800 5
30 PU_CLERK 13900 5
80 SA_MAN 61000 5
50 SH_CLERK 64300 20
20 MK_MAN 13000 1
30 PU_MAN 11000 1
50 ST_CLERK 55700 20
70 PR_REP 10000 1
110 AC_ACCOUNT 8300 1
50 ST_MAN 36400 5
100 FI_ACCOUNT 39600 5
10 AD_ASST 4400 1
40 HR_REP 6500 1
20 rows selected.
Elapsed: 00:00:00.00
--having复习1.使用rollup操作符产生汇总结果
--使用rollup或cube通过交叉列可产生高级汇总结果集
--rollup分组产生得结果集包含常规分组和分类汇总
--cube分组结果包括rollup结果和交叉汇总
hr@ORCLPDB01 2023-02-26 20:10:43> select department_id,job_id,sum(salary)
2 from employees
3 where department_id < 60
4 group by rollup(department_id, job_id);
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
10 4400
20 MK_MAN 13000
20 MK_REP 6000
20 19000
30 PU_MAN 11000
30 PU_CLERK 13900
30 24900
40 HR_REP 6500
40 6500
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
50 156400
2112002.使用cube产生交叉汇总结果
hr@ORCLPDB01 2023-02-26 20:15:45> r
1 select department_id,job_id,sum(salary)
2 from employees
3 where department_id < 60
4* group by cube ( department_id,job_id)
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
211200
HR_REP 6500
MK_MAN 13000
MK_REP 6000
PU_MAN 11000
ST_MAN 36400
AD_ASST 4400
PU_CLERK 13900
SH_CLERK 64300
ST_CLERK 55700
10 4400
10 AD_ASST 4400
20 19000
20 MK_MAN 13000
20 MK_REP 6000
30 24900
30 PU_MAN 11000
30 PU_CLERK 13900
40 6500
40 HR_REP 6500
50 156400
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
24 rows selected.
Elapsed: 00:00:00.013.使用grouping函数标识rollup或cube产生结果
hr@ORCLPDB01 2023-02-26 20:15:47> select department_id deptid,job_id job,
2 sum(salary),
3 grouping(department_id) grp_dept,
4 grouping(job_id) grp_job
5 from employees
6 where department_id < 50
7 group by rollup(department_id,job_id);
DEPTID JOB SUM(SALARY) GRP_DEPT GRP_JOB
---------- ---------- ----------- ---------- ----------
10 AD_ASST 4400 0 0
10 4400 0 1
20 MK_MAN 13000 0 0
20 MK_REP 6000 0 0
20 19000 0 1
30 PU_MAN 11000 0 0
30 PU_CLERK 13900 0 0
30 24900 0 1
40 HR_REP 6500 0 0
40 6500 0 1
54800 1 1
11 rows selected.
Elapsed: 00:00:00.004.使用grouping sets产生简单结果集
hr@ORCLPDB01 2023-02-26 20:20:48> r
1 select department_id,job_id,manager_id,avg(salary)
2 from employees
3* group by grouping sets ((department_id,job_id),(job_id,manager_id))
DEPARTMENT_ID JOB_ID MANAGER_ID AVG(SALARY)
------------- ---------- ---------- -----------
90 AD_VP 17000
100 FI_MGR 12008
80 SA_REP 8396.55172
SA_REP 7000
90 AD_PRES 24000
20 MK_REP 6000
110 AC_MGR 12008
60 IT_PROG 5760
30 PU_CLERK 2780
80 SA_MAN 12200
50 SH_CLERK 3215
20 MK_MAN 13000
30 PU_MAN 11000
50 ST_CLERK 2785
70 PR_REP 10000
110 AC_ACCOUNT 8300
50 ST_MAN 7280
100 FI_ACCOUNT 7920
10 AD_ASST 4400
40 HR_REP 6500
ST_CLERK 124 2925
SA_MAN 100 12200
AC_MGR 101 12008
FI_ACCOUNT 108 7920
MK_REP 201 6000
PR_REP 101 10000
AD_VP 100 17000
PU_MAN 100 11000
AD_ASST 101 4400
HR_REP 101 6500
PU_CLERK 114 2780
SH_CLERK 120 2900
ST_CLERK 123 3000
SH_CLERK 122 3200
AC_ACCOUNT 205 8300
ST_CLERK 120 2625
ST_CLERK 121 2675
SA_REP 145 8500
SA_REP 147 7766.66667
SA_REP 148 8650
SH_CLERK 121 3675
SH_CLERK 124 2825
IT_PROG 103 4950
FI_MGR 101 12008
ST_MAN 100 7280
SH_CLERK 123 3475
AD_PRES 24000
IT_PROG 102 9000
ST_CLERK 122 2700
SA_REP 146 8500
SA_REP 149 8333.33333
MK_MAN 100 13000
52 rows selected.5.复合列
--复合列是被作为整体处理得一组列得集合
--使用括号将若干组成复合列在rollup或cube中作为整体进行操作
--在rollup或cube中复合列可避免产生不必要得分组结果
hr@ORCLPDB01 2023-02-26 20:25:25> select department_id,job_id,manager_id
2 ,sum(salary)
3 from employees
4 group by rollup( department_id,(job_id,manager_id));
DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
SA_REP 149 7000
7000
10 AD_ASST 101 4400
10 4400
20 MK_MAN 100 13000
20 MK_REP 201 6000
20 19000
30 PU_MAN 100 11000
30 PU_CLERK 114 13900
30 24900
40 HR_REP 101 6500
40 6500
50 ST_MAN 100 36400
50 SH_CLERK 120 11600
50 SH_CLERK 121 14700
50 SH_CLERK 122 12800
50 SH_CLERK 123 13900
50 SH_CLERK 124 11300
50 ST_CLERK 120 10500
50 ST_CLERK 121 10700
50 ST_CLERK 122 10800
50 ST_CLERK 123 12000
50 ST_CLERK 124 11700
50 156400
60 IT_PROG 102 9000
60 IT_PROG 103 19800
60 28800
70 PR_REP 101 10000
70 10000
80 SA_MAN 100 61000
80 SA_REP 145 51000
80 SA_REP 146 51000
80 SA_REP 147 46600
80 SA_REP 148 51900
80 SA_REP 149 43000
80 304500
90 AD_VP 100 34000
90 AD_PRES 24000
90 58000
100 FI_MGR 101 12008
100 FI_ACCOUNT 108 39600
100 51608
110 AC_MGR 101 12008
110 AC_ACCOUNT 205 8300
110 20308
691416
46 rows selected.
Elapsed: 00:00:00.006.连接分组集
--连接分组可以产生有用的对分组项得集合
--将各分组集，rollup和cube用逗号连接，Oracle自动在group by子句中将各分组集进行连接
--连接得结果是对各分组生成笛卡尔集
hr@ORCLPDB01 2023-02-26 20:26:15> select department_id,job_id,manager_id,sum(salary)
2 from employees
3 group by department_id,
4 rollup(job_id),
5 cube(manager_id);
DEPARTMENT_ID JOB_ID MANAGER_ID SUM(SALARY)
------------- ---------- ---------- -----------
SA_REP 149 7000
10 AD_ASST 101 4400
20 MK_MAN 100 13000
20 MK_REP 201 6000
30 PU_MAN 100 11000
30 PU_CLERK 114 13900
40 HR_REP 101 6500
50 ST_MAN 100 36400
50 SH_CLERK 120 11600
50 SH_CLERK 121 14700
50 SH_CLERK 122 12800
50 SH_CLERK 123 13900
50 SH_CLERK 124 11300
50 ST_CLERK 120 10500
50 ST_CLERK 121 10700
50 ST_CLERK 122 10800
50 ST_CLERK 123 12000
50 ST_CLERK 124 11700
60 IT_PROG 102 9000
60 IT_PROG 103 19800
70 PR_REP 101 10000
80 SA_MAN 100 61000
80 SA_REP 145 51000
80 SA_REP 146 51000
80 SA_REP 147 46600
80 SA_REP 148 51900
80 SA_REP 149 43000
90 AD_VP 100 34000
90 AD_PRES 24000
100 FI_MGR 101 12008
100 FI_ACCOUNT 108 39600
110 AC_MGR 101 12008
110 AC_ACCOUNT 205 8300
149 7000
7000
10 101 4400
10 4400
20 100 13000
20 201 6000
20 19000
30 100 11000
30 114 13900
30 24900
40 101 6500
40 6500
50 100 36400
50 120 22100
50 121 25400
50 122 23600
50 123 25900
50 124 23000
50 156400
60 102 9000
60 103 19800
60 28800
70 101 10000
70 10000
80 100 61000
80 145 51000
80 146 51000
80 147 46600
80 148 51900
80 149 43000
80 304500
90 24000
90 100 34000
90 58000
100 101 12008
100 108 39600
100 51608
110 101 12008
110 205 8300
110 20308
SA_REP 7000
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_MAN 11000
30 PU_CLERK 13900
40 HR_REP 6500
50 ST_MAN 36400
50 SH_CLERK 64300
50 ST_CLERK 55700
60 IT_PROG 28800
70 PR_REP 10000
80 SA_MAN 61000
80 SA_REP 243500
90 AD_VP 34000
90 AD_PRES 24000
100 FI_MGR 12008
100 FI_ACCOUNT 39600
110 AC_MGR 12008
110 AC_ACCOUNT 8300
93 rows selected.
Elapsed: 00:00:00.00
