PostgreSQL中数据分组操作(一)

如何使用PostgreSQL GROUP BY子句将行分组。

官网地址

声明: 由于操作系统, 版本更新等原因, 文章所列内容不一定100%复现, 还要以官方信息为准

https://www.postgresql.org/

PostgreSQL概述

PostgreSQL: 世界上最先进的开源关系数据库。

PostgreSQL中GROUP BY命令理论

• GROUP BY子句将SELECT语句返回的行划分为多个组。

• 基础语法

  SELECT 
     column_1, 
     column_2,
     ...,
     aggregate_function(column_3)
  FROM 
     table_name
  GROUP BY 
     column_1,
     column_2,
     ...;
  

• 当有多条指令执行时的顺序
  

PostgreSQL中GROUP BY命令实战

数据库样例数据来源: https://blog.csdn.net/zwq56693/article/details/137473602

• 使用GROUP BY子句从付款表中检索customer_id

  SELECT 
    customer_id 
  FROM 
    payment 
  GROUP BY 
    customer_id 
  ORDER BY 
    customer_id;
  

• 使用GROUP BY子句检索每个客户支付的总付款

  SELECT 
    customer_id, 
    SUM (amount) 
  FROM 
    payment 
  GROUP BY 
    customer_id 
  ORDER BY 
    customer_id;
  

• 使用ORDER BY子句和GROUP BY子句按付款总额对组进行排序

  SELECT 
    customer_id, 
    SUM (amount) 
  FROM 
    payment 
  GROUP BY 
    customer_id 
  ORDER BY 
    SUM (amount) DESC;
  

• 使用GROUP BY子句检索每个客户的付款总额，并显示客户名称和金额

  SELECT 
    first_name || ' ' || last_name full_name, 
    SUM (amount) amount 
  FROM 
    payment p
    INNER JOIN customer c on p.customer_id = c.customer_id
  GROUP BY 
    full_name 
  ORDER BY 
    amount DESC;
  

• 使用GROUP BY子句和COUNT（）函数来计算每个员工处理的付款数量

  SELECT
  	staff_id,
  	COUNT (payment_id)
  FROM
  	payment
  GROUP BY
  	staff_id;
  

• 使用GROUP BY子句按两列中的值对行进行分组

  SELECT 
    customer_id, 
    staff_id, 
    SUM(amount) 
  FROM 
    payment 
  GROUP BY 
    staff_id, 
    customer_id 
  ORDER BY 
    customer_id;
  

• 使用GROUP BY子句按付款日期对付款进行分组

  SELECT 
    payment_date::date payment_date, 
    SUM(amount) sum 
  FROM 
    payment 
  GROUP BY 
    payment_date
  ORDER BY 
    payment_date DESC;
  

更新计划

欲知后事如何, 请听下回分解