如何将补全缺失行的R代码转换为Netezza SQL代码？

数据集与需求 #

现有如下R数据集，部分人员存在年份缺失行（如John的年份从2011跳到2014）：

name = c("john", "john", "john", "sarah", "sarah", "peter", "peter", "peter", "peter")
year = c(2010, 2011, 2014, 2010, 2015, 2011, 2012, 2013, 2015)
age = c(21, 22, 25, 55, 60, 61, 62, 63, 65)
gender = c("male", "male", "male", "female", "female", "male", "male", "male", "male" )
country_of_birth = c("australia", "australia", "australia", "uk", "uk", "mexico", "mexico", "mexico", "mexico")
source = "ORIGINAL"

my_data = data.frame(name, year, age, gender, country_of_birth, source)

原始数据展示：

name year age gender country_of_birth   source
1  john 2010  21   male        australia ORIGINAL
2  john 2011  22   male        australia ORIGINAL
3  john 2014  25   male        australia ORIGINAL
4 sarah 2010  55 female               uk ORIGINAL
5 sarah 2015  60 female               uk ORIGINAL
6 peter 2011  61   male           mexico ORIGINAL
7 peter 2012  62   male           mexico ORIGINAL
8 peter 2013  63   male           mexico ORIGINAL
9 peter 2015  65   male           mexico ORIGINAL

已用R代码实现缺失行补全：按姓名分组生成完整年份序列，插值计算缺失年龄，填充性别、出生国等字段，标记数据来源：

library(tidyverse)
library(dplyr)

# R Code to Convert into SQL
final = my_data %>% 
    group_by(name) %>% 
    complete(year = first(year): last(year)) %>% 
    mutate(age = ifelse(is.na(age), first(age)+row_number()-1, age)) %>% 
    fill(c(gender, country_of_birth), .direction = "down") %>% 
    mutate(source = ifelse(is.na(source), "NOT ORIGINAL", source))

补全后的部分结果：

# A tibble: 16 x 6
# Groups:   name [3]
   name   year   age gender country_of_birth source       
   <chr> <dbl> <dbl> <chr>  <chr>            <chr>        
 1 john   2010    21 male   australia        ORIGINAL    
 2 john   2011    22 male   australia        ORIGINAL    
 3 john   2012    23 male   australia        NOT ORIGINAL

尝试用dbplyr转换为Netezza SQL时出现错误：

Error in `fill()`:
x `.data` does not have explicit order.
i Please use `arrange()` or `window_order()` to make determinstic.
Run `rlang::last_error()` to see where the error occurred.

错误原因 #

dbplyr转换fill()函数时，要求数据有明确的排序规则——因为SQL是基于集合的，没有默认顺序。原R代码中complete()后未显式指定分组内的排序逻辑，导致fill()无法确定填充顺序，从而触发报错。

解决方法 #

方法1：修正dbplyr代码 #

在complete()后添加arrange(year, .by_group = TRUE)，确保分组内按年份排序，让fill()有确定的执行顺序：

final = my_data %>% 
    group_by(name) %>% 
    complete(year = first(year): last(year)) %>% 
    arrange(year, .by_group = TRUE) %>%  # 新增：按分组内年份排序
    mutate(age = ifelse(is.na(age), first(age)+row_number()-1, age)) %>% 
    fill(c(gender, country_of_birth), .direction = "down") %>% 
    mutate(source = ifelse(is.na(source), "NOT ORIGINAL", source))

修改后dbplyr能生成带有窗口函数排序的SQL代码，适配Netezza语法。

方法2：纯Netezza SQL实现（JOINS方式） #

通过递归CTE生成完整年份序列，再与原始数据关联实现补全：

WITH name_year_ranges AS (
    -- 提取每个人员的年份范围和基准年龄
    SELECT 
        name,
        MIN(year) AS min_year,
        MAX(year) AS max_year,
        MIN(age) AS base_age
    FROM my_data
    GROUP BY name
),
name_full_years AS (
    -- 递归生成每个人员的完整年份序列及对应插值年龄
    SELECT 
        name,
        min_year AS year,
        base_age AS age
    FROM name_year_ranges
    UNION ALL
    SELECT 
        ny.name,
        nfy.year + 1 AS year,
        nfy.age + 1 AS age
    FROM name_full_years nfy
    JOIN name_year_ranges ny ON nfy.name = ny.name
    WHERE nfy.year < ny.max_year
),
final_data AS (
    -- 关联原始数据，补全字段并标记来源
    SELECT 
        nfy.name,
        nfy.year,
        COALESCE(md.age, nfy.age) AS age,
        FIRST_VALUE(md.gender) OVER (PARTITION BY nfy.name ORDER BY nfy.year) AS gender,
        FIRST_VALUE(md.country_of_birth) OVER (PARTITION BY nfy.name ORDER BY nfy.year) AS country_of_birth,
        CASE WHEN md.source IS NULL THEN 'NOT ORIGINAL' ELSE md.source END AS source
    FROM name_full_years nfy
    LEFT JOIN my_data md ON nfy.name = md.name AND nfy.year = md.year
)
SELECT * FROM final_data ORDER BY name, year;

说明：

• name_year_ranges获取每个人员的年份区间和首次出现的年龄（作为插值基准）
• name_full_years用递归CTE生成从最小到最大年份的完整序列，并逐年计算年龄
• final_data通过左连接关联原始数据，用FIRST_VALUE()窗口函数填充性别、出生国，用COALESCE()和CASE处理年龄和数据来源标记

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