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

一尘不染

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

sql

有一个源表如下：

我想得到以下结果：

源表说明：

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

例如，蓝色的 request = ‘NC2’ 有 3 个任务：

task1 随着时间的推移发生 3 次。
task2 随着时间的推移发生 2 次。
task3 随着时间的推移发生 1 次。

结果表说明：

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

例如，蓝色的 request = ‘NC2’ 有 3 个任务：

得分栏：

task1：第一次发生在 2022 年 1 月 5 日，然后发生在 2022 年 1 月 7 日，最后发生在 2022 年 1 月 9 日“对于相同的请求号”

，如黄色标记，任务 2 相同，它仅在 2022 年 1 月 6 日和 2022 年 1 月 8 日发生两次，任务 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 我迷路了，我无法解决它，可能我在盒子里思考，所以欢迎任何建议。

阅读 84

2022-07-21

共1个答案

一尘不染

一个稍微高效的版本：

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. 这意味着可以使用相同的窗口子句，因此不需要其他排序，也不需要假脱机。

2022-07-21