sql - 游标/递归是这里唯一可能的选择吗？基于某些滞后条件的集合逻辑

Question

我正在使用 SQL Server，我有一个包含 3 列(时间序列)的表 data ，带日期，hour开始，AwardStatus .

大部分奖励状态是随机生成的。有两种选择，授予或未授予。

但是，业务要求是，如果状态为 NotAwarded，我们必须连续 3 行打印“NotAwarded”，如果状态为 Awarded，则必须连续打印 4 行。

目标 : 新专栏ShouldBe细节。

一旦满足最低要求，它就会检查当前行的 AwardStatus并继续覆盖逻辑。

问题 : 在没有任何游标/循环的情况下，这在 SQL 中是可能的吗？

以下图为例。



下面是一个例子:

AwardStatusMinimum  3       
AwardStatusMaximum  4       

Date    Hour    AwardStatus ShouldBe
--------------------------------------
1/1/2019    1   NotAwarded  NotAwarded
1/1/2019    2   NotAwarded  NotAwarded
1/1/2019    3   Awarded     NotAwarded
1/1/2019    4   Awarded     Awarded
1/1/2019    5   NotAwarded  Awarded
1/1/2019    6   NotAwarded  Awarded
1/1/2019    7   Awarded     Awarded
1/1/2019    8   NotAwarded  NotAwarded
1/1/2019    9   Awarded     NotAwarded
1/1/2019    10  Awarded     NotAwarded

Answer 1

由于提到了递归。
这是一个使用递归 CTE 的解决方案。
样本数据:

CREATE TABLE Table1 (
  [Date] DATETIME NOT NULL, 
  [Hour] INT NOT NULL, 
  [AwardStatus] VARCHAR(10)
);

INSERT INTO Table1
 ([Date], [Hour], [AwardStatus])
VALUES
 ('2019-01-01', 1, 'NotAwarded'),      
 ('2019-01-01', 2, 'NotAwarded'),     
 ('2019-01-01', 3, 'Awarded'),
 ('2019-01-01', 4, 'Awarded'),
 ('2019-01-01', 5, 'NotAwarded'),
 ('2019-01-01', 6, 'NotAwarded'),
 ('2019-01-01', 7, 'Awarded'),
 ('2019-01-01', 8, 'NotAwarded'),
 ('2019-01-01', 9, 'Awarded'),
 ('2019-01-01', 10, 'Awarded');

查询:

;with CTE_DATA AS 
(
  select *
  , dense_rank() 
    over (order by cast([Date] as date)) as grp
  , row_number() 
    over (partition by cast([Date] as date) order by [Hour]) as rn
  from Table1
)
, RCTE_AWARDS as
(
  select [Date], [Hour]
  , AwardStatus
  , grp
  , rn
  , 1 as Lvl
  , AwardStatus AS CalcStatus
  from CTE_DATA
  where rn = 1
  
  union all
  
  select t.[Date], t.[Hour]
  , t.AwardStatus
  , t.grp
  , t.rn
  , case
    when (c.lvl < 3)
      or (c.lvl < 4 and c.CalcStatus = 'Awarded')
    then c.lvl+1
    else 1
    end
  , case 
    when (c.lvl = 3 and c.CalcStatus = 'NotAwarded')
      or (c.lvl = 4)
    then t.AwardStatus
    else c.CalcStatus
    end
  from RCTE_AWARDS c
  join CTE_DATA t 
    on t.grp = c.grp
   and t.rn = c.rn + 1
)
select [Date], [Hour], AwardStatus
, CalcStatus AS NewAwardStatus
from RCTE_AWARDS
order by [Date], [Hour]

GO

Date                    | Hour | AwardStatus | NewAwardStatus:---------------------- | ---: | :---------- | :-------------2019-01-01 00:00:00.000 |    1 | NotAwarded  | NotAwarded    2019-01-01 00:00:00.000 |    2 | NotAwarded  | NotAwarded    2019-01-01 00:00:00.000 |    3 | Awarded     | NotAwarded    2019-01-01 00:00:00.000 |    4 | Awarded     | Awarded       2019-01-01 00:00:00.000 |    5 | NotAwarded  | Awarded       2019-01-01 00:00:00.000 |    6 | NotAwarded  | Awarded       2019-01-01 00:00:00.000 |    7 | Awarded     | Awarded       2019-01-01 00:00:00.000 |    8 | NotAwarded  | NotAwarded    2019-01-01 00:00:00.000 |    9 | Awarded     | NotAwarded    2019-01-01 00:00:00.000 |   10 | Awarded     | NotAwarded    

db<>fiddle here 上的测试