SQL：使用前一行的值填充当前行

Question

我有两个表table1和table2，如下所示。你可以看到时间上有间隙。

table1
date      item time amount
----------------------------
1/1/2000  a    1    100
1/1/2000  a    2    100
1/1/2000  a    3    200
1/1/2000  a    6    300
1/1/2000  b    1    100
1/1/2000  b    2    100
1/1/2000  b    5    200
2/1/2000  a    1    500
2/1/2000  a    3    500
2/1/2000  a    4    550

我也有一个table2，我会填补其中的空缺。

table2
date      item time amount new
-------------------------------------------
1/1/2000  a    1    100    N
1/1/2000  a    2    100    N
1/1/2000  a    3    200    N
1/1/2000  a    4           Y  <-- added amount should be 200
1/1/2000  a    5           Y  <-- added amount should be 200
1/1/2000  a    6    300    N
1/1/2000  b    1    100    N
1/1/2000  b    2    100    N
1/1/2000  b    3           Y  <-- added amount should be 100 
1/1/2000  b    4           Y  <-- added amount should be 100
1/1/2000  b    5    200    N
2/1/2000  a    1    500    N
2/1/2000  a    2    500    N
2/1/2000  a    3           Y  <-- added amount should be 500
2/1/2000  a    4    550    N

“金额”这一行的间隔应该取上一次/前一次的值。我已经成功找到了缺失的行并添加了间隔行，但是我尝试将金额复制到间隔行中却没有成功。我查看了Stackoverflow中类似的问题，并尝试了解决方案，但仍然不起作用，例如：

update t2
set t2.amount = t1.amount
from table2 t2
inner join table1 t1 on t2.date = t1.date and t1.item = t2.item
where t2.new = 'Y'
and t2.time > (select t2.time 
              from table1 t3
              where max(t3.time) < t2.time)


update t2
set t2.amount = t1.amount
from table1 t1
inner join table2 t2 on t1.date = t2.date and t1.item = t2.item
where t2.new = 'Y' and max(t1.time) < t2.time     

有人知道如何访问上一行的数量吗？游标可以解决问题，但那是最后的手段。谢谢您从百忙之中抽出时间来帮助。

添加我的创建表代码：

create table #table1  (or #table2)
(
   date smalldatetime,
   item char(1),
   [time] int,
   amount int
   ,new char(1) -- for new row flag 
)

- ithoughtso

---

这个表2是由你的查询创建的吗？ - DarkRob

是的，我有原始的table1，然后我创建了一个新的table2并填补了间隙。我喜欢看到表格的不同版本以进行错误检查。 - ithoughtso

请分享您的查询以创建表2... - DarkRob

既然您已经使用tab1和间隙填充了tab2的值，为什么还要加入tab1来尝试获取填充间隙所需的值呢？所需的值已经在tab2中了，或者我错过了什么吗？ - forpas

这就是我做的。我在我的问题中发布了我的代码，但 SQL Server 不喜欢我的查询。 - ithoughtso

你正在使用哪个版本的SQL Server？ - Giorgos Betsos

Answer 1

你需要找到amount的前一个非空值：最初的回答

update t
set amount = (
  select amount from table2 
  where
  date = t.date and item = t.item and time = (
    select max(time) from table2
    where 
    date = t.date and item = t.item 
    and time < t.time and amount is not null and new = 'N'
  ) 
)
from table2 t
where t.amount is null and t.new = 'Y'

查看演示。

最初的回答

- forpas

---

非常感谢！这个可行，但我必须在第二个选择语句中添加“and time < t.time”。 - ithoughtso

如果它能正常工作就好了。我在最新的编辑中只在内部子查询中加入了 time < t.time。 - forpas

Answer 2

你应该使用窗口查询：

select 
*,sum(amount) over (partition by time order by time) as previous_amount_for_null_values
from table2 

- saman tr

---

那个方法不起作用，我没有使用累加总数，只是直接复制。有其他建议吗？谢谢。 - ithoughtso

Answer 3

也许这个Oracle的解决方案会有所帮助（我希望，sql-server有类似rownum或类似的解决方案）。首先，对数据进行排序并获取唯一数字（使用rownum伪列）。其次，对于每个数字，计算具有非空值的最大前一个数字。连接数据。

也许有一种使用分析函数的解决方案，但是我目前没有想到。

-- create test data
drop table tab1;
create table tab1 (
    ym varchar2(7),
    val number
);
insert into tab1 values('2016/01',3);
insert into tab1 values('2016/04',6);
insert into tab1 values('2016/08',4);
insert into tab1 values('2016/09',2);
insert into tab1 values('2016/01',5);
insert into tab1 values('2016/09',8);
insert into tab1 values('2016/05',7);
insert into tab1 values('2016/12',3);
insert into tab1(ym) values('2016/03');
insert into tab1(ym) values('2016/11');
insert into tab1(ym) values('2016/12');
insert into tab1(ym) values('2016/12');

-- solution
with q0 as (-- get rownum for each row
    select a.*, rownum as rnm -- get rownums
    from (
        select *
        from tab1
        order by ym -- order by, to further get proper order numbers from rownum
    ) a
),

q1 as (-- for each rnm get previous (maximal) rnm with non missing value
    select a.rnm, max(b.rnm) as rnm_prev
    from q0 a
    left join q0 b on a.rnm > b.rnm and b.val is not null -- get only smaller rnms with values
    group by a.rnm
)


select q0.ym, q0.rnm, q1.rnm_prev,
q0.val, pv.val as val_prev, nvl(q0.val, pv.val) as val_cor
from q0
left join q1 on q0.rnm = q1.rnm
left join q0 pv on q1.rnm_prev = pv.rnm
order by q0.rnm

- Tomasz

Answer 4

update tab2
set tab2.amount=abc.PREV_AMOUNT
from
table2 tab2
join
(SELECT *,T2.AMOUNT PREV_AMOUNT
FROM TABLE1 T1
JOIN
(
SELECT
MAX(TIME) TIME,DATE,ITEM,MAX(AMOUNT) AMOUNT
FROM TABLE1
WHERE TIME!=(SELECT MAX(TIME) FROM TABLE1
GROUP BY DATE,ITEM)
GROUP BY DATE,ITEM) T2
ON T1.DATE=T2.DATE
AND T1.ITEM=T2.ITEM
AND T1.TIME=T2.TIME) abc
on tab2.date=abc.date
and tab2.item=abc.item
where tab2.new='Y' and tab2.amount is null

分解： 对于每个日期和商品组合，查找第二大的时间。 将第二大的时间与原始Table1连接，并按日期和商品获取金额作为前一个金额（prev Amount）。 使用这个前一个金额来更新Table2中新选项为'Y'的日期和商品。

附注：我没有在SQL开发人员上运行此查询。但我使用的想法应该可以行得通。

- BlackSwan

Answer 5

你可以尝试这个...

    UPDATE T SET T.Amount = ( SELECT MAX(T2.Amount) FROM table2  T2 
    WHERE T2.Amount IS NOT NULL AND T2.[time] <= T.[time] and T2.item = T.item and t2.[date]=T.[date]
                      ) from table2  as T
      WHERE T.Amount IS NULL

如果正常工作，请将其标记为接受。

- DarkRob