本文主要是介绍OCA/OCP Oracle 数据库12c考试指南读书笔记：第9章: Group Functions，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
上一章讲了单行函数，就是对于结果集中的每一行只返回一个值。而本章介绍的多行函数是针对多行执行操作，称为Group或Aggregate函数。通常用于报表。
描述Group函数
单行函数：
F(x, y, z, . . .) = result
Group函数：
F(g1, g2, g3, . . . , gn) = result1, result2, result2, . . . , resultn
select count(*), department_id, from employees order by department_id group by department_id;
group函数每次只针对一个集合操作；并且绝大多数不支持NULL。
使用Group函数
AVG, SUM, MIN, MAX, COUNT, STDDEV和VARIANCE，都返回数字值。此外，MAX和MIN还可以返回字符或日期。
除COUNT在某些情况下允许操作控制外，其它函数均不允许。
COUNT
COUNT ({ * | [DISTINCT|ALL] expr})
如果指定了列名，则不会计算空值。否则会计算，如count(1)，count(*)
SUM
SUM ({ [DISTINCT|ALL] expr})，不计入空值
AVG
AVG ({ [DISTINCT|ALL] expr})，不计入空值
STDDEV 和 VARIANCE
STDDEV表示standard deviation，中文为标准差或均方差。标准差是一组数值自平均值分散开来的程度的一种测量观念。一个较大的标准差，代表大部分的数值和其平均值之间差异较大；一个较小的标准差，代表这些数值较接近平均值。
STDEDEV先算出平均值，然后用每一个值减去平均值后求平方根，平方根求和后除以N-1。
SQL> select salary from employees where department_id = 90;SALARY
----------240001700017000SQL> select avg(salary), variance(salary), stddev(salary) from employees where department_id = 90;AVG(SALARY) VARIANCE(SALARY) STDDEV(SALARY)
----------- ---------------- --------------19333.3333 16333333.3 4041.45188MAX和MIN
不考虑控制。可操作字符串，数字和日期。
LISTAGG
返回拼接的串，可以指定分隔符，可以排序。
对所有行操作，但只返回一行。例如：
SQL> select * from countries order by region_id, country_name desc;CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
UK United Kingdom 1
CH Switzerland 1
NL Netherlands 1
IT Italy 1
DE Germany 1
FR France 1
DK Denmark 1
BE Belgium 1
US United States of America 2
MX Mexico 2
CA Canada 2
BR Brazil 2
AR Argentina 2
SG Singapore 3
ML Malaysia 3
JP Japan 3
IN India 3
CN China 3
AU Australia 3
ZW Zimbabwe 4
ZM Zambia 4
NG Nigeria 4
KW Kuwait 4
IL Israel 4
EG Egypt 425 rows selected.-- 返回一行
SQL> select listagg(country_name, ',') within group (order by region_id, country_name desc) countries from countries;COUNTRIES
--------------------------------------------------------------------------------
United Kingdom,Switzerland,Netherlands,Italy,Germany,France,Denmark,Belgium,Unit
ed States of America,Mexico,Canada,Brazil,Argentina,Singapore,Malaysia,Japan,Ind
ia,China,Australia,Zimbabwe,Zambia,Nigeria,Kuwait,Israel,Egypt-- 返回多行
SQL> select listagg(country_name, ',') within group (order by country_name desc) Countries,count(*) "# per Region" from countries group by region_id;COUNTRIES # per Region
-------------------------------------------------------------------------------- ------------
United Kingdom,Switzerland,Netherlands,Italy,Germany,France,Denmark,Belgium 8
United States of America,Mexico,Canada,Brazil,Argentina 5
Singapore,Malaysia,Japan,India,China,Australia 6
Zimbabwe,Zambia,Nigeria,Kuwait,Israel,Egypt 6
Group by 子句
group是共享某一属性的数据子集，属性通常是一列，也可以是多列或表达式。group的数量取决于唯一属性值的数量。
group by位于where之后，order by之前
Any item in the SELECT list that is not a group function must be a grouping attribute of the GROUP BY clause.
Note that only grouping attributes and group functions are permitted in the SELECT clause when using GROUP BY.
SQL> select department_id, max(salary), count(*) from employees group by department_id order by department_id;DEPARTMENT_ID MAX(SALARY) COUNT(*)
------------- ----------- ----------10 4400 120 13000 230 11000 640 6500 150 8200 4560 9000 570 10000 180 14000 3490 24000 3100 12008 6110 12008 27000 112 rows selected.Group By语句的一些限制：
-- job_id在 带group函数的select列表中，但没有对应的group by语句。
SQL> select job_id, department_id, max(salary), count(*) from employees group by department_id order by department_id,job_id;
select job_id, department_id, max(salary), count(*) from employees group by department_id order by department_id,job_id*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
-- 加上order by job_id就正确了
SQL> select job_id, department_id, max(salary), count(*) from employees group by department_id, job_id order by department_id;JOB_ID DEPARTMENT_ID MAX(SALARY) COUNT(*)
---------- ------------- ----------- ----------
AD_ASST 10 4400 1
MK_MAN 20 13000 1
MK_REP 20 6000 1
PU_CLERK 30 3100 5
PU_MAN 30 11000 1
HR_REP 40 6500 1
SH_CLERK 50 4200 20
ST_CLERK 50 3600 20
ST_MAN 50 8200 5
IT_PROG 60 9000 5
PR_REP 70 10000 1
SA_MAN 80 14000 5
SA_REP 80 11500 29
AD_PRES 90 24000 1
AD_VP 90 17000 2
FI_ACCOUNT 100 9000 5
FI_MGR 100 12008 1
AC_ACCOUNT 110 8300 1
AC_MGR 110 12008 1
SA_REP 7000 120 rows selected.
-- 下面的错误是因为没有group by
SQL> select job_id, department_id, max(salary), count(*) from employees job_id order by department_id;
select job_id, department_id, max(salary), count(*) from employees job_id order by department_id*
ERROR at line 1:
ORA-00937: not a single-group group function
-- 以下错误需通过having解决，where条件中不允许Group函数
SQL> select * from employees where count(*) > 5;
select * from employees where count(*) > 5*
ERROR at line 1:
ORA-00934: group function is not allowed hereGroup函数可以嵌套，但最多两层。
Having子句
Having子句在Group Level限制输出。只有group by出现时，才能出现Having子句。
SQL> select department_id from job_history where department_id in (50, 60, 80, 110);DEPARTMENT_ID
-------------50506080801101107 rows selected.SQL> select department_id, count(*) from job_history where department_id in (50, 60, 80, 110) group by department_id;DEPARTMENT_ID COUNT(*)
------------- ----------50 260 180 2110 2
SQL> select department_id, count(*) from job_history where department_id in (50, 60, 80, 110) group by department_id having count(*) > 1;DEPARTMENT_ID COUNT(*)
------------- ----------50 280 2110 2另一个示例：
SQL> select job_id, avg(salary), count(*) from employees group by job_id;JOB_ID AVG(SALARY) COUNT(*)
---------- ----------- ----------
SH_CLERK 3215 20
HR_REP 6500 1
AD_VP 17000 2
FI_ACCOUNT 7920 5
PU_CLERK 2780 5
AC_MGR 12008 1
PU_MAN 11000 1
ST_CLERK 2785 20
AD_ASST 4400 1
AC_ACCOUNT 8300 1
IT_PROG 5760 5
SA_MAN 12200 5
FI_MGR 12008 1
ST_MAN 7280 5
MK_MAN 13000 1
AD_PRES 24000 1
MK_REP 6000 1
PR_REP 10000 1
SA_REP 8350 3019 rows selected.SQL> select job_id, avg(salary), count(*) from employees group by job_id having avg(salary) > 10000;JOB_ID AVG(SALARY) COUNT(*)
---------- ----------- ----------
AD_VP 17000 2
AC_MGR 12008 1
PU_MAN 11000 1
SA_MAN 12200 5
FI_MGR 12008 1
MK_MAN 13000 1
AD_PRES 24000 17 rows selected.SQL> select job_id, avg(salary), count(*) from employees group by job_id having avg(salary) > 10000 and count(*) > 1;JOB_ID AVG(SALARY) COUNT(*)
---------- ----------- ----------
AD_VP 17000 2
SA_MAN 12200 5这篇关于OCA/OCP Oracle 数据库12c考试指南读书笔记：第9章: Group Functions的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
