テーブル結合の件

質問

今回以下の内容を
mysql Client API version 3.23.49
PHP/4.4.5
で動作させようと思っています。
sqlに関してあまり理解していません。

table member
id name local
1 yamada saga
2 kawasaki osaka
3 tanaka miyagi
4 ozawa aomori

table net
id watch bag price day
1 2 0 \10,000 12/3
3 1 2 \50,000 12/6
1 0 3 \60,000 12/9
4 1 0 \15,000 12/15

table shop
id watch bag price day
3 5 1 \48,000 12/1
3 5 2 \80,000 12/6
1 3 3 \60,000 12/10
3 4 2 \50,000 12/12
1 5 3 \60,000 12/13

結果
id name local net shop
1 yamada saga 2 2
2 kawasaki osaka 0 0
3 tanaka miyagi 1 3
4 ozawa aomori 1 0

という契約にいたった件数を得たいのですがどうすればいいのでしょうか？
SELECT a. * , count( b.id ) AS idcount1, count( c.feed_id ) AS idcount2
FROM member AS a
LEFT JOIN net AS b ON a.feed_id = b.feed_id
LEFT JOIN shop AS c ON a.feed_id = c.feed_id
GROUP BY a.feed_id

という形でやってみたのですが
片方のカウントがうまく表示できません。

どうすればいいのでしょうか？
宜しくお願いいたします。

回答No.2

agricap · ベストアンサー

なぜ提示されたSQLではダメなのか、は文章だけの説明ではむずかしいので、説明はしません。
「JOIN」のことをもう少しよく勉強してみてください。
まずは、「INNER JOIN」、理解できたら次に「LEFT OUTER JOIN」

簡単に説明すると、・・・
「INNER JOIN」であれ「LEFT OUTER JOIN」であれ、JOINした結果のレコード数は、
かけ算になります。

このSQLでは、netにもshopにも両方存在するidの場合、それがかけ算になってカウント
されていしまいます。
たとえばid=1のレコードは、netに２個、shopに２個あります。
この場合、count(b.id)、count(c.id)ともに４（＝２×２）になってしまいます。
同様に、id=3に関しては、
count(b.id)、count(c.id)ともに３（３×１）になってしまいます。
count(b.id)、count(c.id)は、NULL値がカウントされないため、少なくとも片方が
ゼロの場合のみ、両方正しくカウントされることになります。
id=2, id=4に関しては正しくカウントされていると思います。

回答としては・・・
２つ一気に、と考えるよりは、１つずつ分けてカウントしてから、というのがわか
りやすいと思います。

手元にMySQLがないため、厳密には確認できません。
提示したSQLは文法エラーになる可能性もあります。
できれば、まずは意味を理解してください。

SELECT a.*, COALESCE(b.count1,0), COALESCE(c.count2,0) FROM
FROM member as a
LEFT JOIN ((SELECT id, count(*) as idcount1 FROM net GROUP BY id) as b)
　　　ON a.id=b.id
LEFT JOIN ((SELECT id, count(*) as idcount1 FROM shop GROUP BY id) as c)
　　　ON a.id=b.id

COALESCEは、１つもなかった場合にNULLではなく、0と表示してほしいために
使いました。

どうしても一気にカウントしたい場合は・・・
netテーブルとshopテーブルにプライマリキーとなる列（一意にレコードを識別できる列）
（たとえばrowidと名付けます）を追加し、以下のようなSQLを書くことも考えられます。

SELECT a.* , count(DISTINCT b.rowid) AS idcount1, count(DISTINCT c.rowid) AS idcount2
FROM member AS a
LEFT JOIN net AS b ON a.id = b.id
LEFT JOIN shop AS c ON a.id = c.id
GROUP BY a.id

お礼

ありがとうございます。
SELECT a.* , count(DISTINCT b.rowid) AS idcount1, count(DISTINCT c.rowid) AS idcount2
FROM member AS a
LEFT JOIN net AS b ON a.id = b.id
LEFT JOIN shop AS c ON a.id = c.id
GROUP BY a.id
でフィールド作って解決しました。
修正いただいた分も使ってみますね！

もともとデザインの方が専門なのですがプログラムはひとつひとつ全てに意味がありますね。
理解しないまま何かを参考に作ってしまうとどうしても失敗します。

今回の件で根本的な質問なんですが
memberにshop netの集計フィールドを作って、shop netが更新されたら
集計フィールドへ代入という事も考えました。

他にもいくつかあると思います。shop netの登録頻度や閲覧頻度などで変わってくるとも思います。
効率的でなお且つデータベースにやさしい方法は今回がベストなのでしょうか？

回答No.5

agricap

＞ひとつ驚いたのが３つのSELECTを書いているほうが断然処理速度速いですね。

これもデータ数や分布状況、内部処理の仕組みにもよるので、むずかしいところです。
思うのは・・・
SELECT１つパターンだと、前述のようにレコード数がかけ算になってしまいますから、その多数のレコードを内部的に一時的であれ作らなければなりません。
その時間がかかるのだと思います。

インデックスの付け方を工夫すれば早くなる可能性もあります。
たとえばnetテーブルとshopテーブルのidにインデックスがついていない場合、つけると早くなるかもしれません。

回答No.4

agricap

＞他にもいくつかあると思います。shop netの登録頻度や閲覧頻度などで変わってくるとも思います。
＞効率的でなお且つデータベースにやさしい方法は今回がベストなのでしょうか？

おっしゃる通り、登録頻度や閲覧頻度やデータ数などによるのでいちがいにはいえません。

しかし、「shop netが更新されたら集計フィールドへ代入」というような設計はした経験がありません。よほどのことがない限りやらないでしょう。
そんなことをしなくても、その都度集計しても実用的な速度が出るからだと思います。
集計速度を最大限に重視するなら、あえてそうすることも考えられなくはないです。

もう１つ、これをやってしまうと整合性の問題があります。
たとえば、shopテーブルの内容に間違いがあったら、shopテーブルのレコードを修正なり削除なりしてさらに集計フィールドも修正、などの手順が必要になります。
わずらわしいですよね。

お礼

ありがとうございます。
今回の２件うまくいきました。
ひとつ驚いたのが３つのSELECTを書いているほうが断然処理速度速いですね。
逆だと思ったんですが・・・　まだ理解していないので勉強します。

回答No.3

agricap

すみません、回答の１つ目のＳＱＬですが、誤りがありました。
SELECT a.*, COALESCE(b.idcount1,0), COALESCE(c.idcount2,0) FROM
FROM member as a
LEFT JOIN ((SELECT id, count(*) as idcount1 FROM net GROUP BY id) as b)
　　　ON a.id=b.id
LEFT JOIN ((SELECT id, count(*) as idcount2 FROM shop GROUP BY id) as c)
　　　ON a.id=c.id

回答No.1

agricap

feed_idとは何でしょうか？
idとは別物ですか？

補足

そうです！　間違えていました。
SELECT a.* , count( b.id ) AS idcount1, count( c.id ) AS idcount2
FROM member AS a
LEFT JOIN net AS b ON a.id = b.id
LEFT JOIN shop AS c ON a.id = c.id
GROUP BY a.id