SQLで運賃変更をアラートする方法
- SQLコードを使用して、運賃テーブルの前日と比較し、運賃変更があればアラートを表示する方法について教えてください。
- 運賃テーブルには、出発地、到着地、日付、運賃の情報があります。運賃を日付ごとに比較し、運賃変更がある場合にアラートを表示したいです。
- また、比較データがない場合や、比較対象のデータが過去1週間前までさかのぼる必要がある場合についても考慮する必要があります。
- ベストアンサー
SQL 前日分と相違があればアラート その2
いつもお世話になっております。 以前下記についてお伺いいたしました。 今回はその続編です。 http://okwave.jp/qa/q7772587.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でピボットをかけるという方法も 同様な理由で不可能でした。 何卒よろしくお願いいたします。
- ice1982
- お礼率96% (24/25)
- Oracle
- 回答数5
- ありがとう数6
- みんなの回答 （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パラメータの値を増やします。 並列処理を行うためには、次のいずれかが必要です。 ・リモート表がレンジ・パーティション化されている。 ・数値列のヒストグラム情報が利用可能である。 ・数値の索引または主キーが存在する。 少々難解で理解がおよびませんでした。
関連するQ&A
- SQL 前日分と相違があればアラート
いつもお世話になっております。 表題の件について教えてください。 使用SQL：Oracle 例えば下記のようなテーブルがあるとします。 【運賃テーブル】 日付 出発 到着 運賃 10月1日 東京 青森 8000円 10月1日 東京 大阪 5000円 10月1日 東京 福岡 8000円 10月1日 青森 大阪 6000円 10月1日 青森 福岡 9500円 10月1日 大阪 福岡 5000円 10月1日 大阪 青森 9800円 10月2日 東京 青森 8000円 10月2日 東京 大阪 5000円 10月2日 東京 福岡 8500円 10月2日 青森 大阪 6000円 10月2日 青森 福岡 9500円 10月2日 大阪 福岡 5000円 10月2日 大阪 青森 9000円 仮に運賃を日付で比較した場合、下記のようになります。 【運賃比較】 10月1日 10月2日 東京 青森 8000円 8000円 東京 大阪 5000円 5000円 東京 福岡 8000円 8500円 青森 大阪 6000円 6000円 青森 福岡 9500円 9500円 大阪 福岡 5000円 5000円 大阪 青森 9800円 9000円 この場合、値上がりがあったら、下記の様に 値上がり（値下がり）があった路線について一目で分かる SQLコードはどの様にかけば良いのか教えてください。 【運賃変更】 出発 到着 10月1日 10月2日 東京 福岡 8000円 8500円 大阪 青森 9800円 9000円 もし上記が可能であるならば、【運賃比較】テーブルの様に データを選択剃る方法についてもご教授ください。 よろしくお願いいたします。
- ベストアンサー
- Oracle
- SQLローダーについて
カンマ区切りで""で囲まれたCSVファイルがあります。 例） "1","東京","おとこ","山田太郎","","","" このデータをOracle 9iデータベースにinsert、updateするプログラムをVBで書いたのですが パフォーマンスが悪いのでSQLローダーでやりたいと考えています。 更新テーブルはひとつなのですが、"東京"なら1、"大阪"なら2、"福岡"なら3というようにデータを突っ込む前に比較・変換する必要があります。 このような場合でも、SQLローダーって使えるんでしょうか？ よろしくお願いします。
- ベストアンサー
- その他(データベース)
- 以下のSQLについて
AccessMDBで、以下のテーブルがあったとします。 テーブル１ ID,NAME 1,おなまえ 2,名前 3,なまえ テーブル２ ID,DB1_ID,NAME,開始日,終了日 1,1,4月,2006/4/1,2006/4/30 2,1,6月,2006/6/1,2006/6/30 3,2,2006年度,2006/4/1,2007/3/31 このとき、以下のように出力したいのですが ID(オートナンバーと仮定),NAME,月,開始日,終了日 1,おなまえ,4月,2006/4/1,2006/4/30 2,おなまえ,6月,2006/6/1,2006/6/30 3,おなまえ,(null),(null),(null) 4,名前,2006年度,2006/4/1,2007/3/31 5,名前,(null),(null),(null) 6,なまえ,(null),(null),(null) 試しにクェリーを作ってみた所、(null)の値を手入力しなければいけないのですが nullをいれさせるようにするには、どのようにしないといけないでしょうか？ ＃テーブルをいじることはできず、SQLのみで対処することを考えています。 SELECT テーブル1.NAME, テーブル2.NAME, テーブル2.START, テーブル2.END FROM テーブル1 LEFT JOIN テーブル2 ON テーブル1.ID=テーブル2.DB1_ID UNION SELECT テーブル1.NAME, テーブル2.NAME,テーブル2.START,テーブル2.END FROM テーブル1; よろしくお願いします。
- ベストアンサー
- その他(データベース)
- SQLサーバのテーブルのデータを<NULL>にしたい！
もともとテーブルにデータをダミーで入力して、そのデータを削除し<NULL>に変更したいのですが・・・ 確か、Ctrl＋Enterで<NULL>を設定できたと思うのですが、うまくいきません・・・ <NULL>に変更したいのは、レコードの一箇所のみです。 初歩的な質問ですが、よろしくお願いします。
- ベストアンサー
- SQL Server
- 項目数分レコードにしたい
テーブルには住所履歴が格納されていて 氏名 履歴1 履歴2 履歴3 ------ ------- ------- ----- aさん 北海道 東京 沖縄 bさん 岩手 東京 cさん 大阪 兵庫 福岡 というデータがある場合、 下のような結果に表示させるSQLを教えてください。 氏名 履歴 ------ ------- aさん 北海道 aさん 東京 aさん 沖縄 bさん 岩手 bさん 東京 cさん 大阪 cさん 兵庫 cさん 福岡 よろしくおねがいします。
- 締切済み
- オフィス系ソフト
- SQL文で
すみません。質問させてください。 A,B,C,D,Eと5個のフィールドで構成されたテーブルtableがあるとします。 tableにはデータがすべて格納され(NULLなし）ています。 ですが、フィールドA,C,DについてA,C,Dとも同じデータが格納される ことがあります。 その重複したデータ以外を取得したいのですが （要するにフィールドA,C,Dでユニークのデータのみ取得) SQL文でどのように書けばいいかわかりません。 よろしくお願いいたします
- 締切済み
- その他（プログラミング・開発）
- SQL文の書き方
イベント情報をキーワード検索できるページを制作しており、下記テーブルからデータを取得したいのですが、日付関係の条件指定の方法が分かりません。 下記のような条件で取得したい場合どのようにすればよいでしょうか？（キーワード検索の部分はわかります） テーブル event date_s：開始日（Ymd形式の8桁 もしくは md形式の4桁） date_s_year：開始日の年（Y形式の4桁 もしくは NULL値） date_s_month：開始日の月（m形式の2桁） date_s_day：開始日の日（d形式の2桁） date_e：終了日（Ymd形式の8桁 もしくは md形式の4桁 もしくは NULL値） date_e_year：終了日の年（Y形式の4桁 もしくは NULL値） date_e_month：終了日の月（m形式の2桁 もしくは NULL値） date_e_day：終了日の日（d形式の2桁 もしくは NULL値） ※複数日に渡って行われるイベントには終了日が入っていて、単日のイベントの終了日はNULL値です。 ※その年だけに行われる単発のイベントには年と月と日がすべて入っています。毎年決まった日に行われるイベントには月と日のみが入っており、年はNULL値です。 ・検索対象は今日から1年先までのデータ ・開催中（開始日が本日以前で終了日が本日以降）のイベント → 開始日が今日に近いイベントという順番で並べたい。 MySQL：5.1.22-rc PHP：5.2.5 説明が分かりにくいかもしれませんが、アドバイスをいただければ幸いです。
- ベストアンサー
- MySQL
- SQLとデータベースについて？
すいません質問させてください。 ２つのデータベースがあります。 データベース名が -------------- SC CM -------------- の２つです。 そこに複数のテーブルがあります。 そこで質問なのですが、SCのデータベースのテーブル名AAAとCMのデータベースのテーブル名がBBBの 中から列名の店コードが違う物だけ抽出したいです。 ------------------------------- データーベースSC テーブルAAA 店コード、店名、店地域 123,あ,東京 231,い,大阪 312,う,京都 111,え,福岡 ------------------------------ ------------------------------- データーベースCM テーブルBBB 店コード、店地域 123,東京 111,福岡 ------------------------------ 上の様な造りになっています。 抽出結果 ----------------------------- 店コード、店名、店地域 231,い,大阪 312,う,京都 ---------------------------- 私なりの考えなのですが SELECT * FROM AAA,BBB where AAA.店コード <> BBB.店コード このSQL文を改変して出来ると思われますか？ SCのデータベースからCMのデータベースを比べることは、出来ますか？ よろしくお願いします。
- ベストアンサー
- SQL Server
- ACCESS２０００のSQLについて
ACCESS2000のSQL文について質問です。 K情報テーブル：(支店(テキスト型),所属(テキスト),氏名(テキスト),ログ(テキスト),抽出件数(数値),処理日(日付型)) 支店 所属 氏名 ログ 抽出件数 処理日 ----------------------------------------- 関東 埼玉 太郎 A 50 2009/02/25 関西 大阪 次郎 B 15 2009/04/01 関東 埼玉 太郎 A 10 2009/03/05 関西 大阪 次郎 B 5 2009/04/06 東北 青森 三郎 C 2 2000/01/02 東北 青森 三郎 A 2 2000/01/02 ・ ・ ・ ----------------------------------------- 以上のテーブル情報を、SQL文にて抽出したいのですが、抽出条件が私には難解で解決の見通しが立ちません。ご教授をお願いします。 ***条件*** [ログ] = "A" のみ抽出。 現在日から過去3ヶ月前までのデータのみ抽出。 [氏名]ごと[ログ]の総合計。 [氏名]ごとの[抽出件数]の総合計。 [ログ]の総合計の上位30件を降順で。 順位にNo.をふる。 上記の条件で抽出したデータをを下記の作業テーブルに出力したと思っております。 作業テーブル：順位,所属,氏名,ログ件数,抽出件数 宜しくお願いします。
- ベストアンサー
- その他（プログラミング・開発）
- 12月3日から有効、都区内→札幌市内の乗車券
12月3日から有効の都区内から札幌市内行きの乗車券（経由：東京・新幹線・八戸・海峡線・静狩）を購入したとします。 その乗車券で3日に仙台でも盛岡でも八戸でも途中下車し、4日に八戸から先に乗車しようとすると東北本線はもうありません。 この場合の取り扱いとしては正しいのはどれでしょうか？ ・過渡期の特例として青い森鉄道に乗車できる。 ・東北新幹線（八戸→新青森）、奥羽本線（新青森→青森）にこのまま乗車できる。運賃差額の払い戻しなし。 ・同上だが、八戸→青森を比較して、在来線経由（96.0キロ1,620円）と新幹線経由（85.7キロ1,450円）の差額170円払い戻し。 ・単なる経路変更扱いとなり、やはり払い戻しなし。
- ベストアンサー
- 路線・駅・電車
お礼
dda167さん おはようございます。 時間がなくて今週なかなか試すことができませんでしたが おかげさまでデータはエラーなく戻って来ました。 データ的にも問題無さそうなので、無事解決とさせていただきます。 いつもいつもホントにありがとうございます。 また困った時には、ご助言いただければと思います。 よろしくお願いいたします。