基于唯一ID计算平均值且保留全表数据的SQL实现方案（适配自动添加的WHERE过滤）

看起来你碰到了一个典型的聚合与去重结合的问题，而且还要兼容动态的WHERE过滤——这个需求在报表类场景里很常见。你之前用LAG的思路方向是对的，但因为SQL执行顺序的限制，没法直接在聚合函数里嵌套窗口函数，我给你两个可行的解决方案：

方案1：子查询先去重，再聚合计算 #

从你的示例数据来看，同一个year+id组合对应的value是完全相同的，所以我们可以先通过子查询对每个year和id保留唯一的value，再按year计算平均值。这个方案简单直接，而且完美兼容WHERE子句存在或不存在的场景：

SELECT 
    year,
    AVG(value) AS avg_value
FROM (
    -- 子查询：对过滤后的（或全表）数据，按year+id去重
    SELECT DISTINCT 
        year,
        id,
        value
    FROM your_table
    -- 这一行可由自动化软件动态添加或移除
    WHERE `group` = 'A' -- 注意：group是SQL关键字，建议用反引号包裹
) AS deduplicated_data
GROUP BY year;

测试验证你的场景： #

• 无过滤（Overall）：子查询保留所有year+id的唯一组合，2019年为(1,10),(2,20),(3,30)，平均值为(10+20+30)/3=20；2020年为(1,5),(2,10),(3,15)，平均值为10，符合预期。
• WHERE group='A'：子查询仅保留group为A的行，去重后的year+id组合和无过滤场景一致，所以平均值同样是20和10。
• WHERE group='B'：子查询仅保留group为B的行，2019年为(1,10),(2,20)，平均值为15；2020年为(1,5),(2,10)，平均值为7.5，完全匹配你的预期。

方案2：用窗口函数标记唯一ID，过滤后聚合 #

如果后续你的数据中，同一个year+id对应的value可能出现不同值，你可以用ROW_NUMBER()窗口函数标记每个id的首次出现，再过滤出唯一行后计算平均值。这个方案同样支持动态WHERE过滤：

SELECT 
    year,
    AVG(value) AS avg_value
FROM (
    SELECT 
        year,
        id,
        value,
        -- 按year分组，给每个id的行标记序号，这里order by可根据需求调整
        ROW_NUMBER() OVER (PARTITION BY year, id ORDER BY `group`) AS row_num
    FROM your_table
    -- 动态WHERE子句，可存在或不存在
    WHERE `group` = 'B'
) AS numbered_data
WHERE row_num = 1 -- 只保留每个id的第一行数据
GROUP BY year;

这个方案的结果和方案1在你的示例数据中完全一致，如果你需要指定保留哪一行（比如优先保留group为A的行），只需要调整ORDER BY的字段即可。

为什么你之前的LAG方案无法运行？ #

SQL的执行顺序是：FROM/WHERE → GROUP BY → 聚合函数 → 窗口函数。你尝试在AVG（聚合函数）中嵌套LAG（窗口函数），但窗口函数是在聚合之后才执行的，所以SQL引擎会报错。这也是为什么我们需要把去重或窗口标记的逻辑放到子查询中，先处理好重复的id，再进行聚合计算。

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