スケジュール(実行順序,起動時刻)の妥当性をSQLで確認する

はじめに

こんにちはキャスレーコンサルティングの岩井です。

今回はSQLについて書いてみます。

とある問題の調査のために考えたSQLの紹介です。
Tipsみたいな感じになりますが…

スケジュール(実行順序,起動時刻)データの妥当性を確認するためにSQLを考えたのですが、
思ったより頭を使いました(→おもしろかった)ので書いてみようかと。

スケジュールデータの仕様

あるシステムのスケジューラーはDB(Oracle)に
プログラムの「実行順序」と「起動時刻」を保持しています。
(「スケジュールTBL」に保持していることとします)

例1.スケジュールTBL

実行順序 起動時刻 プログラム
1 0900 A
2 1200 B
3 1500 C

スケジューラーは「実行順序」昇順に、
「起動時刻」を迎えたプログラムを実行していくといったものです。
「実行順序」は、前のプログラムが終了しないと次のプログラムは実行できません。

 09:00にプログラムAを実行
 12:00にプログラムBを実行
 15:00にプログラムCを実行
 18:00にプログラムDを実行
 21:00にプログラムEを実行

その他に
・「実行順序」は1以上の整数
・「実行順序」の重複はNG
・「実行順序」の欠番はOK
・「起動時刻」の重複はOK
・「起動時刻」に設定できるのは00:00~23:59(24h以内に完結すること)
・「実行順序」の昇順≠「起動時刻」の昇順 でもOK
 (「実行順序」と「起動時刻」の逆転OK…日跨ぎOK)

といったルールがあり、下記例2.のような設定が可能です。

例2.スケジュールTBL

実行順序 起動時刻 プログラム
1 2000 A
6 2300 B
11 0200 C
16 0200 D
21 0500 E

 20:00にプログラムAを実行
 23:00にプログラムBを実行
 02:00にプログラムCを実行
 プログラムC終了後にプログラムDを実行
 05:00にプログラムEを実行

とある問題

上記のようなシンプルなスケジューラーですが、データのチェックが行われておらず…

結果、以下のようなデータが設定されている可能性があり、
スケジューラーがまともに動けない(かもしれない)という問題がありました。
&そんなスケジューラーが数十の環境にリリースされている…

例3.スケジュールTBL

実行順序 起動時刻 プログラム
1 0900 A
2 1500 B
3 1200 C

(問題点)
プログラムCは12:00に実行したいようだが
12:00には実行されず、プログラムBを15:00に実行した後に実行される
→ 実行順序の誤り?起動時刻の誤り?

例4.スケジュールTBL

実行順序 起動時刻 プログラム
1 1200 A
6 1800 B
11 0000 C
16 0600 D
21 1300 E

(問題点)
プログラムEは、プログラムD実行後の13:00に実行したいようだが、
プログラムDを06:00に実行した後、13:00前に実行される12:00には実行されず
(スケジュールが24h以上設定されている(開始:12:00、終了:(翌日)13:00))12:00には実行されず
→ 実行順序の誤り?起動時刻の誤り?

 

仕様をまとめると

そんなNGデータが設定されているかもしれないスケジューラー、
1環境ずつデータを直接確認するのではなく、SQLで1発で OK/NG を判定しようと考えました。

ということで、データの仕様(あるべき形)をまずは考えました。

結果、

・「実行順序」昇順=「起動時刻」昇順 であればOK
・「実行順序」と「起動時刻」の逆転は1回までOK
 (ただし、実行順序MINの起動時刻>実行順序MAXの起動時刻)

のいずれかを満たす場合はOK。

ということは、NGの条件は…

・「実行順序」と「起動時刻」の逆転が2回以上
・「実行順序」と「起動時刻」の逆転が1回 且つ 実行順序MINの起動時刻≦実行順序MAXの起動時刻

のいずれかを満たすということに。

SQLを考える

上記条件を満たすSQLを考えると…

「実行順序」昇順で並べたレコードと「起動時刻」昇順で並べたレコードが一致するか
を確認すれば、逆転の数がカウントできるのでは?

と最初考えましたが、それではうまくいかず…

結局、逆転をカウントするには

