いつもお世話になっております。
以前下記についてお伺いいたしました。
今回はその続編です。
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ベストアンサー
- 回答日時:
>MAX(日付) OVER (PARTITION BY 出発, 到着, 年齢) AS 最新,
>LAG(運賃) OVER (PARTITION BY 出発, 到着, 年齢 ORDER BY 日付) AS 前日
上記の記述で大丈夫だと思います。
Good luck!
dda167さん
おはようございます。
時間がなくて今週なかなか試すことができませんでしたが
おかげさまでデータはエラーなく戻って来ました。
データ的にも問題無さそうなので、無事解決とさせていただきます。
いつもいつもホントにありがとうございます。
また困った時には、ご助言いただければと思います。
よろしくお願いいたします。
No.4
- 回答日時:
アレンジしすぎです(笑)
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 前日
の様に記述して良いのでしょうか?
よろしくお願いいたします。
No.3
- 回答日時:
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のバージョンもお忘れなく)
理解いたしました。ありがとうございます。
引き続き何かございましたらご指導ください。
よろしくお願いいたします。
No.1
- 回答日時:
NULLを無視すればOKかと
-- 運賃変更その1
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 日付 = 最新;
-- 運賃変更その2
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パラメータの値を増やします。
並列処理を行うためには、次のいずれかが必要です。
・リモート表がレンジ・パーティション化されている。
・数値列のヒストグラム情報が利用可能である。
・数値の索引または主キーが存在する。
少々難解で理解がおよびませんでした。
dda167さん
ご回答頂きましてありがとうございます。
しかも二通りのご回答までいただきまして。
本日は試せる環境におりませんので
月曜日に是非試させていただきまして
改めて結果お伝えしたいと思います。
>技術者なら「データが大量」という表現はなしにしませんか?
ごもっともです。言葉足らずで失礼いたしました。
「Excelで作業するにはデータ量が多すぎる」という意味です。
(もとのデータが100万行を超えておりました)
引き続きよろしくお願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 転職 長く続けられる好条件の求人でしょうか? 3 2023/07/12 18:45
- 電車・路線・地下鉄 東京から東北一周したい 8 2023/06/23 15:31
- その他(国内) 3時!夜中なのか早朝なのか微妙な時間、5時に出るならシャワー浴びてお茶淹れて神棚、仏壇 2 2022/05/20 03:58
- 電車・路線・地下鉄 JR西日本が特定運賃区間の運賃を10%程度値上げするそうですが、特定運賃廃止したら客減るかな? 4 2022/05/17 14:17
- 会社経営 家電メーカー(事業部別)売上ランキングTOP1位~10位 1 2023/05/10 20:23
- ボードゲーム マクドの値上げ。全てロシアが悪い。 3 2023/06/20 17:38
- 邦楽 サザンオールスターズの皆さんは、何故武道館まで時間がかかったのでしょうか 3 2022/06/03 21:06
- 新幹線 2030年開業の東北北海道新幹線東京始発のはやぶさの案を考えて見ました。いかがでしょうか? 途中停車 4 2023/02/11 19:26
- 転職 下記のような募集は、良い会社でしょうか?どう思いますか?入ってみたら良くない事が無いでしょうか? 4 2023/03/03 16:40
- 地図・道路 青森市から宮古市までの行程・距離感・時間について 4 2023/03/31 10:03
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
日付型のフィールドに空白を入...
-
SQL文で パラメータが少なすぎ...
-
Excleピボットでデータのない部...
-
Accessの日付時刻型から日付、...
-
エクセル-過去6カ月分の合計を...
-
BCPでCSV内の文字列をテーブル...
-
アクセスで月ごとの集計
-
質問
-
アクセス:既定値に土日含まず...
-
sql update で 抽出条件データ...
-
VBA 別シートの同じ日付の欄に...
-
Access dcount 複数条件
-
SQL SEREVER で選択した固定値...
-
【Accessで困っています...
-
DSumの計算式の入れ方
-
Accessでnow()で取り出した日付...
-
指定日付を起点にして最新日付...
-
クロス集計のユニオンクエリー...
-
前日分のデータだけをスケジュ...
-
【vba】日付の形式が勝手に変わ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
日付型のフィールドに空白を入...
-
SQL文で パラメータが少なすぎ...
-
Excleピボットでデータのない部...
-
VBA 別シートの同じ日付の欄に...
-
Accessの日付時刻型から日付、...
-
BCPでCSV内の文字列をテーブル...
-
アクセスで月単位の抽出
-
Excel→Accessへの日付データの...
-
指定日付を起点にして最新日付...
-
Access クエリで、レコードの無...
-
ACCESSの空白をカウントする
-
【vba】日付の形式が勝手に変わ...
-
【エクセル】指定した日付に一...
-
アクセス:既定値に土日含まず...
-
Accessで日付の比較がうまくい...
-
【Accessで困っています...
-
Accessのデータ型の日付/時刻型...
-
クロス集計のユニオンクエリー...
-
アクセスの昇順並び替えで空白...
-
Accessでnow()で取り出した日付...
おすすめ情報