聚合函数
聚合函数(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;标签:salary,count,PostgreSQL,汇总,分组,子句,department,id From: https://www.cnblogs.com/wdh01/p/17233447.html