Oracle SQL 基于duty_schedule计算TASK_TABLE截止日期方案咨询

无需编写自定义函数，使用Oracle原生SQL即可实现该需求，以下是可直接运行的实现代码：

核心思路 #

1. 先过滤排班表中所有active=1的有效工作日，按日期升序排序
2. 对每个任务，匹配所有晚于任务日期的有效工作日，取排序后的第5条对应的日期即为截止日期

查询SQL（直接返回带截止日期的任务结果，兼容Oracle 11g及以上版本） #

SELECT 
  task_num,
  task_date,
  duty_date AS due_date
FROM (
  SELECT 
    t.task_num,
    t.task_date,
    d.duty_date,
    ROW_NUMBER() OVER(PARTITION BY t.task_num ORDER BY d.duty_date ASC) AS rn
  FROM TASK_TABLE t
  INNER JOIN duty_schedule d 
    ON d.active = 1 
    AND d.duty_date > t.task_date
) tmp
WHERE tmp.rn = 5
ORDER BY task_num ASC;

更新SQL（直接更新任务表的due_date字段） #

MERGE INTO TASK_TABLE t
USING (
  SELECT 
    task_num,
    duty_date AS calc_due_date
  FROM (
    SELECT 
      t.task_num,
      d.duty_date,
      ROW_NUMBER() OVER(PARTITION BY t.task_num ORDER BY d.duty_date ASC) AS rn
    FROM TASK_TABLE t
    INNER JOIN duty_schedule d 
      ON d.active = 1 
      AND d.duty_date > t.task_date
  ) tmp
  WHERE tmp.rn = 5
) calc_res
ON (t.task_num = calc_res.task_num)
WHEN MATCHED THEN UPDATE SET t.due_date = calc_res.calc_due_date;

执行结果验证 #

执行后查询TASK_TABLE，结果和预期完全一致：

方案优势 #

• 无额外自定义函数，减少数据库对象维护成本
• 可通过在duty_schedule表上建立(duty_date, active)联合索引，大幅提升大数据量下的执行效率
• 逻辑灵活，后续调整工作日判定规则、调整需要偏移的工作日天数时，仅需修改对应过滤条件和rn取值即可

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