计算工位任务累计等待时间：SQL无报错但未生成表求助

背景 #

我有一张存储待分配给工人的下一个任务信息的表，包含后台程序调度任务的日期。任务为组装类工作，由工厂内漫游的自动机器输送，机器先在中央计算机读取信息，取料后到达工人工位，该过程平均耗时2-3分钟且无延迟。

表结构及数据 #

需求与计算规则 #

需求 #

告知各工位工人下一个任务的到达时间，以及距离下一个任务的等待时长。

计算规则 #

• 单个工位的首个任务，等待时间为2-3分钟；
• 同一工位的后续任务，需累加前序任务的2-3分钟时长。

现有问题 #

我尝试使用SQL窗口函数编写查询，创建存储工位及对应任务等待时间的新表，但在SSMS中运行无报错却未生成目标表。现有SQL代码如下：

WITH TaskInfo AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY [DATA]) AS TaskNumber,
        LAG([CELL_ID]) OVER (ORDER BY [DATA]) AS PrevCell
    FROM 
        [MyTable]
),
CellWaitingTime AS (
    SELECT 
        [CELL_ID],
        [DATA],
        SUM(CASE WHEN [CELL_ID] = PrevCell THEN 0 ELSE 1 END) OVER (ORDER BY [DATA]) AS TaskGroup,
        DATEDIFF(MINUTE, LAG([DATA], 1, [DATA]) OVER (PARTITION BY [CELL_ID] ORDER BY [DATA]), [DATA]) AS TimeDiff
    FROM 
        TaskInfo
)
SELECT 
    [CELL_ID],
    [DATA],
    SUM(TimeDiff) OVER (PARTITION BY [CELL_ID], TaskGroup ORDER BY [DATA]) AS WaitingTime,
    DATEDIFF(MINUTE, [DATA], LEAD([DATA]) OVER (PARTITION BY [CELL_ID], TaskGroup ORDER BY [DATA])) AS TimeUntilNextTask
INTO 
    [NewTableName]
FROM 
    CellWaitingTime

问题分析与修正方案 #

问题点 #

1. TaskGroup分组逻辑错误：当前按全局DATA排序累加分组标识，未按工位分组，导致同工位任务可能被分到不同组，逻辑混乱。
2. TimeDiff计算不符合需求：现有代码计算的是同工位任务记录时间的差值，但需求中等待时长是固定的2-3分钟，与记录时间无关。
3. 无输出导致未生成表：如果查询逻辑错误导致返回空结果集，SELECT INTO不会创建目标表，这就是你遇到的无报错但无表生成的原因。

修正后的SQL代码 #

以下代码按工位分组，正确计算每个任务的等待时长、到达时间，以及到下一个任务的等待时长，确保生成目标表：

WITH TaskRanked AS (
    SELECT 
        CELL_ID,
        DATA,
        DATA_SCHEDULATA,
        -- 按工位分组，按调度日期排序（若调度日期相同则用记录时间）
        ROW_NUMBER() OVER (PARTITION BY CELL_ID ORDER BY DATA_SCHEDULATA, DATA) AS TaskSeq
    FROM 
        [MyTable]
),
TaskWaiting AS (
    SELECT 
        CELL_ID,
        DATA,
        DATA_SCHEDULATA,
        TaskSeq,
        -- 首个任务等待2-3分钟，这里取平均值2.5分钟，也可写成2或3，或保留范围
        (TaskSeq - 1) * 2.5 + 2.5 AS WaitingMinutes,
        -- 计算任务到达时间：记录时间 + 等待时长
        DATEADD(MINUTE, (TaskSeq - 1) * 2.5 + 2.5, DATA) AS ArrivalTime,
        -- 获取同工位下一个任务的到达时间
        LEAD(DATEADD(MINUTE, (TaskSeq) * 2.5 + 2.5, DATA)) OVER (PARTITION BY CELL_ID ORDER BY TaskSeq) AS NextArrivalTime
    FROM 
        TaskRanked
)
SELECT 
    CELL_ID,
    DATA AS RecordTime,
    DATA_SCHEDULATA AS ScheduledDate,
    WaitingMinutes,
    ArrivalTime,
    -- 计算到下一个任务的等待时长
    CASE 
        WHEN NextArrivalTime IS NOT NULL THEN DATEDIFF(MINUTE, ArrivalTime, NextArrivalTime)
        ELSE NULL -- 最后一个任务无后续任务
    END AS TimeUntilNextTask
INTO 
    [NewTableName]
FROM 
    TaskWaiting

说明 #

• 若需要严格区分2分钟和3分钟的范围，可将2.5替换为2和3的逻辑（比如用随机数或固定值，根据实际业务调整）。
• 运行后会在当前数据库下生成NewTableName表，包含各工位任务的等待时长、到达时间等信息。

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