(SQL)平均値と標準偏差の計算について


SQL勉強中の初心者ですが、よろしくお願いします。
以下のSQL文によって、全てのpriceの平均値や標準偏差の取得はできました。

SELECT
tableA.id, tableA.name, FROM_UNIXTIME(tableB.date,'%Y-%m-%d'), tableB.price price1, AVG(tableB.price) price2, STD(tableB.price) price3
FROM
tableA , tableB
WHERE
tableA.id = tableB.id


GROUP BY
tableB.id
ORDER BY
tableB.date DESC


これに加えてやりたいことは、
1.「それぞれのIDの直近日付の20件につき」IDごとにtableB.priceの平均値と標準偏差を算出したい
2. price >= (price2 + price3) のものだけ抽出したい

WHERE句で絞り込めばよさそうなのですが、色々試してもエラーになり、やり方がわからないためご教授願います。

(参考)
仕様:MYSQL5.1
日付:UNIXTIMEのINT型(一応SQLの時点でdate型に直してますが、PHPのプログラム側で直しても構いません。)
(構造)
tableA
id,name
tableB
id,date,price
※商品によっては売上の無い日もあるので、INTERVAL -20 day はたぶん使えません。
説明に足りない部分があればご指摘ください。

回答の条件
  • 1人3回まで
  • 登録:2010/02/05 14:18:23
  • 終了:2010/02/06 01:18:22

ベストアンサー

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012010/02/05 21:54:31

ポイント300pt

分析関数を備えたRDBMSなら苦も無く高速処理できるのですが、先の質問でも、

トラブルとなったように、まだサブクエリ内でLIMITを使う事すらできませんし、

どの道、内部的に何度も集計をやらせるような事にならざるを得ないので、

いっその事、通し番号を振ってしまうのが楽で処理も軽いかもしれないです。

(複数ユーザーが平行してシステムを利用しているような場面には向きません。

ユーザー定義関数などの作成も実質不可なMySQLで集計関係は難題の一つです。

ブログなどのようなシステムには向いているのですが・・・)


前置きが長くなりましたが、以下、一時テーブルを使った例(動作確認済み)です。

<html>
<head>
    <title>MySQLテスト</title>
