SQL 前日分と相違があればアラート その2

いつもお世話になっております。

以前下記についてお伺いいたしました。
今回はその続編です。
http://oshiete.goo.ne.jp/qa/7772587.html

何卒ご教授ください。
よろしくお願いいたします。


例えば下記のようなテーブルがあるとします。
【運賃テーブル】
日付　　　出発　 到着　運賃
10月1日 東京 青森 8000円
10月1日 東京 大阪 5000円
10月1日 東京 福岡 8000円
10月1日 青森 大阪 6000円
10月1日 青森 福岡 9500円
10月1日 大阪 福岡 5000円
10月1日 大阪 青森 9800円
10月2日 東京 青森 null
10月2日 東京 大阪 5000円
10月2日 東京 福岡 8500円
10月2日 青森 大阪 6000円
10月2日 青森 福岡 9500円
10月2日 大阪 福岡 null
10月2日 大阪 青森 9000円
10月3日 東京 青森 8000円
10月3日 東京 大阪 5000円
10月2日 東京 福岡 8500円
10月2日 青森 大阪 6000円
10月2日 青森 福岡 9500円
10月2日 大阪 福岡 5500円
10月2日 大阪 青森 9000円

仮に運賃を日付で比較した場合（上記を水平展開した場合）
下記のようになります。
【運賃比較】
　　　　　　10月1日 10月2日 10月3日
東京 青森 8000円 ------ 8000円
東京 大阪 5000円 5000円 5000円
東京 福岡 8000円 8500円 8500円
青森 大阪 6000円 6000円 6000円
青森 福岡 9500円 9500円 9500円
大阪 福岡 5000円 ------ 5500円
大阪 青森 9800円 9000円 9000円


ここからが前回と違うのですが
上記の場合、nullがあったら、さらにその前日と
比較する方法を教えていただきたいのです。
※ 最悪、比較データがない場合、過去1週間前
　　までさかのぼる必要がでることもありえます。

つまり上記でいえば
10月3日の料金は前日値と比べ…

【運賃変更】
東京 青森　変更なし
東京 大阪　変更なし
東京 福岡　変更なし
青森 大阪　変更なし
青森 福岡　変更なし
大阪 福岡　変更あり
大阪 青森　変更なし
もしくは
大阪 福岡　変更あり
の様に、「大阪 福岡」に変更があったことを
アラート表示させたいのです。


【その他条件】
・このSQLコードは毎日回します
・前日にもデータがなく、前々日のデータと
　比較しなくてはならないこともあります。
　もしくは過去1週間ほどさかのぼらないと
　比較対象のデータがないこともあります。
・case文を使って【運賃比較】の様なテーブルを作り
　ローカルでVBA等で比較させることも考えましたが
　データが大量すぎて、その方法は使えませんでした。
・【運賃テーブル】の様な状態でデータをexportし
　それをローカルのExcelでピボットをかけるという方法も
　同様な理由で不可能でした。


何卒よろしくお願いいたします。

No.5 ベストアンサー 回答者： dda167 回答日時： 2012/11/24 15:28

>MAX(日付) OVER (PARTITION BY 出発, 到着, 年齢) AS 最新,

>LAG(運賃) OVER (PARTITION BY 出発, 到着, 年齢 ORDER BY 日付) AS 前日

上記の記述で大丈夫だと思います。

Good luck!

この回答へのお礼

dda167さん

おはようございます。

時間がなくて今週なかなか試すことができませんでしたが
おかげさまでデータはエラーなく戻って来ました。

データ的にも問題無さそうなので、無事解決とさせていただきます。

いつもいつもホントにありがとうございます。
また困った時には、ご助言いただければと思います。
よろしくお願いいたします。

お礼日時：2012/12/02 10:43

No.4 回答者： dda167 回答日時： 2012/11/24 10:25

アレンジしすぎです（笑）

SELECT 出発, 到着, 運賃, 特急, 特急料金
FROM (
SELECT 日付, 出発, 到着, 運賃, 特急, 特急+運賃 AS 特急料金,
MAX(日付) OVER (PARTITION BY 出発, 到着) AS 最新,
LAG(運賃) OVER (PARTITION BY 出発, 到着 ORDER BY 日付) AS 前日
FROM 運賃テーブル
WHERE 運賃 IS NOT NULL
) WHERE 日付 = 最新 AND 運賃 <> 前日;

でいかがでしょうか？

この回答へのお礼

dda167さん

度々ご回答いただきまして誠にありがとうございます。

なるほど、余計なものを加えすぎておりましたか。
すみません、勝手にアレンジし過ぎてましたね笑
ぜひぜひ週明けにさっそく試させて頂きます。


あとよろしければ下記、1点追加でお伺いしたいです。

