重複した項目を取得するクエリの書き方

質問

Oracle 11g
オブジェクトブラウザーを使用して、SQL文を作成します。

SQLの書き方がよくわからず困っております。
どうか助けてください。お願いします。

・月毎の累計ポイントテーブル、
・月毎の請求金額テーブル

2つのテーブルを作成し、
顧客毎にポイント管理をしています。

月毎の累計ポイントテーブルの項目は、
顧客NO ｜ 年月 ｜ 累計ポイント
000001 201205 100
000002 201205 200
000003 201205 300

月毎の請求金額テーブルの項目は、
顧客NO ｜ 年月 ｜ 金額
000001 201206 1000
000002 201206 1000
000003 201206 1000

というイメージになります。

ポイント確定処理時には、
新たに、年月をプラス1した累計ポイントデータを作成します。

顧客NO ｜ 年月 ｜ 累計ポイント
000001 201205 100
000002 201205 200
000003 201205 300
↓追加
000001 201206 200
000002 201206 400
000003 201206 500

実現させたいことは、

月毎の請求金額テーブルの金額に
ポイント還元率(固定1％)をかけて切り捨て（1000円＝100ポイント）したポイントを
ポイント累計テーブルに該当月のレコードを追加していきたいというものです。

顧客NOが、累計ポイントテーブルに存在して、
請求金額データにない場合は、そのまま前月分の累計ポイントをコピーします。

逆もありで、
請求金額テーブルには、
累計ポイントテーブルにない顧客NOが存在する場合があります。

その場合は、新たに発生したポイントだけを累計ポイントとして追加します。

上記条件でのSQLの書き方がわからず困っております。

累計ポイントテーブルと請求金額テーブルの両方全部を足したら良いという
安直な考えのもと、FULL OUTER JOIN で実現させようとしましたが、
どうも間違えているようでして。

そのあたりも含め、どんな書き方が良いか教えていただけると助かります。

宜しくお願いします。

回答No.2

hogya · ベストアンサー

可読性のために、INTERSECTやMINUSで説明しましたが、OUTER JOINやEXISTSでも構いません。

要するに、前回の回答の1,2,3のようにデータを分けて、FULL OUTER JOINでまとめてもよいし、UNIONでまとめてもよいでしょう。
1,2,3の条件のデータはサブクエリーで行うか、WITH句で先頭に書く、ビューで定義しておくかなどもパフォーマスとSQLの複雑さとのバランスでどの方法を使うかでしょうね。

お礼

わかりやすい回答ありがとうございました。
1,2,3のクエリーをまず作って、結合の仕方を考えてみます。

とても勉強になりました。

回答No.1

hogya

仕様をまとめます。下記の３つの処理があるということでよかったでしょうか。

１．累計ポイントテーブルと請求金額テーブルの両方にデータのある顧客
　　→請求金額テーブルからポイントを計算し、翌月の累計ポイントデータを作成

２．累計ポイントテーブルだけにデータのある顧客
　　→前月分の累計ポイントをコピーして、翌月の累計ポイントデータを作成

３．請求金額テーブルだけにデータのある顧客
　　→新たに発生したポイントだけを累計ポイントとし、翌月の累計ポイントデータを作成

次に、それぞれの処理についてロジックを考えてみましょう。
いきなりコードを書き始めるよりも実際に紙にでも書いて決定した上で実装するとよいと思います。
そうしておくと整理がつくだけでなく、他の人に説明する時や、時間がたって仕様を確認する際にも助かります。

１．累計ポイントテーブルと請求金額テーブルの顧客NOをINTERSECTで取得すると、両方にあるデータのみが取得できるので、
　　取得した顧客に対してポイントを計算し、翌月の累計ポイントデータを作成すればよいでしょう。

２．累計ポイントテーブルと請求金額テーブルの顧客NOをMINUSで取得すると、片方にあるデータのみが取得できるので、
　　取得した顧客の累計ポイントをコピーして、翌月の累計ポイントデータを作成すればよいでしょう。

３．請求金額テーブルと累計ポイントテーブルの顧客NOをMINUSで取得すると、片方にあるデータのみが取得できるので、
　　取得した顧客の金額からポイントを計算し、翌月の累計ポイントデータを作成すればよいでしょう。

以上、これらの処理を１トランザクションでまとめて行うのか、処理毎に行うのか、はたまた顧客ごとに成功した分をコミットしていくのかは要件によって変わると思いますので一概にこうすればよいとは言えないと思います。

補足

ご丁寧にご回答、処理フローありがとうございます。
記載頂いたもので、間違いないです。

またINTERSECTや、MINUSは初めて知りました。
勉強になります。

申し訳ございません。
今、タイトルを間違えて記載してしまったことに気づきました。

「2つのテーブル間で重複行と重複しない行との両方を取得したい」
ということでした。

可能かどうかはわかりませんが、簡単に書くと、

ISNERT INTO 累計ポイントテーブル
（SELECT 顧客NO,処理年月,累計ポイント(1～3を一括計算)
FROM 累計ポイントテーブル FULL OUTER JOIN 請求金額テーブル ON)

というようなイメージです。

1.処理月の前月の累計ポイントと新たに発生したポイントを足したポイント
2.前月分の累計ポイント
3.新たに発生したポイント

をまとめて取得、1回のSQLで3つ処理を同時に行いたいのです。

今のところ、確定処理時のタイミングで
同様の他の処理をストアドプロシージャで処理しておりますので、
その中の1プロシージャとして作成するのが良いかと考えております。

具体的には、

請求金額テーブル......TB_SEIKYU
累計ポイントテーブル..TB_POINT

還元率はWK_RITU NUMBER　(処理時までに格納済)

請求年月.......SeikyuYM
顧客NOスタート.KokyakuNO_S
顧客NOエンド...KokyakuNO_E
累計ポイント...Point

とし、基本的に顧客NOは1～99999..の全顧客分を対象。

CREATE OR REPLACE PROCEDURE UPDATE
　(S_SeikyuYM IN TB_SEIKYU.SeikyuYM%TYPE,
　 S_KokyakuNO_S IN TB_SEIKYU.KokyakuNO%TYPE,
S_KokyakuNO_E IN TB_SEIKYU.KokyakuNO%TYPE）

IS
WK_RITU NUMBER := 1

------他のプロシージャ----------

PROCEDURE UPDATE_TB_POINT
　 (P_SeikyuYM IN TB_POINT.SeikyuYM%TYPE,
　 P_KokyakuNO_S IN TB_POINT.KokyakuNO%TYPE,
P_KokyakuNO_E IN TB_POINT.KokyakuNO%TYPE）
IS.....

というような流れです。

処理前に一旦累計ポイントテーブルは、
顧客NO毎に削除し、再作成を行います。

どの方法が良いかということは、難しいとは思いますが、

ISNERT INTO 累計ポイントテーブル
（SELECT 顧客NO,処理年月,累計ポイント(1～3を一括計算)
FROM 累計ポイントテーブル FULL OUTER JOIN 請求金額テーブル ON)

のSQL作成で止まっております。