SQL主/子查询筛选求和实现及分组统计结果缺失问题排查

需要对所有日志进行求和统计，每条日志包含facility_id、card_id和登录日期，通过关联logs表与members表计算年龄和性别，最终按性别年龄分组展示各设施的统计数据（含gender_age_category、facility_id、total、general、course、club等字段）。

当前查询存在错误：同一card_id对应不同facility_id的两条日志未被统计，导致gender_age_category中缺少对应记录。关联后的原始数据存在同一tc_no（即card_id）对应多个facility_id的记录，且每条记录包含会员性别、出生日期及各类型日志统计数。

当前使用的SQL语句：

SELECT
    ROW_NUMBER() OVER () AS id, 
    CASE 
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18' 
        END AS gender_age_category,
    mlcs.facility_id,
    COALESCE(SUM(mlc.total),0) AS total,
    COALESCE(SUM(mlc.general), 0) AS general,
    COALESCE(SUM(mlc.course), 0) AS course, 
    COALESCE(SUM(mlc.club), 0) AS club 
FROM
    members m
LEFT JOIN
    total_member_log_counts mlcs ON mlcs.tc_no = m.tc_no
LEFT JOIN
    (
     SELECT
         tc_no, 
         facility_id,
         COALESCE(SUM(total),0) AS total,
         COALESCE(SUM(general), 0) AS general,
         COALESCE(SUM(course), 0) AS course,
         COALESCE(SUM(club), 0) AS club 
     FROM 
         total_member_log_counts
     GROUP BY
         tc_no, facility_id
    ) as mlc ON mlc.tc_no = m.tc_no
GROUP BY
    gender_age_category,
    mlcs.facility_id
ORDER BY gender_age_category ASC;

1. 冗余且错误的关联逻辑：同时左连接原表total_member_log_counts（mlcs）和它的分组子查询mlc，但仅通过tc_no关联，未匹配facility_id，导致同一tc_no下的不同设施记录被错误聚合，部分数据遗漏。
2. 分组依据混乱：用未分组的mlcs.facility_id作为分组字段，结合冗余连接，无法按tc_no+facility_id的正确维度关联会员信息，分组逻辑失效。
3. 不必要的多表连接：子查询mlc已经按tc_no和facility_id完成统计，主查询无需再连接原total_member_log_counts表，重复连接反而干扰数据匹配。

核心思路：先按tc_no和facility_id统计各设施的日志数据，再关联members表计算性别年龄分组，最后按gender_age_category和facility_id汇总。

SELECT
    ROW_NUMBER() OVER () AS id,
    CASE 
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'
        WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18' 
        END AS gender_age_category,
    mlc.facility_id,
    COALESCE(SUM(mlc.total), 0) AS total,
    COALESCE(SUM(mlc.general), 0) AS general,
    COALESCE(SUM(mlc.course), 0) AS course,
    COALESCE(SUM(mlc.club), 0) AS club
FROM
    members m
LEFT JOIN (
    SELECT
        tc_no,
        facility_id,
        COALESCE(SUM(total), 0) AS total,
        COALESCE(SUM(general), 0) AS general,
        COALESCE(SUM(course), 0) AS course,
        COALESCE(SUM(club), 0) AS club
    FROM
        total_member_log_counts
    GROUP BY
        tc_no, facility_id
) mlc ON m.tc_no = mlc.tc_no
GROUP BY
    gender_age_category,
    mlc.facility_id
ORDER BY
    gender_age_category ASC;

如果需要展示所有性别年龄分组（即使分组下无对应设施数据），可生成分组与设施的笛卡尔积后左连接统计数据：

WITH all_categories AS (
    SELECT unnest(ARRAY['f>17', 'm>17', 'f<18', 'm<18']) AS gender_age_category
),
all_facilities AS (
    SELECT DISTINCT facility_id FROM total_member_log_counts
),
member_stats AS (
    SELECT
        CASE 
            WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'female' THEN 'f>17'
            WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) >= 18 AND m.gender = 'male' THEN 'm>17'
            WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'female' THEN 'f<18'
            WHEN EXTRACT(YEAR FROM age(current_date, m.birthdate)) < 18 AND m.gender = 'male' THEN 'm<18' 
            END AS gender_age_category,
        mlc.facility_id,
        COALESCE(SUM(mlc.total), 0) AS total,
        COALESCE(SUM(mlc.general), 0) AS general,
        COALESCE(SUM(mlc.course), 0) AS course,
        COALESCE(SUM(mlc.club), 0) AS club
    FROM
        members m
    LEFT JOIN (
        SELECT
            tc_no,
            facility_id,
            COALESCE(SUM(total), 0) AS total,
            COALESCE(SUM(general), 0) AS general,
            COALESCE(SUM(course), 0) AS course,
            COALESCE(SUM(club), 0) AS club
        FROM
            total_member_log_counts
        GROUP BY
            tc_no, facility_id
    ) mlc ON m.tc_no = mlc.tc_no
    GROUP BY
        gender_age_category,
        mlc.facility_id
)
SELECT
    ROW_NUMBER() OVER () AS id,
    ac.gender_age_category,
    af.facility_id,
    COALESCE(ms.total, 0) AS total,
    COALESCE(ms.general, 0) AS general,
    COALESCE(ms.course, 0) AS course,
    COALESCE(ms.club, 0) AS club
FROM
    all_categories ac
CROSS JOIN all_facilities af
LEFT JOIN member_stats ms 
    ON ac.gender_age_category = ms.gender_age_category 
    AND af.facility_id = ms.facility_id
ORDER BY
    ac.gender_age_category ASC;

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