先にコードを記載した際に、追記漏れがあったのですが
本来のコードは単純に「出発→到着」だけの単純な比較でなく
「年齢」というフィールドが基のテーブルに用意されてます（完全に失念してました）
（成人か（O18）、未成年（U18）かで料金が変わります）

つまり
　　　　　年齢 10月1日 10月2日 10月3日
東京 青森 O18 8000円 ------ 8000円
東京 青森 U18 4000円 4000円 4000円
東京 大阪 O18 5000円 5000円 5000円
東京 大阪 U18 2500円 2500円 2500円
東京 福岡 O18 8000円 8500円 8500円
東京 福岡 U18 4000円 ------ 4500円
青森 大阪 O18 6000円 6000円 6000円
青森 大阪 U18 3000円 3000円 3000円
青森 福岡 O18 9500円 9500円 9500円
青森 福岡 U18 4750円 4750円 4750円
大阪 福岡 O18 5000円 ------ 5500円
大阪 福岡 U18 2500円 2500円 2500円
大阪 青森 O18 9800円 9000円 9000円
大阪 青森 U18 4000円 ------ 4500円
の様に、Over18かUnder18かで料金が変わってくるため
同じ「出発→到着」でも、別々に比較が必要となります。

この場合、
MAX(日付) OVER (PARTITION BY 出発, 到着, 年齢) AS 最新,
LAG(運賃) OVER (PARTITION BY 出発, 到着, 年齢 ORDER BY 日付) AS 前日
の様に記述して良いのでしょうか？

よろしくお願いいたします。

お礼日時：2012/11/24 14:29

No.3 回答者： dda167 回答日時： 2012/11/24 08:36

SELECT 日付, 出発, 到着, 通常, 特急, (特急+料金) AS 特急料金

FROM 運賃テーブル

「料金」という項目は運賃テーブルに存在するのでしょうか？

上記のSQLを単体で動作させた場合、正常に終了するのでしょうか？

元の質問に特急料金がどうからむのか……

>原因としては「FROM 運賃テーブル」の部分であろうかと思われます。
>実際にはこのテーブルはもう少々複雑で、その部分で何らか私の
>コード記述ミスがあるのかもしれません。

そのコードを挙げることは難しいのでしょうか？

「ORA-12801」以外のメッセージは出ていないのでしょうか？

私自身は「ORA-12801」というエラーメッセージに遭遇したことはありません。
（サポート行きかなとも思ってしまいます）

問題点を整理して新規の質問として挙げなおしたほうがいいかもしれませんね。
（その場合は、Oracleのバージョンもお忘れなく）

>ちなみにエラーの内容ですが・・・

これエラーの内容ではなくて、対処方法ではないですか？

この回答へのお礼

dda167さん

おはようございます。

>「料金」という項目は運賃テーブルに存在するのでしょうか？
>上記のSQLを単体で動作させた場合、正常に終了するのでしょうか？
>元の質問に特急料金がどうからむのか……

フィールド名に記述ミスがあるまま
投稿したことに気付かずに大変失礼いたしました。

ただしくは下記の通りです。
単体では問題なく動きます。

【運賃テーブル】
日付　　　出発　 到着　運賃　特急
10月1日 東京 青森 8000円 1500円
10月1日 東京 大阪 5000円 1000円
10月1日 東京 福岡 8000円 1500円
10月1日 青森 大阪 6000円 1000円
10月1日 青森 福岡 9500円 1500円
10月1日 大阪 福岡 5000円 1000円
10月1日 大阪 青森 9800円 1500円

のようになっており、「FROM 運賃テーブル」は
SELECT 日付, 出発, 到着, 運賃, 特急, (特急+運賃) AS 特急料金
FROM 運賃テーブル
になっております。


>これエラーの内容ではなくて、対処方法ではないですか？
>「ORA-12801」以外のメッセージは出ていないのでしょうか？

こちらも失礼いたしました。コピペができてませんでした。
ORA-12801: error signaled in parallel query server P080
ORA-01722: invalid number


>そのコードを挙げることは難しいのでしょうか？

実際のフィールド名とは少々違いますが、記述したコードは下記のとおりです。
SELECT tbl2.出発, tbl2.到着, tbl2.運賃, tbl2.特急, tbl2.特急料金
FROM (
SELECT tbl1.出発, tbl1.到着, tbl1.運賃, tbl1.特急, tbl1.特急料金
MAX(tbl1.日付) OVER (PARTITION BY tbl1.出発, tbl1.到着, tbl1.運賃, tbl1.特急, tbl1.特急料金) AS 最新,
LAG(tbl1.運賃) OVER (PARTITION BY tbl1.出発, tbl1.到着, tbl1.運賃, tbl1.特急, tbl1.特急料金 ORDER BY tbl1.日付) AS 前日
FROM (
　　　SELECT 日付, 出発, 到着, 運賃, 特急, (特急+運賃) AS 特急料金
　　　FROM 運賃テーブル
　　　) tbl1
WHERE 運賃 IS NOT NULL
)tbl2 WHERE tbl2.日付 = tbl2.最新 AND tbl2.運賃 <> tbl2.前日;