</head>
<body>
<p>動作開始</p>
<hr />
<?php
//----------SQLを実行してHTMLのTABLEとして出力するためのルーチン
function comm_table($sqlstr, $caption){
    $ret = "";
    if ($res = mysql_query($sqlstr)) {
        $ret .= "<table border=\"1\">\n";
        if ($caption != "") {
            $ret .= "<caption>" . $caption . "</caption>\r\n";
        }
        $ret .= "<thead>\r\n";
        $ret .= "<tr>";
        $cols = mysql_num_fields($res);
        for ($lp1 = 0; $lp1 < $cols; $lp1++) {
            $ret .= "<th>" . mysql_field_name($res, $lp1) . "</th>";
        }
        $ret .= "</tr>\r\n";
        $ret .= "</thead>\r\n";
        $ret .= "<tbody>\n";
        while ($rows = mysql_fetch_array($res, MYSQL_NUM)) {
            $ret .= "<tr><td>" . join('</td><td>', $rows);
        }
        $ret .= "</tbody>\r\n";
        $ret .= "</table>\r\n";
    }
    return($ret);
}
//---------- メインルーチン
if ($con = mysql_connect('接続文字列')) {
    if((mysql_select_db('データベース名'))) {
        // カウント用変数の準備
        mysql_query("
            SET @c = 0;
        ");
        // MySQL には分析関数も無ければ、サブクエリの中でLIMITなどが使えないなど
        // まだまだ、いろいろと制約があるので、一時テーブルを作成
        mysql_query("
            CREATE TEMPORARY TABLE t1 AS
            SELECT id, date, price, @c:=@c + 1 AS cnt
            FROM tableB
            ORDER BY id, date;
        ");
        // 一時テーブルの内容確認用出力
        echo comm_table("
            SELECT id, FROM_UNIXTIME(date,'%Y-%m-%d') date, price, cnt FROM t1
        ", "一時テーブル");
        // さらに、MySQL では1つの一時テーブルを
        // 1つのクエリ内でJOINとかできないので、もう一つ作成
        mysql_query("
            CREATE TEMPORARY TABLE t2 AS
            SELECT id, date, price, cnt
            FROM t1;
        ");
        // 結合速度向上を期待してインデックス作成
        // 主となるほうは、どのみちシーケンシャルスキャン(全スキャン)なので
        // 従となるほうだけ作成
        mysql_query("
            CREATE INDEX t2idx ON t2(id);
        ");
        // 結合&結果出力!!
        echo comm_table("
            SELECT t1.id
                , (SELECT DISTINCT name FROM tableA WHERE id = t1.id) AS name
                , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date
                , t1.price price1
                , AVG(t2.price) price2
                , STD(t2.price) price3
            FROM t1
            LEFT JOIN t2 ON t2.id = t1.id
            WHERE t2.cnt BETWEEN t1.cnt AND t1.cnt + 4
            GROUP BY t1.id, t1.date, t1.price
            HAVING price1 > price2 + price3;
        ", "結果表示");
    }
    if ('' != $err = mysql_error($con)) { // エラー出力があった場合の出力
        echo "<br />\r\n直近のエラー : " . $err;
    }
    mysql_close();
}
?>
<hr />
<p>動作終了</p>
</body>
</html>

以上のように、SQLをいくつも使う必要があったりして面倒(面倒=トラブルの種)なので、

このような使い方をしている人も少なく、なかなか回答を得られなかったのかと・・・。

php+MySQLだったのでWeb系かと思ってたのですが、目的が違う場合は、

早いうちにDBのチョイスから考えなおしたほうが良いかもしれません。

ORACLEやDB2、SQLServerなどの商用DBの無料版もありますし、

完全なフリー(MySQLは完全ではない)がよければPostgreSQLなどもあります。

いずれも下記のように php から簡単に利用できるようになってますし、

利用方法については関数名が違うだけで、基本的には大きな違いはないです。

http://www.php.net/manual/ja/refs.database.php

id:dekapurio

打ち込みが遅く少し時間いただきましたが、うまく動作しました。

どうも有り難うございます。

今のところ、どうしてこうなったのかわからないところもあるのですが、これからゆっくりと理解していくことにします。

2010/02/06 00:35:45

その他の回答(1件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012010/02/05 21:54:31ここでベストアンサー

ポイント300pt

分析関数を備えたRDBMSなら苦も無く高速処理できるのですが、先の質問でも、

トラブルとなったように、まだサブクエリ内でLIMITを使う事すらできませんし、

どの道、内部的に何度も集計をやらせるような事にならざるを得ないので、

いっその事、通し番号を振ってしまうのが楽で処理も軽いかもしれないです。

(複数ユーザーが平行してシステムを利用しているような場面には向きません。

ユーザー定義関数などの作成も実質不可なMySQLで集計関係は難題の一つです。

ブログなどのようなシステムには向いているのですが・・・)


前置きが長くなりましたが、以下、一時テーブルを使った例(動作確認済み)です。

<html>
<head>
    <title>MySQLテスト</title>
</head>
<body>
<p>動作開始</p>
<hr />
<?php
//----------SQLを実行してHTMLのTABLEとして出力するためのルーチン
function comm_table($sqlstr, $caption){
    $ret = "";
    if ($res = mysql_query($sqlstr)) {
        $ret .= "<table border=\"1\">\n";
        if ($caption != "") {
            $ret .= "<caption>" . $caption . "</caption>\r\n";
        }
        $ret .= "<thead>\r\n";
        $ret .= "<tr>";
        $cols = mysql_num_fields($res);
        for ($lp1 = 0; $lp1 < $cols; $lp1++) {
            $ret .= "<th>" . mysql_field_name($res, $lp1) . "</th>";
        }
        $ret .= "</tr>\r\n";
        $ret .= "</thead>\r\n";
        $ret .= "<tbody>\n";
        while ($rows = mysql_fetch_array($res, MYSQL_NUM)) {
            $ret .= "<tr><td>" . join('</td><td>', $rows);
        }
        $ret .= "</tbody>\r\n";
        $ret .= "</table>\r\n";
    }
    return($ret);
}
//---------- メインルーチン
if ($con = mysql_connect('接続文字列')) {
    if((mysql_select_db('データベース名'))) {
        // カウント用変数の準備
        mysql_query("
            SET @c = 0;
        ");
        // MySQL には分析関数も無ければ、サブクエリの中でLIMITなどが使えないなど
        // まだまだ、いろいろと制約があるので、一時テーブルを作成
        mysql_query("
            CREATE TEMPORARY TABLE t1 AS
            SELECT id, date, price, @c:=@c + 1 AS cnt
            FROM tableB
            ORDER BY id, date;
        ");
        // 一時テーブルの内容確認用出力
        echo comm_table("
            SELECT id, FROM_UNIXTIME(date,'%Y-%m-%d') date, price, cnt FROM t1
        ", "一時テーブル");
        // さらに、MySQL では1つの一時テーブルを
        // 1つのクエリ内でJOINとかできないので、もう一つ作成
        mysql_query("
            CREATE TEMPORARY TABLE t2 AS
            SELECT id, date, price, cnt
            FROM t1;
        ");
        // 結合速度向上を期待してインデックス作成
        // 主となるほうは、どのみちシーケンシャルスキャン(全スキャン)なので
        // 従となるほうだけ作成
        mysql_query("
            CREATE INDEX t2idx ON t2(id);
        ");
        // 結合&結果出力!!
        echo comm_table("
            SELECT t1.id
                , (SELECT DISTINCT name FROM tableA WHERE id = t1.id) AS name
                , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date
                , t1.price price1
                , AVG(t2.price) price2
                , STD(t2.price) price3
            FROM t1
            LEFT JOIN t2 ON t2.id = t1.id
            WHERE t2.cnt BETWEEN t1.cnt AND t1.cnt + 4
            GROUP BY t1.id, t1.date, t1.price
            HAVING price1 > price2 + price3;
        ", "結果表示");
    }
    if ('' != $err = mysql_error($con)) { // エラー出力があった場合の出力
        echo "<br />\r\n直近のエラー : " . $err;
    }
    mysql_close();
}
?>
<hr />
<p>動作終了</p>
</body>
</html>

以上のように、SQLをいくつも使う必要があったりして面倒(面倒=トラブルの種)なので、

このような使い方をしている人も少なく、なかなか回答を得られなかったのかと・・・。

php+MySQLだったのでWeb系かと思ってたのですが、目的が違う場合は、

早いうちにDBのチョイスから考えなおしたほうが良いかもしれません。

ORACLEやDB2、SQLServerなどの商用DBの無料版もありますし、

完全なフリー(MySQLは完全ではない)がよければPostgreSQLなどもあります。

いずれも下記のように php から簡単に利用できるようになってますし、

利用方法については関数名が違うだけで、基本的には大きな違いはないです。

http://www.php.net/manual/ja/refs.database.php

id:dekapurio

打ち込みが遅く少し時間いただきましたが、うまく動作しました。

どうも有り難うございます。

今のところ、どうしてこうなったのかわからないところもあるのですが、これからゆっくりと理解していくことにします。

2010/02/06 00:35:45
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542010/02/05 23:38:53

ポイント300pt

要件が非常に分かりづらいですが。。。

MySQLの「サブクエリ中のLIMITの制限」、「一時表のジョインの制限」などは、他の方が具体的に説明してくれている通りです。

MySQL 5.1とのことなので、ストアドプロシジャにしてしまう方法もあります。

今回は、MySQLの「サブクエリ中でorder byの指定が可能」、「ユーザ変数」といったことを使った例を提示します。


1.ユーザ変数の初期化

set @id=null    -- id値の変化を拾うための変数
   ,@rank=0;    -- id値毎のdate降順の通番

2.検索

select
  B.`id`
 ,from_unixtime(`date`,'%Y-%m-%d') as hizuke
 ,rank
 ,B.`price` as price1
 ,GB.price2
 ,GB.price3
 from `tableA` as A
  inner join
  (select
     *
    ,@rank:=if(`id`=@id,@rank+1,1) as rank     -- id値が同じなら@rankを+1、変われば1に戻す
    ,@id:=if(`id`=@id,@id,`id`) as `upd_id`    -- id値が変われば@idを置換
    from `tableB`
    order by `id`,`date` desc
  ) as B
  on A.`id`=B.`id`
  inner join 
   (
    select
      `id`
     ,avg(`price`) as price2
     ,std(`price`) as price3
     from `tableB`
     group by `id`
   ) as GB
   on B.`id`=GB.`id`
      and B.`price` >= price2 + price3
      and rank<=2                             -- id毎に何件得るか
 order by B.`id`,hizuke desc
id:dekapurio

chuken_kenkou様、回答有難うございます。

コード通りに記載して出来ました!

説明が下手でわかりづらい文章にもにもかかわらず、その辺りも組んでいただいて申し訳ないです。

正直こんなに難しいSQL文になるとは想像もしてなかったので驚いているのですが、これから理解していきたいと思います。

どうも有り難うございました。

2010/02/06 01:16:27
  • id:chuken_kenkou
    まず、前置きですが。。。

    >以下のSQL文によって、全てのpriceの平均値や標準偏差の取得はできました。

    一般的なSQLとして間違っているし、MySQLでも保証されないことをやっていますよ?

    group by指定時、selectの選択リストに書けるのは、group byで指定した列、集計(集合)関数、定数などだけです。group byで指定していない列名を書くことはできません。
    主要なRDBMSでは構文エラーになりますが、MySQLではこの部分に拡張仕様を持っています。

    それは、「構文エラーにはしないけど、group byで結果的に一意になるなら指定してもよい。しかし、一意にならないなら結果を保証しない」というものです。

    例えば、次のようなselect文を書いたとします。

    select c1,c2,c3,avg(c4)
    from t1
    group by c1,c2

    group byで指定のないc3は、標準SQLや主要なRDBMSでは指定できません。
    しかし、MySQLでは、標準インストールした状態では、エラーにもならず、c3の値として何が返るか保証していません。group by c1,c2により、c3も一意になる場合のみ、こういった使い方を許しています。

    つまり、標準SQLや他のRDBMSでも使えるSQL、MySQLでも動作が保証されるのは、次のように書いても結果が同じになる場合のみです。

    select c1,c2,c3,avg(c4)
    from t1
    group by c1,c2,c3

    http://dev.mysql.com/doc/refman/5.1/ja/group-by-hidden-fields.html

    >これに加えてやりたいことは、
    >1.「それぞれのIDの直近日付の20件につき」IDごとにtableB.priceの平均値と標準偏差を算出したい
    >2. price >= (price2 + price3) のものだけ抽出したい

    IDと日付で、行がユニークになるのでしょうかね?
    どういう母体データから、どういう結果を得たいのでしょうか?
  • id:dekapurio
    chuken_kenkou 様 返信有難うございます。

    丁寧に解説していただきとても参考になりました。

    >SQLとして間違っている

    以下のようなSQLで正しいでしょうか?

    SELECT
    tableA.id, tableB.price price1, AVG(tableB.price) price2, STD(tableB.price) price3
    FROM
    tableA , tableB
    WHERE
    tableA.id = tableB.id
    GROUP BY
    tableB.id
    ORDER BY
    tableB.date DESC


    >IDと日付で、行がユニークになるのでしょうかね?
    そうなります。

    >どういう母体データから、どういう結果を得たいのでしょうか?

    結果につきましては、「それぞれのIDの直近日付の20件のみ」でIDごとにtableB.priceの平均値と標準偏差を算出し、「price >= (price2 + price3)」という条件にあっているものだけ表示したいと考えています。

    テーブル内容としましては、
    tableAは商品一覧テーブルで、tableBは売上表テーブルとなっています。
    【tableA】
    id, name
    1 , りんご
    2 , みかん
    3 , いちご
    【tableB】
    id, date, price
    1 , 1265375594 ,50
    1 , 1260265023 ,57
    2 , 1265375594 ,150
    2 , 1260265023 ,160
  • id:kn1967
    間違い箇所2点
    (1)+ 4
    + 19
    テスト用に作ったtableBの件数が200件なので当日を含めた直近5件にしてました。
    (2)price1 > price2 + price3
    price1 >= price2 + price3
    単純に=が抜けてました(>と=は実際は半角です。)
    スミマセンが、それぞれ変更してください。
  • id:kn1967
    コメントが無いようなので横から失礼。

    >SQLとして間違っている

    FROM tableA , tableB
    条件が指定されていない場合の結合は
    tableA が 3レコード
    tableB が 4レコード
    だとすれば、この時点でまずは 3 x 4 = 12 といことで・・・
    1 , りんご , 1 , 1265375594 ,50
    1 , りんご , 1 , 1260265023 ,57
    1 , りんご , 2 , 1265375594 ,150
    1 , りんご , 2 , 1260265023 ,160
    2 , みかん , 1 , 1265375594 ,50
    2 , みかん , 1 , 1260265023 ,57
    2 , みかん , 2 , 1265375594 ,150
    2 , みかん , 2 , 1260265023 ,160
    3 , いちご , 1 , 1265375594 ,50
    3 , いちご , 1 , 1260265023 ,57
    3 , いちご , 2 , 1265375594 ,150
    3 , いちご , 2 , 1260265023 ,160
    というものを準備した事に相当します。

    この時点であまり効率の良い流れとは言えない場合があります。
    回答欄でも使ってますが条件を指定しての結合も考慮しましょう。
    ※パフォーマンスについてはそれなりの知識が揃わないと、
     会話が通じませんので今のところは「考慮しましょう」までにします。

    話をもどして、次に
    WHERE tableA.id = tableB.id
    で件数が絞られて
    1 , りんご , 1 , 1265375594 ,50
    1 , りんご , 1 , 1260265023 ,57
    2 , みかん , 2 , 1265375594 ,150
    2 , みかん , 2 , 1260265023 ,160
    になります。

    データが絞り込めたら、
      GROUP BY tableB.id
    でグループ化を実施してから SELECT と進むのですが、
    ここで問題が発生します。

    第一に、
    仮に tableB.id が 2 のものを対象に考えてみましょう。
    2 , みかん , 2 , 1265375594 ,150
    2 , みかん , 2 , 1260265023 ,160
    上記2件となりましたが、さて、
    tableB.price price1 のprice はどれを持ってきたら良いでしょうか?
    指示されてませんので、通常はここでエラーとなります。

    第二に、
    SQL は テーブルの各フィールドに施されている制約は認知しません。
    ※制約とはUNIQUE(重複不可)とか、NOT NULLといったようなものです。

    それはつまり、tableA.id にUNIQUE制約が課せられていて重複は無いとしても、
    SQLはそんな事は知りませんので、
      GROUP BY tableB.id
    で括った場合に、tableB.id 1件につき tableA.idが何件あるかなどは、
    SQLからは想像もつかず、
      SELECT tableA.id, tableB.price price1
    と書かれた場合に、tableA のどのレコードの id を持ってきたらよいのか、
    判らずエラーになります。

    テーブルの制約等を知っているシステム製作者の目から見れば、
      WHERE tableA.id = tableB.id
    だけで良いように思ってしまうのですが、そうではないという事です。

    MySQLの拡張仕様として、このような曖昧な操作を容認して、
    結果を返してくれる場合もありますが、推奨できる方法とは言えません。
    (仕様を熟知して、トリッキーに曖昧さを逆利用するならば話は別ですが、
     そうでなければ、偶然に頼った不完全なものという事です。)

    曖昧さを回避するには、上記のようにSQLがどのような順序で実行され、
    それぞれの段階で、どのような作業を行っているかを知らないといけません。
    それがSQLを覚えるということであり、今すぐにというのは無理でしょう。
    まずは書籍に載っているものなど、色々なサンプルを見比べていただくしかないです。

    焼きなおした一例としては下記のような感じです。
    SELECT t1.id
    , (SELECT DISTINCT name FROM tableA WHERE id = t1.id) AS name
    , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date
    , t1.price price1
    , AVG(t2.price) price2
    , STD(t2.price) price3
    FROM tableB t1
    LEFT JOIN tableB t2 ON t2.id = t1.id
    GROUP BY t1.id, t1.date, t1.price
    既にお気づきかもしれませんが、これは回答欄のSQLから抜き出してます。
    回答では、上記に加えてやりたいことの2点を実現するために少し増えてるだけです。
    ただ、その前段階として、ごにょごにょと面倒な手続きを踏んでたりするわけですが・・・。
  • id:dekapurio
    kn1967 様、お世話になっております。
    うまく動作しました。コードを書いて頂いてありがとうございます。
    また、SQLの要点を解説していただき参考になります。

    >tableB.price price1 のprice はどれを持ってきたら良いでしょうか?
    >指示されてませんので、通常はここでエラーとなります。

    こういったことを常に考えながらやると、正しいSQLがかけそうですね。
    SQLの本も2冊ほど購入してきたので、夜な夜な眺めていこうと思ってます。

    >ORACLEやDB2、SQLServerなどの商用DBの無料版もありますし、完全なフリー(MySQLは完全ではない)がよければPostgreSQLなどもあります。
    もしDBを変えるなら書籍が豊富なORACLEかPostgreSQLがいいかなと考えていますが・・どうですかね。
    とりあえずはこれからやりたい用途とかを考えつつ調べてみます。

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

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

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

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