SQL Server按银行维度筛选周记录求和并更新表列的实现方法
核心实现规则
- 筛选阶段:提取不重复的BankName、BankMonth、BankYear字段组合,针对每个BankName维度,匹配其下最新且唯一的四个BankWeek(Week1-Week4)取值,确保每个银行维度最终输出4条有效匹配记录
- 计算阶段:对上述筛选得到的所有匹配记录的RevenueCodeTotal字段做求和计算
- 回写阶段:将计算得到的总和更新至原表对应的数据列
可直接落地的SQL Server实现代码
-- 第一步:筛选符合唯一性要求的有效记录 WITH ValidMatchRecords AS ( SELECT BankName, BankMonth, BankYear, BankWeek, RevenueCodeTotal, -- 按维度去重,保留最新唯一的记录 ROW_NUMBER() OVER ( PARTITION BY BankName, BankMonth, BankYear, BankWeek ORDER BY (SELECT 1) -- 存在数据更新时间字段时,替换为对应时间字段降序排序以取最新值 ) AS record_priority FROM 业务数据表 WHERE BankWeek IN ('Week1', 'Week2', 'Week3', 'Week4') ), -- 第二步:按维度聚合计算RevenueCodeTotal的求和值 RevenueSumCalc AS ( SELECT BankName, BankMonth, BankYear, BankWeek, SUM(RevenueCodeTotal) AS SummedRevenueTotal FROM ValidMatchRecords WHERE record_priority = 1 -- 过滤重复记录,保证每个银行的每个周维度仅保留1条有效数据 GROUP BY BankName, BankMonth, BankYear, BankWeek ) -- 第三步:将计算结果更新回原表对应列 UPDATE original SET original.RevenueCodeTotal = calc.SummedRevenueTotal FROM 业务数据表 original INNER JOIN RevenueSumCalc calc ON original.BankName = calc.BankName AND original.BankMonth = calc.BankMonth AND original.BankYear = calc.BankYear AND original.BankWeek = calc.BankWeek
落地注意事项
- 若原表存在标识数据更新时间、数据生成时间的字段,需要将
ROW_NUMBER()窗口函数内的排序规则调整为按该时间字段倒序排列,才能精准获取每个维度下的最新唯一BankWeek值,避免取到历史旧数据 - 执行更新操作前,可先单独查询
RevenueSumCalcCTE的返回结果,校验是否每个BankName刚好对应4条BankWeek的匹配记录、求和值是否符合预期,再执行更新动作避免误改数据
内容的提问来源于stack exchange,提问作者aasonu
相关产品推荐
相关产品推荐

