根据与系统日志日期是否连续相同，将手动输入的日期值分组-后续问题

Question

这是我第一个帖子的后续问题

示例情况:订单系统通过记录始终唯一的系统日志日期来跟踪手动输入的到期日期(这将是一个日期时间，但为了简单起见，我使用了日期，使每个日期唯一)。

我想为每个截止日期组分配一个部分编号，其中截止日期按时间顺序保持不变。

Stu的响应解决了我最初帖子中的表格，但我注意到，如果我将与1/16/2022 SysLogDate相关的4/15/2022到期日期替换为4/13/2022，则期望的顺序似乎无法维持:

注:4/13/2022日期为任意更改。如果我使用DueDate列中尚未包含的任何其他唯一日期，也会出现同样的问题。最终，我还需要能够处理更改/从NULL，有人"忘记"输入日期，但用NULL替换日期也会产生同样的问题。

更新表:

CREATE TABLE #DueDates (OrderNo INT, DueDate Date, SysLogDate Date)
INSERT INTO #DueDates Values (1, '4/10/2022',   '1/10/2022')
,(1, '4/10/2022',   '1/11/2022')
,(1, '4/15/2022',   '1/15/2022')
,(1, '4/13/2022',   '1/16/2022')  -- Due Date Altered since prior post
,(1, '4/15/2022',   '1/17/2022')
,(1, '4/10/2022',   '1/18/2022')
,(1, '4/10/2022',   '1/19/2022')
,(1, '4/10/2022',   '1/20/2022')
,(2, '4/10/2022',   '2/16/2022')
,(2, '4/10/2022',   '2/17/2022')
,(2, '4/15/2022',   '2/18/2022')
,(2, '4/15/2022',   '2/20/2022')
,(2, '4/15/2022',   '2/21/2022')
,(2, '4/10/2022',   '2/22/2022')
,(2, '4/10/2022',   '2/24/2022')
,(2, '4/10/2022',   '2/26/2022') 

期望的结果是:

OrderNo DueDate         SysLogDate      SectionNumber_WithinDueDate
1       2022-04-10      2022-01-10      1
1       2022-04-10      2022-01-11      1
1       2022-04-15      2022-01-15      2
1       2022-04-13      2022-01-16      3
1       2022-04-15      2022-01-17      4
1       2022-04-10      2022-01-18      5
1       2022-04-10      2022-01-19      5
1       2022-04-10      2022-01-20      5
2       2022-04-10      2022-02-16      1
2       2022-04-10      2022-02-17      1
2       2022-04-15      2022-02-18      2
2       2022-04-15      2022-02-20      2
2       2022-04-15      2022-02-21      2
2       2022-04-10      2022-02-22      3
2       2022-04-10      2022-02-24      3
2       2022-04-10      2022-02-26      3

…但是将我之前的帖子中的解决方案应用到这个更新的表中会产生:

OrderNo DueDate         SysLogDate      SectionNumber_WithinDueDate
1       2022-04-10      2022-01-10      1
1       2022-04-10      2022-01-11      1
1       2022-04-15      2022-01-15      2
1       2022-04-13      2022-01-16      3 **
1       2022-04-15      2022-01-17      3 **
1       2022-04-10      2022-01-18      3 **
1       2022-04-10      2022-01-19      3 **
1       2022-04-10      2022-01-20      3 **
2       2022-04-10      2022-02-16      1
2       2022-04-10      2022-02-17      1
2       2022-04-15      2022-02-18      2
2       2022-04-15      2022-02-20      2
2       2022-04-15      2022-02-21      2
2       2022-04-10      2022-02-22      3
2       2022-04-10      2022-02-24      3
2       2022-04-10      2022-02-26      3

下面是一个演示，它使用了上面更新的表和我之前的帖子中的解决方案，并显示了上面不期望的结果:

演示日期被NULL替换后的效果:

从我之前的帖子中选择的解决方案的副本(在上面的Fiddles中使用):

select OrderNo, DueDate, SysLogDate, 
dense_rank() over(partition by orderno order by gp) SectionNumber_WithinDueDate
from (
select *,
Row_Number() over(partition by OrderNo order by SysLogDate)
- Row_Number() over(partition by OrderNo, DueDate order by SysLogDate) gp
from #DueDates
)t
order by OrderNo, SysLogDate;

这是数据的一个小变化，但我还没能弄清楚如何改变子查询中的"Row_Number差异行"以获得所需的结果。

感谢您提供的任何建议:)

编辑:事后看来，我意识到section编号字段应该更合适地命名为"sectionnumber_withinorder";而不是"SectionNumber_WithinDueDate，"但我保留了这些东西，以保持名称与所建议的代码示例保持一致。

Answer 1

对于缺口和孤岛问题，我更倾向于使用lag()窗口函数，因为它更容易理解。

使用lag()比较之前的行值，当更改时，设置一个标志(值1)。对标志执行累加和，得到grp。在grp上使用dense_rank()，它会给你SectionNumber_WithinDueDate

当您有NULL值时，使用ISNULL()返回日期值(99991231)进行比较

select OrderNo, DueDate, SysLogDate, 
SectionNumber_WithinDueDate = dense_rank() over (partition by OrderNo 
order by grp)
from
(
select *, grp = sum(g) over (partition by OrderNo 
order by SysLogDate)
from   
( 
select *,
g = case when isnull(DueDate, '99991231')
<>   isnull(lag(DueDate) over (partition by OrderNo 
order by SysLogDate), '99991231')
then 1
else 0
end
from   #DueDates
) d
) d
order by OrderNo, SysLogDate;

修改您的示例数据:

小提琴1

小提琴2