PostgreSQL / 系统课时

多级分组与排序

多级分组与排序

本课解决“怎样按多个业务维度汇总,并把最重要的统计结果排在前面”的问题。完成后,你可以控制分组粒度,按聚合值排序,并设置稳定的并列排序规则。

学习目标

  • 使用多个字段形成多级分组
  • 按聚合结果进行升序或降序排列
  • 使用次级排序保证结果稳定

多级分组的含义

GROUP BY region, category 表示按地区和品类的组合分组。它不是先生成地区小计,再自动生成品类小计,而是只返回每个唯一组合的统计结果。

例如,同一个地区下的 book 和 digital 会分别形成两个分组;不同地区的 book 也属于不同分组。分组之后,COUNT(*) 统计每个组合的订单数,SUM(amount) 计算该组合的销售额。

若需要地区小计或总计,应另用 GROUPING SETS、ROLLUP 等功能;普通多字段分组不会自动产生这些汇总层级。

主示例

下面统计各地区、各品类的订单数与销售额,并优先展示销售额更高的组合。示例通过 WITH 构造六条销售记录,可以直接在 PostgreSQL 中执行,不需要预先创建表。

sql
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;

预期结果:

text
 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.00

ORDER 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 的不同分页请求仍可能出现记录重复或遗漏。要求跨页数据严格一致时,应在一致的数据快照上查询。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

多级分组与排序

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通