「実行順序」が昇順で次のレコードの「起動時刻」が
自身の「起動時刻」未満のレコード件数をカウント

すれば良いという結論に至りました。

で、どうすれば!?

実行順序 起動時刻 プログラム
     
1 0900 A
2 1500 B
3 1200 C
次の実行順序 次の起動時刻 次のプログラム
1 0900 A
2 1500 B
3 1200 C
     

上記のようにレコードをつなげれば「次の起動時刻」がわかります。

じゃあ、どうやってつなぐ?

「次の実行順序」=「実行順序」+1

のように見えますが、「実行順序」は連番ではないので結合要素としては使えません…

じゃ採番しなおしましょう!

実行順序 新実行順序
1 1
6 2
11 3

加えて、それぞれ余分なレコードが1件ずつ(MIN実行順序のレコードとMAX実行順序のレコード)ありますが…

取り除きましょう!

ということでSQLにすると…

SELECT
    COUNT(C.*) AS 逆転回数
FROM
    (
        SELECT – 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番して取得
            ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
            A.起動時刻 AS 起動時刻
        FROM
            スケジュールTBL A
        WHERE
            A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
    ) C,
    (
        SELECT – 実行順序MIN以外のレコードの起動時刻を実行順序昇順に採番して取得
            ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
            A.起動時刻 AS 起動時刻
        FROM
            スケジュールTBL A
        WHERE
            A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
    ) D
WHERE
    C.新実行順序=D.新実行順序 AND
    C.起動時刻>D.起動時刻

C … 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番(新実行順序)して取得 D … 実行順序MIN以外のレコードの起動時刻(次起動時刻)を実行順序昇順に採番(新実行順序)して取得

「新実行順序」は、CとDを結合するための値です。
CとDを結合し、「起動時刻」と「次起動時刻」を求め比較します。

例3.で考えると

C(例3)

新実行順序 起動時刻
1 2000
2 0000
3 0800
4 01200

D(例3)

新実行順序 起動時刻
1 0000
2 0800
3 01200
4 0400

C+D

新実行順序 C起動時刻 D起動時刻 C>D
1 2000 0000 TRUE
2 0000 0800 FALSE
3 0800 1200 FALSE
4 1200 0400 TRUE

→COUNT:2

と逆転が2回あることがわかります。

このSQLに
「逆転が1回 且つ 実行順序MINの起動時刻≦実行順序MAXの起動時刻」
という条件も踏まえたものが、以下になります。

SELECT
    ‘スケジュールデータに問題があります’
FROM
    (
        SELECT – 実行順序MAX以外のレコードの起動時刻を実行順序昇順に採番して取得
            ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
            A.起動時刻 AS 起動時刻
        FROM
            スケジュールTBL A
        WHERE
            A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
    ) C,
    (
        SELECT – 実行順序MIN以外のレコードの起動時刻を実行順序昇順に採番して取得
            ROW_NUMBER() OVER (ORDER BY A.実行順序) AS 新実行順序,
            A.起動時刻 AS 起動時刻
        FROM
            スケジュールTBL A
        WHERE
            A.実行順序<(SELECT MAX(B.実行順序) FROM スケジュールTBL B)
    ) D
WHERE
    C.新実行順序=D.新実行順序 AND
    C.起動時刻>D.起動時刻
HAVING
    COUNT(*)>=2 OR
    (
        COUNT(*)=1 AND
        (SELECT E.起動時刻 FROM スケジュールTBL E WHERE
        E.実行順序=(SELECT MIN(F.実行順序) FROM スケジュールTBL F))
        <=
        (SELECT G.起動時刻 FROM スケジュールTBL G WHERE
        G.実行順序=(SELECT MIN(H.実行順序) FROM スケジュールTBL H))
    )

いかがでしたでしょうか?

順序と時刻が適切に設定されているか

人が目で確認すれば簡単にわかりそうなものですが、
SQLにするとこんなにも長々としたものになります。

人間すげぇ!

とも思いますし

けど、SQLにおとせないものでもない

とも思いました。
そこが個人的に、おもしろかったところです。

ではでは

岩井 洋平
CSVIT事業部/インテグレーションテック部 岩井 洋平
入社4年目(中途)、金融系の客先常駐でお仕事してます。