Netezza SQL中如何正确处理NULL值统计学生专业变更次数

场景与问题 #

现有数据表my_table结构及数据如下：

student_id year school_major
1        123 2010         arts
2        123 2011         arts
3        123 2012      science
4        222 2020      science
5        222 2021         NULL
6        222 2022         arts

需求是统计学生大学期间的专业变更相关情况，最初编写的SQL无法正确处理NULL值，无法追踪NULL到ARTS、NULL到SCIENCE这类转换：

select sequence, count(*) as count
from(
select student_id,
school_major  || ' , '|| lead(school_major) over (partition by student_id order by student_id) as sequence
from my_table)q
group by sequence;

执行结果：

sequence count
1           <NA>     2
2    NULL , arts     1
3    arts , arts     1
4 arts , science     1
5 science , NULL     1

尝试用'MISSING'替换NULL后，结果仍出现NULL值：

with my_cte as (select student_id, year, case when school_major is NULL then 'MISSING' else school_major end as school_major from my_table)
    
select sequence, count(*) as count
from(
select student_id,
school_major || ' , '|| lead(school_major) over (partition by student_id order by student_id) as sequence
from my_cte)q
group by sequence;

问题原因 #

1. 排序逻辑错误：窗口函数中用order by student_id，同一学生的记录会因student_id相同导致排序顺序随机，无法按年份顺序获取下一年的专业。
2. NULL拼接特性：Netezza中字符串拼接只要包含NULL，整体结果就会是NULL。每个学生的最后一条记录中，lead(school_major)返回NULL，即使原字段的NULL已替换为'MISSING'，拼接后仍会得到NULL。

解决方案 #

方案1：过滤无后续专业的记录（推荐） #

如果只需要统计有下一年专业的转换情况，修正排序字段并过滤NULL结果：

with my_cte as (
    select 
        student_id, 
        year, 
        case when school_major is NULL then 'MISSING' else school_major end as school_major 
    from my_table
)
select 
    sequence, 
    count(*) as count
from(
    select 
        student_id,
        school_major || ' , '|| lead(school_major) over (partition by student_id order by year) as sequence
    from my_cte
)q
where sequence is not null  -- 过滤无后续专业的记录
group by sequence;

方案2：处理lead返回的NULL值 #

如果需要保留“无后续专业”的标记，将lead的结果也替换为'MISSING'：

with my_cte as (
    select 
        student_id, 
        year, 
        case when school_major is NULL then 'MISSING' else school_major end as school_major 
    from my_table
)
select 
    sequence, 
    count(*) as count
from(
    select 
        student_id,
        school_major || ' , '|| coalesce(lead(school_major) over (partition by student_id order by year), 'MISSING') as sequence
    from my_cte
)q
group by sequence;

方案3：用concat_ws简化拼接逻辑 #

Netezza的concat_ws函数会自动跳过NULL值（仅当所有参数为NULL时才返回NULL），可以避免拼接出NULL：

with my_cte as (
    select 
        student_id, 
        year, 
        case when school_major is NULL then 'MISSING' else school_major end as school_major 
    from my_table
)
select 
    sequence, 
    count(*) as count
from(
    select 
        student_id,
        concat_ws(' , ', school_major, lead(school_major) over (partition by student_id order by year)) as sequence
    from my_cte
)q
where sequence is not null  -- 可选，根据需求决定是否保留无后续的记录
group by sequence;

补充：统计每位学生的变更次数 #

如果需求是统计每位学生的专业变更次数（前后专业不同的次数），可用以下SQL：

with my_cte as (
    select 
        student_id, 
        year, 
        case when school_major is NULL then 'MISSING' else school_major end as school_major 
    from my_table
)
select 
    student_id,
    sum(case when current_major != next_major then 1 else 0 end) as change_count
from(
    select 
        student_id,
        school_major as current_major,
        lead(school_major) over (partition by student_id order by year) as next_major
    from my_cte
)q
where next_major is not null  -- 仅统计有后续专业的情况
group by student_id;

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