お仕事でSQLを書いてるのだけど、 〇〇日というDATE型のカラムをよく扱うことがある。
日付なんてのはなにをするにしてもつきまとうし、 それを扱うデータの方が独立して存在していることからも 扱うことに不思議はない。
ただ、今回はこの1列の◯◯日を期間として扱う必要がでてきた。
便宜上、ここではある特定の商品の 「販売日」（発売日）を扱う必要が出てきたとして、 私がやりたいことはこの販売日を、 販売開始日～販売終了日の期間にしたい、という感じだ。
実際のデータは以下のようなイメージ
商品テーブル ROWNUM,商品名,販売日 ,価格 1 ,商品A ,2019/10/01,100 2 ,商品A ,2019/10/10,150 3 ,商品A ,2019/10/20,200 4 ,商品A ,2019/11/01,250
このデータを以下のように変換するのが目的
商品テーブル ROWNUM,商品名,販売開始日,販売終了日,価格 1 ,商品A ,2019/10/01,2019/10/09,100 2 ,商品A ,2019/10/10,2019/10/19,150 3 ,商品A ,2019/10/20,2019/10/29,200 4 ,商品A ,2019/10/30,2019/11/09,250
この時、私はROWNUM=1から2を参照させるにはどうすればいいかを考えた。 ROWNUM=2の開始日-1がつまり価格100の最終販売日であるからだ。
そこで、商品テーブルに対し、 商品テーブルを内部結合で結合してしまおうと考えた。
元々の商品テーブルをテーブルAとし、 結合するものをテーブルBとした。 テーブルBは結合の際にROWNUMを「ROWNUM-1」とする。
そしてテーブルA、テーブルBともに 「ROWNUM || 商品名」の列を作ってこれを結合キーとするとどうだろう。
どうだろうといっても以下のような感じ。
テーブルA 商品テーブル 結合キー,商品名,販売日 ,価格 1商品A ,商品A ,2019/10/01,100 2商品A ,商品A ,2019/10/10,150 3商品A ,商品A ,2019/10/20,200 4商品A ,商品A ,2019/11/01,250
テーブルB 結合キー,商品名,販売開始日,価格 商品A ,商品A ,2019/10/01,100 1商品A ,商品A ,2019/10/10,150 2商品A ,商品A ,2019/10/20,200 3商品A ,商品A ,2019/10/30,250
こうすることによって結合キー「1商品A」は テーブルAの販売日は10/01 テーブルBの販売日は10/10
これで、元々やりたかった次レコードの値を取得することができる。 想定のひょうを作成する場合は取得時に-1してあげれば前日を取得するので 販売終了日の期間を作成できる。ということだ。
※実際に書いたときは、 商品名に対して販売日のソートを行った上でROWNUMを振ったので、 SELECT文では ROW_NUMBER() OVER (ORDER BY 商品名,販売日 ) と記述した。（テーブルBでは最後に-1） このままだと、結合キーとして利用できない為 結合キー用にもう一個別にインラインビューを作成する必要があった。
つまり、実際には
元々のテーブル 内部結合：元々のテーブルに商品Aの結合キーを渡すテーブルA 外部結合：テーブルB
というややこしすぎるSQLになった。
ちなみにテーブルBが外部結合なのは、 テーブルAで存在する結合キー「4商品A」が テーブルBに存在しない場合に日付最大値等の ダミー値を格納するため、外部結合にしている。
これを書ききった時の達成感は凄まじかった。 高校生の時、山から花火大会を見ながらBBQをしないか？ と高校生のノリで思いBBQ道具を持って山を登った事がある。 あの時の達成感に非常に似ていた。
感慨に耽け続けるわけにもいかないので、 嬉しさを堪えつつも私はウキウキで上席に書いたSQLを報告した。
上席の返事は一言だった。
「LEAD関数でよくない？」
LEAD関数？それはなんでしょうか？ 読む(READ)んですか？と真顔で聞き返したが、 渋い顔をされたので、大人しくググってみた。
LEAD関数を調べてみると、先程感慨に耽けたBBQのときのことを思い出した。
あの時、山に登った時の達成感はすごかった。 しかし、いかんせん企画が当日だったため、 急遽道具はすべて100均で揃えていた。
あのときは炭の火力が弱すぎて肉が全く焼けなかったのを思い出した。
私は自分の書いたSQLに別れを告げてLEAD関数を使った。
今の世界において無知より重い罪は無い。
※LEAD関数は私が並びROWNUMを採番した時の要領で、 次のレコードの値を取得してくれる賢い子です。
インラインビュー2つもつけて次レコード取得するやつ、おりゅ？
Comments