【Oracle】获取最近工作日及前N个工作日

需求： 日历表 TCALENDAR_DATES，工作日flag=‘1’，非工作日=‘0’，取任一查询日期最近工作日，及最近工作日前n个工作日

日历表TCALENDAR_DATES样式

SELECT T.BASE_DATE, T.CAL_DAY, C.LAST_3_DAY
  FROM (SELECT C1.CAL_DAY AS BASE_DATE, MAX(C2.CAL_DAY) AS CAL_DAY --最近工作日
          FROM XIR_MD.TCALENDAR_DATES C1
          LEFT JOIN XIR_MD.TCALENDAR_DATES C2
            ON (C1.CAL_DAY >= C2.CAL_DAY AND C2.CAL_FLAG = '1')
         WHERE C1.CAL_CODE = 'CHINA_IB'
           AND C1.CAL_DAY >= '2020-01-01'
           AND C1.CAL_DAY <= '2020-12-31'
         GROUP BY C1.CAL_DAY
         ORDER BY C1.CAL_DAY) T
  LEFT JOIN (SELECT C.CAL_DAY,
                    LAG(C.CAL_DAY, 3, '') OVER(ORDER BY C.CAL_DAY) AS LAST_3_DAY --最近工作日 前3个工作日
               FROM XIR_MD.TCALENDAR_DATES C
              WHERE C.CAL_CODE = 'CHINA_IB'
                AND C.CAL_FLAG = '1'
                AND C.CAL_DAY >= '2020-01-01'
                AND C.CAL_DAY <= '2020-12-31') C
    ON T.CAL_DAY = C.CAL_DAY

2022-11-05 修改为新方式

SELECT A.CAL_DAY, A.CAL_FLAG,
       LEAD(A.CAL_DAY_NEW IGNORE NULLS, 1) OVER(PARTITION BY A.CAL_CODE ORDER BY A.CAL_DAY) AS END_1_DATE,
       LEAD(A.CAL_DAY_NEW IGNORE NULLS, 5) OVER(PARTITION BY A.CAL_CODE ORDER BY A.CAL_DAY) AS END_5_DATE
  FROM (SELECT DA.CAL_DAY,
               DECODE(DA.CAL_FLAG, 0, NULL, DA.CAL_DAY) AS CAL_DAY_NEW,
               DA.CAL_FLAG,
               DA.CAL_CODE
          FROM XIR_MD.TCALENDAR_DATES DA
         WHERE DA.CAL_CODE = 'CHINA_IB'
           AND DA.CAL_DAY >= '2022-01-01'
           AND DA.CAL_DAY <= '2022-12-31') A

查询结果