如何在字段为空时获取上一行非空值并提取账户最新记录

原交易表数据 #

查询要求 #

1. 当acct_eff_dt字段为空时，显示该账户对应的上一个非空值
2. 使用row_number()窗口函数提取每个账户的最新更新记录（按acct_updt_tm降序排序）

预期输出 #

现有查询语句 #

select * from (select acct_updt_tm,acct_id,acct_eff_dt,acct_cncl_dt,acct_canc_cd, row_number() over (partition by acct_id order by acct_updt_tm desc) as rnk) a where a.rnk=1

修改后的查询语句 #

主流SQL方言版本（支持IGNORE NULLS） #

SELECT 
    acct_updt_tm,
    acct_id,
    -- 提取当前账户下截至当前行的最后一个非空acct_eff_dt值
    LAST_VALUE(acct_eff_dt IGNORE NULLS) OVER (
        PARTITION BY acct_id 
        ORDER BY acct_updt_tm 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS acct_eff_dt,
    acct_cncl_dt,
    acct_canc_cd
FROM (
    SELECT 
        acct_updt_tm,
        acct_id,
        acct_eff_dt,
        acct_cncl_dt,
        acct_canc_cd,
        ROW_NUMBER() OVER (PARTITION BY acct_id ORDER BY acct_updt_tm DESC) AS rnk
    FROM 交易表名 -- 替换为实际表名
) a 
WHERE a.rnk = 1;

MySQL等不支持IGNORE NULLS的方言版本 #

SELECT 
    acct_updt_tm,
    acct_id,
    -- 取账户下所有非空acct_eff_dt的唯一值（因业务场景中该值唯一）
    MAX(acct_eff_dt) OVER (PARTITION BY acct_id) AS acct_eff_dt,
    acct_cncl_dt,
    acct_canc_cd
FROM (
    SELECT 
        acct_updt_tm,
        acct_id,
        acct_eff_dt,
        acct_cncl_dt,
        acct_canc_cd,
        ROW_NUMBER() OVER (PARTITION BY acct_id ORDER BY acct_updt_tm DESC) AS rnk
    FROM 交易表名 -- 替换为实际表名
) a 
WHERE a.rnk = 1;

说明 #

• 内层子查询保留原有的row_number()逻辑，筛选每个账户的最新更新记录
• 外层查询通过窗口函数补全空的acct_eff_dt值：
  • 主流版本用LAST_VALUE+IGNORE NULLS精准获取当前行之前的最后一个非空值
  • MySQL版本用MAX()，因为业务场景中每个账户的非空acct_eff_dt是唯一的，取最大值等价于补全唯一非空值

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