• ベストアンサー

平均・分散

以下の状況で行き詰ってしまい進むことが出来ません. ヒントをいただければ幸いです. 店の名前と店の種類が入った 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 文が上手くかけない状況です. 初心者のため質問文におかしいところもあるかもしれませんが,よろしくお願いします.

  • BNL
  • お礼率91% (22/24)
  • MySQL
  • 回答数4
  • ありがとう数5

質問者が選んだベストアンサー

  • ベストアンサー
回答No.1

表の列構成を、考え直しませんか? まず、表の定義に関してアドバイスします。 (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 商品 ;

BNL
質問者

お礼

ご回答ありがとうございます. > 表の列構成を、考え直しませんか? 実は,私が構築したものではないデータベースを扱うことになりまして, 実際には(例示した果物等ではなく)他にもたくさんテーブルがあり, それらがいろいろと絡み合ってるのでここだけ変更することも出来ず, この定義でいくしかない状況です. 書いていただいた1つ目の sql 文を参考にもう少し頑張ってみます. > (1)TEXT型は制限があるので、 > 長さが不確定な「文章」など以外は無闇に使用しない。 > (3)「扱わない商品」に「0」を入れると、 > 平均値などが正しく求められない。 > こういうケースでは、nullを格納すべき。 このあたりも大変参考になりました. ありがとうございます.

その他の回答 (3)

回答No.4

#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でも違いはありません。

BNL
質問者

お礼

再度の回答ありがとうございます. よくわかりました. 皆さんのおかげでやりたかったことは上手く出来ました. ありがとうございました.

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.3

破綻寸前のデータベース構成ですね。 なにをやるにも制限が大きくて実用には向かない可能性が 高いですが、どうしてもこれでやるならこんな感じでしょうか。 SELECT AVG(`watermelon`) AS `スイカの平均` ,VARIANCE(`watermelon`) AS `スイカの分散` FROM `stock` INNER JOIN `shop` ON `shop`.`shopname`=`stock`.`shopname` AND `type`='果物屋' もし初心者ということであれば、このDBで学習すると へんなくせがつきそうなのでお勧めできません。

BNL
質問者

お礼

ご回答ありがとうございます. 何せ1週間前に始めたところなので 皆さんにいろいろな例を挙げていただいて大変助かっております. > もし初心者ということであれば、このDBで学習すると > へんなくせがつきそうなのでお勧めできません。 数学等をやっているので(一応)論理的思考で全体を見ると 整合性が取れているような気がするのですが, 私の例が良くなかったのかも知れませんね. 実際には,ユーザのアクションごとにテーブルが存在し, その数が50を超えているような大きめのDBです. 一番の間違いはそれをまったくやったことのない 私に任せたことのような気がします(;^_^A

BNL
質問者

補足

おかげさまでこの部分については目処が立ってきました. No.2 さんの LEFT JOIN と No.3 さんの INNER JOIN の違い等を 追加質問しようと思ったのですが, 知識が足りなさ過ぎてきりがない気がしてきました. よろしければ皆さんのおすすめの書籍等を ご紹介いただけたらと思います. 2冊ほど入門書を買ってみたのですが, 今扱おうとしているデータベースには まったく役に立たないくらい基本的なものしか載ってなかったもので….

  • fxdwg99
  • ベストアンサー率45% (43/94)
回答No.2

テーブル構造をいじれない以上、現状でなんとかするしかないですね。 select * from stock left join shop on stock.shopname = shop.shopname これでshopnameを通じて二つのテーブルをつなげた状態になります。

BNL
質問者

お礼

ご回答ありがとうございます. あまりの素人で join についても知らなかったので 大変参考になりました.

関連するQ&A

  • SQLの条件検索について

    下記のような「果物TBL」というテーブルがあるとします。 果物TBL -------------------------------- ID    名前      値段 - - - - - - - - - - - - - - - - - - - - 01    りんご     150 02    バナナ     250 03    みかん     250 04    ぶどう     300 -------------------------------- また上記のテーブルと全く同じ構成の「果物検索TBL」というテーブルがあるとします。 そのテーブルには、例として下記のような値を入れておきます。 果物検索TBL -------------------------------- ID    名前      値段 - - - - - - - - - - - - - - - - - - - - NULL   りんご     250 NULL   バナナ     NULL -------------------------------- この時、下記のようなSQLだと SELECT DISTINCT 果物TBL.名前 FROM 果物TBL INNER JOIN 果物検索TBL ON (果物TBL.ID = 果物検索TBL.ID AND LEN(果物検索TBL.ID) > 0) OR (果物TBL.名前 = 果物検索TBL.名前 AND LEN(果物検索TBL.名前) > 0) OR (果物TBL.値段 = 果物検索TBL.値段 AND LEN(果物検索TBL.値段) > 0); -------------------------------- 名前 - - - - - - - - - - - - - - - - - - - - バナナ みかん りんご -------------------------------- という結果になります。(名前:りんご・バナナと値段:250のOR検索) その結果を -------------------------------- 名前 - - - - - - - - - - - - - - - - - - - - バナナ -------------------------------- という結果にするにはどうすればいいでしょうか?(名前:りんご・バナナと値段:250のAND検索、バナナのみマッチ) 単純にORをANDにしてもうまくいきませんでした。 動的にSQLを作れれば良いのですが、この構成は変えられないのでNULL値がネックになり分かりません。 教えてください。 また、記載してるSQLよりもっとスマートな書き方があれば教えてください。

  • select文の検索結果と文字列を照合したい

    JAVAプログラミング初心者です。 PostgresQLとServletを使います。 あるテーブル(no=int型、name=text型(?)が存在してます。)を検索して、ある文字列(ここでは入力された名前なんです。)と検索結果を照合、テーブルに存在しなければSQL文INSERTで登録したいのですが、どのよう にするといいのかわかりません。どなたかご意見をお願いします。 "照合"する部分が特にわからないのです。 よろしくお願いします。

    • ベストアンサー
    • Java
  • CでSQLのテーブルを読む

    SQLのテーブルがあります。 レコードを1行ずつ読むソースを教えてください。 データベースはさっぱり分かりません。 create table mytable (  name text,  age int ); このテーブルからint型のageを列挙したいと思います。 intを読むわけですから EXEC SQL BEGIN DECLARE SECTION; int i; EXEC SQL END DECLARE SECTION; が必要ですよね。あとはさっぱり分かりません。

  • for文の組み合わせ方

    PHPで以下のTableからデータを取ってきて 取り扱い表を作成したいと思っています。 Table:SHOPは店舗、Table:ITEMは商品、Table:LINKは 先の二つの紐付けを表します。 DBの保存データは、 Table:SHOP ID  SHOPNAME ------------------- 1   東京 2   大阪 3   福岡 (略) Table:ITEM ID ITEMNAME -------------------- 1  化粧品 2  家具 3  園芸 4  食品 5  家電 (略) Table:LINK ID  SHOP  ITEM --------------------- 1   1    4 2   2    5 3   3    1 4   3    2 (略) となっていて、結果として画面に   |化粧品|家具|園芸|食品|家電| ---------------------------------------- 東京|   |   |   | ○ |   | 大阪|   |   |   |   | ○ | 福岡| ○ | ○ |   |   |   | (略) と表示したいのですが、 $sql="SELECT A.ID,B.SHOPNAME,C.ITEMNAME FROM ・・・・・ としたのでは紐付けを単純に抽出するだけですし、 for文をどう使えば縦列に項目をだして○をつければ いいかがわかりません。 HELPお願いします。

    • ベストアンサー
    • PHP
  • PostgreSQLの列分割(水平分割)について

    PostgreSQLで次のテーブルを作るとして create table test1(id int , name text , pass text , img bytea , dates date); このテーブルをパーティショニングしたいと思っています。 水平分割(dates列の値によって行をパーティショニング)についてのSQL文は、公式ドキュメントに例示されていたため理解したのですが、 垂直分割(列自体を分割)する設計のテーブルを作成する場合について調べているのですが、見つけることができませんでした。 id,name,pass,dateのテーブルと、id,imgのテーブルにパーティショニングするSQL文についてご教授願えないでしょうか

  • スーパーで売ってる野菜と八百屋の野菜の違いとは?

    私は八百屋で野菜をよく買うのですが、どちらかと言えば スーパーの方が安いです。 玉葱などは八百屋で売ってる物の方が大きいです。 スーパーで3玉100円の玉葱と八百屋で4玉300円の玉葱を 比べると大きさが全然違います。値段が違うからでしょうけど。 八百屋で売ってる野菜とスーパーで売ってる野菜・・ どちらが質が良いのでしょうか? 八百屋だけでは食材が揃わないのでスーパーも併用してますが たまに「この品はスーパーの方が安かったな~」と思うときもあります。(同じ産地で同じサイズの椎茸など) やっぱり専門のお店の方が品物の質が良いんでしょうか? 果物も果物屋で買う方が結果としていいのですか?

  • PHPでmySQLのテーブルを作成したい

    今PHPの練習をしているのですが、作ろうとしているPHPの、 0:$tbname="データベース名"、とする。 1:データベースにアクセスする 2:アクセスしたデータベースに$tbnameと同じ名前のテーブルがあるかないかをチェック 3:同じ名前のテーブルがあるならそこで終了 4:同じ名前のテーブルがないなら、$tbnameをテーブル名、ID(INT)とNAME(CHAR)をカラム名とするテーブルを新規に作成する という動作をする部分のコードの書き方(どのようなSQL文を使えばいいのか)がわかりません。 どなたかご教示のほど、どうかよろしくお願いします。

    • ベストアンサー
    • PHP
  • SQLで取得したフィールドをSQL文として利用

    お世話になっております。 MYSQLで sql_id (int PK) sql_data (text) のようなテーブル(sql_db)を持ち select sql_id from where (sql_data をsqlとして実行した結果 ) > 200 のように、フィールドから取得した値をSQLとして実行したりする方法はございますでしょうか。 ご教授よろしくお願いいたします。

  • ORDER BYを条件に一致した順にしたい

    たとえば、 SELECT 果物名 FROM 果物種類 WHERE 色 = '赤' OR 値段 < 100 というようなSQL文を書いたとして、 「ORDER BY」 として ”値段 < 100”を先に、 ”色 = '赤' ”を後に というような指定はできますか?

  • MySQL文法エラーの原因調査のお願い

    SQL文の文法エラーの原因が分からないので、ご教示ください。 ものは、掲示板になります。 PHPからSQLを実行しています。 テーブルの中身とSQL文とエラー文は以下の通りです。 【テーブル】 category_id | tinyint(3) unsigned shop_id | tinyint(3) unsigned id | int(10) unsigned name | varchar(255) 【SQL文】 $sql="insert into テーブル名 value(".$_SESSION['category_id'].",".$_SESSION['shop_id'].",".$_SESSION['thread_id'].",'".$_SESSION['name']."')"; 【エラー文】 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',testname)' at line 1 エラー文中の「testname」は、テストのために、カラム「name」のところにこちらで入れたものになります。 情報に不足があれば、補足するのでおっしゃってください。 以上、よろしくお願い申し上げます。

    • ベストアンサー
    • MySQL