在 redshift 中获取第 N 个非空列

debugcn 发表于 Dev

麦克风

我有一个包含可空列集合的表。我需要做的是“左移”这些列，以便第 1 列包含第一个非空值，第 2 列包含第二个非空值等。

例如

This:

<null>, <null>, foo,    bar,    baz,    buz
<null>, <null>, <null>, <null>, foo,    bar
<null>, <null>, <null>, foo,    bar,    baz

需要变成这样：

foo,    bar,    baz,    buz,    <null>, <null>
foo,    bar,    <null>, <null>, <null>, <null>
foo,    bar,    baz,    <null>, <null>, <null>

我可以确定数据包含 0 或更多列，后跟 1 或更多非空列。

我可以想象一种通过无偿使用 CASE 语句来实现这一点的方法，但我宁愿不创造这样一个可憎的东西。

任何人都知道如何轻松完成这项工作？

戈登·利诺夫

我认为最简单的方法是 unpivot/pivot。你可以这样做：

select id,
       max(case when seqnum = 1 then col end) as col1,
       max(case when seqnum = 2 then col end) as col2,
       max(case when seqnum = 3 then col end) as col3,
       max(case when seqnum = 4 then col end) as col4,
       max(case when seqnum = 5 then col end) as col5,
       max(case when seqnum = 6 then col end) as col6
from (select t.*, row_number() over (partition by id order by pos) as seqnum
      from ((select id, col1 as col, 1 as pos from t) union all
            (select id, col2 as col, 2 as pos from t) union all
            (select id, col3 as col, 3 as pos from t) union all
            (select id, col4 as col, 4 as pos from t) union all
            (select id, col5 as col, 5 as pos from t) union all
            (select id, col6 as col, 6 as pos from t) 
           ) t
      where col is not null
     ) t
group by id;

因为 Redshift 是一个列式数据库，所以它union all应该与任何其他逆透视数据方式一样有效。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。