合并以显示最新的非空值。

Question

我有以下模式，并为这个问题添加了有代表性的示例数据：

SQL Fiddle

Oracle 11g R2架构设置

create table foo(id integer, att1 varchar(9), att2 varchar(9), from_at date);
insert into foo(id, att1, att2, from_at) values(1, 'J', null, sysdate-5);
insert into foo(id, att1, att2, from_at) values(1, null, 'L', sysdate-4);
insert into foo(id, att1, att2, from_at) values(1, 'B', null, sysdate-3); 
insert into foo(id, att1, att2, from_at) values(1, 'C', null, sysdate-2); 
insert into foo(id, att1, att2, from_at) values(1, null, 'H', sysdate-1); 
insert into foo(id, att1, att2, from_at) values(1, 'A', 'H', sysdate);
insert into foo(id, att1, att2, from_at) values(2, null, 'H', sysdate-1); 
insert into foo(id, att1, att2, from_at) values(2, 'A', null, sysdate);


| ID |   ATT1 |   ATT2 |                         FROM_AT |
----------------------------------------------------------
|  1 |      J | (null) | December, 01 2012 15:13:42+0000 |
|  1 | (null) |      L | December, 02 2012 15:13:42+0000 |
|  1 |      B | (null) | December, 03 2012 15:13:42+0000 |
|  1 |      C | (null) | December, 04 2012 15:13:42+0000 |
|  1 | (null) |      H | December, 05 2012 15:13:42+0000 |
|  1 |      A |      H | December, 06 2012 15:13:42+0000 |
|  2 | (null) |      H | December, 05 2012 15:13:42+0000 |
|  2 |      A | (null) | December, 06 2012 15:13:42+0000 |

我希望能够显示每一行，但是将att1和att2替换为id的最新值。null属性表示与上一次插入行时相比没有变化的属性，非null属性表示新值。输出应该如下所示：

| ID |   ATT1 |   ATT2 |
------------------------
|  1 |      J | (null) |
|  1 |      J |      L |
|  1 |      B |      L |
|  1 |      C |      L |
|  1 |      C |      H |
|  1 |      A |      H |
|  2 | (null) |      H |
|  2 |      A |      H |

Answer 1

您可以使用lag和ignore nulls来完成这一任务。

查询

select id, 
       nvl(att1,lag(att1 ignore nulls) over (partition by id order by from_at))
         as att1,
       nvl(att2,lag(att2 ignore nulls) over (partition by id order by from_at)) 
         as att2
from foo

结果

| ID |   ATT1 |   ATT2 |
------------------------
|  1 |      J | (null) |
|  1 |      J |      L |
|  1 |      B |      L |
|  1 |      C |      L |
|  1 |      C |      H |
|  1 |      A |      H |
|  2 | (null) |      H |
|  2 |      A |      H |