SQLの質問です。MySQL5です。


Table_A
・book_id
・title

Table_B
・book_id
・tag

上記2つのテーブルがあります。2つのテーブルはbook_idで関連付けされています。
Table_Bはユーザーからの投稿データ(本に対する「タグ」)を蓄積しています。

book_idが200の本に対して以下のタグが付けられたとします。

面白い
面白い
つまらない
高い

最も多く投稿されたタグを取り出す(上の例だと面白い)場合は

SELECT tag FROM Table_A INNER JOIN Table_B USING(book_id)
WHERE Table_A.book_id = 200 GROUP BY Table_B.tag ORDER BY count(Table_B.tag) desc limit 1");

と書けました。


それでは以下の場合はどうすればいいでしょうか。

***最も多く投稿されたタグが「面白い」本を全て抽出***


検索機能に上記SQLを使いたいのです。MySQL5なのでサブクエリが使えますが、サブクエリを勉強した事が無いので使い方がわかりません。

みなさんよろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/03/30 10:16:24
  • 終了:2007/04/06 10:20:03

回答(4件)

id:b-wind No.1

b-wind回答回数3344ベストアンサー獲得回数4402007/03/30 10:32:02

ポイント23pt

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.4.2.3 ANY、IN、SOME とともに使用したサブクエリ

SELECT book_id,title FROM Table_A AS a
  WHERE ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id AND tab = '面白い' )
    >= ANY ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id GROUP BY tag );

うーん、工夫すればもっと効率よく出来そうな…。

id:tokyosmash

うーんこれはとても複雑ですね。

リファレンス見ながら勉強してみますけど時間がかかりそうです。

ありがとうございます。

2007/03/30 10:55:20
id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552007/03/30 10:55:44

ポイント23pt

>最も多く投稿されたタグが「面白い」本を全て抽出

1. それぞれの本の投稿されたタグの

  数を数える。

SQLにすると

select book_id,tag,count(tag) from Table_B

group by tag;

2.もっとも多く投稿されたものを抽出する

select book_id,tag,count(tag) from Table_B

group by tag order by count(tag) desc limit 1;

3. タグが「面白い」をのみを抽出

select a.book_id,a.title

from Table_A as a

inner join

(select book_id,tag,count(tag)

from Table_B as b

group by tag

order by count(tag)

desc limit 1

) as t

on a.book_id = t.book_id and b.tag = '面白い'

id:tokyosmash

コメント欄で大量の情報ありがとうございました。たまたまMySQL5を使っていたので「トリガ」に挑戦してみようと思います。クエリした結果をさらにクエリして・・という処理は現時点で自力で書くのは非常に効率が悪いです。みなさんのSQL文を見て理解するのも厳しいですから。。

2007/03/31 17:07:45
id:Yota No.3

Yota回答回数453ベストアンサー獲得回数282007/03/30 13:24:19

ポイント22pt

これでどうでしょう。

SELECT shukei.book_id, MAX(shukei.kaisu), shukei.tag FROM (SELECT book_id,tag,COUNT(tag) AS kaisu FROM Table_B GROUP BY book_id,tag ) AS shukei GROUP BY shukei.book_id HAVING shukei.tag='おもしろい' ;

1.内側のクエリでbook_idごとtagごとの集計をする。

SELECT book_id,tag,COUNT(tag) AS kaisu FROM Table_B GROUP BY book_id,tag ;

ここでできたテーブルの名前をshukeiとする。

2.book_idごとにグループ化して、kaisuが最大のものでtagおもしろいだけ抽出する。

id:tokyosmash

ありがとうございます。

今回はなんとか成功しましたが、このような処理を毎回自力で書くのは現時点では厳しそうです。

オライリーのSQLクックブックに細かい事例が載っていたので買ってきました。今から勉強します。回答ありがとうございました。

2007/03/31 17:08:59
id:bonlife No.4

回答回数421ベストアンサー獲得回数752007/03/30 17:59:31

