SQL Server中考虑分支合并的增长率计算问题排查

场景说明 #

在SQL Server中有两张表：

• MyData：存储不同日期各分支的数值数据
• MyMerge：记录分支合并关系，OldBranch对应合并后的MergedIn分支

需求是计算合并后分支的增长率，例如A分支的增长率应为(110-(50+40))/(50+40)=0.22，但现有SQL语句计算结果不符合预期，需要修正。

表结构与示例数据 #

MyData表 #

MyMerge表 #

预期输出 #

原SQL问题分析 #

原SQL代码如下：

SELECT m.MergedIn as MergedIn, (sum(b.Value)-sum(a.Value))/sum(a.Value) as Growth
From MyMerge as m
Inner join MyData as a on a.branch=m.OldBranch
Inner join MyData as b on b.branch=m.OldBranch
Where a.date=20220701 and b.date=20230501
Group by m.MergedIn

问题在于：使用两次INNER JOIN关联MyData时，当某个OldBranch在目标新日期无数据（如B分支20230501无记录），该行会被过滤，导致旧日期的总和仅计算了有新日期数据的分支（仅A分支的50），最终增长率计算错误。

修正后的SQL代码 #

方案一：使用CASE语句分组统计 #

SELECT 
    MergedIn,
    ROUND((NewValue - OldValue) / CAST(OldValue AS FLOAT), 2) AS Growth
FROM (
    SELECT 
        m.MergedIn,
        SUM(CASE WHEN d.Date = 20220701 THEN d.Value ELSE 0 END) AS OldValue,
        SUM(CASE WHEN d.Date = 20230501 THEN d.Value ELSE 0 END) AS NewValue
    FROM MyMerge m
    LEFT JOIN MyData d ON d.Branch = m.OldBranch
    WHERE d.Date IN (20220701, 20230501)
    GROUP BY m.MergedIn
) t
WHERE OldValue > 0 -- 避免除数为0

方案二：分日期统计后关联 #

SELECT 
    o.MergedIn,
    ROUND((n.TotalValue - o.TotalValue) / CAST(o.TotalValue AS FLOAT), 2) AS Growth
FROM (
    -- 统计旧日期合并分支总数值
    SELECT m.MergedIn, SUM(d.Value) AS TotalValue
    FROM MyMerge m
    JOIN MyData d ON m.OldBranch = d.Branch
    WHERE d.Date = 20220701
    GROUP BY m.MergedIn
) o
JOIN (
    -- 统计新日期合并分支总数值
    SELECT m.MergedIn, SUM(d.Value) AS TotalValue
    FROM MyMerge m
    JOIN MyData d ON m.OldBranch = d.Branch
    WHERE d.Date = 20230501
    GROUP BY m.MergedIn
) n ON o.MergedIn = n.MergedIn

修正说明 #

1. 确保合并分支数据完整统计：通过分组统计或分日期子查询，确保旧日期所有合并分支的数值都被计入总和，不会因新日期无数据而被过滤。
2. 处理整数除法：将OldValue转换为FLOAT类型，避免SQL Server中整数除法导致的精度丢失。
3. 格式化输出：使用ROUND函数保留两位小数，匹配预期输出格式。

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