以下のような4のテーブルがあるとします。

※casts,genre,storeのカラム「id」はmoviesテーブルのカラム「id」の外部キーであるものとします。

movies
|*id|*title|
|1|スターウォーズ|
|2|ハリーポッター|
|3|ウルヴァリン|
|4|インセプション|
|5|第9地区|

casts
|*id|*title|
|1|taro|
|1|miki|
|1|takumi|
|2|jun|
|2|ai|
|3|hizuka|
|4|ami|
|5|eisuke|

genre
|*id|*genre|
|1|ドキドキする|
|1|感動する|
|2|童心に帰れる|
|3|ドキドキする|
|4|不思議な世界|
|5|グロ注意|

store
|*id|*genre|
|1|*tsutaya|
|1|*dorama|
|2|*tsutaya|
|3|*tsutaya|
|3|*dorama|
|4id|*tsutaya|
|5id|*tsutaya|


これらのテーブルから以下の条件に一致するレコードを抽出するには
どのようなSQLが最適でしょうか
※SQL以外の手段を用いる場合は、その方法を具体的に示していただけると助かります。

条件
・castsにtaroとhizukaが含まれる
・genreに不思議な世界とグロ注意が含まれる
・storeにtsutayaとdoramaが含まれる

環境 postgresql 8.3.7

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/05/21 15:36:24
  • 終了:2011/05/21 16:21:38

ベストアンサー

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912011/05/21 16:00:57

ポイント60pt

movies 以外は id に対する項目が複数あるようですから、テーブル結合よりサブクエリの方が良いでしょうか。

すべてが And 条件ですので、サブクエリを重ね合わせればよいかと思います。

テーブルとフィールド名が少し混乱しているようですが、例に合わせれば、

SELECT * FROM movies WHERE
  id IN ( SELECT id FROM casts WHERE
		   ( title IN ( 'taro', 'hizuka' ) )
   	       AND ( id IN ( SELECT id FROM genre WHERE
                                ( genre IN ( '不思議な世界', 'グロ注意' ) )
                            AND ( id IN ( SELECT id FROM store WHERE genre IN ( 'tsutaya', 'dorama' ) ) )
                       )
               )
         )

のような形だと思います。

id:muggy0812

参考になります。

条件が増えれば増えるほど、SQLも長くなってしまうのはやはりしょうがないことなんでしょうか。

あとは、SQL発行時のパフォーマンスが気になります。

ここらへんは実データを登録したテーブルでexplain analyzeして確認するしかないですね。

