用先前记录中的单元格填充一个空单元格 [英] fill in a null cell with cell from previous record

问题描述

我正在使用DB2 sql填写下表中的某些缺失数据：

Hi I am using DB2 sql to fill in some missing data in the following table:

Person       House     From           To      
------       -----     ----           --
1            586       2000-04-16     2010-12-03 
2            123       2001-01-01     2012-09-27
2            NULL      NULL           NULL
2            104       2004-01-01     2012-11-24
3            987       1999-12-31     2009-08-01
3            NULL      NULL           NULL

第2个人在3所房屋中住过的地方，但中间地址不知道在何时何地。对于他们所住的房子，我无法做任何事情，但是我想拿走他们以前住过的房子，并使用以前的截止日期替换NULL起始日期，然后使用下一个地址信息并使用起始日期替换为空的To date，即。

Where person 2 has lived in 3 houses, but the middle address it is not known where, and when. I can't do anything about what house they were in, but I would like to take the previous house they lived at, and use the previous To date to replace the NULL From date, and use the next address info and use the From date to replace the null To date ie.

Person       House     From           To      
------       -----     ----           --
1            586       2000-04-16     2010-12-03 
2            123       2001-01-01     2012-09-27
2            NULL      2012-09-27     2004-01-01
2            104       2004-01-01     2012-11-24
3            987       1999-12-31     2009-08-01
3            NULL      2009-08-01     9999-01-01

我了解，如果在空地址之前没有先前的地址，则必须保持为空，但是如果空地址是最后一个知道的地址，我想像第3个人一样将收件人日期更改为9999-01-01。

I understand that if there is no previous address before a null address, that will have to stay null, but if a null address is the last know address I would like to change the To date to 9999-01-01 as in person 3.

这种类型的问题在我看来w这里的集合论不再是一个好的解决方案，但是我必须找到一个DB2解决方案，因为那是我老板使用的解决方案！

This type of problem seems to me where set theory no longer becomes a good solution, however I am required to find a DB2 solution because that's what my boss uses!

欢迎任何指针/建议。

谢谢。

推荐答案

它可能看起来像这样：

select 
 person,
 house,
 coalesce(from_date, prev_to_date) from_date,
 case when rn = 1 then coalesce (to_date, '9999-01-01')
  else coalesce(to_date, next_from_date) end to_date
from
(select person, house, from_date, to_date,
 lag(to_date) over (partition by person order by from_date nulls last) prev_to_date,
 lead(from_date) over (partition by person order by from_date nulls last) next_from_date,
 row_number() over (partition by person order by from_date desc nulls last) rn
 from temp
) t

以上内容未经测试，但可能会给您一个提示。

The above is not tested but it might give you an idea.

我希望您的实际情况在表中，您除了 to_date 和 from_date 以外的其他列，可以为每个人排序行，否则，您将无法对NULL日期进行排序，因为您无法知道实际的顺序。

I hope in your actual table you have a column other than to_date and from_date that allows you to order rows for each person, otherwise you'll have trouble sorting NULL dates, as you have no way of knowing the actual sequence.

这篇关于用先前记录中的单元格填充一个空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！