Redshift使用LEAD窗口函数返回多余非预期行问题咨询

窗口函数LEAD()是逐行计算的，不会自动合并同分区的行。你的test_scores表中每个用户对应4条原始分数记录，执行查询时会对这4条记录分别计算4个偏移位置的LEAD值：

• 按id升序排的第1条记录：偏移0/1/2/3的位置都有对应分数，就是你需要的完整目标行
• 第2条记录：偏移3的位置没有数据，fourth_score返回NULL
• 第3条记录：偏移2、3的位置没有数据，third_score、fourth_score都返回NULL
• 第4条记录：偏移1/2/3的位置都没有数据，后3个分数字段全为NULL
  这就是你看到额外多出3行、每行NULL值逐行增加的根本原因。

方案1：过滤窗口计算结果 #

最简便的修改方式是在原窗口逻辑里加row_number()行号标记，过滤出每个用户按id排序后的第1行即可——只有这一行能取到全部4个偏移位置的有效分数，其余行的偏移量会超出当前分区的记录范围，必然出现NULL，直接过滤掉就行：

select
  user_id,
  first_score,
  second_score,
  third_score,
  fourth_score
from (
  select
    user_id,
    lead(score, 0) IGNORE NULLS over (partition by user_id order by id asc) as first_score,
    lead(score, 1) IGNORE NULLS over (partition by user_id order by id asc) as second_score,
    lead(score, 2) IGNORE NULLS over (partition by user_id order by id asc) as third_score,
    lead(score, 3) IGNORE NULLS over (partition by user_id order by id asc) as fourth_score,
    row_number() over (partition by user_id order by id asc) as rn
  from test_scores
) t
where rn = 1;

方案2：条件聚合行转列 #

如果不想嵌套多层窗口函数，也可以先给每个用户的分数按id打上行号，再用条件聚合直接把行转成列，逻辑更直白，执行效率和窗口函数方案基本一致：

select
  user_id,
  max(case when rn = 1 then score end) as first_score,
  max(case when rn = 2 then score end) as second_score,
  max(case when rn = 3 then score end) as third_score,
  max(case when rn = 4 then score end) as fourth_score
from (
  select
    user_id,
    score,
    row_number() over (partition by user_id order by id asc) as rn
  from test_scores
) t
group by user_id;

注意：如果业务中存在用户不足4条考试记录的情况，对应缺失位置的分数字段会默认返回NULL，需要填充默认值的话可以在取值逻辑外层套coalesce()函数指定默认内容。

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