在T-SQL中高效选择GROUP BY分组变量的函数依赖列

背景 #

• 仅能编写CREATE VIEW My_View AS ...中的查询主体，使用SQL Server 2019 (RTM-CU19)
• 所有表由扁平文件同步而来，函数依赖关系（如Name完全依赖Person_ID）可通过业务逻辑和列名可靠推断

问题 #

对表My_Measures按Person_ID聚合Measure字段（求最小、最大、平均值）时，需同时返回Name字段，但直接SELECTName会因未加入GROUP BY或聚合函数触发语法错误。

现有方案缺陷 #

• GROUP BY包含依赖列：将Name加入GROUP BY虽能得到结果，但误以为会浪费性能
• 聚合依赖列：用MIN(Name)等聚合函数取值，仅支持可比较数据类型，且额外消耗计算资源
• 聚合后JOIN：先聚合再关联去重后的Person_ID与Name，需额外JOIN或CTE，增加资源开销
• FIRST_VALUE()+PARTITION：虽能实现需求，但多依赖列时性能表现不稳定，代码冗余

最优解决方案 #

直接将Person_ID和Name同时加入GROUP BY，代码如下：

SELECT 
    Person_ID,
    Name,
    MIN(Measure) AS Min_Measure,
    MAX(Measure) AS Max_Measure,
    AVG(CAST(Measure AS DECIMAL(18,2))) AS Avg_Measure -- 按需转换数据类型
FROM My_Measures
GROUP BY Person_ID, Name

方案优势

1. 性能最优：SQL Server查询优化器可识别Name对Person_ID的函数依赖（即使表无显式唯一约束，只要实际数据中每个Person_ID对应唯一Name），会自动将分组逻辑优化为仅按Person_ID执行，不会额外增加分组开销
2. 兼容性强：支持所有数据类型（包括XML、地理类型等不可比较类型），无需依赖特定聚合函数
3. 代码简洁：语法符合T-SQL规范，可读性高，扩展方便（新增其他依赖列时只需添加到SELECT和GROUP BY中即可）
4. 符合约束：完全适配仅能编写VIEW主体的受限环境，无需额外对象（如CTE、JOIN、窗口函数）

原理说明

当Name函数依赖于Person_ID时，每个Person_ID对应的Name值唯一，GROUP BY Person_ID, Name的分组基数与GROUP BY Person_ID完全一致。SQL Server优化器会自动消除冗余的分组列，实际执行计划与仅按Person_ID分组无差异，不会产生额外性能损耗。

备选方案（特殊场景） #

若因特殊原因无法使用GROUP BY多列，可使用CROSS APPLY结合聚合结果，性能略逊但仍优于聚合后JOIN：

SELECT 
    agg.Person_ID,
    p.Name,
    agg.Min_Measure,
    agg.Max_Measure,
    agg.Avg_Measure
FROM (
    SELECT 
        Person_ID,
        MIN(Measure) AS Min_Measure,
        MAX(Measure) AS Max_Measure,
        AVG(CAST(Measure AS DECIMAL(18,2))) AS Avg_Measure
    FROM My_Measures
    GROUP BY Person_ID
) agg
CROSS APPLY (
    SELECT TOP 1 Name
    FROM My_Measures m
    WHERE m.Person_ID = agg.Person_ID
) p

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