基于条件补全缺失行：Netezza SQL实现方案问询

实现思路 #

由于Netezza不支持递归CTE，我们依赖table_a作为连续年份维度表（需包含从所有ID最早出现年份到2014年的完整年份值），通过基础CTE分三步实现需求：

1. 预计算每个ID的最早出现年份，以及该ID是否存在var1='Z'的记录
2. 为每个ID生成从最早年份到2014年的完整年份序列
3. 将完整年份序列与原表左连接，按规则填充缺失的var1值

完整代码 #

WITH id_metadata AS (
    -- 计算每个ID的最早年份，以及对应的默认var1值
    SELECT
        id,
        MIN(year) AS min_year,
        -- 若存在Z值则默认Z，否则默认not available
        CASE WHEN SUM(CASE WHEN var1 = 'Z' THEN 1 ELSE 0 END) > 0 THEN 'Z' ELSE 'not available' END AS default_var1
    FROM table_b
    GROUP BY id
),
id_year_full AS (
    -- 生成每个ID需要补全的所有年份行
    SELECT
        im.id,
        a.year
    FROM id_metadata im
    CROSS JOIN table_a a
    WHERE a.year >= im.min_year
      AND a.year <= 2014
)
-- 左连接原表，填充最终var1值
SELECT
    iy.id,
    iy.year,
    -- 原表有数据则用原var1，无数据则用预计算的默认值
    COALESCE(b.var1, iy.default_var1) AS var1
FROM id_year_full iy
LEFT JOIN table_b b
    ON iy.id = b.id
    AND iy.year = b.year
ORDER BY iy.id, iy.year;

代码说明 #

• id_metadata CTE：按ID分组，提取每个ID的起始年份，并判断该ID是否需要将新增行的var1设为'Z'
• id_year_full CTE：通过交叉连接，为每个ID生成从起始年份到2014年的所有年份组合，确保无缺失
• 最终查询：左连接原表table_b，优先保留原表已有数据的var1，缺失行则使用预计算的默认值填充

替代方案（若table_a不是年份维度表） #

如果table_a不包含连续年份，可手动生成年份序列替代：

WITH year_list AS (
    SELECT 2000 AS year UNION ALL
    SELECT 2001 AS year UNION ALL
    SELECT 2002 AS year UNION ALL
    SELECT 2003 AS year UNION ALL
    SELECT 2004 AS year UNION ALL
    SELECT 2005 AS year UNION ALL
    SELECT 2006 AS year UNION ALL
    SELECT 2007 AS year UNION ALL
    SELECT 2008 AS year UNION ALL
    SELECT 2009 AS year UNION ALL
    SELECT 2010 AS year UNION ALL
    SELECT 2011 AS year UNION ALL
    SELECT 2012 AS year UNION ALL
    SELECT 2013 AS year UNION ALL
    SELECT 2014 AS year
),
id_metadata AS (
    SELECT
        id,
        MIN(year) AS min_year,
        CASE WHEN SUM(CASE WHEN var1 = 'Z' THEN 1 ELSE 0 END) > 0 THEN 'Z' ELSE 'not available' END AS default_var1
    FROM table_b
    GROUP BY id
),
id_year_full AS (
    SELECT
        im.id,
        yl.year
    FROM id_metadata im
    CROSS JOIN year_list yl
    WHERE yl.year >= im.min_year
      AND yl.year <= 2014
)
SELECT
    iy.id,
    iy.year,
    COALESCE(b.var1, iy.default_var1) AS var1
FROM id_year_full iy
LEFT JOIN table_b b
    ON iy.id = b.id
    AND iy.year = b.year
ORDER BY iy.id, iy.year;

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