首页 > 数据库 >PostgreSQL 分组汇总(一)

PostgreSQL 分组汇总(一)

时间:2023-07-14 11:47:06浏览次数:38  
标签:salary count PostgreSQL 汇总 分组 子句 department id

聚合函数

聚合函数(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

相关文章

  • postgresql 简单使用
    编译安装的启动数据库:/usr/local/postgresql/bin/pg_ctl-D/data/postgresql-llogfilestart停止数据库:/usr/local/postgresql/bin/pg_ctl-D/data/postgresqlstop-mfast登录数据库:/usr/local/postgresql/bin/psqlpostgres 配置文件:/data/postgresql/postgresql.con......
  • 【FAQ】API6低代码开发问题汇总
    ​参考文档:低代码开发参考文档:https://developer.harmonyos.com/cn/docs/documentation/doc-guides/ide-low-code-0000001158284713基于景区模板开发元服务:https://developer.huawei.com/consumer/cn/doc/distribution/app/agc-lowcode-templateoverview-0000001548015654 ......
  • 我遇到的hadoop错误和解决方法汇总
     ==================================windowscygwin环境下,遇到这个错误:org.apache.hadoop.security.AccessControlException:Permissiondenied:user=cyg_server,access=EXECUTE,inode="job_201010161322_0003":heipark:supergroup:rwx------ 解决:刚开始还使用“./bin/hadoo......
  • PostgreSQL 限定结果数量
    查询语句的结果可能包含成百上千行数据,但是前端显示时也许只需要其中的小部分,例如TOP-N排行榜;或者为了便于查看,每次只显示一定数量的结果,例如分页功能。为了处理这类应用,SQL提供了标准的FETCH和OFFSET子句。另外,PostgreSQL还实现了扩展的LIMIT语法。Top-N查询这类查询通常是为了......
  • Postgresql统计所有表的基本信息(如行数、大小等)
    pg_class目录pg_class记录表和几乎所有具有列或者像表的东西。这包括索引(但还要参见pg_index)、序列、视图、物化视图、组合类型和TOAST表。pg_class中的一些逻辑标志被以一种懒惰的方式维护:在正确状态时它们被保证为真,但是当条件不再为真时它们并不会被立刻重置为假。例如......
  • PHP 生成数据库的 markdown 字段说明文档,支持 mysql,postgresql
    安装composerrequirepeng49/db2markdown命令行使用phpvendor/bin/db2markdown输入数据库的地址(host),端口(port)用户名,密码,要导出的表,默认是*,生成所有表的文档,指定多个表明用逗号隔开,如:table1,table2$phpsrc/bin/db2markdownpleaseenterthedb(1mys......
  • PostgreSQL在线修改数据类型
    修改大表中列的数据类型几乎总是一件痛苦的事情。由于altertable语句获得的排他锁,它可能会锁定整个表的读写。本文中,我们将探讨如何以最小的影响、最少的锁来执行这样的操作。它适用于任何数据类型;让我们以int到bigint的变化为例。 示范用例假设我们有一个有许多列的表。其......
  • Python Web:创建、删除、复制、移动文件及目录命令Python Web篇学习汇总:
    1.创建、删除文件及目录命令的使用命令说明touch文件名创建指定文件mkdir目录名创建目录(文件夹)rm文件名或者目录名删除指定文件或者目录rmdir目录名删除空目录touch命令效果图:mkdir命令效果图:rm命令效果图:rm删除目录效果图 说明:rm命令想要删除目录需要加上-r选项,-r表示......
  • 简单的排序方法汇总
    #include<stdio.h>//定义交换数值函数voidSwap(int*a,int*b);//定义输出数组函数voidPrint_arr(inta[],intsize);//简单插入排序voidFuns_Charu(inta[],intsize);//简单冒泡排序voidFuns_Maopao(inta[],intsize);//简单选择排序voidFuns_Xuanze(inta[],......
  • Stream - Collectors.groupingBy实现分组后,且每个分组也进行排序
    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 Stream-实现分组后,且每个分组也进行排序前言一、groupingBy高级用法二、先分组,再排序总结 前言之前记录过:stream的三个常用方式(toMap,groupingBy,findFirst)。这里继续记录下groupingBy的几个高......