SQLでN労働日後の日付を計算してみたため、記録のためやったことをまとめた。
前提となるテーブルは以下
CREATE TABLE CALENDAR (
DATE DATE NOT NULL,
IS_WORKING CHAR(1) NOT NULL WITH DEFAULT '',
PRIMARY KEY ( DATE ) )
CALENDARテーブルに日付と労働日かどうかのIS_WORKINGが定義されている。
1. 指定された日付のN労働日後の日付を取得するSQL
SELECT MAX(DATE) FROM (
SELECT DATE
FROM CALENDAR
WHERE
DATE > '基準日付'
AND IS_WORKING = '1'
ORDER BY DATE ASC
FETCH FIRST N+1 ROWS ONLY)
2. 直近5年分の日付一覧とそのN労働日後の日付のペアを取得するSQL
SELECT T1.DATE AS BEFORE_DATE, T2.DATE AS AFTER_DATE
FROM CALENDAR AS T1
INNER JOIN CALENDAR AS T2
ON (T2.IS_WORKING = '1'
AND T2.DATE > T1.DATE)
INNER JOIN CALENDAR AS T3
ON (T3.IS_WORKING = '1'
AND T3.DATE > T1.DATE
AND T3.DATE <= T2.DATE)
WHERE
T1.DATE <= CURRENT_DATE
AND T1.DATE > CURRENT_DATE - 5 YEARS
GROUP BY T1.DATE, T2.DATE
HAVING COUNT(T3.DATE) = N+1
ただし、このSQLが重すぎるため、私のローカルでは実行時間が455秒以上になってしまう。
いろいろ調べて、ROW_NUMBER()を利用した改善版SQLは以下
SELECT T21.DATE AS BEFORE_DATE, T22.DATE AS AFTER_DATE
FROM
(SELECT T01.DATE, MIN(T02.ROW_NUMBER) AS ROW_NUMBER
FROM CALENDAR AS T01
INNER JOIN (SELECT DATE, ROW_NUMBER() OVER (ORDER BY DATE ASC) AS ROW_NUMBER FROM CALENDAR WHERE IS_WORKING = '1') AS T02
ON T02.DATE > T01.DATE
GROUP BY T01.DATE) AS T21
INNER JOIN (SELECT DATE, ROW_NUMBER() OVER (ORDER BY DATE ASC) AS ROW_NUMBER FROM CALENDAR WHERE IS_WORKING = '1') AS T22
ON T21.ROW_NUMBER = T22.ROW_NUMBER - NWHERE
T21.DATE <= CURRENT_DATE
AND T21.DATE > CURRENT_DATE - 5 YEARS
こちらのSQLはローカルで1秒以内で終わっている。
