Netezza SQL：满足条件返回行号及关联数据的实现求助

使用Netezza SQL处理下表数据：

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首次变化的行号（row_num），并保留该行的var1_before/var1_after、var2_before/var2_after等完整信息；
• 若某name的var1全程无变化，则返回其对应最后年份的完整行及行号。

已写出用于查看年度变化的CTE代码：

WITH CTE AS (
    SELECT 
        name, 
        year, 
        var1, 
        var2,
        LAG(var1, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var1_before,
        LEAD(var1, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var1_after,
        LAG(var2, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var2_before,
        LEAD(var2, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var2_after,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY year ASC) AS row_num
    FROM 
        mytable
)
SELECT 
  *
FROM 
    CTE;

预期结果示例：

name           category total_number_of_rows year_when_var1_changed var1_before var1_after var2_before var2_after
 John Var1 Never Changed                    3                   NULL           a          a           a          b
 Mary       Var1 Changed                    3                      2           b          a           a          b

基于你已有的CTE，新增筛选逻辑即可实现需求，完整SQL如下：

WITH CTE AS (
    SELECT 
        name, 
        year, 
        var1, 
        var2,
        LAG(var1, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var1_before,
        LEAD(var1, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var1_after,
        LAG(var2, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var2_before,
        LEAD(var2, 1) OVER (PARTITION BY name ORDER BY year ASC) AS var2_after,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY year ASC) AS row_num,
        COUNT(*) OVER (PARTITION BY name) AS total_number_of_rows,
        -- 标记当前行var1是否与上一行发生变化
        CASE WHEN var1 != LAG(var1,1) OVER (PARTITION BY name ORDER BY year ASC) THEN 1 ELSE 0 END AS var1_changed_flag
    FROM 
        mytable
),
CTE_TARGET_ROW AS (
    SELECT 
        name,
        total_number_of_rows,
        -- 确定目标行号：有变化取首次变化的最小行号，无变化取总行数（最后一行）
        COALESCE(MIN(CASE WHEN var1_changed_flag = 1 THEN row_num END), total_number_of_rows) AS target_row_num
    FROM 
        CTE
    GROUP BY 
        name, total_number_of_rows
)
SELECT 
    c.name,
    -- 生成分类标签
    CASE 
        WHEN cr.target_row_num = cr.total_number_of_rows AND MAX(c.var1_changed_flag) = 0 THEN 'Var1从未变化' 
        ELSE 'Var1已变化' 
    END AS category,
    cr.total_number_of_rows,
    -- 仅当有变化时返回变化年份，否则为NULL
    CASE WHEN cr.target_row_num != cr.total_number_of_rows THEN c.year ELSE NULL END AS year_when_var1_changed,
    c.var1_before,
    c.var1_after,
    c.var2_before,
    c.var2_after
FROM 
    CTE c
JOIN 
    CTE_TARGET_ROW cr ON c.name = cr.name AND c.row_num = cr.target_row_num
ORDER BY 
    c.name;

逻辑说明 #

1. CTE扩展：新增total_number_of_rows统计每个name的总行数，var1_changed_flag标记当前行与上一行的var1是否不同；
2. CTE_TARGET_ROW：分组计算每个name的目标行号——存在变化时取首次变化的最小行号，无变化则取总行数（对应最后一行）；
3. 最终查询：关联两个CTE筛选出目标行，同时生成分类标签和变化年份，匹配预期结果格式。

针对你的测试数据，执行后会得到如下结果：

name   category       total_number_of_rows year_when_var1_changed var1_before var1_after var2_before var2_after
Alice  Var1已变化      3                     2                      a           b          b          a
Bob    Var1从未变化    3                     NULL                   b          b           b          a
John   Var1从未变化    3                     NULL                   a          a           a          b
Mary   Var1已变化      3                     2                      b           a          a          b

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