SQL JOIN后如何准确计算month_joined计数并保留预约信息？

问题背景 #

现有两张业务表：

Patients表 #

ID   | month_joined
--------------------
A110 | jan 2013 
A111 | feb 2013
A112 | april 2013

Appointments表 #

ID   | month_of_appt | number_of_appts
--------------------------------------
A110 | jan 2013      |       2
A110 | feb 2013      |       1
A111 | april 2013    |       3
A112 | dec 2013      |       1

需求为：统计每月新增患者数（基于Patients表month_joined字段计数），同时获取每月预约数据（可选择月度汇总或单条预约明细）。直接使用LEFT JOIN会出现问题：存在多条预约记录的患者，其month_joined字段会重复出现，导致统计该字段的计数结果偏大（比如A110的jan 2013会被统计两次，但实际该月份仅新增1位患者）。

解决方案 #

方案1：按月份聚合后关联（获取月度汇总数据） #

先分别对两张表做月度聚合，再关联所有有数据的月份（用FULL JOIN避免遗漏任何有数据的月份）：

-- 统计每月新增患者数
WITH monthly_patients AS (
    SELECT month_joined AS month, COUNT(*) AS new_patients
    FROM Patients
    GROUP BY month_joined
),
-- 统计每月预约总数
monthly_appointments AS (
    SELECT month_of_appt AS month, SUM(number_of_appts) AS total_appointments
    FROM Appointments
    GROUP BY month_of_appt
)
-- 关联两个统计结果，补全缺失值
SELECT 
    COALESCE(p.month, a.month) AS month,
    COALESCE(p.new_patients, 0) AS new_patients,
    COALESCE(a.total_appointments, 0) AS total_appointments
FROM monthly_patients p
FULL JOIN monthly_appointments a ON p.month = a.month
ORDER BY STR_TO_DATE(COALESCE(p.month, a.month), '%b %Y'); -- 按日期排序，不同数据库函数可能不同，比如PostgreSQL用TO_DATE

该方案输出每个月份的新增患者数和预约总数，例如jan 2013对应1位新增患者、2次预约；feb 2013对应1位新增患者、1次预约。

方案2：保留单条预约记录，同时显示对应加入月份的新增数 #

如果需要保留每条预约的详细信息，同时显示该患者加入月份的总新增患者数，可采用以下两种方式：

方法A：子查询实现

SELECT 
    a.ID,
    p.month_joined,
    a.month_of_appt,
    a.number_of_appts,
    -- 子查询获取该加入月份的新增患者总数
    (SELECT COUNT(*) FROM Patients WHERE month_joined = p.month_joined) AS new_patients_in_join_month
FROM Appointments a
LEFT JOIN Patients p ON a.ID = p.ID;

方法B：窗口函数实现（更高效）

WITH patient_join_stats AS (
    SELECT 
        ID,
        month_joined,
        -- 按month_joined分组统计新增患者数
        COUNT(*) OVER (PARTITION BY month_joined) AS new_patients_in_join_month
    FROM Patients
)
SELECT 
    a.ID,
    p.month_joined,
    a.month_of_appt,
    a.number_of_appts,
    p.new_patients_in_join_month
FROM Appointments a
LEFT JOIN patient_join_stats p ON a.ID = p.ID;

该方案会保留每条预约记录，同时正确显示对应患者加入月份的新增患者数，例如A110的两条预约记录中，new_patients_in_join_month都会显示1（因为jan 2013仅新增1位患者）。

关键说明 #

直接LEFT JOIN导致统计失真的核心原因是：关联后单条患者记录会被其所有预约记录复制，此时直接统计month_joined的COUNT会把重复行算入。通过先聚合患者数据，或用窗口函数在原始患者表中预计算分组统计数，即可避免重复统计的问题。

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