LATERAL JOIN使用问题：聚合查询结果不符预期的修正需求

现有表结构 #

create table strikes
(
    id                                bigint generated always as identity
        constraint pk_strikes
            primary key,
    planned_on_date                   date,
    goals                             text    not null,
    results                           text,
    overall_num_of_employees_involved integer not null
        constraint ck_strikes_overall_num_of_employees_involved_positive
            check (overall_num_of_employees_involved >= 1),
    updated_at                        timestamp with time zone,
    duration                          tstzrange,
    enterprise_id                     bigint  not null
        constraint fk_strikes_enterprise_id_enterprises
            references enterprises,
    created_by_id                     bigint  not null
        constraint fk_strikes_created_by_id_users
            references users,
    union_in_charge_id                bigint
        constraint fk_strikes_union_in_charge_id_unions
            references unions
            on delete set null
);
create table strike_to_user_associations
(
    strike_id bigint not null
        constraint fk_strike_to_user_associations_strike_id_strikes
            references strikes
            on delete cascade,
    user_id   bigint not null
        constraint fk_strike_to_user_associations_user_id_users
            references users
            on delete cascade,
    role      userrole,
    constraint pk_strike_to_user_associations
        primary key (strike_id, user_id)
);

其中strike_to_user_associations是连接user与strike的多对多中间表。

原查询语句 #

select count(*), sub.aa, sub.ab
from strikes
join lateral (
    select
        array_agg(strike_to_user_associations.strike_id) as aa,
        array_agg(strike_to_user_associations.strike_id) filter ( where strikes.duration @> now()) as ab
    from strike_to_user_associations
    where strike_to_user_associations.user_id = strikes.created_by_id
    ) as sub on true
where created_by_id = 34
group by  sub.aa, sub.ab
;

需求与问题 #

预期查询返回：

• strike表中指定用户（created_by_id = 34）创建的罢工总数；
• strike_to_user_associations表中user_id与该用户一致的strike_id数组；
• 额外满足strikes.duration @> now()（当前活跃）条件的strike_id数组。

当前查询返回结果：

+-----+-----+-----+
|count|aa   |ab   |
+-----+-----+-----+
|1    |{176}|{176}|
|3    |{176}|null |
+-----+-----+-----+

结果被拆分为两行，分别统计活跃和非活跃罢工。

期望返回单行结果：

+-----+-----+-----+
|count|aa   |ab   |
+-----+-----+-----+
|4    |{176}|{176}|
+-----+-----+-----+

问题分析 #

原查询的核心问题：

1. LATERAL子查询会针对strikes表的每一行记录单独聚合中间表数据，导致每条罢工记录对应一组aa和ab值；
2. 后续按sub.aa, sub.ab分组时，活跃罢工的ab为非空数组，非活跃罢工的ab为null，因此被拆分为两个分组。

修正后的查询 #

select 
    count(s.id) as total_strikes,
    array_agg(distinct sua.strike_id) as aa,
    array_agg(distinct sua.strike_id) filter (where s.duration @> now()) as ab
from strikes s
left join strike_to_user_associations sua 
    on sua.user_id = s.created_by_id
where s.created_by_id = 34
group by s.created_by_id;

逻辑说明 #

1. 直接关联strikes和中间表，避免逐行聚合的问题；
2. count(s.id)准确统计用户创建的所有罢工总数；
3. array_agg(distinct sua.strike_id)确保数组中无重复的strike_id（多对多关联可能产生重复行）；
4. filter子句仅筛选活跃罢工记录进行聚合，生成目标数组；
5. 按created_by_id分组，确保最终返回单行统计结果。

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