PostgreSQL / 系统课时
多级分组与排序
多级分组与排序
本课解决“怎样按多个业务维度汇总,并把最重要的统计结果排在前面”的问题。完成后,你可以控制分组粒度,按聚合值排序,并设置稳定的并列排序规则。
学习目标
- 使用多个字段形成多级分组
- 按聚合结果进行升序或降序排列
- 使用次级排序保证结果稳定
多级分组的含义
GROUP BY region, category 表示按地区和品类的组合分组。它不是先生成地区小计,再自动生成品类小计,而是只返回每个唯一组合的统计结果。
例如,同一个地区下的 book 和 digital 会分别形成两个分组;不同地区的 book 也属于不同分组。分组之后,COUNT(*) 统计每个组合的订单数,SUM(amount) 计算该组合的销售额。
若需要地区小计或总计,应另用 GROUPING SETS、ROLLUP 等功能;普通多字段分组不会自动产生这些汇总层级。
主示例
下面统计各地区、各品类的订单数与销售额,并优先展示销售额更高的组合。示例通过 WITH 构造六条销售记录,可以直接在 PostgreSQL 中执行,不需要预先创建表。
WITH sales(region, category, amount) AS (
VALUES
('east', 'book', 100.00::numeric),
('east', 'book', 80.00::numeric),
('east', 'digital', 260.00::numeric),
('west', 'book', 120.00::numeric),
('west', 'digital', 150.00::numeric),
('west', 'digital', 200.00::numeric)
)
SELECT
region,
category,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM sales
-- 地区与品类的每个唯一组合形成一个分组
GROUP BY region, category
-- 先按销售额排序,再使用订单数和分组键处理并列
ORDER BY total_amount DESC, order_count DESC, region, category;预期结果:
region | category | order_count | total_amount
--------+----------+-------------+--------------
west | digital | 2 | 350.00
east | digital | 1 | 260.00
east | book | 2 | 180.00
west | book | 1 | 120.00ORDER BY total_amount DESC 按销售额从高到低排序。后续的订单数、地区和品类用于处理并列值,使输入数据不变时,多次执行仍能得到相同的结果顺序。
验证时可以先检查分组数量:六条原始记录共有四种不同的地区与品类组合,因此结果应有四行。再核对 west/digital 的两条记录,销售额为 150.00 + 200.00 = 350.00,订单数为 2;east/book 的销售额为 180.00,订单数同样为 2。最后检查四行销售额是否按 350.00、260.00、180.00、120.00 递减排列。
排序表达式选择
PostgreSQL 允许在 ORDER BY 中使用选择列别名,因此可以直接写 total_amount。也可以写 ORDER BY SUM(amount) DESC,但别名通常更易读。
排序条件按从左到右的优先级依次生效。只有前一个条件的值相同时,数据库才比较后一个条件。本例先比较销售额,再比较订单数;如果两者仍相同,则按地区、品类升序排列。未指定方向的 region 和 category 默认使用 ASC。
由于 GROUP BY region, category 保证每个地区与品类组合只出现一次,将这两个分组字段放在排序条件末尾,可以为每个结果行确定唯一的位置。如果只写 ORDER BY total_amount DESC,当两个分组的销售额相同时,它们之间的顺序不受保证。
报表分页时尤其要补充能够区分结果行的排序条件。只按可能重复的聚合值排序,页与页之间可能出现顺序漂移。对于分组统计结果,可以使用完整的分组键作为最终排序条件;对于普通明细查询,则通常使用主键打破最后的并列。
需要注意,稳定排序只保证同一数据快照下的顺序确定。如果分页期间原始订单发生新增、删除或修改,即使排序条件完整,使用 LIMIT 和 OFFSET 的不同分页请求仍可能出现记录重复或遗漏。要求跨页数据严格一致时,应在一致的数据快照上查询。