PostgreSQL 積集合の数を抽出する

Question

前提

漫画のDBがあるとします。カラムはidとgenresです。

id	genres

実現したいこと

ある漫画のジャンルと、各idにあるジャンルとの積集合の数を求めたいです。

例

ある漫画のジャンル（仮にid = 10) = ファンタジー,コメディー,アクション

漫画DB

id	genres
1	アドベンチャー,ファンタジー
2	スポーツ,ドラマ
3	ファンタジー,コメディー
...	...
10	ファンタジー,コメディー,アクション

id = 1 と id = 10 の積集合の数は1
id = 2 と id = 10 の積集合の数は0
id = 3 と id = 10 の積集合の数は2

理想のDB

id	積集合数
1	1
2	0
3	2

試したこと

id = 10 のジャンルを行列に変換して、各idのジャンルも行列に変換してintersectをやってもうまくいきません。

SELECT id, count(genre)
FROM manga, (
    SELECT unnest(string_to_array(genres, ',')) AS genre
    FROM manga
    INTERSECT
    SELECT unnest(string_to_array(genres, ',')) AS genre
    FROM manga
    WHERE id = 10
) AS seki
GROUP BY id
;

バージョン

PostgreSQL 13.1

Comments

[退会済みユーザー]

genres をカンマ区切りで持たず、1つの単語で持つ構造にすることはできないのでしょうか。id, manga_id, genre というテーブル構造なら、容易に求められそうです。

[退会済みユーザー]

できれば CREATE TABLE文とCOPY文などでサンプルテーブルを作りやすく提供してもらえると良いのだけど。

[shima7]

https://www.kaggle.com/code/ayushv322/anime/data
ここのanime.csvのデータを使っています。
例の漫画DBの id は mal_id で genresはそのままgenresです。
ジャンルは複数にしたいです！

Answer 1

やりかたはいろいろあると思いますが、たとえば以下のようなクエリで実現できるかと思います。

SELECT A.id, SUM(CASE WHEN A.genre = B.genre THEN 1 ELSE 0 END)
FROM (
  SELECT id, unnest(string_to_array(genres, ',')) AS genre
  FROM manga
) A
CROSS JOIN (
  SELECT unnest(string_to_array(genres, ',')) AS genre
  FROM manga
  WHERE id = 10
) B
GROUP BY A.id

Comments

[shima7]

ありがとうございます、求めていた理想のクエリになりました！