求助：使用Lag/Lead函数标记异动数据中的异常记录

异动原始数据 #

ID Name       DateDone             Moved_To     By
01 John Smith 2023-08-03T15:30:23  Left         JP --EG1
01 John Smith 2023-08-03T17:41:45  Right        KS --EG2

01 John Smith 2023-08-03T18:58:42  Still        KS 
01 John Smith 2023-08-03T20:59:55  Still        KS --EG2

02 Tina Platt 2023-08-04T03:11:34  Left         PK
02 Tina Platt 2023-08-04T03:19:10  Left         MO --EG3

02 Tina Platt 2023-08-04T06:19:49  Right        MO  
02 Tina Platt 2023-08-04T08:57:14  Left         MO --EG4

02 Tina Platt 2023-08-04T14:05:12  Right        PK 
02 Tina Platt 2023-08-04T14:11:52  Right        MO --EG5

02 Tina Platt 2023-08-04T16:43:02  Right        MO 
02 Tina Platt 2023-08-04T16:45:49  Right        PK --EG6

02 Tina Platt 2023-08-05T13:21:10  -            SO --EG7

客户搬运人数说明 #

客户John Smith（ID:01）的物品需1人搬运，Tina Platt（ID:02）的需2人，该信息来自[Customers]表的[num_of_ppl]字段。

标记规则 #

• 若客户物品需2人搬运，两名员工的异动记录需间隔15分钟内；若第二条同Moved_To值的记录间隔超15分钟，标记最新行；
• 若客户当前Moved_To与上一条相同（无论需1人或2人），标记该行（2人客户标记最新行），除非Moved_To='Still'则忽略；
• 若1人搬运记录（或2人客户的两条记录中的最新一条）与上一次记录间隔超4小时，标记最新行；
• 若Moved_To未记录，标记该行（或最新行）。

示例数据规则验证结果 #

• EG1 - 1人搬运，间隔4小时内且无重复Moved_To - 不标记
• EG2 - 1人搬运，间隔4小时内且Moved_To='Still' - 不标记
• EG3 - 2人搬运，间隔上一组4小时内且两人记录间隔<15分钟 - 不标记
• EG4 - 2人搬运，间隔上一组4小时内但仅1人记录 - 标记
• EG5 - 2人搬运，与上一组间隔超4小时但有两人记录 - 标记
• EG6 - 2人搬运，间隔上一组4小时内，两人记录但Moved_To与上一组相同 - 标记
• EG7 - 2人搬运，未记录Moved_To - 标记

当前代码（初期版本） #

select
case when ID = lag(ID) over(order by DateDone)
   and moved_to = lag(moved_to) over(order by DateDone) 
   and moved_to != 'Still'
or 
   moved_to = lead(ID) over(order by DateDone)
   and moved_to = lean(moved_to) over(order by DateDone)
   and moved_to != 'Still'
then 'Repeated Move_To' end as "Flag", *
from [Movements]

期望输出结果 #

ID Name       DateDone             Moved_To    By  Issue
02 Tina Platt 2023-08-04T14:15:11  Right       MO  No 2nd
02 Tina Platt 2023-08-04T14:11:52  Right       MO  >4hrs
02 Tina Platt 2023-08-04T16:45:49  Right       PK  Repeat
02 Tina Platt 2023-08-05T13:21:10  -           SO  No Pos

恳请各位提供帮助，谢谢！

内容的提问来源于stack exchange，提问作者Dan