受限条件下如何统计患者就诊次数并筛选超均值记录

嘿，这个需求其实用SQL的窗口函数就能轻松搞定，完美避开你提到的限制——不需要预计算平均值，也不用在WHERE/HAVING里直接做聚合值的比较。下面给你详细拆解：

核心思路 #

利用AVG() OVER()窗口函数，它可以在统计完每个患者的就诊次数后，直接计算所有患者就诊次数的全局平均值，并把这个平均值附加到每一行结果中。之后我们只需要在外层查询里过滤出就诊次数高于这个平均值的患者即可。

完整SQL示例 #

假设你的患者表是patients（包含patient_id、patient_name等字段），就诊记录表是visits（包含visit_id、patient_id等字段），代码如下：

SELECT patient_id, patient_name, visit_count
FROM (
    SELECT 
        p.patient_id,
        p.patient_name,
        COUNT(v.visit_id) AS visit_count,
        -- 计算所有患者就诊次数的全局平均值
        AVG(COUNT(v.visit_id)) OVER() AS overall_avg_visits
    FROM patients p
    -- LEFT JOIN 确保未就诊的患者也被统计（visit_count为0）
    LEFT JOIN visits v ON p.patient_id = v.patient_id
    GROUP BY p.patient_id, p.patient_name
) patient_stats
WHERE visit_count > overall_avg_visits;

为什么这能行？ #

• LEFT JOIN 处理未就诊患者：确保10名患者全部被纳入统计，没就诊的患者visit_count会被计算为0，不会被遗漏。
• 窗口函数计算全局平均值：AVG(COUNT(v.visit_id)) OVER()会先完成每个患者的就诊次数统计（GROUP BY后的聚合），再基于整个结果集计算平均值，这个平均值会被添加到每一行中。
• 外层过滤避开限制：因为平均值已经作为普通列存在于子查询结果里，外层的WHERE子句可以直接比较visit_count和overall_avg_visits，完全符合你的要求。

可选写法：用CTE让代码更清晰 #

如果你觉得嵌套子查询可读性差，也可以用公共表表达式（CTE）来拆分逻辑：

WITH patient_visit_counts AS (
    SELECT 
        p.patient_id,
        p.patient_name,
        COUNT(v.visit_id) AS visit_count
    FROM patients p
    LEFT JOIN visits v ON p.patient_id = v.patient_id
    GROUP BY p.patient_id, p.patient_name
)
SELECT patient_id, patient_name, visit_count
FROM (
    SELECT 
        *,
        AVG(visit_count) OVER() AS overall_avg_visits
    FROM patient_visit_counts
) stats
WHERE visit_count > overall_avg_visits;

内容的提问来源于stack exchange，提问作者Hoang Minh Quan Le