PostgreSQL分组查询GROUP BY列报错及MySQL结果差异解决方案

这个报错是PostgreSQL遵循标准SQL分组规则的正常提示：SELECT子句中没有被聚合函数包裹的字段，必须全部加入GROUP BY子句才能正常执行。
你之前尝试给total_amount、items套AVG()/MIN()/MAX()/ARRAY_AGG()聚合函数的写法本身逻辑不符合需求：这类聚合是对用户所有有效订单字段做统计计算，根本不是取「最早下单时间对应的那笔订单」的属性值。你之前在MySQL中得到的结果本身就不可靠——旧版本MySQL默认关闭ONLY_FULL_GROUP_BY校验时，分组后会随机返回组内某条记录的非聚合字段，和你要的「用户首次实际购买商品」的目标不匹配，两边结果有差异是必然的。

核心逻辑是先定位每个用户total_amount != 0的有效订单里，time_in最早的那笔订单，再提取对应的items值填充目标字段，下面两种写法都可以准确实现需求：

方案1：窗口函数（通用推荐，兼容多数主流数据库） #

用ROW_NUMBER()窗口函数按用户ID分区，按下单时间升序排序，取每个分区排序第一的记录就是用户首次购买记录：

-- 先执行查询验证结果正确性
WITH first_purchase_record AS (
    SELECT
        cust_id,
        total_amount,
        items,
        time_in,
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY time_in ASC) AS row_rank
    FROM tr_invoice
    WHERE total_amount <> 0
)
SELECT cust_id, total_amount, items, time_in
FROM first_purchase_record
WHERE row_rank = 1;

-- 验证结果无误后，执行更新填充tr2_invoice表的first_actual_item字段
UPDATE tr2_invoice t2
SET first_actual_item = fpr.items
FROM (
    SELECT
        cust_id,
        items,
        ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY time_in ASC) AS row_rank
    FROM tr_invoice
    WHERE total_amount <> 0
) fpr
WHERE t2.cust_id = fpr.cust_id AND fpr.row_rank = 1;

特殊场景说明：如果同一个用户存在多笔time_in完全一致的最早有效订单，ROW_NUMBER()会随机返回其中一条的商品信息。如果需要把同时间所有首次购买的商品都保留，可以把ROW_NUMBER()替换为RANK()，再用ARRAY_AGG()或STRING_AGG()聚合items字段即可。

方案2：PostgreSQL专属DISTINCT ON写法（性能更优，语法简洁） #

PostgreSQL原生支持的DISTINCT ON语法可以直接按指定字段去重，返回每个分组按规则排序后的第一条记录，写法更简单：

-- 查询验证结果
SELECT DISTINCT ON (cust_id)
    cust_id, total_amount, items, time_in
FROM tr_invoice
WHERE total_amount <> 0
ORDER BY cust_id, time_in ASC;

-- 对应更新逻辑
UPDATE tr2_invoice t2
SET first_actual_item = fp.items
FROM (
    SELECT DISTINCT ON (cust_id)
        cust_id, items
    FROM tr_invoice
    WHERE total_amount <> 0
    ORDER BY cust_id, time_in ASC
) fp
WHERE t2.cust_id = fp.cust_id;

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