まえがき
おもむろにlateral久しぶりに触ってみた。ハンディに環境ほしいとき、oracle livesqlとっても便利。sql developerぐらいの機能があったら、もっと便利ですごく嬉しい。😆
環境
Now running on Oracle Database 19c
基準日から何日後の日付を生成
サブクエリで基準日と基準日にある日数を足しこんだ値にリニアに1から連番を足した値を用意。
コラム
日次処理等使用するため、保持期間日数を各テーブルごとにマスタ等で用意して、レコード更新日時からの経過日数がマスタの保持期間日数を超過する際は、テーブルデータを削除するようなロジックとかと考え方は通じるものはあると思う。採番系カラムを保持しているテーブルはこの仕組みの管理下にあるんじゃないかなって思っている。運用開始するまえに全業務の紐付きを把握しているレベルでないと、最初の設定はできないと思う。最初に設定する人はすごいと思う。日次処理が終わらない場合は保持期間日数を短くしたり。思ったより早く終了したら、保持期間日数を長くしたり。メンテできるようにテーブル設計しておくものなんであろう。あっ、旅人算みたいな間隔なのかな。2人（マスタとトラン）の距離（日数=日次実行日時-レコード更新日時）が縮まる。縮まり方を早めたり、ゆるくしたり。距離が0になったら、削除。日次なので、業務終了後、毎日動く。これがコケてたりすると、番号被ったりして大変。
そういった状況とか想定してテストデータを作成するときに使えるのではないかと思っている。😆
削除されるべきものが削除されて、削除されてほしくないものは削除されずに残っていることを確認するとか。
WITH sub AS (
SELECT
LEVEL AS rn
,sysdate + level as self
,(sysdate +30 ) + level as other
,(sysdate +60 ) + level as another
FROM
dual
CONNECT BY
LEVEL <= 4
)SELECT
dense_rank()over(order by s1.rn,s2.rn,s3.rn) as seq
,s1.rn as grp1
,s1.SELF
,s2.rn as grp2
,s2.OTHER
,s3.rn as grp3
,s3.another
FROM
sub s1
,LATERAL(SELECT s2.* FROM sub s2 WHERE s1.self <= s2.other -30) s2
,LATERAL(SELECT s3.* FROM sub s3 WHERE s1.self <= s3.another -60) s3
ORDER BY
s1.RN,s2.RN,s3.RN
;
基準日から何日前の日付を生成
サブクエリで基準日と基準日からある日数を引きこんだ値にリニアに1から連番を足した値を用意。
コラム
ある卸の会社があったとして、製品や食品等管理する際に、製品・食品ごとにいつまでに物流倉庫に入庫して、いつまでには物流倉庫から出庫するといった日付を算出するため、マスタに許容日数を管理していることがある。入庫許容期間日数・出庫許容期間日数とか呼んだりする。業務ないし用途ごとにこの許容日の算出の仕方はいろいろだと思うが、「何日までに何する」といったバリエーションはいくつかあると思う。出庫許容日から何日超過してますよーと警告したり、あと何日で許容日に到達しますよーと警告したり、食品だったら、賞味期間日数をマスタに保持して、入庫時から起算して賞味期間あとこれだけですよーと警告したりすることがある。
そういった状況とか想定してテストデータを作成するときに使えるのではないかと思っている。😆
警告されるべきものが警告対象として扱われ、警告されてほしくないものは警告対象外として扱われていることを確認するとか。
WITH sub AS (
SELECT
LEVEL AS rn
,sysdate + level as self
,(sysdate -30 ) + level as other
,(sysdate -60 ) + level as another
FROM
dual
CONNECT BY
LEVEL <= 4
)SELECT
dense_rank()over(order by s1.rn,s2.rn,s3.rn) as seq
,s1.rn as grp1
,s1.SELF
,s2.rn as grp2
,s2.OTHER
,s3.rn as grp3
,s3.another
FROM
sub s1
,LATERAL(SELECT s2.* FROM sub s2 WHERE s1.self-30 <= s2.other) s2
,LATERAL(SELECT s3.* FROM sub s3 WHERE s1.self-60 <= s3.another) s3
ORDER BY
s1.RN,s2.RN,s3.RN
;
あとがき
lateralは組み合わせを生み出すとき、大変便利だと思う。日付関連の処理はいろいろ頭使う。こういう処理を漸化的に表現できたら、面白そう。許容日を算出するファンクションを一つ用意するのかな。
はじめてコラム書いてみた。
コラム
新聞・雑誌などで、線で囲んだ、ちょっとした記事。囲み欄。
以上、ありがとうございました。