需求： 日历表 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
查询结果