ポイント22pt
SELECT good_book.book_id, a.title
FROM (
    SELECT t1.book_id
    FROM (
--
--      book_id ごとのタグの最大値を取得
--      t1 テーブルとする
--
        SELECT book_id, max(tag_count) AS tag_max
        FROM (
            SELECT book_id, tag, count( tag ) AS tag_count
            FROM Table_B
            GROUP BY book_id, tag
        ) tmp
        GROUP BY book_id
    ) AS t1 INNER JOIN (
--
--      book_id、tag ごとのタグ数を取得
--      t2 テーブルとする
--
        SELECT book_id, tag, count( tag ) AS tag_count
        FROM Table_B
        GROUP BY book_id, tag
    ) AS t2
--
--  t1 と t2 を結合して、book_id ごとにタグの最大値を持つ行を取得
--  その中でタグが '面白い' もののみを抽出
--  つまり、面白いの数がタグの中でもっとも多いものを抽出
--  (SELECT句では t1.book_id のみ選択)
--  good_book テーブルとする
--
    ON t1.book_id = t2.book_id AND t1.tag_max = t2.tag_count
    WHERE t2.tag = '面白い'
) good_book
--
--  good_book テーブルと Table_A を結合して title を取得
--
INNER JOIN Table_A As a
ON good_book.book_id = a.book_id
;

パフォーマンスはあやしいですが、「面白い」タグの数が同率1位でも正しく取得できるはずです。

参考になれば幸いです。

id:tokyosmash

回答ありがとうございます。

とても見やすくて助かります。

ただ理解はできるのですが、ちょっと応用させて自分でSQL文を書くとなると非常に時間がかかります。質問文ではテーブルが2つですが、本当は3つのテーブルなんですね。それを結合させた上でクエリを2回やって・・となるともう複雑すぎます。

今回の処理であれば、事前にトリガーでそれぞれの本ごとに一番投稿されたタグを計算させておくのが簡単だと気付きました。

せっかくMySQL5を使っているので新しい機能を試してみようと思います。情報が少なくて困っていますが・・

