外出自粛中でも楽しく過ごす!QAまとめ>>

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

以前下記についてお伺いいたしました。
今回はその続編です。
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でピボットをかけるという方法も
 同様な理由で不可能でした。


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

このQ&Aに関連する最新のQ&A

A 回答 (5件)

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


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

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

Good luck!
    • good
    • 0
この回答へのお礼

dda167さん

おはようございます。

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

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

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

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

アレンジしすぎです(笑)



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

でいかがでしょうか?
    • good
    • 0
この回答へのお礼

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

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


FROM 運賃テーブル

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

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

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

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

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

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

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

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

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

これエラーの内容ではなくて、対処方法ではないですか?
    • good
    • 0
この回答へのお礼

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

追記



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

私は10gR2までしか使ったことないので……(自宅では10gXE)
    • good
    • 0

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パラメータの値を増やします。

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

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

補足日時:2012/11/23 10:25
    • good
    • 0
この回答へのお礼

dda167さん

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

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


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

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

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QSQLで違うテーブルの値を比較して値に差があるレコードを抽出したいので

SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいのですがヒントをいただけないでしょうか。
下に例を作ってみました。(テキストに貼りなおしてもらうと見易くなると思います)
<Aテーブル>
A1A2A3A4A5
------- ------- ------- ------- ----------
XXXXXXX XX1ABC32009/05/08
WWCWWCW WW2CCB12008/03/21
DDDDDDD DD1JPN52007/08/08
GGGGGGG GX9SOX21977/01/04
FFFFFFF USJNPB32001/09/11


<Bテーブル>
B1B2B3B4B5
------- ------- ------- ------- ----------
XXXXXXX XX1ibichaoshimu2002/07/05
XXXXXXX XX1takeshiokada2005/07/15
XXXXXXX XX1kamoshu1857/09/25
WWCWWCW WW2waowao2008/10/22
DDDDDDD DD1uihhh2006/06/30
DDDDDDD DD1jojoj x5xx1999/09/09
DDDDDDD DD1momohara2005/03/07
DDDDDDD DD1itaiu-2003/12/22
DDDDDDD DD1komanogoal2007/04/26
GGGGGGG GX9 damerecord2009/11/14
FFFFFFF USJ ikitai1995/08/15
FFFFFFF USJ sstebuspi2004/01/05
FFFFFFF USJ bbpp2009/08/23

A1とB1は主キー、A2とB2は副キーです。
Aテーブルの「A4」カラムにはBテーブルのレコード数を持っています。(主キー、副キーが同じものの)
しかし、よくよく見るとAテーブルの「GGGGGGG」のA4には「2」のはずなのにBテーブルには実際にレコードは1つしかありません。
こういう状態になってしまっているのを割り出したいのですがどのようにA4とBテーブルで数があってないものを抽出できるでしょうか。
よろしくお願いいたします。

SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいのですがヒントをいただけないでしょうか。
下に例を作ってみました。(テキストに貼りなおしてもらうと見易くなると思います)
<Aテーブル>
A1A2A3A4A5
------- ------- ------- ------- ----------
XXXXXXX XX1ABC32009/05/08
WWCWWCW WW2CCB12008/03/21
DDDDDDD DD1JPN52007/08/08
GGGGGGG GX9SOX21977/01/04
FFFFFFF USJNPB32001/09/11


<Bテーブル>
B1B2B3B4B5
------- ------- ------- ------- ----------
XXXXXXX XX1ibichaoshimu200...続きを読む

Aベストアンサー

SELECT A.* FROM Aテーブル A
INNER JOIN
TABLE(SELECT B1,B2,COUNT(*) SU
FROM Bテーブル GROUP BY B1,B2) B
ON A.A1=B.B1 AND A.A2=B.B2
AND A.A4<>B.SU

または

WITH B(B1,B2,SU) AS
(SELECT B1,B2,COUNT(*) SU
FROM Bテーブル GROUP BY B1,B2)
SELECT A.* FROM Aテーブル A
INNER JOIN B ON A.A1=B.B1
AND A.A2=B.B2 AND A.A4<>B.SU

こんなのでどうでしょうか?
後者の方が標準的(他のDBシステムでも
使える可能性が高い)かと思います。

QCASE文のエラーについて

下記のように打つと「行1でエラーが発生しました。:
ORA-00905: キーワードがありません」というエラーが表示されます。どうすればいいでしょうか?

SELECT ENAME AS 名前, SAL AS 給与, CASE SAL WHEN SAL <= 1100 THEN SAL * 1.055 WHEN SAL <=1400 THEN SAL * 1.05 WHEN SAL <=3000 THEN SAL * 1.0 ELSE SAL*1.045 AS 昇給率 FROM EMP;

Aベストアンサー

2カ所誤りがあります。

1つ目

