SQL Server中无需子查询实现分组聚合统计的更优方法

现有业务数据表包含TYPE、A、B、C四个字段，样例数据如下：

按TYPE字段分组统计，输出结果格式如下：

统计规则：

• 每组A字段值求和，作为结果的A列
• 取每组C字段最大值对应的B字段值，作为结果的D列
• 取每组C字段的最大值，作为结果的C列

当前使用嵌套子查询+窗口函数的写法实现需求，代码如下：

SELECT type, SUM(A) AS A, SUM(D) AS D, MAX(C) AS C
FROM
    (SELECT 
         type, A, B, C,
         CASE 
             WHEN C = MAX(C) OVER(PARTITION BY type) 
                 THEN B
                 ELSE 0
         END AS D
     FROM
         data) AS bbb
GROUP BY
    type

使用数据库为SQL Server，需要找不使用子查询的更优实现方案。

完全不需要子查询/CTE，仅通过单次分组聚合即可实现，利用字符串排序特性匹配最大C值对应的B值，代码如下：

SELECT
    TYPE,
    SUM(A) AS A,
    CAST(SUBSTRING(MAX(CONVERT(CHAR(10), C, 120) + CAST(B AS VARCHAR(20))), 11, 20) AS INT) AS D,
    MAX(C) AS C
FROM data
GROUP BY TYPE

写法说明 #

• 用CONVERT(CHAR(10), C, 120)把日期型C转为yyyy-MM-dd格式的定长字符串，该格式字符串的字典序和日期大小顺序完全一致，不会出现排序偏差
• 把转换后的定长日期字符串和B字段转为的字符串拼接，取拼接结果的最大值，自然就是最大C值对应的拼接串
• 用SUBSTRING截掉前10位的日期部分，剩下的就是对应B值，转回原数值类型即可
• 该写法仅需要对表做一次全表扫描+分组聚合，执行效率比嵌套子查询+窗口函数的写法更高
• 注意：如果单个TYPE分组下存在多条记录C值同为最大值，该写法会取这些记录中B值最大的那条；如果业务要求这种场景下对B求和，还是需要用子查询/窗口函数方案；如果业务上C在同组内唯一（比如C是精确到秒/毫秒的时间戳），该写法完全匹配需求。

内容的提问来源于stack exchange，提问作者陳冠儒