SQL分组查询问题：按非主键user_id聚合id为数组

1. 第一个查询的问题 #

你最初的查询GROUP BY tab.id是错误的——因为id是主键，每条记录的id唯一，分组后每条记录单独成组，自然无法按user_id聚合。正确的分组字段应该是user_id，这也是你后续尝试的方向，但遇到了排序相关的报错。

2. 排序报错的核心原因 #

报错[42803] ERROR: column "tab.email_notification" must appear in the GROUP BY clause or be used in an aggregate function的本质是：
分组后，数据库仅能处理分组字段或经聚合函数处理后的字段。email_notification既不是分组字段（你按user_id分组），也没有用聚合函数（如MAX()、MIN()）包裹，数据库无法确定每个user_id组对应的email_notification值（一个user_id可能对应多条记录，每条的email_notification可能不同）。

3. 可行解决方案 #

根据你的实际数据情况，分两种场景处理：

场景A：每个user_id对应的email_notification值唯一 #

如果每个user_id的email_notification是固定值（比如user_id关联的用户表中该字段唯一），直接将email_notification加入GROUP BY即可：

SELECT tab.user_id, ARRAY_AGG(tab.id) AS array_ids
FROM table tab
GROUP BY tab.user_id, tab.email_notification
ORDER BY tab.user_id ASC, tab.email_notification DESC NULLS LAST

场景B：每个user_id对应多个email_notification值 #

如果一个user_id有多条不同的email_notification记录，需要用聚合函数明确指定取哪个值用于排序，比如取最大值或最小值：

SELECT tab.user_id, ARRAY_AGG(tab.id) AS array_ids
FROM table tab
GROUP BY tab.user_id
ORDER BY tab.user_id ASC, MAX(tab.email_notification) DESC NULLS LAST

额外优化：对数组内的id排序 #

如果需要数组中的id按顺序排列，可以在ARRAY_AGG中直接添加排序规则：

SELECT tab.user_id, ARRAY_AGG(tab.id ORDER BY tab.id ASC) AS array_ids
FROM table tab
GROUP BY tab.user_id
ORDER BY tab.user_id ASC, MAX(tab.email_notification) DESC NULLS LAST

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