PostgreSQL / 系统课时
使用 GROUP BY 分组
使用 GROUP BY 分组
本课解决“怎样按地区、类型等维度分别统计”的问题。完成后,你可以按一个或多个字段分组,计算每组的订单数量和金额,并理解为什么查询列通常必须出现在 GROUP BY 中或由聚合函数处理。
学习目标
- 使用
GROUP BY计算每组统计值 - 掌握多字段分组的组合含义
- 避免选择未分组、未聚合的列
分组查询的执行结果
GROUP BY 会把分组字段取值相同的行放入同一组,再对每组执行聚合函数。例如,按地区分组后,COUNT(*) 统计各地区的订单数量,SUM(amount) 统计各地区的订单金额。结果通常是每个分组一行,而不是每笔订单一行。
如果使用 GROUP BY region, channel,只有地区和支付方式都相同的记录才属于同一组。因此,east + card 与 east + qr 是两个不同的分组。
选择列表中的普通列通常需要出现在 GROUP BY 中,否则 PostgreSQL 无法确定一组多行应该返回该列的哪个值。聚合表达式则负责把组内多个值计算成一个结果。
主示例
下面按“地区 + 支付方式”组合分组。每一种不同组合都会形成独立结果行。示例使用 WITH 定义临时数据,不需要提前创建数据库表,可以直接在 PostgreSQL 查询工具或 psql 中执行。
-- 构造五笔订单,便于独立验证分组结果
WITH orders(region, channel, amount) AS (
VALUES
('east', 'card', 100.00::numeric),
('east', 'card', 150.00::numeric),
('east', 'qr', 80.00::numeric),
('west', 'card', 120.00::numeric),
('west', 'qr', 150.00::numeric)
)
SELECT
region,
channel,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
-- 两列的不同取值组合分别形成独立分组
GROUP BY region, channel
ORDER BY region, channel;预期结果:
region | channel | order_count | total_amount
--------+---------+-------------+--------------
east | card | 2 | 250.00
east | qr | 1 | 80.00
west | card | 1 | 120.00
west | qr | 1 | 150.00可以用 east + card 这一组验证计算是否正确:原始数据中有两笔订单,金额分别为 100.00 和 150.00,因此 order_count = 2,total_amount = 250.00。其余三个组合各有一笔订单,统计结果也与原始数据对应。
如果只按 region 分组,并且不再选择 channel,支付方式就会被合并,最终只得到 east 和 west 两行。其中 east 的订单数量为 3、总金额为 330.00;west 的订单数量为 2、总金额为 270.00。
分组字段越多,统计粒度通常越细。实际编写查询时,应先确定需要的是“每个地区一行”,还是“每个地区的每种支付方式一行”,再决定 GROUP BY 包含哪些列。
还需要注意两个边界:GROUP BY 本身不保证结果的显示顺序,本例通过 ORDER BY region, channel 固定排序;如果分组列包含 NULL,这些空值会归入同一个分组,但不能据此认为它们代表相同的实际业务值。
常见错误
下面这种写法选择了 channel,却只按 region 分组。由于同一地区可能存在多种支付方式,PostgreSQL 无法为每个地区确定唯一的 channel 值,因此会报错。
WITH orders(region, channel, amount) AS (
VALUES
('east', 'card', 100.00::numeric),
('east', 'qr', 80.00::numeric)
)
-- channel 未参与分组,也没有使用聚合函数
SELECT region, channel, SUM(amount)
FROM orders
GROUP BY region;预期结果:
ERROR: column "orders.channel" must appear in the GROUP BY clause or be used in an aggregate function修复方式取决于统计目标:如果需要分别统计支付方式,就把 channel 加入 GROUP BY;如果只需要地区汇总,就从 SELECT 中删除 channel,保留 region 和 SUM(amount)。
不要使用 MIN(channel) 等没有业务含义的聚合函数来掩盖分组问题。虽然这样可能让 SQL 执行成功,但结果只会选出组内某个支付方式,不能代表该地区全部订单。
验证分组查询时,先检查结果行数是否符合预期的组合数量,再选取一个分组手工核对 COUNT(*) 和 SUM(amount)。如果行数或金额不对,优先检查分组字段是否遗漏、是否包含多余维度,以及原始记录是否符合统计范围。