中間テーブルを2つまたがるリレーションがある時適切な検索ヒットをしたい。

Question

###前提

• Table A
• Table B
• A, Bの中間テーブルであるTable AB
• Table C
• B, Cの中間テーブルであるTable BC
• Table Bのレコードを２個あるいは3個のみがTable Cの１つのレコードとリレーションを

持つように登録している。

中間テーブル以外にはPKがあり(ここでは便宜上A_id, B_id, C_idとします)
中間テーブルは例えばTable ABならA_id, B_idというカラムのみを持ち、
Unique(A_id, B_id)と設定されています。

###実現したいこと
A_idを決めた時(例えばA_id=1とした時)
それとリレーションのあるTable AB→Table B→Table BC→Table Cとたどり、
最終的にリレーションのあるTable Cの数をカウントしたいです。

追記
例えば以下の例では

A_id=1に関しては、Table CのC_id=500, 501, 502の3つを検索ヒットし
C_id=503, 504は検索ヒットしたくありません。
(A_id=1はB_id=33, 34とリレーションがないため)

A_id=2に関しては、Table CのC_id=500の1つを検索ヒットし
C_id=501, 502, 503, 504は検索ヒットしたくありません。
(A_id=2はB_id=32, 33, 34とリレーションがないため)

A_id=3に関しては、Table CのC_id=503, 504, 505の3つを検索ヒットし
C_id=500, 502は検索ヒットしたくありません。
(A_id=3はB_id=30, 32とリレーションがないため)

###発生している問題・エラーメッセージ
追記
単純に中間テーブルを用いたSQL文を以下のように考えました。

SELECT COUNT(DISTINCT(C_id)) FROM BC WHERE B_id in (
 SELECT B_id FROM AB WHERE A_id = 1
)

しかし、この自分で考えたSQL文では
以下のようなリレーションがある時、A_id=2を満たすBのB_idを取得して、
そのB_idを含むTable Cという単純な条件で検索をすると(31, 502)のレコードも
ヒットしてしまいます。
しかし、Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので、
ヒットしたくありません。

Table A

A_id	value
1	foo
2	baz
3	bar

Table B

B_id	value
30	-1
31	-2
32	-3
33	-4
34	-5

Table AB

A_id	B_id
1	30
1	31
1	32
2	30
2	31
3	31
3	33
3	34

Table C

C_id	value
500	Alice
501	Bob
502	Chris
503	Dylan
504	Emily
505	Franc

Table BC

B_id	C_id
30	500
31	500
30	501
32	501
31	502
32	502
31	503
33	503
31	504
34	504
33	505
34	505

どのようにすれば、私が望むように検索ヒットすることができるでしょうか？
このテーブルにこだわる必要はありませんので、
そもそもテーブルの構築があまり良くないと言ったアドバイスでも結構です。
ご指導ご鞭撻よろしくお願いします。

###補足情報
PostgreSQL (version9.5.7)

###追記
専門的なことをしているので、抽象的に書いたほうがわかりやすいと思いましたが、
かえってわかりにくいという印象を与えてしまったようなので、
具体的なテーブルも下に書いておきます。

化学構造のデータを扱っていまして

A: 化学構造を含むライブラリ(データベース)名
B: 化学構造自体のレコード
AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
BC: Cの新規の化学構造のために組み合わせたBの化学構造

となっております。

Comments

[karamarimo]

「Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので」とありますが、どのA_id由来かはどうやって判断するのでしょうか？

[acre_maker]

コメントありがとうございます。saziさんへのコメントのような方法で識別できるかなと思ったのですが、難しいでしょうか？

[A.Ichi]

A_id=1で作成されたTableBCはどの値でしょうか？関係ない事ですが、TableBのマイナス値は何を表しているのでしょうか？

[acre_maker]

ご質問ありがとうございます。A_id=1はB_id=30,31,32とリレーションがあるので、TableBCのC_id=500, 501, 502の計6個を作成しています。ただし、A_id=2に関するレコードが先に作られた場合C_id=500のレコード2個はすでに作成されているのでそちらに関しては追加では作りません。Table Bのマイナス値は抽象的なTableを作るためにかいただけなので特に意味はありません。すいません。実際のテーブルでは化学構造を扱うための文字列であるSMILESキーと呼ばれる値が入っています。

