平均・分散

以下の状況で行き詰ってしまい進むことが出来ません．
ヒントをいただければ幸いです．

店の名前と店の種類が入った
shop(text shopname, text type)
　shop1, 果物屋
　shop2, 八百屋
　shop3, 果物屋
　　：
というテーブルと，店の名前と置いてある品物の値段が入った
stock(text shopname, int ringo, int watermelon)
　shop1, 100, 1000
　shop2, 0, 1500
　shop3, 200, 1200
　　：
というテーブルがあるとします．

このときに，果物屋だけの西瓜の値段の平均，分散などを出すには
どのような sql 文を書けばよいのでしょうか？

いろいろ調べたところ GROUP BY を使うのかな，
というところまで来たのですが，
2つのテーブルを使う sql 文が上手くかけない状況です．

初心者のため質問文におかしいところもあるかもしれませんが，よろしくお願いします．

No.1 ベストアンサー 回答者： chukenkenkou 回答日時： 2006/09/24 16:49

表の列構成を、考え直しませんか？

まず、表の定義に関してアドバイスします。

(1)TEXT型は制限があるので、長さが不確定な「文章」など以外は無闇に使用しない。

(2)「りんご」や「めろん」をそれぞれ列としてしまうと、扱う種類の増減のたびに表の定義変更が必要になる。
→店名、商品名、価格といった構成にするのが、一般的。

(3)「扱わない商品」に「0」を入れると、平均値などが正しく求められない。こういうケースでは、nullを格納すべき。

上記のような見直しを行えば、(2)の対策を行うことで、商品でグループ化するSQLになります。

現状の定義では、グループ化はできず、以下のようなSQLになります。

select avg(suika)
from shop,stock
where shop.shopname=stock.shopname
and shoptype='果'
;

(2)の対策を行った場合は、一つのSQLで全商品ごとの平均価格などを求められるようになります。

select 商品,avg(価格)
from shop,stock
where shop.shopname=stock.shopname
and shoptype='果'
group by 商品
;

この回答へのお礼

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

> 表の列構成を、考え直しませんか？
実は，私が構築したものではないデータベースを扱うことになりまして，
実際には(例示した果物等ではなく)他にもたくさんテーブルがあり，
それらがいろいろと絡み合ってるのでここだけ変更することも出来ず，
この定義でいくしかない状況です．

書いていただいた1つ目の sql 文を参考にもう少し頑張ってみます．

> (1)TEXT型は制限があるので、
> 長さが不確定な「文章」など以外は無闇に使用しない。
> (3)「扱わない商品」に「0」を入れると、
> 平均値などが正しく求められない。
> こういうケースでは、nullを格納すべき。
このあたりも大変参考になりました．
ありがとうございます．

お礼日時：2006/09/24 17:08

No.4 回答者： chukenkenkou 回答日時： 2006/09/25 16:40

#1回答者です。

>No.2 さんの LEFT JOIN と
>No.3 さんの INNER JOIN の違い等

#1回答で書いた
select ～ from 表1,表2 where 表1.列1=表2.列1
は、inner joinと等価です。

表1と表2に、対応するデータがあるものだけがヒットします。

left [outer] joinは、表1にあって表2にないデータがあっても、表1のデータがヒットします。

shop表
shop1、shop2、shop3、shop4、shop5

stock表
shop1、shop2、shop3、shop5

と登録していたとすると、#1のSQLやinner joinでは、shop4のデータは検索されません。
left joinならshop4のデータも検索されます。

「shop表にはあるがstock表にはない」というデータの登録をしないなら、inner joinでもleft joinでも違いはありません。

この回答へのお礼

再度の回答ありがとうございます．
よくわかりました．

皆さんのおかげでやりたかったことは上手く出来ました．
ありがとうございました．

お礼日時：2006/09/26 13:27

No.3 回答者： yambejp 回答日時： 2006/09/24 21:47

破綻寸前のデータベース構成ですね。

なにをやるにも制限が大きくて実用には向かない可能性が
高いですが、どうしてもこれでやるならこんな感じでしょうか。

SELECT
AVG(`watermelon`) AS `スイカの平均`
,VARIANCE(`watermelon`) AS `スイカの分散`
FROM `stock`
INNER JOIN `shop` ON `shop`.`shopname`=`stock`.`shopname`
AND `type`='果物屋'


もし初心者ということであれば、このDBで学習すると
へんなくせがつきそうなのでお勧めできません。

この回答への補足

おかげさまでこの部分については目処が立ってきました．

No.2 さんの LEFT JOIN と
No.3 さんの INNER JOIN の違い等を
追加質問しようと思ったのですが，
知識が足りなさ過ぎてきりがない気がしてきました．

よろしければ皆さんのおすすめの書籍等を
ご紹介いただけたらと思います．

2冊ほど入門書を買ってみたのですが，
今扱おうとしているデータベースには
まったく役に立たないくらい基本的なものしか載ってなかったもので…．

補足日時：2006/09/24 22:48

この回答へのお礼

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

何せ1週間前に始めたところなので
皆さんにいろいろな例を挙げていただいて大変助かっております．

> もし初心者ということであれば、このDBで学習すると
> へんなくせがつきそうなのでお勧めできません。
数学等をやっているので（一応）論理的思考で全体を見ると
整合性が取れているような気がするのですが，
私の例が良くなかったのかも知れませんね．

実際には，ユーザのアクションごとにテーブルが存在し，
その数が50を超えているような大きめのDBです．
一番の間違いはそれをまったくやったことのない
私に任せたことのような気がします(;^_^A

お礼日時：2006/09/24 22:48

No.2 回答者： fxdwg99 回答日時： 2006/09/24 17:56

テーブル構造をいじれない以上、現状でなんとかするしかないですね。

select * from stock left join shop on stock.shopname = shop.shopname
これでshopnameを通じて二つのテーブルをつなげた状態になります。

この回答へのお礼

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

あまりの素人で join についても知らなかったので
大変参考になりました．

お礼日時：2006/09/24 22:34