SQL按条件取同邮箱最近日期非空值替换列NULL值

• 填充规则：按employeemail（员工邮箱）维度分组，组内visw_feature_country、join_country字段为NULL时，取与该记录日期间隔在±1天（当日、前1日、后1日）范围内的同列最近非空值完成填充
• 原始代码存在3个问题：
  • 未实现相邻日期非空值填充逻辑，全外连接后直接输出原始空值，无法满足填充需求
  • 计算join_country字段时直接引用同层级SELECT生成的别名visw_feature_country，Teradata环境下会触发语法错误或取值异常
  • 关联new_vwonly表时使用OR拼接多组关联条件，易产生笛卡尔积导致结果行数膨胀、数值错误

-- 第一步：生成逻辑修正后的基础临时表
CREATE VOLATILE TABLE base_temp AS (
    SELECT 
        COALESCE(visw.employeemail, vnotw.employeemail, vwo.employee_email) AS employeemail,
        COALESCE(visw.completed_at, vnotw.completed_at, vwo.completed_at) AS completed_at,
        -- 统一做国家值归一，避免同层级别名引用错误
        CASE 
            WHEN COALESCE(visw.feature_country, vnotw.feature_country, vwo.feature_country) IN ('UK', 'UK/IE', 'AU') THEN 'UK'
            ELSE COALESCE(visw.feature_country, vnotw.feature_country, vwo.feature_country) 
        END AS raw_visw_feature_country,
        CASE 
            WHEN COALESCE(visw.feature_country, vnotw.feature_country, vwo.feature_country) IN ('UK', 'UK/IE', 'AU') THEN 'UK'
            ELSE COALESCE(visw.feature_country, vnotw.feature_country, vwo.feature_country) 
        END AS raw_join_country
    FROM new_visw visw
    FULL OUTER JOIN new_vnotw vnotw
        ON visw.employeemail = vnotw.employeemail 
        AND visw.completed_at = vnotw.completed_at 
        AND visw.feature_country = vnotw.feature_country
    FULL OUTER JOIN new_vwonly vwo
        ON COALESCE(visw.employeemail, vnotw.employeemail) = vwo.employee_email
        AND COALESCE(visw.completed_at, vnotw.completed_at) = vwo.completed_at
        AND COALESCE(visw.feature_country, vnotw.feature_country) = vwo.feature_country
    GROUP BY 1,2,3,4
) WITH DATA ON COMMIT PRESERVE ROWS;

-- 第二步：按±1天规则填充空值，输出最终结果
CREATE VOLATILE TABLE new_base_count AS (
    SELECT 
        t1.employeemail AS visw_employeemail,
        t1.completed_at AS visw_completed_at,
        -- 优先取当前行非空值，空值则取范围内最近日期的非空值
        COALESCE(
            t1.raw_visw_feature_country,
            (
                SELECT t2.raw_visw_feature_country
                FROM base_temp t2
                WHERE t2.employeemail = t1.employeemail
                  AND t2.raw_visw_feature_country IS NOT NULL
                  -- 若completed_at为TIMESTAMP类型，需先CAST为DATE再计算差值：ABS(CAST(t2.completed_at AS DATE) - CAST(t1.completed_at AS DATE)) <=1
                  AND ABS(t2.completed_at - t1.completed_at) <= 1
                QUALIFY ROW_NUMBER() OVER (ORDER BY ABS(t2.completed_at - t1.completed_at), t2.completed_at) = 1
            )
        ) AS visw_feature_country,
        COALESCE(
            t1.raw_join_country,
            (
                SELECT t2.raw_join_country
                FROM base_temp t2
                WHERE t2.employeemail = t1.employeemail
                  AND t2.raw_join_country IS NOT NULL
                  AND ABS(t2.completed_at - t1.completed_at) <= 1
                QUALIFY ROW_NUMBER() OVER (ORDER BY ABS(t2.completed_at - t1.completed_at), t2.completed_at) = 1
            )
        ) AS join_country
    FROM base_temp t1
) WITH DATA ON COMMIT PRESERVE ROWS;

• 第一步先修正原全外连接的关联逻辑，用COALESCE取前两表非空关联键匹配第三张表，避免OR条件导致的数据膨胀，同时统一处理国家字段的归一逻辑，解决同层级别名引用错误
• 第二步通过关联子查询匹配同员工下日期差在1天内的非空记录，用ROW_NUMBER()取日期差最小的记录值填充空值，严格限制填充范围不超过前后1天
• 取值优先级：当前行非空值 > 当日非空值 > 间隔1天的非空值；若前1天、后1天同时存在非空值，默认取日期更早的记录值，可通过修改窗口函数的ORDER BY子句调整优先级

• 当前输出效果：
• 期望输出效果：

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