[A.Ichi]

上記より察するにTableAが追加された場合にTableBCへ追加は、既に同キーが存在していない場合と言う事でしょうか。さらに選択する場合には、追加したA_id以外のidでは対象外にする事になりますでしょうか。

[acre_maker]

＞上記より察するにTableAが追加された場合にTableBCへ追加は、既に同キーが存在していない場合と言う事でしょうか。→はい。その通りです。

[acre_maker]

＞さらに選択する場合には、追加したA_id以外のidでは対象外にする事になりますでしょうか。→すいません。選択とはどういう意味でしょうか？他のidからも呼び出されることはあります。

[A.Ichi]

Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので、 ヒットしたくありません。の由来とは作成のA_id事ではないかどおもいまして

[acre_maker]

そうですね。作成時のみわかるので本来は作成時に何かしらの情報を残すべきだったと思っています。

[A.Ichi]

そうであれば、Table BCに作成のA_idを加える事で、SELECTでの排他条件に加える事ができるのではと考ました。

[acre_maker]

そういうのがあるのですね。排他制御というものでしょうか？勉強してみます。

Answer 1

Table BCのレコード(31, 502), (32, 502)はA_id=1由来で登録したレコードなので

このことが分かる条件が無い限り無理でしょう。
c_idが識別可能なルールで採番されているなら条件として使用できますが、
そうでなければ、table cにその識別が必要かと思います。

追記

以下の構造で十分な気がします。

TableA(A_id,Value)
TableB(B_id,Value,A_id[])
TableC(C_id,Value,A_id[])
TableBC(B_id,C_id)

※BCの組み合わせがあるので、BC追加。

この構造であれば、目的の件数取得は以下になります。

select count(*) from TableC where A_id @> array[1]

配列の個数をA_idと対比させ固定するなら以下のようになります。

select count(*) from TableC where A_id[1]=1
--A_id[]をBoolean型とするなら
select count(*) from TableC where A_id[1]

追記２

以前提案していた構造を今回のもので表すと

TableA(A_id,Value)
TableB(B_id,Value,B_id[],A_id[])

となります。
TableBはBとCを合わせたもので、B_id[]が分子構造の構成要素になります。
元々のTableBの識別が必要なら、属性項目を持たせた方が良いでしょう。
基本となる構成要素も検索対象にするならB_id[]にはB_idも含めます。
含めないならB_id[]は空にしておけば検索対象にはなりません。

で、B_id[]のGinインデックスを作成し、

where B_id[]@>array[30,32]　-- B_id[]は便宜上の表現

のようにすれば良いのではないかと。

Comments

[acre_maker]

ご回答ありがとうございます。

>このことが分かる条件が無い限り無理でしょう。

(1)A_id=2の条件よりTable ABからB_id=30, 31 ((a)とします)を検索する。
(2)(a)の条件とTable BCからC_id=500, 501, 502を検索する。
(3)Table BC上で502に対してB_id=31, 32をみつけるけど、
32は(a)に含まれていないから502は除外する。
としたら行けそうな気がしましたけどSQL文では難しい工程でしょうか？

>c_idが識別可能なルールで採番されているなら条件として使用できますが、
>そうでなければ、table cにその識別が必要かと思います。

例えば、Table BCにA_idを外部キーとしてカラムを追加するといった
解決策がよいでしょうか？

[karamarimo]

つまり検索したいのは「A_id=2と関係があるBすべてに関係があるC」ということですか？

[sazi]

A_idは２件だけなのでしょうか？３件以上の場合はその排他の条件はどうなるのでしょう？

[acre_maker]

>karamarimo様
おっしゃるとおりになります。

>sazi様
A_idは3件以上になる予定です。
私の理解不足のせいか、2件が3件以上になって、
考慮しなければならない点がみえていません。
排他の条件とはそちらを指しているのでしょうか？
より詳しく教えていただけたら助かりす。

[sazi]

>(3)Table BC上で502に対してB_id=31, 32をみつけるけど、32は(a)に含まれていないから502は除外する。

その条件がid=3に含まれるとどうなりますか？
id毎に自身以外のIDにないものの件数を取得したいということ？

[acre_maker]

