如何根据以前的行创建列?

Question

我有下表：

id  type
1   NULL
2    A
3   NULL
4   NULL
5    A
6   NULL
7    B
8    A
9    B
10   NULL

我想创建一个列，其中每一行都采用当前状态(如果存在(，如果不采用前一行的状态。 基本上想得到这个：

id  type   new_type
1   NULL    NULL   -- firs one no previous so it can only be null
2    A       A     -- current type A so take it
3   NULL     A     -- no type so take the new_type of previous
4   NULL     A
5    A       A
6   NULL     A
7    B       B
8    A       A
9    B       B
10   NULL    B

我知道我在这里需要窗口函数，但我不知道窗口函数如何引用"正在进行"的列，基本上窗口函数需要同时引用type和new_type但new_type尚不存在.. 它是输出。

如何在SQL/Presto中做到这一点？

Answer 1

Presto对窗口函数有全面的支持。在这里，您可以将lag()与ignore nulls选项一起使用来替换第type列中的null值：

select 
id,
type,
coalesce(
type,
lag(type ignore nulls) over(order by id)
) new_type
from mytable

Answer 2

需要一个游标，特别是如果 id 不能保证是连续的并且没有间隙。

这将在 MS-SQL 中运行：

-- stage sample data
drop table if exists oldTable
create table oldTable (id int, old_type nvarchar(1))
go
insert into oldTable values (1, null), (2, 'A'), (3, null), (4, null), (5, 'A'), (6, null), (7, 'B'), (8, 'A'), (9, 'B'), (10, null)
go
-- get new table ready
drop table if exists newTable
create table newTable (
	id int,
	old_type nvarchar(1),
	new_type nvarchar(1)
)
GO
-- prepare for lots of cursing
declare @the_id int
declare @the_type nvarchar(1)
declare @running_type nvarchar(1)
declare mycursor cursor for
	select
		id, old_type
	from
		oldTable
-- do a barrel roll
open mycursor
fetch mycursor into @the_id, @the_type
while @@ERROR = 0 and @@FETCH_STATUS = 0
begin
	set @running_type = COALESCE(@the_type, @running_type)
	
	insert into newTable(id, old_type, new_type)
	values (@the_id, @the_type, @running_type)
	
	fetch mycursor into @the_id, @the_type
end
close mycursor
deallocate mycursor
go
-- verify results
select * from newTable
go

Answer 3

如何在SQL中做到这一点

例如，它可以是

SELECT t1.id,
t1.type,
( SELECT t2.type
FROM sourcetable t2
WHERE t2.id <= t1.id
AND t2.type IS NOT NULL
ORDER BY id DESC
LIMIT 1 ) new_type
FROM sourcetable t1