sql - SQL Server 中 OVER 和 PARTITION BY 生成的值的状态

Question

我有一个源表如下:

我想得到以下结果:

源表的解释:

这张表包含在特定日期的请求及其相关任务，每个请求可以有多个任务并且这些任务可以出现多次，我用不同颜色标记了每个请求及其相关任务

例如，蓝色的 request = 'NC2' 有 3 个任务:

1. task1 随时间发生 3 次。
2. task2 随时间发生 2 次。
3. task3 随时间发生 1 次。

结果表说明:

分数表示基于发生时间的每个请求的任务数。

例如，蓝色的 request = 'NC2' 有 3 个任务:

分数栏:

任务 1:第一次出现是在 2022 年 1 月 5 日，然后是 2022 年 1 月 7 日，最后一次出现在 2022 年 1 月 9 日“对于相同的请求编号”

，如黄色标记，任务二相同，仅在 1/6/2022 和 1/8/2022 出现两次，任务 3 也是如此。

您可以看到分数列包含任务的分数，这些分数基于特定请求编号随时间发生的任务。

状态栏:

对于状态，它显示了特定请求的任务的第一次和最后一次出现，它忽略了中间像 task1 标记为红色它不是第一次出现也不是最后一次出现，所以它被忽略了。

我能够实现的目标:

通过下面的查询，我能够获得 SCORE 列:

select RequestNumber, Task, StartDate, row_number() over(partition by RequestNumber, TaskName 
order by START_DATE) as score
from [SOURCE_TABLE] order by RequestNumber, START_DATE

对于 STATUS 我迷路了，我无法解决它，可能是我在框内思考，所以欢迎任何建议。

Answer 1

@Larnu 的出色回答的稍微更有效的版本:

SELECT
  RequestNumber,
  Task,
  StartDate,
  ROW_NUMBER() OVER (PARTITION BY RequestNumber, TaskName ORDER BY StartDate) AS score,
  CASE WHEN ROW_NUMBER()    OVER (PARTITION BY RequestNumber, TaskName ORDER BY StartDate) = 1
         THEN 'First'
       WHEN LEAD(StartDate) OVER (PARTITION BY RequestNumber, TaskName ORDER BY StartDate) IS NULL
         THEN 'Last'
       END AS Status
FROM dbo.[SOURCE_TABLE];

它使用 LEAD 而不是另一个 ROW_NUMBER。这意味着可以使用相同的窗口子句，因此不需要另一种类型，也不需要假脱机。