お返事ありがとうございます。

id=3に含まれる場合もヒットしてもいいです。
あくまで注目しているA_idとリレーションがあるBから構成されていたらいいです。

テーブルのデータ数を増やして各A_idの時
どうヒットしてほしいか追記してみました。
お暇な時にご確認ください。

[sazi]

>A: 化学構造を含むライブラリ(データベース)名
>B: 化学構造自体のレコード
>AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
>C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
>BC: Cの新規の化学構造のために組み合わせたBの化学構造
それぞれのライブラリごとに化学構造が幾つあるかカウントしたいということなのですね。
それ以外の目的としてはそのデータがどのライブラリからの由来であるかどうかでしょうから、
最終的に参照できれば良いかと思うのですが、ライブラリも検索条件になりますか？
検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

[acre_maker]

ご回答ありがとうございます。

＞それぞれのライブラリごとに化学構造が幾つあるかカウントしたいということなのですね。
＞それ以外の目的としてはそのデータがどのライブラリからの由来であるかどうかでしょうから、
＞最終的に参照できれば良いかと思うのですが、ライブラリも検索条件になりますか？
＞検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

おっしゃるとおりですね。
各ライブラリから作成した新規の化学構造が何個あるか確認したいです。

その後、新規の化学構造を以前アドバイスいただいたように別のライブラリに対して部分構造検索する
と言った流れになっています。
(話がそれますがこちらを先に作らないと前アドバイスいただいた式によるインデックスが試せません)

＞ライブラリも検索条件になりますか？
ライブラリは基本的に検索条件にはなりません。
A_id = 1, 2, 3, ...のいずれかで絞り込むだけですね。

＞検索対象としての頻度が低いならBやCに配列として持たせても十分だと思いますが。

メインは上にかいた部分構造検索を行うため、
基本的に確認などで検索するだけなのでおっしゃるとおり頻度は低いです。

配列として保存するならTable BCを

B_id | C_id |
----------------------
[30, 31] | 500
[30, 32] | 501
[31, 32] | 502
[31, 33] | 503
[31, 34] | 504
[33, 34] | 505

と保存することが考えられますがこちらはあまり好ましくありませんか？
これなら2,3つの組み合わせどちらにも対応できそうですが。

お時間あるときにお答えいただけると助かります。

[acre_maker]

追記ありがとうございました！
自身で紙の上でテーブルを書いてみて意図が理解できました。

追記２でご提示してもらっているものがしっくりきました。

＞基本となる構成要素も検索対象にするなら
はい。質問には書いていませんでしたが、
基本構成1つからなるものも含めるにこしたことはないのでこちらを採用します。

ARRAY_LNEGTH(B_id[ ], 1)を使えばいくつの構成要素からなるかで
絞り込みもできますしよさそうですね。

長々とありがとうございました！

[sazi]

構成要素からその構成を参照する用途がある場合は、unnest(B_id[])のように配列を行に直してから、結合するのが効率良いかと思います。
また、この構造が親子での２階層であればよいのですが、多階層で、階層を辿る必要があるような場合は、自己結合用の項目を（親のB_id)を持つと共に再帰問い合わせ(WITH RECURSIVE)を用い、B_id[]に含まれるレベルを揃えるなどの工夫が必要になることが予想されます。

[sazi]

>自己結合用の項目を（親のB_id)
これは、十分ではないし、B_id[]で肩代わりできるので不要でした。

Answer 2

A: 化学構造を含むライブラリ(データベース)名

B: 化学構造自体のレコード
AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
BC: Cの新規の化学構造のために組み合わせたBの化学構造

A:B＝１：多
C:B＝１：多
Aが含まれるCをカウントするということですね。
Aが含まれるBを抽出し、そのBが含まれるCをカウントするというイメージでしょうか。
効率化を考えなければ、単純にサブクエリーを利用するのが考えやすそうです。

Answer 3

質問に記載のデータ構造に不足しているのは、A_id = 1 の場合にABテーブルから得られるB_idの組(30,31,32)が(30,31),(30,32),(31,32)の組み合わせとならなければいけないという意味付けです。

B_id 2つにより C_id が特定できるという情報を持っているのはBCテーブルですので、BCテーブルの定義を下記とすることで特定が可能になります。

