如何在SQL多表连接分组聚合后基于求和列计算百分比

我查阅了多个与需求相近的案例，但这些案例均未涉及多表连接或多列分组求和的场景。需求为连接两张表，按日期、物业字段分组，对另外两列的值分别求和，且需在分组求和完成后，基于这两个求和得到的列计算百分比。

• 示例数据：
• 现有报错SQL：

SELECT US.DT_Uploaded, PL.AH_Property,  
SUM(CAST(US.Vacant_Unrented_Ready AS int)) AS Vacant_Unrented_Ready, 
SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)) AS Vacant_Unrented_Not_Ready, 
FORMAT(CAST(US.Vacant_Unrented_Ready AS decimal) / NULLIF (CAST(US.Vacant_Unrented_Not_Ready AS int) + CAST(US.Vacant_Unrented_Ready AS int), 0), 'P') AS Perc_Tot_Vac_Ready 
FROM dbo.AH_Unit_Availability_Summary US 
LEFT OUTER JOIN dbo.AH_Property_Name_Link PL ON US.YD_Property = PL.YD_Property 
GROUP BY US.DT_Uploaded, PL.AH_Property

报错原因：计算百分比时直接引用了未包含在GROUP BY子句中的原始表字段US.Vacant_Unrented_Ready和US.Vacant_Unrented_Not_Ready，分组后非分组字段不能直接引用原始值，只能使用聚合计算后的结果。

• 期望最终结果：

最简单的实现方式是直接在百分比计算逻辑中复用两个字段的SUM聚合结果，无需额外嵌套子查询或CTE，修改后的SQL如下：

SELECT 
    US.DT_Uploaded, 
    PL.AH_Property,  
    SUM(CAST(US.Vacant_Unrented_Ready AS int)) AS Vacant_Unrented_Ready, 
    SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)) AS Vacant_Unrented_Not_Ready, 
    -- 直接使用聚合后的SUM结果计算百分比，无需引用原始表字段
    FORMAT(
        SUM(CAST(US.Vacant_Unrented_Ready AS int)) * 1.0 
        / NULLIF(SUM(CAST(US.Vacant_Unrented_Ready AS int)) + SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)), 0), 
        'P'
    ) AS Perc_Tot_Vac_Ready 
FROM dbo.AH_Unit_Availability_Summary US 
LEFT OUTER JOIN dbo.AH_Property_Name_Link PL ON US.YD_Property = PL.YD_Property 
GROUP BY US.DT_Uploaded, PL.AH_Property

补充说明 #

• 数据库优化器会自动识别重复的聚合逻辑，只会执行一次求和计算，不会额外消耗性能
• 如果更看重代码可读性，也可以用CTE包装聚合结果后再计算百分比，写法如下：

WITH agg_result AS (
    SELECT 
        US.DT_Uploaded, 
        PL.AH_Property,  
        SUM(CAST(US.Vacant_Unrented_Ready AS int)) AS Vacant_Unrented_Ready, 
        SUM(CAST(US.Vacant_Unrented_Not_Ready AS int)) AS Vacant_Unrented_Not_Ready
    FROM dbo.AH_Unit_Availability_Summary US 
    LEFT OUTER JOIN dbo.AH_Property_Name_Link PL ON US.YD_Property = PL.YD_Property 
    GROUP BY US.DT_Uploaded, PL.AH_Property
)
SELECT 
    *,
    FORMAT(
        Vacant_Unrented_Ready * 1.0 / NULLIF(Vacant_Unrented_Ready + Vacant_Unrented_Not_Ready, 0), 
        'P'
    ) AS Perc_Tot_Vac_Ready
FROM agg_result

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