PostgreSQL 14：保留9个最大值并将其余值求和为Other（禁用子查询）

需求说明 #

现有一张包含name和value两列的表，需按value列降序排序后保留前9个最大值对应的记录；剩余所有记录的value求和后，以「Other」作为名称展示。要求不得使用嵌套子查询（允许使用CTE公共表表达式，其不属于传统嵌套子查询范畴），可使用窗口函数，基于PostgreSQL 14实现。

初始数据 #

"name"  "value"
"A"        1
"B"        1
"C"        2
"D"        4
"E"        5
"F"        1
"G"        4
"H"        7
"I"        5
"J"        4
"K"        2
"L"        6
"M"        5
"N"        4

预期结果 #

"name"  "value"
  "H"      7
  "L"      6
  "E"      5
  "I"      5
  "M"      5
  "D"      4
  "G"      4
  "J"      4
  "N"      4
 "Other"   7

建表与插入数据代码 #

CREATE TABLE temp_table (
    name varchar(20),
    value int
);

INSERT INTO temp_table VALUES ('A', 1);
INSERT INTO temp_table VALUES ('B', 1); 
INSERT INTO temp_table VALUES ('C', 2); 
INSERT INTO temp_table VALUES ('D', 4); 
INSERT INTO temp_table VALUES ('E', 5); 
INSERT INTO temp_table VALUES ('F', 1); 
INSERT INTO temp_table VALUES ('G', 4); 
INSERT INTO temp_table VALUES ('H', 7); 
INSERT INTO temp_table VALUES ('I', 5); 
INSERT INTO temp_table VALUES ('J', 4); 
INSERT INTO temp_table VALUES ('K', 2); 
INSERT INTO temp_table VALUES ('L', 6);
INSERT INTO temp_table VALUES ('M', 5); 
INSERT INTO temp_table VALUES ('N', 4);

解决方案代码 #

WITH ranked_data AS (
    SELECT
        name,
        value,
        -- 按value降序排名，相同value的记录排名一致
        RANK() OVER (ORDER BY value DESC) AS rnk
    FROM temp_table
)
SELECT
    CASE WHEN rnk <= 9 THEN name ELSE 'Other' END AS name,
    SUM(value) AS value
FROM ranked_data
GROUP BY CASE WHEN rnk <= 9 THEN name ELSE 'Other' END
-- 排序规则：Other放在最后，其余按value降序排列
ORDER BY
    CASE WHEN name = 'Other' THEN 1 ELSE 0 END,
    value DESC;

代码说明 #

1. CTE排名计算：使用RANK()窗口函数按value降序为每条记录分配排名，相同value的记录获得相同排名，确保所有高value的记录都能被纳入Top9（例如3个value=5、4个value=4的记录都会被包含）。
2. 分组聚合：通过CASE表达式将排名≤9的记录保留原name，其余统一标记为「Other」，再按该标记分组求和。
3. 结果排序：通过ORDER BY中的CASE表达式将「Other」放在结果集最后，其余记录按value降序排列，与预期结果一致。

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