Q:

Fetching intersection on self from a table

I have the following (simplified) table:
structure_id | hash_id
1              1
1              2
1              3
2              4
2              5
2              1
3              6
3              1
3              4

And I want to get the intersection of repeated hash ids as a result - which means  the following for this example:
- 1 2 3
1 3 1 1
2 1 3 2
3 1 2 3

Because 1 and 1 structure IDs share 3 records, 1 and 2 share 1 record, etc. SQL result would be:
id | intersected_id | count
1    1                3
1    2                1
1    3                1
2    1                1
2    2                3
2    3                2
3    1                1
3    2                2
3    3                3

It's worth mentioning that the table has about 500mln records, so the query must be as optimized as possible. How can I do that?
What I tried so far is self join:
SELECT t1.structure_id, COUNT(t1.hash_id) FROM table t1 INNER JOIN table t2 ON t1.structure_id != t2.strucutre_id AND t1.hash_id = t2.hash_id GROUP BY t1.structure_id;

But it doesn't work correctly - it finds repeated rows in all other structure ids.

A:

You can do this with a self join:
select t1.structure_id, t2.structure_id, count(*)
from test t1 join
     test t2
     on t1.hash_id = t2.hash_id
group by t1.structure_id, t2.structure_id;

