SQL技术咨询：如何查询各Provider的每位患者平均Session数

原SQL的问题 #

• 语法错误：SQL不允许在同一级SELECT中嵌套使用聚合函数，avg(count(session_id)) 这种写法违反规则——count()作为聚合函数，不能直接作为avg()的参数在同一查询层级中使用。
• 逻辑冗余：需求是计算每个医生下每位病人的平均就诊次数，直接用总就诊次数除以该医生的唯一病人数即可，不需要嵌套聚合函数。

正确SQL写法 #

方法一：分步计算（贴合需求逻辑） #

先统计每个医生每个病人的就诊次数，再对这些次数求平均值：

SELECT 
    p.name,
    AVG(patient_session_count) AS avg_sessions
FROM providers p
JOIN (
    -- 子查询：统计每个医生每个病人的就诊次数
    SELECT 
        provider_id,
        patient_id,
        COUNT(session_id) AS patient_session_count
    FROM sessions
    GROUP BY provider_id, patient_id
) s ON p.id = s.provider_id
GROUP BY p.name

方法二：简洁等价写法 #

利用数学等价性，总就诊次数除以该医生的唯一病人数，结果与方法一完全一致：

SELECT 
    p.name,
    COUNT(s.session_id) / COUNT(DISTINCT s.patient_id) AS avg_sessions
FROM providers p
JOIN sessions s ON p.id = s.provider_id
GROUP BY p.name

扩展：保留无就诊记录的医生 #

如果需要展示所有医生（包括没有任何就诊记录的），可以使用LEFT JOIN并处理空值，避免除以0的错误：

SELECT 
    p.name,
    COALESCE(COUNT(s.session_id) / NULLIF(COUNT(DISTINCT s.patient_id), 0), 0) AS avg_sessions
FROM providers p
LEFT JOIN sessions s ON p.id = s.provider_id
GROUP BY p.name

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