如何高效使用SQL获取每个ISIN的最后非零值？

问题背景 #

有一张存储日期、0-2整数及ISIN字符串的大表T，样例数据如下：

需要生成每个ISIN对应的各字段最后非零值的结果表，预期输出如下：

现有低效实现 #

当前实现通过多次分组查询和表连接完成，但在大表场景下因多次扫描表和连接操作，运行效率极低：

with 
    isins as (select distinct isin from t),
    a1 as (select max(date) as md, isin from t where v1 > 0 group by isin),
    a2 as (select max(date) as md, isin from t where v2 > 0 group by isin),
    a3 as (select max(date) as md, isin from t where v3 > 0 group by isin),
    a4 as (select max(date) as md, isin from t where v4 > 0 group by isin),
    b1 as (select t.isin, t.V1 from t inner join a1 on a1.isin = t.isin and a1.md = t.date),
    b2 as (select t.isin, t.V2 from t inner join a2 on a2.isin = t.isin and a2.md = t.date),
    b3 as (select t.isin, t.V3 from t inner join a3 on a3.isin = t.isin and a3.md = t.date),
    b4 as (select t.isin, t.V4 from t inner join a4 on a4.isin = t.isin and a4.md = t.date)

select isins.isin, b1.V1, b2.V2, b3.V3, b4.V4 from isins 
    full outer join b1 on isins.isin = b1.isin
    full outer join b2 on isins.isin = b2.isin
    full outer join b3 on isins.isin = b3.isin
    full outer join b4 on isins.isin = b4.isin

高效优化方案 #

方案1：使用ROW_NUMBER()窗口函数（推荐） #

通过窗口函数仅扫描表一次，为每个ISIN下的非零字段记录标记最新日期的行号，再聚合提取目标值：

WITH ranked_records AS (
    SELECT
        ISIN,
        V1,
        V2,
        V3,
        V4,
        -- 为每个ISIN下的V1非零记录按日期倒序排号
        ROW_NUMBER() OVER (PARTITION BY ISIN ORDER BY CASE WHEN V1 > 0 THEN 1 ELSE 0 END DESC, 日期 DESC) AS rn_v1,
        ROW_NUMBER() OVER (PARTITION BY ISIN ORDER BY CASE WHEN V2 > 0 THEN 1 ELSE 0 END DESC, 日期 DESC) AS rn_v2,
        ROW_NUMBER() OVER (PARTITION BY ISIN ORDER BY CASE WHEN V3 > 0 THEN 1 ELSE 0 END DESC, 日期 DESC) AS rn_v3,
        ROW_NUMBER() OVER (PARTITION BY ISIN ORDER BY CASE WHEN V4 > 0 THEN 1 ELSE 0 END DESC, 日期 DESC) AS rn_v4
    FROM T
)
SELECT
    ISIN,
    MAX(CASE WHEN rn_v1 = 1 AND V1 > 0 THEN V1 END) AS V1,
    MAX(CASE WHEN rn_v2 = 1 AND V2 > 0 THEN V2 END) AS V2,
    MAX(CASE WHEN rn_v3 = 1 AND V3 > 0 THEN V3 END) AS V3,
    MAX(CASE WHEN rn_v4 = 1 AND V4 > 0 THEN V4 END) AS V4
FROM ranked_records
GROUP BY ISIN;

方案2：使用LAST_VALUE()窗口函数 #

利用LAST_VALUE直接获取每个ISIN分组内最后一个非零值，需注意窗口范围设置：

WITH window_values AS (
    SELECT
        ISIN,
        LAST_VALUE(CASE WHEN V1 > 0 THEN V1 END) IGNORE NULLS OVER (PARTITION BY ISIN ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS V1,
        LAST_VALUE(CASE WHEN V2 > 0 THEN V2 END) IGNORE NULLS OVER (PARTITION BY ISIN ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS V2,
        LAST_VALUE(CASE WHEN V3 > 0 THEN V3 END) IGNORE NULLS OVER (PARTITION BY ISIN ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS V3,
        LAST_VALUE(CASE WHEN V4 > 0 THEN V4 END) IGNORE NULLS OVER (PARTITION BY ISIN ORDER BY 日期 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS V4
    FROM T
)
SELECT DISTINCT ISIN, V1, V2, V3, V4 FROM window_values;

优化说明 #

• 现有方案需要对表进行至少8次扫描（4次分组+4次连接查询），且多次JOIN会带来额外开销；
• 优化方案仅需扫描表1次，通过窗口函数在内存中完成分组排序，再通过聚合或去重得到结果，大幅降低IO和计算开销，尤其适合大表场景；
• 若数据库支持IGNORE NULLS（如PostgreSQL、Oracle），方案2会更简洁；若不支持，方案1兼容性更好。

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