SQL堂上作业五

组函数

组函数用于对一个组进行操作

常见的组函数有以下几种

AVG，SUM，MAX，MIN

我们输入以下的指令，就可以看到这一组的基本情况

SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

输出如下：

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
 8272.72727       11500        6000      273000

MIN和MAX

我们可以使用min和max来求最小值和最大值

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

输出如下：

MIN(HIRE_DAT MAX(HIRE_DAT
------------ ------------
17-JUN-87    21-APR-00

COUNT

COUNT(*)

我们可以用COUNT(*)统计有多少行

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

输出如下：

  COUNT(*)
----------
        45

COUNT(expr)

我们可以在COUNT()中添加一个列名expr，这样就可以统计有多少行中，expr的值是飞空的

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;

输出如下：

COUNT(COMMISSION_PCT)
---------------------
                   34

COUNT(DISTINCT expr)

用于统计有在这些行中，有多少种不同的，非空的值

SELECT COUNT(DISTINCT department_id)
FROM employees;

输出如下：

COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11

组函数与空值

一般情况

注意：组函数中的运算，将全部忽略空值，不会收到空值的影响

SELECT AVG(commission_pct)
FROM employees;

输出如下：

AVG(COMMISSION_PCT)
-------------------
         .222857143

需要统计的情况

在部分情况下，我们需要将空值一并进行统计

此时我们可以用上节课讲解过的NVL等函数对空值进行转换

SELECT AVG(NVL(commission_pct, 0))
FROM employees;

输出如下：

AVG(NVL(COMMISSION_PCT,0))
--------------------------
                .072897196

创建组数据

什么是“创建组数据”，我们可以用一张图来表示：

在该节中，GROUP BY将会是一个重要的函数

GROUP BY语句

常规用法

我们可以通过GROUP BY语句，打印出每一组所对应的数据出来

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

输出如下：

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
           30        4150
                     7000
           90  19333.3333
           20        9500
           70       10000
          110       10150
           50  3475.55556
           80  8955.88235
           40        6500
           60        5760
           10        4400

已选择12行。

组别输出

注意：分组的依据department_id不一定需要出现在输出中

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

输出如下

AVG(SALARY)
-----------
       8600
       4150
       7000
 19333.3333
       9500
      10000
      10150
 3475.55556
 8955.88235
       6500
       5760
       4400

已选择12行。

基于更多的数据进行分组

我们可以基于两个甚至更多的组进行分组

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

输出如下：

   DEPT_ID JOB_ID               SUM(SALARY)
---------- -------------------- -----------
       110 AC_ACCOUNT                  8300
        90 AD_VP                      34000
        50 ST_CLERK                   55700
        80 SA_REP                    243500
        50 ST_MAN                     36400
        80 SA_MAN                     61000
       110 AC_MGR                     12000
        90 AD_PRES                    24000
        60 IT_PROG                    28800
       100 FI_MGR                     12000
        30 PU_CLERK                   13900
        50 SH_CLERK                   64300
        20 MK_MAN                     13000
       100 FI_ACCOUNT                 39600
           SA_REP                      7000
        70 PR_REP                     10000
        30 PU_MAN                     11000
        10 AD_ASST                     4400
        20 MK_REP                      6000
        40 HR_REP                      6500

已选择20行。

语句错误分析

分组函数和列混合输出分析

我们SELECT出的东西中，要么全是列，要么全是分组函数

下面这个语句中，既有列又有分组函数，则会出现报错

SELECT department_id, COUNT(last_name)
FROM employees;

在这个语句中，将会出现报错，因为department_id不是分组函数

SELECT department_id, COUNT(last_name)
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数

WHERE和GROUP同时使用分析

注意：当一个语句中包含有GROUP BY和分组函数（如AVG）时，不可以通过WHERE语句对分组函数的值进行限制

下面这个语句中，同时有GROUP BY和WHERE限制的AVG，就会报错

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;

输出如下

WHERE AVG(salary) > 8000
      *
第 3 行出现错误:
ORA-00934: 此处不允许使用分组函数

我们可以将WHERE改为HAVING，即可获得正确的输出

SELECT department_id, AVG(salary)
FROM employees
HAVING AVG(salary) > 8000
GROUP BY department_id;

输出如下

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
           90  19333.3333
           20        9500
           70       10000
          110       10150
           80  8955.88235

已选择6行。

我们仍可以用WHERE语句限制department_id

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 70
GROUP BY department_id;

输出如下

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100        8600
           90  19333.3333
          110       10150
           80  8955.88235

进行一系列的缝合后也是可以的

SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 70
HAVING AVG(salary) > 10000
GROUP BY department_id;

输出如下

DEPARTMENT_ID AVG(SALARY)
------------- -----------
           90  19333.3333
          110       10150

HAVING语句

HAVING语句通常在以下情况下对组进行限制

1，每一组的输出与组有关

2，有组函数被使用

3，有和HAVING匹配的组

HAVING使用案例一

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

输出如下：

DEPARTMENT_ID MAX(SALARY)
------------- -----------
          100       12000
           30       11000
           90       24000
           20       13000
          110       12000
           80       14000

已选择6行。

HAVING使用案例二

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);

输出如下：

JOB_ID                  PAYROLL
-------------------- ----------
PU_CLERK                  13900
AD_PRES                   24000
IT_PROG                   28800
AD_VP                     34000
ST_MAN                    36400
FI_ACCOUNT                39600
ST_CLERK                  55700
SA_MAN                    61000
SH_CLERK                  64300

已选择9行。

组函数复合

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

输出如下：

MAX(AVG(SALARY))
----------------
      19333.3333