LEFT JOIN多表关联下waves数量统计的SQL问题解决与优化

需要在单条SQL语句中统计指定event关联的waves（通过LEFT OUTER JOIN）与invitations的相关统计数据。已知一个event对应多个waves和多个invitations，尝试两种直接关联方案均出现统计异常：

• 方案1返回的是invitations的数量而非waves数量；
• 方案2添加DISTINCT后始终返回1；
  采用子查询的方案3可正常运行，但存在GROUP BY与LEFT OUTER JOIN选列的依赖顾虑；最终结合WITH子句与LEFT JOIN得到最优SQL版本。

相关表结构 #

CREATE TABLE public.events (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL,
    name character varying NOT NULL
);
CREATE TABLE public.invitations (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL,
    event_id bigint NOT NULL
);
CREATE TABLE public.invitation_waves (
    id bigint NOT NULL,
    uuid uuid DEFAULT public.gen_random_uuid() NOT NULL,
    name character varying NOT NULL,
    wavable_type character varying,
    wavable_id bigint,
    scheduled_at timestamp(6) without time zone
);

尝试的三种方案及问题分析 #

方案1 #

SELECT CAST(sum((case when (waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER) as total_waves
FROM "events"
         LEFT OUTER JOIN "waves" ON "waves"."wavable_type" = 'Event' AND
                                               "waves"."wavable_id" = "events"."id"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
WHERE "events"."uuid" = 'XXX'
GROUP BY "events"."id"

异常原因：同时LEFT JOIN waves和invitations会产生笛卡尔积，每个wave记录会被重复计算，重复次数等于该event对应的invitations数量，最终统计结果变成了invitations的数量。

方案2（含DISTINCT） #

SELECT CAST(sum(DISTINCT(case when (waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER) as total_waves
FROM "events"
         LEFT OUTER JOIN "waves" ON "waves"."wavable_type" = 'Event' AND
                                               "waves"."wavable_id" = "events"."id"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
WHERE "events"."uuid" = 'XXX'
GROUP BY "events"."id"

异常原因：DISTINCT作用在case表达式的结果上，结果只有0或1两种值，sum之后最多返回1，无法统计实际的waves数量。

方案3（子查询）✅ #

SELECT CAST(sum((case
                     when (invitations.status = 4 or invitations.status = 5 or invitations.status = 6) then 1
                     else 0 end)) AS INTEGER) as total_validated,
       total_waves,
       waves_scheduled
FROM "events"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
         LEFT OUTER JOIN (SELECT distinct on (wavable_id)
                                 wavable_id                                                                                      as event_id,
                                 CAST(sum((case when (invitation_waves.id IS NOT NULL) then 1 else 0 end)) AS INTEGER)           as total_waves,
                                 CAST(sum((case when (invitation_waves.scheduled_at IS NOT NULL) then 1 else 0 end)) AS INTEGER) as waves_scheduled
                          FROM invitation_waves
                          WHERE wavable_type = 'Event'
                          GROUP BY 1 
                          ORDER BY wavable_id) as "wave_stats" on "wave_stats"."event_id" = "events"."id"
WHERE "events"."uuid" = '1ee5ec72-6f3c-404c-871c-5c5724f6a1ed'
GROUP BY "events"."id", total_waves, waves_scheduled

说明：通过子查询提前计算每个event的waves统计数据，避免了笛卡尔积问题，能得到正确结果。但GROUP BY子句需要包含统计字段，在部分SQL严格模式下可能引发兼容性问题。

WITH "wave_stats" AS (SELECT wavable_id                                                 as event_id,
                             COUNT(DISTINCT id)                                         AS total_waves,
                             COUNT(DISTINCT id) FILTER (WHERE scheduled_at IS NOT NULL) AS waves_scheduled
                      FROM "invitation_waves"
                      WHERE "invitation_waves"."wavable_type" = 'Event'
                      GROUP BY "invitation_waves"."wavable_id")
SELECT events.*,
       CAST(COUNT(invitations.id) AS INTEGER) AS total_invitations, 
       CAST(SUM((CASE
                    WHEN (invitations.status = 4 OR invitations.status = 5 OR invitations.status = 6) THEN 1
                    ELSE 0 END)) AS INTEGER) AS total_validated, 
       COALESCE(wave_stats.waves_scheduled, 0) AS waves_scheduled,
       COALESCE(wave_stats.total_waves, 0) AS total_waves
FROM "events"
         LEFT OUTER JOIN "invitations" ON "invitations"."event_id" = "events"."id"
         LEFT JOIN wave_stats ON wave_stats.event_id = events.id
GROUP BY "events"."id", wave_stats.waves_scheduled, wave_stats.total_waves

优化点：

1. 使用WITH子句（CTE）提前计算waves统计数据，代码结构更清晰易维护；
2. 用COALESCE替代CASE表达式，简化空值转0的处理逻辑；
3. 修正原代码中表名笔误（对应表结构将waves改为invitation_waves）；
4. 明确GROUP BY字段，避免潜在的选列依赖问题。

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