含Partition by的SQL查询因输入数据差异表现异常的原因解析

需求 #

选取每个foo分组中bar计数最高的行，若计数相同则选取id最大的行。

简化数据集 #

foo bar id
123 1   1
123 1   2
123 2   3
456 1   4
456 2   5

原始查询 #

with barCounter AS
(
    SELECT 
        foo, 
        bar, 
        COUNT(bar) AS barCount
    FROM @input it
    GROUP By foo, bar
)
SELECT 
    MAX(it.id) AS id, 
    foobar.foo
FROM @input it
INNER JOIN
    (
        SELECT DISTINCT 
            foo, 
            FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY barCount desc) AS bar, 
            max(barCount) OVER (PARTITION BY foo) AS barCount
        FROM barCounter
    ) foobar
    ON it.foo = foobar.foo AND it.bar = foobar.bar
Group BY foobar.foo, foobar.bar

不同输入下的结果差异 #

• 完整数据集返回结果：

id  foo
2   123
5   456

• 仅保留456相关数据时返回结果：

id foo
4  456

修复后的查询 #

-- with barCounter as () 与原始查询一致

SELECT DISTINCT
    MAX(it.id) OVER (PARTITION BY it.foo) AS id, 
    it.foo
FROM @input it
INNER JOIN
    (
        SELECT
            foo, 
            bar, 
            max(barCount) over (PARTITION BY foo) AS barCount
        FROM barCounter
    ) foobar
ON it.foo = foobar.foo AND it.bar = foobar.bar

一、原查询结果不一致的原因 #

核心问题出在子查询的FIRST_VALUE(bar) OVER (PARTITION BY foo ORDER BY barCount desc)逻辑：

• 当某个foo分组内存在多个bar的barCount等于最大值时（比如foo=456时，bar=1和bar=2的计数都是1），ORDER BY barCount desc没有指定后续排序规则，数据库的排序是不稳定的——它会根据底层存储的物理顺序或执行计划的临时排序结果返回第一个值。
• 在完整数据集里，foo=456的bar数据可能在排序时bar=2排在前面，FIRST_VALUE取到bar=2，关联后MAX(id)得到5；但单独保留456数据时，物理存储或执行计划排序结果变化，bar=1排在前面，FIRST_VALUE取到bar=1，关联后MAX(id)得到4，最终导致结果不一致。
• 子查询中的SELECT DISTINCT无法解决这个问题，因为当多个bar的barCount相同时，DISTINCT无法确定保留哪一行，最终还是依赖数据库的默认排序行为。

二、修复方案的生效原理 #

修复后的查询通过两个关键调整解决了问题：

1. 保留所有候选bar行：子查询去掉FIRST_VALUE和DISTINCT，改为给每个foo分组的所有bar标记出该组的最大barCount。这一步会把分组内所有计数等于最大值的bar都保留下来，不会因为排序问题丢失候选行。
2. 直接取分组内最大id：主查询使用MAX(it.id) OVER (PARTITION BY it.foo)，关联后会把当前foo分组内所有符合条件（bar计数等于最大值）的id纳入计算，直接取该分组的最大id，完美满足“计数相同则选id最大”的需求。最后用DISTINCT去重，得到每个foo对应的唯一结果行。

这种逻辑不受数据集变化或数据库排序行为的影响，能稳定返回符合需求的结果。

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