R与SQL中LAG()函数差异：将R代码转为SQL实现需求

数据集与需求 #

原始数据集（R语言定义） #

library(dplyr)

df = structure(list(name = c("John", "John", "John", "Mary", "Mary", 
"Mary", "Alice", "Alice", "Alice", "Bob", "Bob", "Bob"), year = c(2001, 
2002, 2003, 2001, 2002, 2003, 2001, 2002, 2003, 2001, 2002, 2003
), var1 = c("a", "a", "a", "b", "a", "b", "a", "b", "a", "b", 
"b", "b"), var2 = c("b", "a", "b", "a", "b", "a", "b", "a", "b", 
"a", "b", "a")), class = "data.frame", row.names = c(NA, -12L
))

数据集预览：

name year var1 var2
  John 2001    a    b
  John 2002    a    a
  John 2003    a    b
  Mary 2001    b    a
  Mary 2002    a    b
  Mary 2003    b    a
 Alice 2001    a    b
 Alice 2002    b    a
 Alice 2003    a    b
   Bob 2001    b    a
   Bob 2002    b    b
   Bob 2003    b    a

处理需求 #

• 针对每个name，找出var1首次发生变化的行，保留该行完整信息，包括var1_before/var1_after、var2_before/var2_after；
• 若某个name的var1始终未变化，则返回该name对应最后一年的完整行信息；
• 最终结果每个name仅保留一行。

R语言实现代码 #

df %>%
    group_by(name) %>%
    mutate(
        row_num = row_number(),
        var1_lag = lag(var1),
        var2_lag = lag(var2),
        var1_change = var1 != var1_lag,
        first_year = first(year)  
    ) %>%
    filter(var1_change | row_num == max(row_num)) %>%
    mutate(
        category = ifelse(var1_change, "Var1 Changed", "Var1 Never Changed"),
        year_when_var1_changed = ifelse(var1_change, year, NA),
        var1_before = ifelse(var1_change, var1_lag, var1),
        var1_after = var1,
        var2_before = ifelse(var1_change, var2_lag, var2),
        var2_after = var2
    ) %>%
    filter(row_number() == 1) %>%
    select(name, first_year, category, year_when_var1_changed, var1_before, var1_after, var2_before, var2_after)

R代码执行结果 #

# A tibble: 4 x 8
# Groups:   name [4]
  name  first_year category           year_when_var1_changed var1_before var1_after var2_before var2_after
  <chr>      <dbl> <chr>                               <dbl> <chr>       <chr>      <chr>       <chr>     
1 John        2001 Var1 Never Changed                     NA a           a          b           b         
2 Mary        2001 Var1 Changed                         2002 b           a          a           b         
3 Alice       2001 Var1 Changed                         2002 a           b          b           a         
4 Bob         2001 Var1 Never Changed                     NA b           b          a           a 

DB2 SQL实现方案 #

以下是适配DB2的完整SQL代码，逻辑完全对齐R语言的处理逻辑：

WITH processed_data AS (
    SELECT 
        name,
        year,
        var1,
        var2,
        -- 计算行号、前一行的var1/var2
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY year ASC) AS row_num,
        LAG(var1) OVER (PARTITION BY name ORDER BY year ASC) AS var1_lag,
        LAG(var2) OVER (PARTITION BY name ORDER BY year ASC) AS var2_lag,
        -- 标记是否发生var1变化
        CASE WHEN var1 != LAG(var1) OVER (PARTITION BY name ORDER BY year ASC) THEN 1 ELSE 0 END AS var1_change,
        -- 获取每个name的首次年份和最大行号
        MIN(year) OVER (PARTITION BY name) AS first_year,
        MAX(ROW_NUMBER() OVER (PARTITION BY name ORDER BY year ASC)) OVER (PARTITION BY name) AS max_row_num
    FROM df
),
filtered_data AS (
    SELECT 
        *,
        -- 确定分类
        CASE WHEN var1_change = 1 THEN 'Var1 Changed' ELSE 'Var1 Never Changed' END AS category,
        -- 填充变化年份和前后值
        CASE WHEN var1_change = 1 THEN year ELSE NULL END AS year_when_var1_changed,
        CASE WHEN var1_change = 1 THEN var1_lag ELSE var1 END AS var1_before,
        var1 AS var1_after,
        CASE WHEN var1_change = 1 THEN var2_lag ELSE var2 END AS var2_before,
        var2 AS var2_after
    FROM processed_data
    -- 筛选：要么是var1变化的行，要么是最后一行（当没有变化时）
    WHERE var1_change = 1 OR row_num = max_row_num
),
final_result AS (
    SELECT 
        *,
        -- 给每个name的筛选结果编号，取第一行（首次变化或最后一行）
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY row_num ASC) AS result_row_num
    FROM filtered_data
)
SELECT 
    name,
    first_year,
    category,
    year_when_var1_changed,
    var1_before,
    var1_after,
    var2_before,
    var2_after
FROM final_result
WHERE result_row_num = 1;

代码说明 #

1. processed_data：计算每个分组的行号、前一行的var1/var2、变化标记，以及每个name的首次年份和最大行号；
2. filtered_data：筛选出符合条件的行（变化行或最后一行），并生成需求中的分类和前后值字段；
3. final_result：对每个name的筛选结果编号，确保只保留一行数据；
4. 最后选择需要的字段输出，结果与R代码完全一致。

内容的提问来源于stack exchange，提问作者Uk rain troll