2011/05/21 16:11:17
  • id:chuken_kenkou
    これらのテーブルから、どういう検索結果を期待しているのでしょうか?

  • id:muggy0812
    期待する検索結果は、指定した条件に合った作品名です。
  • id:Mook
    条件が複雑になれば、クエリも複雑に(長く)なりがちですが、
    必ずしもクエリの長さと実行時間は比例しません。

    このあたりは、
    http://www.atmarkit.co.jp/flinux/rensai/troubleshoot03/ts03b.html
    を参考に、評価を見るとよいと思います。
  • id:muggy0812
    クエリの長さは実行時間と必ずしも比例しないんですね。
    まだまだ知らないことが多いので助かります。
  • id:windofjuly
    うぃんど 2011/05/21 16:42:46
    「xxとyyが含まれる」ではなくて「xxあるいはyyが含まれる」になってますが?
  • id:Mook
    条件検索のようだったので、そう解釈したのですがそこは AND でしょうか。

    であれば、サブクエリ内は
    genre IN ( 'tsutaya', 'dorama' )
    ではなく
    ( genre = 'tsutaya' AND genre = 'dorama' )
    のように変更してください。
  • id:muggy0812
    >>windofjuly さん

    ツッコミどうもです。

    >>Mook さん
    ANDでした。
    わかりづらかったかもですね
    追記どうもです
  • id:Mook
    ちょっと冷静に判断。考えてみると、
    ( genre = 'tsutaya' AND genre = 'dorama' )
    はあり得ないですね。

    ちょっと整理してから再コメントします。
  • id:Mook
    調べている最中ですが、
    単純に思いつくのは 同じテーブルにサブクエリをかける方法ですが、
    かなり長くなりますね。

    MySQL だと group_concat なんてのが使えそうですが、類似のことをやろうとすると
    http://miau.s9.xrea.com/blog/index.php?itemid=585
    のように独自のプロシージャを定義することになりそうです。
    (これを使用したときのパファオーマンスも気になりますし。)

    複数レコードにまたがっての AND 条件は意外と難しいですね。
  • id:muggy0812
    調べていただいてありがとうございます~
    登録データを自由度の高い条件で取得しようとすると急にどうしてよいのやら・・・

    きっかけは↓のサイトを見たときに、これはどういうクエリで作品を取得しているのかなと
    思ったのがきっかけです
    http://gacchi.jp/search/

    windofjuly さんコメ欄のやつ消しちゃったんですね


  • id:windofjuly
    うぃんど 2011/05/21 20:02:47
    >コメ欄のやつ消しちゃった
    残しておきましょか?

    --データ
    CREATE TEMPORARY TABLE movies (id INT, title TEXT);
    INSERT INTO movies VALUES (1,'スターウォーズ'),(2,'ハリーポッター'),(3,'ウルヴァリン'),(4,'インセプション'),(5,'第9地区');
    CREATE TEMPORARY TABLE casts (id INT, name TEXT, UNIQUE(id, name));
    INSERT INTO casts VALUES (1,'taro'),(1,'miki'),(1,'takumi'),(2,'jun'),(2,'ai'),(3,'hizuka'),(4,'ami'),(5,'eisuke');
    CREATE TEMPORARY TABLE genre (id INT, genre TEXT, UNIQUE(id, genre));
    INSERT INTO genre VALUES (1,'ドキドキする'),(1,'感動する'),(2,'童心に帰れる'),(3,'ドキドキする'),(4,'不思議な世界'),(5,'グロ注意');
    CREATE TEMPORARY TABLE store (id INT, store TEXT, UNIQUE(id, store));
    INSERT INTO store VALUES (1,'tsutaya'),(1,'dorama'),(2,'tsutaya'),(3,'tsutaya'),(3,'dorama'),(4,'tsutaya'),(5,'tsutaya');

    --全てに合致
    SELECT *
    FROM movies
    WHERE (SELECT count(*) FROM casts WHERE casts.id=movies.id AND casts.name IN ('taro','hizuka')) = 2
    AND (SELECT count(*) FROM genre WHERE genre.id=movies.id AND genre.genre IN ('不思議な世界','グロ注意a')) = 2
    AND (SELECT count(*) FROM store WHERE store.id=movies.id AND store.store IN ('tsutaya','dorama')) = 2
    ;

    --いずれかに合致
    SELECT *
    FROM movies
    WHERE EXISTS (SELECT * FROM casts WHERE casts.id=movies.id AND casts.name IN ('taro','hizuka'))
    OR EXISTS (SELECT * FROM genre WHERE genre.id=movies.id AND genre.genre IN ('不思議な世界','グロ注意a'))
    OR EXISTS (SELECT * FROM store WHERE store.id=movies.id AND store.store IN ('tsutaya','dorama'))
    ;

    >group_concat
    PostgreSQLは配列が使えるので
    結果レコードを配列に格納してJOINするという手なども使えたりします

    >どういうクエリで作品を取得しているのかな
    配信サイトや気分などそれぞれのマスターテーブルから値を持ってきてHTMLのチェックボックスのVALUEにセットしておき
    データベース処理のほうでは、番号だけを受け取れば良いという手はずになってますね

    例えば下記のようにチェックが入っていたとすれば
     配信サイト haishin[] = Array(1,2,5)
     気分 feel[] = Array(2,5,9)
     ジャンル genre[] = 選択されなかった
     製作年代 s_year[] = 選択されなかった
     製作国 s_country[] = Array(25,59)
    下記のようなSQLを作ってPostgreSQLに実行させて結果を得る
    SELECT *
    FROM テーブル
    WHERE haishin IN (1,2,5)
    AND feel IN (2,5,9)
    AND s_country IN (25,59)
  • id:muggy0812
    残してもらえると助かります。
    あとで読もうと思っていたので。

    確かポイントをどこかから送れたと思うので、少しですが送らせていただきますね

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません