PostgreSQL分组报错与重复键冲突求助：字段需在GROUP BY或聚合函数中

错误原因分析 #

第一个错误（列必须在GROUP BY或聚合函数中） #

原SQL里，窗口函数SUM(soh.subtotal) OVER (PARTITION BY EXTRACT(YEAR FROM soh.orderdate))直接引用了原始表的soh.subtotal字段，但外层已经按com.id和年份做了分组。PostgreSQL要求SELECT子句里的非聚合字段必须包含在GROUP BY中，而这里窗口函数内的soh.subtotal没经过分组聚合处理，所以触发语法错误。

第二个错误（唯一键重复） #

把soh.subtotal加入GROUP BY后，分组粒度变成了com.id、年份、soh.subtotal，同一公司同一年可能出现多条不同subtotal的记录，插入company_abc时就违反了(cid, year)的唯一约束（该字段组合应该是目标表的联合主键或唯一索引）。

正确SQL写法 #

用CTE（公共表表达式）先算出每个公司每年的销售总额，再基于这个结果计算年度总销售额并划分分类：

WITH company_year_sales AS (
    SELECT 
        com.id AS cid,
        EXTRACT(YEAR FROM soh.orderdate) AS year,
        SUM(soh.subtotal) AS salestotal
    FROM salesorderheader soh
    INNER JOIN customer c ON soh.customerid = c.customerid
    INNER JOIN company com ON com.cname = c.companyname
    GROUP BY com.id, EXTRACT(YEAR FROM soh.orderdate)
)
INSERT INTO company_abc (cid, salestotal, cls, year)
SELECT 
    cid,
    salestotal,
    CASE
        WHEN salestotal <= 0.8 * SUM(salestotal) OVER (PARTITION BY year) THEN 'A'
        WHEN salestotal <= 0.95 * SUM(salestotal) OVER (PARTITION BY year) THEN 'B'
        ELSE 'C'
    END AS cls,
    year
FROM company_year_sales
ORDER BY cid, year DESC;

方案说明 #

1. CTE分组去重：通过分组得到每个公司每年的唯一销售总额记录，从根源避免了插入时的重复键问题。
2. 窗口函数合理应用：在分组结果上使用窗口函数，SUM(salestotal) OVER (PARTITION BY year)计算的是该年度所有公司的销售总额总和，以此为依据划分公司分类，匹配业务需求。
3. 去除冗余操作：CTE的分组已经保证了记录唯一性，不需要额外使用DISTINCT。

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