Postgres使用array_agg聚合函数后按start_date排序报错的解决咨询

原查询语句可正常运行：

SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id

查询结果：

尝试添加ORDER BY d.start_date DESC来按start_date排序活动，修改后的语句：

SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id
ORDER BY d.start_date DESC

收到报错：

ERROR: 列"d.start_date"必须出现在GROUP BY子句中或用于聚合函数 LINE 4: ORDER BY d.start_date DESC

疑问：array_agg本身是聚合函数，该如何解决这个问题？

分组后d.start_date属于未聚合的列，每个活动对应多个start_date，数据库无法确定你要基于哪个start_date排序活动行，分两种场景处理：

场景1：让数组内的日期和ID按start_date降序排列 #

如果想让每个活动的dates数组和ids数组内部按start_date倒序排列，直接在array_agg中添加ORDER BY：

SELECT e.id, e.title, 
       array_agg(d.start_date ORDER BY d.start_date DESC) dates, 
       array_agg(d.id ORDER BY d.start_date DESC) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id

执行后第一个活动的dates会变为{2022-10-05,2022-06-05}，ids对应变为{2,1}。

场景2：按活动的最新/最早start_date排序整个活动行 #

如果想让活动行按最新的start_date降序排列，用MAX聚合函数获取每个活动的最新日期，再基于该值排序：

SELECT e.id, e.title, 
       array_agg(d.start_date) dates, 
       array_agg(d.id) ids
FROM event e JOIN event_date d ON e.id = d.event_id
GROUP BY e.id
ORDER BY MAX(d.start_date) DESC

查询结果会先显示第一个活动（最新日期2022-10-05），再显示第二个活动（2022-07-05）。若要按最早日期排序，将MAX替换为MIN即可。

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