关联查询聚合多行明细数据时GROUP BY报错的解决方法

问题背景 #

两张表结构：

event (id, url, name, eventStart, eventEnd)  
slot (id, event_id, date, start_time, end_time,
    location_type, location_id)

原关联查询可正常执行，但单个event对应多个slot时会返回重复的event行：

select * from event as evnt 
inner join slot as slot on evnt.id = slot.event_id
where location_type = 'type of location' and slot.location_id = '12345'
and slot.event_id = :eventId

单独聚合slot数据的查询可以正常运行：

select event_id,
    array_agg(concat(date,':',start_time,':',end_time)) as slotDates
from slot
where event_id = 'event id' and location_id = '12345' 
group by event_id 

但将聚合逻辑加入关联查询时出现报错：

报错查询语句 #

select event_id,
    array_agg(concat(date,':',start_time,':',end_time)) as slotDates,
    evnt.id as eventId, evnt.url as eventUrl,
    evnt.name as eventName, evnt.event_start as eventStart,
    evnt.event_end as eventEnd, slot.location_type as locationType  
from event as evnt 
inner join slot as slot on evnt.id = slot.event_id
where slot.location_type = 'type of location'
and slot.location_id = '12345'
and slot.event_id = 'event id goes here'
group by slot.event_id

报错信息 #

ERROR:  column "evnt.id" must appear in the GROUP BY clause or be used in an aggregate function

解决方案 #

方法1：补充GROUP BY子句的字段 #

PostgreSQL要求SELECT中所有未使用聚合函数的字段，要么出现在GROUP BY子句中，要么是GROUP BY字段的依赖（比如主键对应的其他字段）。由于同一event对应的slot的location_type值相同，我们可以将所有非聚合字段加入GROUP BY：

select 
    evnt.id as eventId, 
    evnt.url as eventUrl,
    evnt.name as eventName, 
    evnt.eventStart as eventStart,
    evnt.eventEnd as eventEnd, 
    slot.location_type as locationType,
    array_agg(concat(slot.date,':',slot.start_time,':',slot.end_time)) as slotDates
from event as evnt 
inner join slot as slot on evnt.id = slot.event_id
where slot.location_type = 'type of location'
and slot.location_id = '12345'
and slot.event_id = 'event id goes here'
group by evnt.id, evnt.url, evnt.name, evnt.eventStart, evnt.eventEnd, slot.location_type

简化写法：因为evnt.id是event表的主键，PostgreSQL允许只GROUP BY主键，其他event字段会被自动识别为依赖字段，所以可以简化为：

select 
    evnt.id as eventId, 
    evnt.url as eventUrl,
    evnt.name as eventName, 
    evnt.eventStart as eventStart,
    evnt.eventEnd as eventEnd, 
    slot.location_type as locationType,
    array_agg(concat(slot.date,':',slot.start_time,':',slot.end_time)) as slotDates
from event as evnt 
inner join slot as slot on evnt.id = slot.event_id
where slot.location_type = 'type of location'
and slot.location_id = '12345'
and slot.event_id = 'event id goes here'
group by evnt.id, slot.location_type

方法2：先聚合slot再关联event（推荐） #

先通过子查询完成slot表的聚合，再和event表关联，避免关联后大量数据分组，性能更优：

select 
    evnt.id as eventId, 
    evnt.url as eventUrl,
    evnt.name as eventName, 
    evnt.eventStart as eventStart,
    evnt.eventEnd as eventEnd, 
    agg_slot.location_type as locationType,
    agg_slot.slotDates
from event as evnt
inner join (
    select 
        event_id,
        location_type,
        array_agg(concat(date,':',start_time,':',end_time)) as slotDates
    from slot
    where location_type = 'type of location'
    and location_id = '12345'
    and event_id = 'event id goes here'
    group by event_id, location_type
) as agg_slot on evnt.id = agg_slot.event_id

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