SQLで行き詰まっています

Question

前任者からの引き継ぎが足らず、困っております。
SQLに不慣れなもので、どなたかご教示下さい。

4つのテーブルからなるデータベースからデータを抽出したいのですが、解決出来ずにおります。

テーブル構成は
1.通話履歴テーブル
CALL_ID(ユニーク)
顧客ID(=顧客テーブル.顧客ID)
アクション名
通話日時
対応優先度

2.顧客テーブル
顧客ID(ユニーク)
電話番号
住所
氏名
etc

3.電話番号履歴管理テーブル
登録日
電話番号
区分ID(=区分管理テーブル.区分ID)

4.区分管理テーブル
区分ID(ユニーク)
区分

という構成で、ここから、
顧客テーブル.電話番号, 通話履歴テーブル.アクション名, 通話履歴テーブル.対応優先度, 区分管理テーブル.区分

を一覧取得する必要があります。
これまでは単純にJOINするだけで実現出来ておりましたが、電話番号履歴管理テーブルに登録したデータに重複するケースがあり、条件をつけてデータを抽出しなければなりません。

例えば
2015/01/01にXという区分で09011112222という電話番号が登録されており、かつ、
2016/01/01にYという区分で09011112222という電話番号が登録されています

この時に2015/07/01に09011112222と通話した記録でデータを抽出するときに、 区分管理テーブル.区分はXで抽出しなければなりません。
つまり、通話した時点での区分を抽出したいのです。
このような組み合わせが多数あり、お手上げです。

抽出対象期間が2015/06/01～2016/01/01の通話履歴だとしたら、どのようなSQLなら、実現できますでしょうか？

Comments

[ricy]

要件を理解しずらいため、エクセルにして、どのデータを抽出したいといった風に質問されると回答しやすいです。このサービスは添付不可だったらすみません・・・
サブクエリをつかうか、動的なSQL（pl/sql等）を使えば実現できそうな気はしますが・・・・あと質問に関係のなさそうなカラムは記載なくてよいかと

Answer 1

2015/01/01にXという区分で09011112222という電話番号が登録されており、かつ、

2016/01/01にYという区分で09011112222という電話番号が登録されています
抽出対象期間が2015/06/01～2016/01/01の通話履歴

この条件で抽出する場合、期待結果は
・2015/06/01～2015/12/31までの09011112222の通話履歴には区分X
・2016/01/01の09011112222の通話履歴にだけ区分Y
が表示されれば良いということでしょうか？

恐らく相関サブクエリにせざるをえないので、抽出行が多いと遅くなるかも知れませんがこんな感じでどうでしょうか？

SELECT 顧客テーブル.電話番号
 , 通話履歴テーブル.アクション名
 , 通話履歴テーブル.対応優先度
 , kubun.区分 
FROM 通話履歴テーブル
LEFT JOIN 顧客テーブル ON 通話履歴テーブル.顧客ID = 顧客テーブル.顧客ID
LEFT JOIN (
 SELECT 区分管理テーブル.区分, 電話番号履歴管理テーブル.電話番号
 FROM 電話番号履歴管理テーブル
 LEFT JOIN 区分管理テーブル ON 電話番号履歴管理テーブル.区分ID = 区分管理テーブル.区分ID
 WHERE 電話番号履歴管理テーブル.電話番号 = 通話履歴テーブル.電話番号
 AND 電話番号履歴管理テーブル.登録日 <= 通話履歴テーブル.通話日時
 ORDER BY 電話番号履歴管理テーブル.登録日 desc
 LIMIT 1
) kubun ON kubun.電話番号 = 通話履歴テーブル.電話番号
WHERE 通話履歴テーブル.通話日時 BETWEEN '2015/06/01' AND '2016/01/01'

所で、

1. ユーザ毎に通話履歴は複数あると思いますが、CALL_IDや通話日時は出力しなくても良いのでしょうか？
2. 2015/06/01に通話履歴があるのに、電話番号履歴管理テーブル に2015/06/01以前にその電話番号の登録が無いといった可能性は無いですよね？（このようなデータがあった場合、上記SQLでは区分に空白(NULL)が出力されます）

Answer 2

これでいかがでしょうか？

select
	サブクエリ.電話番号,
	サブクエリ.アクション名,
	サブクエリ.対応優先度,
	区分管理テーブル.区分
from
	区分管理テーブル,
	電話番号履歴管理テーブル,
	(
		select 
			顧客テーブル.電話番号,
			通話履歴テーブル.アクション名,
			通話履歴テーブル.対応優先度,
			max(電話番号履歴管理テーブル.登録日) 最新登録日
		from 
			通話履歴テーブル,
			顧客テーブル,
			電話番号履歴管理テーブル
		where 通話履歴テーブル.顧客ID = 顧客テーブル.顧客ID
		and   顧客テーブル.電話番号 = 電話番号履歴管理テーブル.電話番号
		and   通話履歴テーブル.通話日時 between 2015/06/01 and 2016/01/01
		and   通話履歴テーブル.通話日時 >= 電話番号履歴管理テーブル.登録日
		group by
			顧客テーブル.電話番号,
			通話履歴テーブル.アクション名,
			通話履歴テーブル.対応優先度
	) サブクエリ
where サブクエリ.電話番号 = 電話番号履歴管理テーブル.電話番号
and   サブクエリ.最新登録日 = 電話番号履歴管理テーブル.登録日
and   電話番号履歴管理テーブル.区分ID = 区分管理テーブル.区分ID
/

条件で通話履歴テーブル.通話日時 >= 電話番号履歴管理テーブル.登録日、max(登録日)で最新の登録日を取得。
その後、電話番号履歴管理テーブルを結合させています。

電話番号履歴管理テーブルに同一登録日、別区分IDがあったら2行出ます(´・ω・`)

Answer 3

＞単純にJOINするだけで実現出来て
これがどのようなものかによりますが、
2015/06/01～2016/01/01の条件を入れればいいのではないかと思いますが。
複数出てくるようなら電話番号履歴管理テーブルのselect でselect distinct ~にするといいですね。