PostgreSQL中按类别分组计算创建序号的高效查询方案咨询

嘿，你现在用的这个子查询方案性能拉胯太正常了——这属于相关子查询，每一行数据都要单独执行一次COUNT(*)查询，相当于对表做了N次重复扫描（N是表的总行数），数据量稍微大一点就会直接卡爆。

给你个PostgreSQL原生的高效解决方案：用窗口函数ROW_NUMBER()，这就是专门为这种分组内排序编号场景设计的，只需要一次扫描就能搞定，性能提升不是一星半点。

直接上代码：

SELECT 
    id, 
    category,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS creation_number
FROM test;

给你拆解下这个语句的逻辑：

• PARTITION BY category：把表按category字段拆分成多个独立的分组窗口，每个窗口里都是同分类的记录
• ORDER BY id：在每个分组内部，按照id的顺序排序（既然你用id来判断创建顺序，假设id是自增主键的话，这个排序就代表了记录的创建先后）
• ROW_NUMBER()：自动在每个分组内从1开始生成递增的序号，完全符合你要的creation_number效果

如果你的创建顺序不是由id决定，而是有专门的created_at时间字段，只需要把ORDER BY id改成ORDER BY created_at就行，非常灵活。

额外性能优化建议 #

如果你的表数据量很大，可以创建一个复合索引：

CREATE INDEX idx_test_category_id ON test(category, id);

这个索引会让数据库直接利用有序的索引数据来生成序号，连分组内的排序步骤都省了，性能会更上一层楼。

内容的提问来源于stack exchange，提问作者DoNotClick