如何基于工厂工序确认记录计算零件各工序对应的生产提前期（LeadTime）

核心逻辑拆解 #

整个需求拆成两个独立计算环节，无冗余复杂逻辑：

• 合并拆分的时间字段：原表将确认信息拆为日期、时间两个独立字段存储，直接将两个字段按“日期 时间”的格式拼接，转为标准datetime类型，即为要求的ConfirmationDateTime输出字段。
• 计算LeadTime值：LeadTime的计算边界是同零件、同厂区的独立分组，组内按工序号从小到大排序，取每一行紧邻的上一条（即工序号小于当前值的最大工序号记录）的确认时间，与当前行的确认时间做差值；组内第一条记录没有前置工序，对应LeadTime为NULL，完全匹配样例规则。

高效实现的窗口函数方案 #

不需要写低效的自关联查询，用主流数仓/数据库（MySQL8.0+、Hive、SparkSQL、PostgreSQL）都支持的窗口函数即可实现，核心用到LAG()窗口取值逻辑：

• 开窗的分区规则：PARTITION BY PartNumber, Plant，严格按零件号、厂区拆分独立计算组，避免跨零件、跨厂区混算时间差
• 开窗的排序规则：ORDER BY OperationNumber，按工序号升序排列组内记录，自动匹配“比当前工序号小的最大工序号上序”的取值要求

可直接替换表名运行的参考代码 #

SELECT
  PartNumber,
  Plant,
  OperationNumber,
  -- 当前时间与上序时间做差，无上序时自动返回NULL
  ConfirmationDateTime - PREV_CONFIRM_TIME AS LeadTime,
  ConfirmationDateTime
FROM (
  SELECT
    PartNumber,
    Plant,
    OperationNumber,
    -- 拼接拆分的日期、时间字段，转为标准时间格式
    TO_TIMESTAMP(CONCAT(ConfirmationDate, ' ', ConfirmationTime)) AS ConfirmationDateTime,
    -- 取同分组下按工序号排序的上一条记录的确认时间
    LAG(TO_TIMESTAMP(CONCAT(ConfirmationDate, ' ', ConfirmationTime)))
      OVER (PARTITION BY PartNumber, Plant ORDER BY OperationNumber) AS PREV_CONFIRM_TIME
  FROM  你的原始工序确认表名
) base
-- 按样例的输出排序规则整理结果
ORDER BY PartNumber, OperationNumber

结果匹配校验 #

用给定的示例数据运行上述代码，输出结果和期望结构完全一致：

• PartA、PartB的300工序为各自分组的首条记录，LeadTime返回NULL
• 500工序与对应300工序的时间差为1天1小时，与样例的0000-00-01 01:00:00格式匹配
• 800工序与对应500工序的时间差同样为1天1小时，无计算偏差

小提示：部分数据库返回的时间差为标准interval类型，与样例中的LeadTime格式完全一致；如果需要转成纯数值型时长，可根据业务需求提取对应的时间差数值即可。

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