如何使用Oracle SQL基于TRIGDT和GAPDAYS生成合规日期行？

需求说明 #

现有表包含JOBID、JOBSPEC、TRIGDT、GAPDAYS列，需按以下规则生成PRINTDT和COPYDT两个新日期列，要求用SQL（优先使用CONNECT BY子句）实现，替代PL/SQL循环：

1. 首行PRINTDT = TRIGDT，COPYDT = PRINTDT + GAPDAYS - 1
2. 后续行PRINTDT = 上一行COPYDT + 1，COPYDT = 新PRINTDT + GAPDAYS - 1
3. 持续生成行直到COPYDT ≤ SYSDATE（当前日期）
4. 每个原始行对应的重复行中，JOBID、JOBSPEC、TRIGDT、GAPDAYS保持不变
5. 若TRIGDT为NULL，不生成任何行
6. 若首行COPYDT > SYSDATE，该行需排除

输入数据示例 #

WITH
    tbl_print_schd
    AS
        (SELECT 'J101' AS JOBID, 'PRINT_DOC164' AS JOBSPEC, TO_DATE ( '06/29/2023', 'MM/DD/YYYY' ) AS TRIGDT, 15 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J2213' AS JOBID, 'PRINT_SYS_TBL22' AS JOBSPEC, TO_DATE ( '8/26/2023', 'MM/DD/YYYY' ) AS TRIGDT, 9 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J66' AS JOBID, 'ILLUM_STIG93' AS JOBSPEC, TO_DATE ( '9/10/2023', 'MM/DD/YYYY' ) AS TRIGDT, 11 AS GAPDAYS FROM SYS.DUAL)
SELECT JOBID
     , JOBSPEC
     , TRIGDT
     , GAPDAYS
  FROM tbl_print_schd;

解决方案SQL #

WITH
    tbl_print_schd
    AS
        (SELECT 'J101' AS JOBID, 'PRINT_DOC164' AS JOBSPEC, TO_DATE ( '06/29/2023', 'MM/DD/YYYY' ) AS TRIGDT, 15 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J2213' AS JOBID, 'PRINT_SYS_TBL22' AS JOBSPEC, TO_DATE ( '8/26/2023', 'MM/DD/YYYY' ) AS TRIGDT, 9 AS GAPDAYS FROM SYS.DUAL
         UNION
         SELECT 'J66' AS JOBID, 'ILLUM_STIG93' AS JOBSPEC, TO_DATE ( '9/10/2023', 'MM/DD/YYYY' ) AS TRIGDT, 11 AS GAPDAYS FROM SYS.DUAL)
SELECT t.JOBID
     , t.JOBSPEC
     , t.TRIGDT
     , t.GAPDAYS
     , t.TRIGDT + (LEVEL - 1) * t.GAPDAYS AS PRINTDT
     , t.TRIGDT + LEVEL * t.GAPDAYS - 1 AS COPYDT
  FROM tbl_print_schd t
 WHERE t.TRIGDT IS NOT NULL
   AND t.TRIGDT + t.GAPDAYS - 1 <= SYSDATE -- 排除首行COPYDT超过当前日期的情况
CONNECT BY LEVEL <= FLOOR((SYSDATE - t.TRIGDT + 1) / t.GAPDAYS)
       AND PRIOR t.JOBID = t.JOBID
       AND PRIOR SYS_GUID() IS NOT NULL; -- 避免循环连接时的笛卡尔积问题

关键逻辑说明 #

1. 层级计算：用LEVEL表示每个原始行生成的第N行，通过TRIGDT + (LEVEL - 1)*GAPDAYS计算每一行的PRINTDT，TRIGDT + LEVEL*GAPDAYS -1计算对应的COPYDT，完全匹配规则1和2。
2. 终止条件：CONNECT BY LEVEL <= FLOOR((SYSDATE - TRIGDT + 1)/GAPDAYS)确保生成的最后一行COPYDT不超过SYSDATE，符合规则3。
3. 过滤规则：
   • WHERE TRIGDT IS NOT NULL直接过滤TRIGDT为空的行，符合规则5；
   • TRIGDT + GAPDAYS -1 <= SYSDATE排除首行COPYDT超过当前日期的情况，符合规则6。
4. 避免笛卡尔积：PRIOR SYS_GUID() IS NOT NULL用于在CONNECT BY时确保每个原始行独立生成自己的层级行，不会和其他行产生交叉。

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