如何在Azure Data Flow中用窗口函数实现多行前后值填充逻辑

问题背景 #

现有按连续代理键（从1开始）排序的数据集，当某行year字段为null时，需要填充距离当前行至少2行的前后非null值：优先取前面≥2行的最近非null值，若前面无符合条件的值，则取后面≥2行的最近非null值，最终实现类似FEI7D.png的输出效果。

实现方法 #

可以通过嵌套子查询直接定位符合条件的前后值，结合COALESCE实现优先级取值，具体SQL示例如下（假设代理键字段为id，原表名为your_table）：

SELECT
  id,
  year AS original_year,
  COALESCE(
    -- 优先取前面≥2行的最近非null值
    (SELECT t2.year 
     FROM your_table t2 
     WHERE t2.id <= t1.id - 2 
       AND t2.year IS NOT NULL 
     ORDER BY t2.id DESC LIMIT 1),
    -- 前面无符合条件值时，取后面≥2行的最近非null值
    (SELECT t2.year 
     FROM your_table t2 
     WHERE t2.id >= t1.id + 2 
       AND t2.year IS NOT NULL 
     ORDER BY t2.id ASC LIMIT 1)
  ) AS filled_year
FROM your_table t1
ORDER BY t1.id;

逻辑说明 #

1. 前面值匹配：针对当前行的id，筛选出所有id小于等于当前id-2的非nullyear记录，按id倒序取第一条，即为距离当前行最近的、满足≥2行间隔的前面非null值。
2. 后面值匹配：如果前面没有符合条件的记录，筛选出所有id大于等于当前id+2的非nullyear记录，按id正序取第一条，即为距离当前行最近的、满足≥2行间隔的后面非null值。
3. 优先级控制：通过COALESCE函数优先使用前面的匹配值，前面无结果时自动切换到后面的匹配值。

注意事项 #

• 确保数据集已通过Sort组件按代理键id连续排序，否则需要调整子查询中的条件（例如用排序后的行号而非id）。
• 若前后均无符合条件的非null值，filled_year会保留为null，可根据需求添加默认值（例如在COALESCE末尾补充默认值参数）。

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