使用LAG更新当前行和下一行，直到上一行值为null

Question

我正在尝试解决LAG的问题。我想用以前的值更新当前行(如果为null(，并保持它的重复直到为null。

这是我的代码：

SELECT PersonID, FirstDateofTermYear,
case 
when PhysicalOVRARiskRating is null then LAG (PhysicalOVRARiskRating, 2, PhysicalOVRARiskRating) OVER (PARTITION BY PersonID ORDER BY FirstDateofTermYear)
ELSE PhysicalOVRARiskRating END AS PhysicalOVRARiskRating,
case 
when  PsychologicalOVRARiskRating is null then  LAG (PsychologicalOVRARiskRating, 2, PsychologicalOVRARiskRating) OVER (PARTITION BY PersonID ORDER BY FirstDateofTermYear) 
ELSE PsychologicalOVRARiskRating  END AS PsychologicalOVRARiskRating
FROM [BI].[vw_Fact_OVT_CCI_V3]
WHERE  PersonID = '0258077'
ORDER BY FirstDateofTermYear;

原始结果-

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
0258077 2020-02-03  NULL    NULL
0258077 2020-04-28  NULL    MEDIUM
0258077 2020-07-20  NULL    NULL
0258077 2020-10-12  NULL    NULL
0258077 2021-02-01  NULL    NULL
0258077 2021-04-19  NULL    NULL
0258077 2021-07-12  NULL    NULL
0258077 2021-10-05  NULL    NULL
0258077 2022-01-31  NULL    NULL
0258077 2022-04-26  NULL    LOW
0258077 2022-07-18  NULL    NULL

当前结果-

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
0258077 2020-02-03  NULL    NULL
0258077 2020-04-28  NULL    MEDIUM
0258077 2020-07-20  NULL    MEDIUM
0258077 2020-10-12  NULL    MEDIUM
0258077 2021-02-01  NULL    NULL
0258077 2021-04-19  NULL    NULL
0258077 2021-07-12  NULL    NULL
0258077 2021-10-05  NULL    NULL
0258077 2022-01-31  NULL    NULL
0258077 2022-04-26  NULL    LOW
0258077 2022-07-18  NULL    LOW

预期结果-

PersonID    FirstDateofTermYear PhysicalOVRARiskRating  PsychologicalOVRARiskRating
258077  03/02/2020  NULL    NULL
258077  28/04/2020  NULL    MEDIUM
258077  20/07/2020  NULL    MEDIUM
258077  12/10/2020  NULL    MEDIUM
258077  01/02/2021  NULL    MEDIUM
258077  19/04/2021  NULL    MEDIUM
258077  12/07/2021  NULL    MEDIUM
258077  05/10/2021  NULL    MEDIUM
258077  31/01/2022  NULL    MEDIUM
258077  26/04/2022  NULL    LOW
258077  18/07/2022  NULL    LOW

如何重复该值，直到找到null以外的值？LAG函数只重复一到两次，而不是每次都重复。

Answer 1

根据@lptr的评论

select PersonID, FirstDateofTermYear
, min(PhysicalOVRARiskRating) over (partition by PersonID, grpphy)
, min(PsychologicalOVRARiskRating) over (partition by PersonID, grppsy)
from (
select *
, count(PhysicalOVRARiskRating) over (partition by PersonID order by FirstDateofTermYear rows unbounded preceding) as grpphy
, count(PsychologicalOVRARiskRating) over (partition by PersonID order by FirstDateofTermYear rows unbounded preceding) as grppsy
from [BI].[vw_Fact_OVT_CCI_V3]
) as d