2007/03/31 17:15:10
  • id:tokyosmash
    ***最も多く投稿されたタグが「面白い」本を全て抽出***
    に関して補足します。

    全ての本の中で「面白い」タグを付けられた数が最も多い本を一つだけ抽出する、という意味ではありません。

    IDが100の本に
    面白い * 10(個)
    つまらない * 2

    IDが101の本に
    面白い * 20
    つまらない * 25

    このようなタグ付けされているとします。この場合はID100の本は抽出しますが、ID101の本は抽出しません。お解かりいただけますでしょうか。

  • id:tokyosmash
    Table_Bについてさらに補足させてください。

    Table_Bには一つのタグにつき一つのレコードが割り当てられています。ID101の本に4つのタグが投稿されていれば、4つのレコードが存在します。一つのレコード内にカンマやスペースで区切って保存してあるわけではありません。
  • id:b-wind
    間違えた。2行目に GROUP BY が抜けている。
    SELECT book_id,title FROM Table_A AS a
    WHERE ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id AND tab = '面白い' GROUP BY tag )
    >= ANY ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id GROUP BY tag );
  • id:kurukuru-neko
    大間違いですね。
    コメンドで一度回答します。
  • id:tokyosmash
    うーん難しいですね。。
  • id:kurukuru-neko
    1. タグ別件数を抽出

    select book_id,tag,count(tag) as tagc from Table_B group by book_id,tag;

    2. 各BOOK_IDのタグの最大件数を抽出

    select book_id,max(tagc) as tmax
    from (select book_id,count(tag) as tagc from Table_B group by book_id,tag) as s1
    group by book_id;

    3.BOOK_IDの面白い件数抽出

    select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag;

    4.BOOK_IDの面白い件数が最大件数の一致したものを抽出

    3.+4.

    select t1.book_id,t1.tagc from
    (select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag) as t1

    left join

    (select book_id,max(tagc) as tmax
    from (select book_id,count(tag) as tagc from Table_B group by book_id,tag) as s1
    group by book_id
    ) as t2
    on t1.book_id = t2.book_id and t1.tagc = t2.tmax;


    4.BOOK_IDの名前と結合

    select t1.book_id,t3.title,t1.tagc from
    (select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag) as t1

    left join
    (select book_id,max(tagc) as tmax
    from (select book_id,count(tag) as tagc from Table_B group by book_id,tag) as s1
    group by book_id
    ) as t2
    on t1.book_id = t2.book_id and t1.tagc = t2.tmax

    left join
    Table_A as t3
    on t1.book_id = t3.book_id;

  • id:kurukuru-neko

    結構複雑になってしまいました。

    >3.+4.
    2. + 3.

    以下でも動作はするとは思いますが
    効率は?

    select * from Table_A as a where
    (select count(tag) from Table_B as b1 where b1.book_id = a.book_id and tag = '面白い' group by tag limit 1)
    = (select count(tag) from Table_B as b2 where b2.book_id = a.book_id group by tag order by count(tag) desc limit 1);
  • id:tokyosmash
    >kurukuru-nekoさん

    長いSQL文ありがとうございます。複雑すぎて細部まで理解できていません。
    とりあえず試してみたのですが、一つでも「面白い」とタギングされている本が表示されてしまいました。
    つまり
    面白い * 1
    楽しい * 3
    このような本でもリストアップされています。


    そこで HAVING tmax > 0 を加えたところうまくいきました!どうでしょうか??
  • id:kurukuru-neko

    >そこで HAVING tmax > 0 を加えたところ
    >うまくいきました!どうでしょうか??

    left joinがinner joinの間違いです。
    (両側にあるものを選択)

    >HAVING tmax > 0

    どこに入れましたか?


  • id:kurukuru-neko
    havingを使うタイプに
    少し改良

    select t3.title,t1.* from
    (select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag) as t1
    inner join
    (select book_id,tagc from (select book_id,tag,count(tag) as tagc from Table_B group by book_id,tag
    order by book_id,count(tag) desc ) as t1 group by book_id having tagc=max(tagc)
    ) as t2
    using(book_id,tagc)
    left join Table_A as t3 using(book_id)
  • id:kurukuru-neko
    ↑ 

    上の改良版でも結果の集約は、
    前提条件として結果の最初に一致する物に
    集約されることを前提があるので移植性には
    問題がある。
  • id:kurukuru-neko
    少し変則

    select a.book_id,a.title,b.count
    from Table_A as a

    inner join
    (select
    book_id,
    if( max(tagc)=(select count(*) from Table_B as b where b.book_id=t1.book_id and b.tag='面白い'),1,NULL) as flg,
    max(tagc) as count
    from (select book_id,tag,count(*) as tagc from Table_B group by book_id,tag) as t1 group by book_id
    ) as b
    on a.book_id = b.book_id and b.flg is not null
    ;
  • id:tokyosmash
    SQL文の末尾に
    HAVING tmax > 0
    を入れるとうまくいくんですよね。
    色々な条件を試しましたが、一応今のところ正常に動いているようです。

    しかしちょっと複雑ですね。。
    何がどうなっているのかほとんどわからないです。
    のんびり理解していこうと思います。

    kurukuru-nekoさんご丁寧にありがとうございます。





  • id:kurukuru-neko

    select b1.book_id,tagc,b1.tag,a.title from
    (select book_id,tag,count(*) as tagc from Table_B group by book_id,tag order by book_id,tag desc) as b1
    left join Table_A as a using(book_id)
    group by b1.book_id
    having tagc=max(tagc) and tag = '面白い' order by tagc desc;


    select *,(select title from Table_A where book_id=b1.book_id) as title from
    (select book_id,tag,count(*) as tagc from Table_B group by book_id,tag order by book_id,tag desc) as b1
    group by book_id having tagc=max(tagc) and tag='面白い' order by tagc desc;


  • id:kurukuru-neko
    >SQL文の末尾に
    >HAVING tmax > 0
    >を入れるとうまくいくんですよね。

    動かないはずなんだけどな??

    速度を比較すると
    一番遅い順に並べると
     SQLの早いのと遅いのでは10倍程度
     速度差があるので実際のデータで
     測定してみるのも面白い。
     SQL4/5が一番効率がよいはず。

    ★SQL1.
    select t1.book_id,t3.title,t1.tagc from
    (select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag) as t1
    inner join
    (select book_id,max(tagc) as tmax
    from (select book_id,count(tag) as tagc from Table_B group by book_id,tag) as s1
    group by book_id
    ) as t2
    on t1.book_id = t2.book_id and t1.tagc = t2.tmax
    left join
    Table_A as t3
    on t1.book_id = t3.book_id order by t1.tagc desc;


    ★SQL2.
    select t3.title,t1.* from
    (select book_id,count(tag) as tagc from Table_B where tag='面白い' group by book_id,tag) as t1
    inner join
    (select book_id,tagc from (select book_id,tag,count(tag) as tagc from Table_B group by book_id,tag
    order by book_id,count(tag) desc ) as t1 group by book_id having tagc=max(tagc)
    ) as t2
    using(book_id,tagc)
    left join Table_A as t3 using(book_id)
    order by t1.tagc desc;



    ★SQL3.
    select * from Table_A as a where
    (select count(tag) from Table_B as b1 where b1.book_id = a.book_id and tag = '面白い' group by tag limit 1)
    = (select count(tag) from Table_B as b2 where b2.book_id = a.book_id group by tag order by count(tag) desc limit 1);


    ★SQL4.
    select b1.book_id,tagc,b1.tag,a.title from
    (select book_id,tag,count(*) as tagc from Table_B group by book_id,tag order by book_id,tag desc) as b1
    left join Table_A as a using(book_id)
    group by b1.book_id
    having tagc=max(tagc) and tag = '面白い' order by tagc desc;

    ★SQL5.
    select *,(select title from Table_A where book_id=b1.book_id) as title from
    (select book_id,tag,count(*) as tagc from Table_B group by book_id,tag order by book_id,tag desc) as b1
    group by book_id having tagc=max(tagc) and tag='面白い' order by tagc desc;
  • id:kurukuru-neko

    データ件数がどの程度か不明ですが、
    現状だと必ず全件検索になるので
    だんだん遅くなります。

    テーブルを見直し、定期的に更新
    MYSQL 5.0のTRIGERで対応。
  • id:kurukuru-neko
    ためしにベンチマークすると

    ランダムデータ をテーブルに入れて
    Table_A 1000/Table_B 17895
    MyISAM
    SQL 1/2. 0.19~0.45
    SQL 3. 0.12~0.31
    SQL 4/5. 0.05~0.13
    InnoDB
    SQL 1/2. 0.23~0.45
    SQL 3. 0.14~0.31
    SQL 4/5. 0.06~0.15

    explain
    SQL1. 42334 ROW
    SQL2. 42334 ROW
    SQL3. 24837 ROW(およそ)
    SQL4. 22108 ROW
    SQL5. 22108 ROW

    おおよそデータ量に比例していることがわかる。
    ためしにTable_Bに
    (book_id,tag)で索引をつけてみると

    SQL1. 変化不明
    SQL2. 若干改善
    SQL3. かなり改善
    SQL4. 変化不明
    SQL5. 変化不明

    (tag)で索引をつけてみると
    SQL1. 若干改善
    SQL2. 変化不明
    SQL3. 変化不明
    SQL4. 変化不明
    SQL5. 変化不明

    ランダムデータ をテーブルに入れて
    Table_A 2000/Table_B 95322
    に増やすと
    InnoDB
    SQL 1/2. 1.27~1.36
    SQL 3. 0.87~0.99
    SQL 4/5. 0.28~0.54

    InnoDBのバッファを1Gにする
    SQL 1/2. 0.94~1.04
    SQL 3. 0.64~0.74
    SQL 4/5. 0.27~0.41

    ?かなり遅い気がする。

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

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

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

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