SQL Server左连接T2_AMOUNT空值替换为同组最近Table2值
问题说明
当前使用的数据库环境为SQL Server,此前曾修正过旧版本查询的逻辑偏差,核心关联查询语句如下:
select t1.A_NR, t1.V_DATE, t1.AMOUNT T1_AMOUNT, t2.AMOUNT T2_AMOUNT from Table_1 t1 left join Table_2 t2 on t1.A_NR = t2.A_NR and t1.V_DATE = t2.V_DATE
执行上述左连接语句后,返回的结果集中T2_AMOUNT列存在null空值,实际返回结果如下:
+------+----------------------+-----------+-----------+ | A_NR | V_DATE | T1_AMOUNT | T2_AMOUNT | +------+----------------------+-----------+-----------+ | 1 | 2020-01-01T00:00:00Z | 100 | 100 | | 1 | 2020-01-02T00:00:00Z | 101 | (null) | | 1 | 2020-01-03T00:00:00Z | 102 | (null) | | 2 | 2020-01-01T00:00:00Z | 200 | 200 | | 2 | 2020-01-02T00:00:00Z | 201 | (null) | | 2 | 2020-01-03T00:00:00Z | 202 | (null) | +------+----------------------+-----------+-----------+
需求目标
将T2_AMOUNT列的null空值替换为对应A_NR分组下、Table_2中距离当前行最近的历史可用非空值,预期得到的结果集如下:
+------+----------------------+-----------+-----------+ | A_NR | V_DATE | T1_AMOUNT | T2_AMOUNT | +------+----------------------+-----------+-----------+ | 1 | 2020-01-01T00:00:00Z | 100 | 100 | | 1 | 2020-01-02T00:00:00Z | 101 | 100 | -- 取自2020-01-01的对应值 | 1 | 2020-01-03T00:00:00Z | 102 | 100 | -- 取自2020-01-01的对应值 | 2 | 2020-01-01T00:00:00Z | 200 | 200 | | 2 | 2020-01-02T00:00:00Z | 201 | 200 | -- 取自2020-01-01的对应值 | 2 | 2020-01-03T00:00:00Z | 202 | 200 | -- 取自2020-01-01的对应值 +------+----------------------+-----------+-----------+
实现方案
可根据实际使用的SQL Server版本,选择对应的兼容实现:
SQL Server 2022及以上版本
直接使用原生支持忽略空值的LAST_VALUE窗口函数,无需自定义逻辑即可完成向前填充:
select t1.A_NR, t1.V_DATE, t1.AMOUNT T1_AMOUNT, -- 按A_NR分组排序,自动取组内当前行之前最近的非空T2_AMOUNT值 LAST_VALUE(t2.AMOUNT) IGNORE NULLS OVER ( PARTITION BY t1.A_NR ORDER BY t1.V_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS T2_AMOUNT from Table_1 t1 left join Table_2 t2 on t1.A_NR = t2.A_NR and t1.V_DATE = t2.V_DATE
SQL Server 2016及以上版本(低版本兼容方案)
如果数据库版本不支持IGNORE NULLS参数,可通过累计标记分组块的方式实现空值填充,核心逻辑是为每个非空的T2_AMOUNT值生成一个连续的块标记,块内所有空值共享同一个非空值:
WITH base_join AS ( -- 先完成原有左连接查询 select t1.A_NR, t1.V_DATE, t1.AMOUNT T1_AMOUNT, t2.AMOUNT T2_AMOUNT from Table_1 t1 left join Table_2 t2 on t1.A_NR = t2.A_NR and t1.V_DATE = t2.V_DATE ), block_calc AS ( -- 为每个非空值累计计数,生成块标记 SELECT *, SUM(CASE WHEN T2_AMOUNT IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY A_NR ORDER BY V_DATE) AS value_block FROM base_join ) -- 按A_NR、块标记分组,取每个块内的非空值填充所有空行 SELECT A_NR, V_DATE, T1_AMOUNT, MAX(T2_AMOUNT) OVER (PARTITION BY A_NR, value_block) AS T2_AMOUNT FROM block_calc ORDER BY A_NR, V_DATE
注:该兼容方案对所有支持窗口函数的SQL Server版本均适用,无特殊语法依赖。
内容的提问来源于stack exchange,提问作者Peter S
相关产品推荐
相关产品推荐