BC (B_id1, B_id2, C_id)

上記定義に変更した場合、下記のように取得できるのではないかと思います。

SELECT C_id FROM BC
INNER JOIN AB AB1 ON BC.B_id1 = AB1.B_id
INNER JOIN AB AB2 ON BC.b_id2 = AB2.b_id
WHERE AB1.A_id = AB2.A_id AND AB1.A_id = 1;

---

追記：
せっかく具体例をご提示いただいたのですが、上手な方法は思いつきませんでした。
後々のことを考えると列を増やすのは確かに得策ではないと感じましたので
もともとのデータ構造で実現するとしたら、ということで下記のように考えてみました。
もしかしたら、組み合わせの列挙方法がご想定のものとは異なるかもものになっているかもしれません。

SELECT BC1.c_id FROM BC BC1
INNER JOIN BC BC2 ON BC1.C_id = BC2.C_id AND BC1.B_id < BC2.B_id
 LEFT JOIN BC BC3 ON BC1.C_id = BC3.C_id AND BC2.B_id < BC3.B_id
INNER JOIN AB AB1 ON BC1.B_id = AB1.B_id
INNER JOIN AB AB2 ON BC2.B_id = AB2.B_id
 LEFT JOIN AB AB3 ON BC3.B_id = AB3.B_id
WHERE (BC3.c_id IS NOT NULL OR BC1.c_id NOT IN (
      SELECT BC1.c_id FROM BC BC1
      INNER JOIN BC BC2 ON BC1.C_id = BC2.C_id AND BC1.B_id < BC2.B_id
      INNER JOIN BC BC3 ON BC1.C_id = BC3.C_id AND BC2.B_id < BC3.B_id
   ))
  AND AB1.A_id = AB2.A_id
  AND
  (
      (BC3.B_id IS NOT NULL AND AB3.B_id IS NOT NULL AND AB1.A_id = AB3.A_id)
   OR (BC3.B_id IS     NULL AND AB3.B_id IS NULL)
  )
  AND AB1.A_id = 1;

Comments

[acre_maker]

ご回答ありがとうございます。

2つの時はおっしゃるSQL文でうまくとりだすことができそうですね！

少しわかりにくいですが
＞Table Bのレコードを２個あるいは3個のみがTable Cの１つのレコードとリレーションを
持つように登録している。

という条件で３個の場合があります。
この場合を考慮するとどうなりますかね？
この場合を考慮するために現在のテーブルみたいにしていたのですが
それだとまたもとに戻ってしまいますしね。

[退会済みユーザー]

BCテーブルのB_id列を3つに増やすか、3つ組の関連テーブルを増やして同様のことをすればよいかと思います。
2つの場合、3つの場合はUNIONで結合する形になります。

ご質問の内容は抽象化されており、A_id、B_id、C_idの関連がわかりませんので、実ケースでのよりよいデータの持ち方はすぐには思いつきませんでした。

[acre_maker]

回答ありがとうございます。

＞BCテーブルのB_id列を3つに増やすか、3つ組の関連テーブルを増やして同様のことをすればよいかと思います。
＞2つの場合、3つの場合はUNIONで結合する形になります。

2つと3つの場合で別々にテーブルを持つ必要があるということなんですね。
一緒にまとめたかったですが、それが最適ならそのようにする必要がありそうですね。

＞ご質問の内容は抽象化されており、A_id、B_id、C_idの関連がわかりませんので、実ケースでのよりよいデータの持ち方はすぐには思いつきませんでした。

かなり専門的なことをしているので抽象化したのですが返ってわかりにくいでしたかね？
すいません。

化学構造のデータを扱っていまして

A: 化学構造を含むライブラリ(データベース)名
B: 化学構造自体のレコード
AB: 複数のライブラリが同じ化学構造を含むことがあるためこのような形でリレーション
C: Bの化学構造のうちの2あるいは3個を組み合わせた新規の化学構造
BC: Cの新規の化学構造のために組み合わせたBの化学構造

となっております。

[acre_maker]

追記ありがとうございました！

なんとかできるんじゃないかなと思っていましたが
やはり相当複雑なSQL文になりますね。
テーブルの構造を見直してみます。

なにはともあれありがとうございました！