Netezza SQL无CTEs实现间隔与孤岛问题：补全年份及年龄数据

问题背景 #

现有存储用户年度信息的表sample_table，部分年份记录缺失：

• 用户的颜色、食物、运动喜好固定不变
• 年龄随年份逐年递增
• 需要补全每个用户最小年份到最大年份之间的所有缺失记录
• 禁止使用CTE，仅用标准查询实现

原表结构与数据 #

CREATE TABLE sample_table 
(
    name VARCHAR(50),
    age INTEGER,
    year INTEGER,
    color VARCHAR(50),
    food VARCHAR(50),
    sport VARCHAR(50)
);

INSERT INTO sample_table (name, age, year, color, food, sport)
VALUES ('aaa', 41,  2010, 'Red', 'Pizza', 'hockey');

INSERT INTO sample_table (name, age, year, color, food, sport)
VALUES ('aaa', 42,  2012, 'Red', 'Pizza', 'hockey');

INSERT INTO sample_table (name, age, year, color, food, sport)
VALUES ('aaa', 47, 2017, 'Red', 'Pizza', 'hockey');

INSERT INTO sample_table (name, age, year, color, food, sport)
VALUES ('bbb', 20, 2000, 'Blue', 'Burgers','football');

INSERT INTO sample_table (name, age,  year, color, food, sport)
VALUES ('bbb', 26,  2006, 'Blue', 'Burgers', 'football');

INSERT INTO sample_table (name, age, year, color, food, sport)
VALUES ('bbb', 30, 2010, 'Blue', 'Burgers', 'football');

原表数据：

+------+-----+------+-------+---------+----------+
| name | age | year | color |  food   |  sport   |
+------+-----+------+-------+---------+----------+
| aaa  |  41 | 2010 | Red   | Pizza   | hockey   |
| aaa  |  42 | 2012 | Red   | Pizza   | hockey   |
| aaa  |  47 | 2017 | Red   | Pizza   | hockey   |
| bbb  |  20 | 2000 | Blue  | Burgers | football |
| bbb  |  26 | 2006 | Blue  | Burgers | football |
| bbb  |  30 | 2010 | Blue  | Burgers | football |
+------+-----+------+-------+---------+----------+

用户尝试的代码 #

create table years_table (year integer);

insert into years_table(year)
values(2010);
insert into years_table(year)
values(2011);
insert into years_table(year)
values(2012);
insert into years_table(year)
values(2013);
insert into years_table(year)
values(2014);
insert into years_table(year)
values(2015);
insert into years_table(year)
values(2016);
insert into years_table(year)
values(2017);
insert into years_table(year)
values(2018);
insert into years_table(year)
values(2019);
insert into years_table(year)
values(2020);


select name, year, max(color) over(partition by name, grp order by year) color, max(sport) over(partition by name, grp order by year) sport, max(food) over(partition by name, grp order by year) food
from ( 
    select n.name, y.year, t.color, t.food, t.sport,
        sum(case when t.name is null then 0 else 1 end) over(partition by n.name order by y.year) grp
    from (
        select name, min(year) min_year, max(year) max_year
        from sample_table
        group by name
    ) n
    inner join years_table y on y.year between n.min_year and n.max_year
    left join sample_table t on t.name = n.name and t.year = y.year
) t

解决方案 #

核心思路：

1. 用LAST_VALUE窗口函数获取每个年份最近的非空年龄及对应年份
2. 通过当前年份 - 最近记录年份 + 最近记录年龄计算动态年龄
3. 继续使用窗口函数填充固定属性（颜色、食物、运动）

完整SQL代码：

-- 先确保年份表包含所需范围
create table if not exists years_table (year integer);

-- 补全记录的查询
select 
    name,
    -- 计算动态年龄：最近的非空年龄 + 当前年份与该记录年份的差值
    last_age + (year - last_year) as age,
    year,
    -- 填充固定属性
    max(color) over(partition by name, grp order by year) as color,
    max(food) over(partition by name, grp order by year) as food,
    max(sport) over(partition by name, grp order by year) as sport
from (
    select 
        n.name,
        y.year,
        t.color,
        t.food,
        t.sport,
        t.age,
        t.year as record_year,
        -- 获取最近的非空年龄
        last_value(t.age ignore nulls) over(partition by n.name order by y.year) as last_age,
        -- 获取最近的非空年龄对应的年份
        last_value(t.year ignore nulls) over(partition by n.name order by y.year) as last_year,
        -- 分组标记用于填充固定属性
        sum(case when t.name is not null then 1 else 0 end) over(partition by n.name order by y.year) as grp
    from (
        select name, min(year) min_year, max(year) max_year
        from sample_table
        group by name
    ) n
    inner join years_table y on y.year between n.min_year and n.max_year
    left join sample_table t on t.name = n.name and t.year = y.year
) t
order by name, year;

预期结果 #

+------+-----+------+-------+---------+----------+
| name | age | year | color |  food   |  sport   |
+------+-----+------+-------+---------+----------+
| aaa  |  41 | 2010 | Red   | Pizza   | hockey   |
| aaa  |  42 | 2011 | Red   | Pizza   | hockey   |
| aaa  |  42 | 2012 | Red   | Pizza   | hockey   |
| aaa  |  43 | 2013 | Red   | Pizza   | hockey   |
| aaa  |  44 | 2014 | Red   | Pizza   | hockey   |
| aaa  |  45 | 2015 | Red   | Pizza   | hockey   |
| aaa  |  46 | 2016 | Red   | Pizza   | hockey   |
| aaa  |  47 | 2017 | Red   | Pizza   | hockey   |
| bbb  |  20 | 2000 | Blue  | Burgers | football |
| bbb  |  21 | 2001 | Blue  | Burgers | football |
| bbb  |  22 | 2002 | Blue  | Burgers | football |
| bbb  |  23 | 2003 | Blue  | Burgers | football |
| bbb  |  24 | 2004 | Blue  | Burgers | football |
| bbb  |  25 | 2005 | Blue  | Burgers | football |
| bbb  |  26 | 2006 | Blue  | Burgers | football |
| bbb  |  27 | 2007 | Blue  | Burgers | football |
| bbb  |  28 | 2008 | Blue  | Burgers | football |
| bbb  |  29 | 2009 | Blue  | Burgers | football |
| bbb  |  30 | 2010 | Blue  | Burgers | football |
+------+-----+------+-------+---------+----------+

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