- ベストアンサー
SQLで運賃変更をアラートする方法
- SQLコードを使用して、運賃テーブルの前日と比較し、運賃変更があればアラートを表示する方法について教えてください。
- 運賃テーブルには、出発地、到着地、日付、運賃の情報があります。運賃を日付ごとに比較し、運賃変更がある場合にアラートを表示したいです。
- また、比較データがない場合や、比較対象のデータが過去1週間前までさかのぼる必要がある場合についても考慮する必要があります。
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
>MAX(日付) OVER (PARTITION BY 出発, 到着, 年齢) AS 最新, >LAG(運賃) OVER (PARTITION BY 出発, 到着, 年齢 ORDER BY 日付) AS 前日 上記の記述で大丈夫だと思います。 Good luck!
その他の回答 (4)
- dda167
- ベストアンサー率76% (55/72)
アレンジしすぎです（笑） 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 前日 の様に記述して良いのでしょうか？ よろしくお願いいたします。
- dda167
- ベストアンサー率76% (55/72)
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のバージョンもお忘れなく） 理解いたしました。ありがとうございます。 引き続き何かございましたらご指導ください。 よろしくお願いいたします。
- dda167
- ベストアンサー率76% (55/72)
追記 11gR2からLag関数でignore nullsオプションが使えるようです。 私は10gR2までしか使ったことないので……（自宅では10gXE）
- dda167
- ベストアンサー率76% (55/72)
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さん ご回答頂きましてありがとうございます。 しかも二通りのご回答までいただきまして。 本日は試せる環境におりませんので 月曜日に是非試させていただきまして 改めて結果お伝えしたいと思います。 >技術者なら「データが大量」という表現はなしにしませんか？ ごもっともです。言葉足らずで失礼いたしました。 「Excelで作業するにはデータ量が多すぎる」という意味です。 （もとのデータが100万行を超えておりました） 引き続きよろしくお願いいたします。
補足
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パラメータの値を増やします。 並列処理を行うためには、次のいずれかが必要です。 ・リモート表がレンジ・パーティション化されている。 ・数値列のヒストグラム情報が利用可能である。 ・数値の索引または主キーが存在する。 少々難解で理解がおよびませんでした。
![その他([地域情報] 旅行・レジャー) イメージ](https://gazo.okwave.jp/okwave/spn/images/related_qa/c932_3_thumbnail_img_sm.png)
お礼
dda167さん おはようございます。 時間がなくて今週なかなか試すことができませんでしたが おかげさまでデータはエラーなく戻って来ました。 データ的にも問題無さそうなので、無事解決とさせていただきます。 いつもいつもホントにありがとうございます。 また困った時には、ご助言いただければと思います。 よろしくお願いいたします。