OCA/OCP Oracle 数据库12c考试指南读书笔记：第9章: Group Functions

本文主要是介绍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.45188

MAX和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 here

Group函数可以嵌套，但最多两层。

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的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---