ここまでで色々試しまして分かっていることは
・tbl1は単体で動く
・tbl2も、tbl1の様に「特急料金」というフィールドを使わず
　シンプルに「FROM 運賃テーブル」と書き換えるとエラーなく動く
　（tbl1は「特急料金」というフィールドが欲しいがために作ったテーブルです）


>私自身は「ORA-12801」というエラーメッセージに遭遇したことはありません。
>（サポート行きかなとも思ってしまいます）
>問題点を整理して新規の質問として挙げなおしたほうがいいかもしれませんね。
>（その場合は、Oracleのバージョンもお忘れなく）

理解いたしました。ありがとうございます。
引き続き何かございましたらご指導ください。
よろしくお願いいたします。

お礼日時：2012/11/24 09:50

No.2 回答者： dda167 回答日時： 2012/11/17 15:29

追記

11gR2からLag関数でignore nullsオプションが使えるようです。

私は10gR2までしか使ったことないので……（自宅では10gXE）

No.1 回答者： dda167 回答日時： 2012/11/17 14:54

NULLを無視すればOKかと

-- 運賃変更その１
SELECT 出発, 到着,
CASE WHEN 運賃 <> 前日 THEN '変更あり' ELSE '変更なし' END AS 変更
FROM (
SELECT 日付, 出発, 到着, 運賃,
MAX(日付) OVER (PARTITION BY 出発, 到着) AS 最新,
LAG(運賃) OVER (PARTITION BY 出発, 到着 ORDER BY 日付) AS 前日
FROM 運賃テーブル
WHERE 運賃 IS NOT NULL
) WHERE 日付 = 最新;

-- 運賃変更その２
SELECT 出発, 到着
FROM (
SELECT 日付, 出発, 到着, 運賃,
MAX(日付) OVER (PARTITION BY 出発, 到着) AS 最新,
LAG(運賃) OVER (PARTITION BY 出発, 到着 ORDER BY 日付) AS 前日
FROM 運賃テーブル
WHERE 運賃 IS NOT NULL
) WHERE 日付 = 最新 AND 運賃 <> 前日;

技術者なら「データが大量」という表現はなしにしませんか？
数万件が大量だという人もいれば、
数億件を普通に扱っている人もいますから。

この回答への補足

dda167さん

お返事遅くなりまして申し訳ありません。
結果としては上手くデータが取得できませんでした。

原因としては「FROM 運賃テーブル」の部分であろうかと思われます。
実際にはこのテーブルはもう少々複雑で、その部分で何らか私の
コード記述ミスがあるのかもしれません。

実際のテーブルは・・・
【運賃テーブル】
日付　　　出発　 到着　運賃　特急
10月1日 東京 青森 8000円 1500円
10月1日 東京 大阪 5000円 1000円
10月1日 東京 福岡 8000円 1500円
10月1日 青森 大阪 6000円 1000円
10月1日 青森 福岡 9500円 1500円
10月1日 大阪 福岡 5000円 1000円
10月1日 大阪 青森 9800円 1500円

のようになっており、「FROM 運賃テーブル」は
SELECT 日付, 出発, 到着, 通常, 特急, (特急+料金) AS 特急料金
FROM 運賃テーブル
になっております。

そのため「 (特急+料金) AS 特急料金」の部分を省くと
データは取り出せるのですが、上記テーブルのまま回すと
「Parallel query error ORA-12801」
となり、データが取得できませんでした。

私の説明が適切ではないかもしれませんが
なにかヒントになる事がお分かりになりましたら
何卒ご教授ください。よろしくお願いいたします。

ちなみにエラーの内容ですが・・・
■Oracleの初期化パラメータ・ファイル内でPROCESSES
　およびSESSIONSパラメータの値を増やします。

　並列処理を行うためには、次のいずれかが必要です。
・リモート表がレンジ・パーティション化されている。
・数値列のヒストグラム情報が利用可能である。
・数値の索引または主キーが存在する。

少々難解で理解がおよびませんでした。

補足日時：2012/11/23 10:25

この回答へのお礼

dda167さん

ご回答頂きましてありがとうございます。
しかも二通りのご回答までいただきまして。

本日は試せる環境におりませんので
月曜日に是非試させていただきまして
改めて結果お伝えしたいと思います。


>技術者なら「データが大量」という表現はなしにしませんか？

ごもっともです。言葉足らずで失礼いたしました。
「Excelで作業するにはデータ量が多すぎる」という意味です。
（もとのデータが100万行を超えておりました）

引き続きよろしくお願いいたします。

お礼日時：2012/11/17 16:58