CASEに対するENDがない。(#1の指摘の通り)

2つ目

"CASE"キーワードの次に比較要素を書いていますが、
そぼ場合、"WHEN"キーワードの次に書くのは、比較式でなく
比較要素になります。

case ○ when △ then ▲ when □ then ■ else ◆ end

case when ○=△ then ▲ when ○=□ then ■ else ◆ end
は、同じ意味のCASE式ですが、これをゴチャゴチャに使っているのが、
間違いです。

SELECT
 ENAME AS 名前,
 SAL AS 給与,
 CASE WHEN SAL <= 1100 THEN SAL * 1.055
    WHEN SAL <=1400 THEN SAL * 1.05
    WHEN SAL <=3000 THEN SAL * 1.0
    ELSE SAL*1.045
 END AS 昇給率
FROM EMP;

のように書くと問題なく使えるかと思います。

2カ所誤りがあります。

1つ目

CASEに対するENDがない。(#1の指摘の通り)

2つ目

"CASE"キーワードの次に比較要素を書いていますが、
そぼ場合、"WHEN"キーワードの次に書くのは、比較式でなく
比較要素になります。

case ○ when △ then ▲ when □ then ■ else ◆ end

case when ○=△ then ▲ when ○=□ then ■ else ◆ end
は、同じ意味のCASE式ですが、これをゴチャゴチャに使っているのが、
間違いです。

SELECT
 ENAME AS 名前,
 SAL AS 給与,
 CASE WHEN SAL <= 1100 THEN...続きを読む

Qユーザーへのディレクトリ権限付与について

WindowsXPのOracle9i環境にて、
まず、以下のようにしてユーザ「username」を作成しました。

create user username identified by Password default tablespace tab_space temporary tablespacetemp;
grant dba,resource,connect to username;

次にsysユーザにて、CREATE DIRECTORY FILE_PATH as 'd:\temp';
ディレクトリを作成しました。
そして、そのディレクトリに対して、以下のように権限付与しようとすると、「ユーザ"username"が存在しません。」エラーが発生します。
grant READ,WRITE ON directory dir to username;
また、grant READ,WRITE ON directory dir to public;
とすると、権限付与は成功しますが、ユーザusernameにはディレクトリ参照権限は与えられていませんでした。

何か間違っておりますでしょうか?
それとも何か手順で足らないものがあるのでしょうか?
ご教示いただければと思います。

WindowsXPのOracle9i環境にて、
まず、以下のようにしてユーザ「username」を作成しました。

create user username identified by Password default tablespace tab_space temporary tablespacetemp;
grant dba,resource,connect to username;

次にsysユーザにて、CREATE DIRECTORY FILE_PATH as 'd:\temp';
ディレクトリを作成しました。
そして、そのディレクトリに対して、以下のように権限付与しようとすると、「ユーザ"username"が存在しません。」エラーが発生します。
grant READ,WRITE ON di...続きを読む

Aベストアンサー

dba_usersに出てこない以上、そのユーザはデータベース上には存在しないかと思います。

そのパッケージソフトが、どのような構成をしているのかわかりませんが、パッケージ上のユーザとデータベースのユーザが別なのではないでしょうか。

パッケージ上のAというユーザが、データベース上のXというユーザにログインして動作するというのは、よくあるパターンです。

パッケージのサポートに確認するのが良いかと思います。

Qテーブルの差分をとる結合

TableAとTableBがあります。構造は同じです。
中身は下記のようだとします。
TableA      TableB
ID VALUE     ID VALUE
1 A       3 C
2 B       4 D
3 C       

差分結合結果を下記のようにとりたいのです。
1 A
2 B
4 D

良い方法をご存じでしたら、ご教授よろしくお願い致します。

Aベストアンサー

SQL Server 2005 以降であれば、EXCEPT が使えるので、TableA - TableB と TableB - TableA の結果を UNION してやれば良いかと。
http://codezine.jp/article/detail/1304?p=2

例) --------------------------------------------------------------
(SELECT * FROM TableA
EXCEPT
SELECT * FROM TableB)
UNION ALL
(SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA);
------------------------------------------------------------------

EXCEPT が使えないバージョンの場合は、 NOT EXISTS を使って差分を抽出してやれば良いでしょう。

例) --------------------------------------------------------------
SELECT * FROM TableA
WHERE NOT EXISTS (
SELECT 'X' FROM TableB
WHERE TableA.ID = TableB.ID AND TableA.VALUE = TableB.VALUE
)
UNION ALL
SELECT * FROM TableB
WHERE NOT EXISTS (
SELECT 'X' FROM TableA
WHERE TableA.ID = TableB.ID AND TableA.VALUE = TableB.VALUE
)
------------------------------------------------------------------

SQL Server 2005 以降であれば、EXCEPT が使えるので、TableA - TableB と TableB - TableA の結果を UNION してやれば良いかと。
http://codezine.jp/article/detail/1304?p=2

例) --------------------------------------------------------------
(SELECT * FROM TableA
EXCEPT
SELECT * FROM TableB)
UNION ALL
(SELECT * FROM TableB
EXCEPT
SELECT * FROM TableA);
------------------------------------------------------------------

EXCEPT が使えないバージョンの場合は、 NOT ...続きを読む


人気Q&Aランキング