基于cid记录数的SQL条件聚合：动态计算平均值需求

这是个挺典型的条件统计需求，核心就是根据每个cid的记录数量，来决定每条记录要展示的平均值类型。下面给你两种可行的查询方案，你可以根据自己的SQL环境来选择：

方案一：用窗口函数实现（推荐，简洁高效） #

窗口函数能一次性计算出分组统计和全局统计，不用多次关联表，性能和可读性都更好。核心思路是：

• 用COUNT(*) OVER(PARTITION BY cid)算出每个cid对应的记录数
• 用AVG(val) OVER(PARTITION BY cid)得到当前cid分组的val平均值
• 用AVG(val) OVER()得到全表所有记录的val平均值
• 最后通过CASE语句根据记录数选择对应的平均值

完整SQL代码：

declare @tbl table(id int, cid int, val float )
insert into @tbl values(1,100,20),(2,100,30),(3,100,25),(4,100,31),(5,100,50), (6,200,30),(7,200,30),(8,300,90)

SELECT 
    id,
    cid,
    val,
    CASE 
        WHEN COUNT(*) OVER(PARTITION BY cid) > 3 THEN AVG(val) OVER(PARTITION BY cid)
        ELSE AVG(val) OVER()
    END AS avg_val
FROM @tbl

方案二：用子查询关联实现（兼容老版本SQL） #

如果你的SQL环境不支持窗口函数（比如一些旧版数据库），可以先通过子查询统计好分组信息和全局信息，再和原表关联：

完整SQL代码：

declare @tbl table(id int, cid int, val float )
insert into @tbl values(1,100,20),(2,100,30),(3,100,25),(4,100,31),(5,100,50), (6,200,30),(7,200,30),(8,300,90)

-- 先统计每个cid的记录数和分组平均值
WITH cid_stats AS (
    SELECT 
        cid,
        COUNT(*) AS cid_count,
        AVG(val) AS cid_avg
    FROM @tbl
    GROUP BY cid
),
-- 统计全表的平均值
global_stats AS (
    SELECT AVG(val) AS global_avg FROM @tbl
)
SELECT 
    t.id,
    t.cid,
    t.val,
    CASE 
        WHEN cs.cid_count > 3 THEN cs.cid_avg
        ELSE gs.global_avg
    END AS avg_val
FROM @tbl t
JOIN cid_stats cs ON t.cid = cs.cid
CROSS JOIN global_stats gs

结果验证 #

执行后你会看到：

• cid=100有5条记录（>3），每条的avg_val都是该组的平均值：(20+30+25+31+50)/5 = 31.2
• cid=200有2条记录（<3）、cid=300有1条记录（<3），它们的avg_val都是全表平均值：(20+30+25+31+50+30+30+90)/8 = 35.75

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