T-SQL中COUNT()配合PARTITION BY返回结果不一致问题咨询

问题原因 #

核心是SQL语句的执行优先级差异：GROUP BY 的执行顺序早于窗口函数，窗口函数统计的是分组去重后的结果集行数，而非原始表符合条件的行数。

执行顺序拆解 #

你提交的第一条查询的实际执行步骤如下：

1. 从valuation_basic表中筛选出isin = 'CH0012221716'的所有原始行，此时总数量和第二条单独COUNT语句的结果一致，为336行
2. 执行GROUP BY isin, v_year, growth_start，对上述336行按照三个字段的组合去重，重复行会被合并，最终得到245行分组后的结果
3. 执行窗口函数COUNT(v_year) OVER (PARTITION BY isin)，此时窗口函数的统计范围是上一步得到的245行分组结果，因此返回的n_count为245
4. 最后执行排序返回结果

验证方法 #

你可以执行下面的语句，返回结果会和n_count完全一致：

SELECT COUNT(*) 
FROM (
    SELECT [isin],v_year,growth_start 
    FROM valuation_basic 
    WHERE isin = 'CH0012221716' 
    GROUP BY isin, v_year, growth_start
) t

解决方案 #

如果你需要n_count返回原始表的统计值，可以将原始计数逻辑放到子查询中关联，参考写法：

SELECT 
    a.[isin],
    a.v_year,
    a.growth_start,
    b.n_count,
    MAX(a.growth_start) OVER (PARTITION BY a.isin) as max_growth_start
FROM (
    SELECT [isin],v_year,growth_start
    FROM valuation_basic
    WHERE isin = 'CH0012221716'
    GROUP BY isin, v_year, growth_start
) a
CROSS JOIN (
    SELECT count(v_year) as n_count 
    FROM valuation_basic 
    WHERE isin = 'CH0012221716'
) b
ORDER BY a.v_year

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