检索行的每一列的最后一个已知值 [英] Retrieve last known value for each column of a row

问题描述

不确定问这个问题的正确单词，所以我将其分解。

Not sure about the correct words to ask this question, so I will break it down.

我有一张桌子，如下：

date_time | a | b | c

最后4行：

15/10/2013 11:45:00 | null   | 'timtim' | 'fred'
15/10/2013 13:00:00 | 'tune' | 'reco'   | null
16/10/2013 12:00:00 | 'abc'  | null     | null
16/10/2013 13:00:00 | null   | 'died'   | null

我如何获取最后一条记录，但忽略了null值，而是从中获取值

How would I get the last record but with the value ignoring the null and instead get the value from the previous record.

在我提供的示例中，返回的行将是

In my provided example the row returned would be

16/10/2013 13:00:00 | 'abc' | 'died' | 'fred'

您可以看到某列的值是否为空，然后它就转到最后一个记录，该记录具有该列的值并使用该值。

As you can see if the value for a column is null then it goes to the last record which has a value for that column and uses that value.

这应该可行，但我无法弄清楚。到目前为止，我只想出了一个问题：

This should be possible, I just cant figure it out. So far I have only come up with:

select 
    last_value(a) over w a
from test
WINDOW w AS (
    partition by a
    ORDER BY ts asc
    range between current row and unbounded following
    );

但这仅适合单个列...

But this only caters for a single column ...

推荐答案

这应该有效，但请记住这是一个严格的解决方案

This should work but keep in mind it is an uggly solution

select * from
(select dt from
(select rank() over (order by ctid desc) idx, dt
  from sometable ) cx
where idx = 1) dtz,
(
select a from
(select rank() over (order by ctid desc) idx, a
  from sometable where a is not null ) ax 
where idx = 1) az,
(
select b from
(select rank() over (order by ctid desc) idx, b
  from sometable where b is not null ) bx 
where idx = 1) bz,
(
select c from
(select rank() over (order by ctid desc) idx, c
  from sometable where c is not null ) cx
where idx = 1) cz

在小提琴上看到它： http://sqlfiddle.com/#!15/d5940/40

结果t将是

DT                                   A        B      C
October, 16 2013 00:00:00+0000      abc     died    fred

这篇关于检索行的每一列的最后一个已知值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！