SQL中从一行到另一行的分配值

Question

我有下表：

CREATE TABLE table_one( person varchar(55), date_value date, proj varchar(2), value int, time varchar(2 ); 

INSERT INTO table_one VALUES 

('A1',2020-10-01'W',10,'T1')
('A1',2020-10-01'A',5,'T2')
('A1',2020-10-01'P',6,'T3')
('A1',2020-10-01'A',9,'T4')
('A1',2020-10-01'P',11,'T5')
('A1',2020-10-01'A',4,'T6')
('A1',2020-10-01'P',2,'T7')
('A1',2020-10-01'A',1,'T8')
('A1',2020-10-01'P',10,'T9')
('A1',2020-10-01'A',8,'T10')

我需要一个SQL查询，它创建一个新列“new”。以下是填充该新列的条件：

当Proj=a和下一行proj=p时，在Proj=a列中取与proj=p对应的值。

例如，对于第2行，proj值是A，第3行proj值是P，因此与第3行对应的新列值应该是5。

Case-2当最后一行=A且第一行为W时，然后在new_value列中分配最后一行的值。

例如，行-10具有proj值A，行-1具有proj值W，因此与第-1行对应的new_value列应该为8。

当proj = A时，CASE-3 New_val应为空。

请参阅下列图片以获得视觉帮助

​

​

上面的查询需要聚合到person，date_value列。

Answer 1

SELECT person,date_value,proj,value,
lag(proj) ignore nulls over (PARTITION BY person order by row_num) last_proj,
lag(value) ignore nulls over (PARTITION BY person order by row_num) last_proj_value,
last_value(proj) ignore nulls over (PARTITION BY person order by row_num) person_last_proj,
first_value(proj) ignore nulls over (PARTITION BY person order by row_num) person_first_proj,
last_value(value) ignore nulls over (PARTITION BY person order by row_num) person_last_proj_value,
CASE 
  WHEN proj='P' AND last_proj = 'A' THEN last_proj_value --Case_1
  WHEN person_last_proj = 'A' AND person_first_proj = 'W' AND row_num = 1 THEN person_last_proj_value --case_2
  WHEN proj='A' THEN NULL --CASE_3
  ELSE NULL
END AS New_value
FROM 
(SELECT person,date_value,proj,value,row_number() over (partition by person order by date_value) as row_num
FROM table_one);