PostgreSQL 分组汇总（一）

聚合函数

聚合函数（aggregate function）针对一组数据行进行运算，并且返回单个结果。PostgreSQL支持以下常见的聚合函数：

• AVG-计算一组值的平均值。
• COUNT-统计一组值的数量。
• MAX-计算一组值的最大值。
• MIN-计算一组值的最小值。
• SUM-计算一组值的和值。
• STRING_AGG-连接一组字符串。

示例：分别返回了IT部门所有员工的平均薪水、员工总数、最高薪水、最低薪水、以及薪水总计

select avg(salary) avg_salary,
       count(*)    cnt,
       max(salary) max_salary,
       min(salary) min_salary,
       sum(salary) sum_salary
from employees
where department_id = 60;

关于聚合函数，需要注意两点：

• 函数参数前添加DISTINCT关键字，可以在计算时排除重复值。
• 忽略参数中的NULL值。

来看以下查询：

select count(*)                       cnt,
       count(distinct salary)         salary_cnt_dis,
       count(distinct commission_pct) cmom_cnt_dis,
       count(department_id)           dept_cnt ,
       count(distinct department_id)  dept_cnt_dis
from employees
where department_id = 60;

 其中，COUNT(*)返回了该部门员工的总数（5），COUNT(DISTINCT salary)返回了薪水不相同的员工数量（4），COUNT(commission_pct)返回了佣金百分比不为空值的数量（0），该部门员工都没有佣金提成，count(department_id) 返回 department_id 记录数（5），count(distinct department_id) 返回去重后的部门id数 1。

以下示例使用STRING_AGG函数将IT部门员工的名字使用分号进行分隔，按照薪水从高到底排序后连接成一个字符串：

SELECT STRING_AGG(first_name, ';' ORDER BY salary DESC)FROM employees WHERE department_id = 60;

分组聚合

 已经获得了IT部门的一些汇总信息，如果还需要知道其他部门的相关信息，可以多次运行相同的查询（修改查询条件中的部门编号）。但是这种明显过于复杂，不适合实际应用。SQL为此提供了GROUP BY子句，它用于将数据分成多个组，然后使用聚合函数对每个组进行汇总。举例来说，如果我们想要知道每个部门内所有员工的平均薪水、员工总数、最高薪水、最低薪水、以及薪水总计，可以使用以下查询语句：

select department_id,
       avg(salary) avg_salary,
       count(*)    cnt,
       max(salary) max_salary,
       min(salary) min_salary,
       sum(salary) sum_salary
from employees
group by department_id order by 1;

查询执行时，首先根据GROUP BY子句中的列（department_id）进行分组，然后使用聚合函数汇总组内的数据。最后一条数据是针对部门编号字段为空的数据进行的分组汇总，GROUP BY将所有的NULL分为一组。GROUP BY并不一定需要与聚合函数一起使用，例如：

SELECT department_id FROM employees GROUP BY department_id ORDER BY department_id;

查询的结果就是不同的部门编号分组，这种查询的结果与DISTINCT效果相同：

SELECT distinct department_id FROM employees   ORDER BY department_id;

GROUP BY不仅可以按照一个字段进行分组，也可以使用多个字段将数据分成更多的组。例如，以下查询将员工按照不同的部门和职位组合进行分组，然后进行汇总：

select department_id,
       job_id,
       avg(salary) avg_salary,
       count(*)    cnt,
       max(salary) max_salary,
       min(salary) min_salary,
       sum(salary) sum_salary
from employees
group by department_id,job_id order by 1;

使用了GROUP BY子句进行分组操作之后需要注意一点，就是SELECT列表中只能出现分组字段或者聚合函数，不能再出现表中的其他字段。下面是一个错误的示例：

SELECT department_id,job_id,AVG(salary),COUNT(*),MAX(salary),MIN(salary),SUM(salary)FROM employees GROUP BY department_id;

错误的原因在于job_id既不是分组的条件，也不是聚合函数。查询要求按照部门进行分组汇总，但是每个部门存在多个不同的职位，数据库无法知道需要显示哪个职位编号。

分组过滤

当需要针对分组汇总后的数据再次进行过滤时，例如找出平均薪水值大于10000的部门，直观的想法就是在WHERE子句中增加一个过滤条件，例如：

select department_id,
       job_id,
       avg(salary) avg_salary,
       count(*)    cnt,
       max(salary) max_salary,
       min(salary) min_salary,
       sum(salary) sum_salary
from employees
where avg(salary)>1000
group by department_id,job_id order by 1;

不过查询并没有返回期望的结果，而是出现了一个错误：WHERE子句中不允许出现聚合函数。因为在SQL询中，如果同时存在WHERE子句和GROUP BY子句，WHERE子句在GROUP BY子句之前执行。因此，WHERE子句无法对分组后的结果进行过滤。WHERE子句执行时还没有进行分组计算，它只能基于分组之前的数据进行过滤。如果需要对分组后的结果进行过滤，需要使用HAVING子句。以上查询的正确写法如下：

select department_id,
       avg(salary) avg_salary,
       count(*)    cnt,
       max(salary) max_salary,
       min(salary) min_salary,
       sum(salary) sum_salary
from employees
group by department_id
having avg(salary) > 10000
order by 1;

HAVING出现在GROUP BY之后，也在它之后执行，因此能够使用聚合函数进行过滤。我们可以同时使用WHERE子句进行数据行的过滤，使用HAVING进行分组结果的过滤。以下示例用于查找哪些部门中薪水大于10000的员工的数量多于2个：

select department_id,
       count(*) cnt
from employees
where salary > 10000
group by department_id
having count(*) > 2
order by 1;