テーブルに紐づく属性が一致する数を数えたい
説明がしにくいため先に例を記載します。
table_a
id
a
b
c
d
table_b
a_id c1 c2
a x 1
a y 2
b x 1
b y 2
c x 1
c y 2
c z 3
d x 1
d y 3
欲しい結果
id count
a 2
b 2
c 1
d 1
table_b内でa_id毎にc1とc2がありますが、これらが完全に一致するレコードをカウントしたいです。(idがaの場合、table_bには2レコードあるので、その2レコードのc1がxでc2が1, c1がyでc2が2の2つのレコードが完全に一致するidをカウントしたい。)
(例だと自分も含んでa(自分)とbが一致するので2としていますが、含まなくても良いです)
※データベースはredshiftですので、DB依存がない一般的なSQLで実現できると助かります。
※実際にはtable_aにはかなりの数のレコードが含まれます。
※table_bで、a_idに紐づくレコードも最大100以上存在します。
※table_bのa_idとc1ではユニークになりません。(a_id, c1, c2でユニークになります)
※table_aにあり、table_bのa_idに含まれない場合にはcountは含まれる必要はありません。(含まれても良いです。)
どのような情報でも助かりますのでお願い致します。