SQL按经理、月末分组统计累计值及环比变化的实现问题

问题原因 #

1. 缺失ManagerId=2的2月记录：原查询基于ClientTable中实际存在的(ManagerId, 月末)组合分组，ClientTable中ManagerId=2在2021年2月没有对应数据，因此分组结果缺失该行。
2. 无法计算环比：未使用窗口函数提取同Manager维度下上月的累计值，无法直接计算差值和变动率。

调整后查询语句 #

WITH
-- 提取业务涉及的所有月末日期
AllDates AS (
    SELECT EOMONTH([Date]) AS EOMDate FROM ClientTable
    UNION
    SELECT EOMONTH(ValueDate) AS EOMDate FROM ClientValues
),
-- 提取所有存在的ManagerId
AllManagers AS (
    SELECT ManagerId FROM ClientTable
    UNION
    SELECT ManagerId FROM ClientValues
),
-- 生成完整的「月末+ManagerId」分组，解决缺失行问题
FullGroups AS (
    SELECT d.EOMDate, m.ManagerId
    FROM AllDates d
    CROSS JOIN AllManagers m
),
-- 预计算每个分组的累计基础指标，避免重复子查询
BaseMetrics AS (
    SELECT
        fg.EOMDate,
        fg.ManagerId,
        -- 累计主管数量
        (SELECT COUNT(DISTINCT SupervisorId) FROM ClientTable ct WHERE ct.ManagerId = fg.ManagerId AND EOMONTH(ct.[Date]) <= fg.EOMDate) AS Supervisors,
        -- 累计客户数量
        (SELECT COUNT(DISTINCT ClientId) FROM ClientTable ct WHERE ct.ManagerId = fg.ManagerId AND EOMONTH(ct.[Date]) <= fg.EOMDate) AS Clients,
        -- 累计Value值，可根据实际统计逻辑调整子查询
        (SELECT ISNULL(SUM(Value), 0) FROM ClientValues cv WHERE cv.ManagerId = fg.ManagerId AND EOMONTH(cv.ValueDate) <= fg.EOMDate) AS Value
    FROM FullGroups fg
)
-- 计算环比指标
SELECT
    EOMDate,
    ManagerId,
    Supervisors,
    Clients,
    Value,
    Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EOMDate) AS ChangeValue,
    CASE
        WHEN LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EOMDate) = 0 THEN NULL
        ELSE FORMAT((Value - LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EOMDate)) * 100.0 / LAG(Value) OVER (PARTITION BY ManagerId ORDER BY EOMDate), '0%')
    END AS ChgPerc
FROM BaseMetrics
ORDER BY EOMDate, ManagerId

补充说明 #

如果你的Value累计逻辑需要和ClientTable的客户准入规则关联，仅统计已录入客户的Value，可以调整BaseMetrics中Value字段的子查询逻辑，增加ClientTable关联过滤即可。

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