updated
I have the following table of updates which you will note contains nulls
| refer | event_date | col1 | col2 | col3 | cat |
|---|---|---|---|---|---|
| 2 | today | null | null | bbb | a |
| 2 | yesterday | null | null | null | b |
| 2 | yesterday | null | xyz | null | b |
| 2 | last week | abc3 | null | null | z |
| 2 | two weeks ago | aab | null | null | z |
| 2 | last month | null | def | AAA | a |
| 2 | last year | vwy | aa123 | null | b |
因此，对于REFER=2
- BBB是基于CAT=a(今天)的最新日期的Col3的最新更新.
- XYZ是基于cat=b的日期(昨天)的col2的最新更新.
- ABC3是COL1的最新更新，基于CAT z的日期(上周).
我之前问了一个类似的问题here，并接受了@gmb友好提供的以下代码，如果上面的表没有空值，它工作得很好.
select refer,
max(col1) filter(where rn3 = 1 and cat = 'a') detail,
max(col1) filter(where rn2 = 1 and cat = 'b') detail2,
max(col1) filter(where rn1 = 1 and cat = 'z') detail3
from (
select t.*,
row_number() over(partition by refer,col3 order by event_date desc) rn1,
row_number() over(partition by refer,col2 order by event_date desc) rn2,
row_number() over(partition by refer,col1 order by event_date desc) rn3
from mytable t
) t
group by refer
预期输出:
| refer | col1 lastest | col2 latest | col3 latest |
|---|---|---|---|
| 2 | bbb | xyz | abc3 |
问题是，如果列中有空值，它就不起作用，而且我想不出如何从窗口分区组中消除空值.如果我可以在分区中添加is not null，如下所示，它将解决我的问题，例如:
select
row_number() over(partition by refer, col3 **(where col3 is not null)** order by event_date desc) rn1
,row_number() over(partition by refer, col2, ***(where col2 is not null)*** order by event_date desc) rn2
,row_number() over(partition by refer, col1, ***(where col1 is not null)*** order by event_date desc) rn3