SQL中如何实现带可变WHERE条件的列均值计算并求解满足阈值的nFirst

原查询的核心错误 #

你写的语句跑不通，本质是三个逻辑偏差：

1. 聚合函数不能直接写在WHERE里：WHERE执行在聚合操作之前，只能做逐行过滤，要过滤聚合结果得用HAVING，但你这里的分组逻辑本身就不对。
2. GROUP BY id完全不符合需求：加了这个分组后，统计的是每个单独id对应的value均值，不是你要的「所有id小于nFirst的行的整体平均值」。
3. 返回字段错了：你最终要找的是符合条件的nFirst值，不是均值本身。

正确实现方案 #

核心逻辑是先算出每个候选n对应的前缀均值：也就是把每个id作为候选nFirst时，所有id比它小的行的value平均值，再从中挑出满足均值大于阈值的最小id，就是你要的结果。

支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server、SQLite等）

用窗口函数计算累计值效率最高，把下面的@your_threshold替换成你实际的阈值即可：

WITH prefix_calc AS (
    SELECT
        id,
        -- 计算所有排在当前id之前的行的value均值，也就是当前id作为nFirst时的统计值
        AVG(value) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS avg_before
    FROM Data
)
SELECT MIN(id) AS nFirst
FROM prefix_calc
WHERE avg_before > @your_threshold;

如果所有候选n对应的前缀均值都没达到阈值，查询会返回NULL，你可以根据业务需要做兜底处理。

兼容不支持窗口函数的旧版MySQL

如果是MySQL5.x这类老环境，可以用自连接实现同样的逻辑：

SELECT MIN(d1.id) AS nFirst
FROM Data d1
LEFT JOIN Data d2 ON d2.id < d1.id
GROUP BY d1.id
HAVING AVG(d2.value) > @your_threshold;

逻辑验证示例 #

比如id为1、2、3对应的value分别是4、8、6，阈值设为5：

• nFirst=2时，id<2的只有id=1，均值为4，小于阈值，不符合
• nFirst=3时，id<3的是id=1、2，均值为(4+8)/2=6，大于阈值，符合
• 查询最终返回3，和预期结果一致。

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