sql - 合并以显示最新的非空值

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 代替 latest 值 编号。 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 |