Help us understand the problem. What is going on with this article?

基準日から何日後の日付、何日前の日付を生成するクエリ with lateral

まえがき

おもむろに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
;

image.png

基準日から何日前の日付を生成

サブクエリで基準日と基準日からある日数を引きこんだ値にリニアに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
;

image.png

あとがき

lateralは組み合わせを生み出すとき、大変便利だと思う。日付関連の処理はいろいろ頭使う。こういう処理を漸化的に表現できたら、面白そう。許容日を算出するファンクションを一つ用意するのかな。

はじめてコラム書いてみた。

コラム
新聞・雑誌などで、線で囲んだ、ちょっとした記事。囲み欄。

以上、ありがとうございました。

ukijumotahaneniarukenia
sqlが大好き。(oracle) WordPressはじめました。sql oracleやそれ以外について調べたこととか記していきます。 https://github.com/ukijumotahaneniarukenia
https://ukijumotahaneniarukenia.site/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした