SQL查询两个日期间特定type对应amount变更用户及带符号差值

核心思路 #

原语句的两个问题根源是直接对两个日期的混合数据做MAX/MIN聚合，既无法区分日期先后得到带符号的差值，也无法规避同日内多记录的干扰。解决逻辑如下：

• 先按日期分别聚合数据：对每个日期下，按Username、Address、Type三个业务维度分组，将同组的amount做汇总（从样例期望结果反推，同组多记录需做求和计算，可根据业务实际调整聚合规则）
• 对两个日期的聚合结果做全外连接，兼容某日期下维度新增/消失的场景
• 直接用后一日期的汇总值减前一日期的汇总值，空值按0处理，即可得到带正负号的精确差值，最后过滤差值为0的无变更记录即可

可直接运行的SQL代码（适配SQL Server语法，和原有库的dbo前缀兼容） #

WITH date1_stat AS (
    -- 聚合起始日期的数据
    SELECT
        Username,
        Address,
        Type,
        SUM(Amount) AS total_amt
    FROM dbo.data
    WHERE Date = '2022-05-22' -- 替换为实际的起始日期a，若需筛选特定type可在此追加AND Type IN ('类型1','类型2')
    GROUP BY Username, Address, Type
),
date2_stat AS (
    -- 聚合结束日期的数据
    SELECT
        Username,
        Address,
        Type,
        SUM(Amount) AS total_amt
    FROM dbo.data
    WHERE Date = '2022-05-23' -- 替换为实际的结束日期b，筛选特定type的条件和date1_stat保持一致
    GROUP BY Username, Address, Type
)
SELECT
    ISNULL(d1.Username, d2.Username) AS Username,
    ISNULL(d1.Address, d2.Address) AS Address,
    ISNULL(d1.Type, d2.Type) AS Type,
    ISNULL(d2.total_amt, 0) - ISNULL(d1.total_amt, 0) AS diff
FROM date1_stat d1
FULL OUTER JOIN date2_stat d2
    ON d1.Username = d2.Username
    AND d1.Address = d2.Address
    AND d1.Type = d2.Type
-- 过滤金额无变化的记录
WHERE ISNULL(d2.total_amt, 0) - ISNULL(d1.total_amt, 0) <> 0
ORDER BY diff;

结果验证 #

用提供的样例数据运行上述代码，输出结果和期望完全一致：

适配调整说明 #

如果业务场景下同日期同维度的多条记录不需要求和（比如需要取最新ID对应的金额、取首次录入的金额），只需要修改两个CTE内的聚合逻辑即可。例如要取同组最新ID对应的金额，可将CTE替换为窗口函数筛选的写法：

WITH date1_rn AS (
    SELECT
        Username,
        Address,
        Type,
        Amount,
        -- 按维度分组，ID倒序排，最新记录rn=1
        ROW_NUMBER() OVER (PARTITION BY Username, Address, Type ORDER BY ID DESC) AS rn
    FROM dbo.data
    WHERE Date = '2022-05-22'
),
date1_stat AS (
    SELECT Username, Address, Type, Amount AS total_amt FROM date1_rn WHERE rn = 1
),
-- date2_stat做相同改写即可

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