53080


説明がしにくいため先に例を記載します。

table_a

[code]

    id
    a
    b
    c
    d
    
[/code]

table_b

[code]

    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
    
[/code]

欲しい結果

[code]

    id  count
    a   2
    b   2
    c   1
    d   1
    
[/code]

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は含まれる必要はありません。（含まれても良いです。）

どのような情報でも助かりますのでお願い致します。


CC BY-SA 4.0
2019-02-27T14:24:10.627
53072
2019-02-28T02:05:47.563
2019-02-28T01:11:11.137
13022
13022
question
sql
テーブルに紐づく属性が一致する数を数えたい


`id`ごとのレコード数をまず計算しておき、2つの`id`間で`c1`,`c2`が同じレコード数を両者のレコード数と比較してはどうでしょうか。

[code]

    WITH n AS (SELECT a_id,count(*) FROM table_b GROUP BY a_id)
    SELECT id1 id, count(id2)
    FROM (SELECT b1.a_id id1, b2.a_id id2
          FROM table_b b1
          JOIN table_b b2
            ON b1.c1=b2.c1 AND b1.c2=b2.c2
          JOIN n n1
            ON b1.a_id=n1.a_id
          JOIN n n2
            ON b2.a_id=n2.a_id
          WHERE n1.count=n2.count
          GROUP BY id1, id2, n1.count
          HAVING n1.count=count(*)) t
    GROUP BY id1;
    
[/code]


CC BY-SA 4.0
2019-02-28T02:05:47.563
53080
2019-02-28T02:05:47.563
9464
53072
answer