人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

(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 はたぶん使えません。
説明に足りない部分があればご指摘ください。

●質問者: dekapurio
●カテゴリ:ウェブ制作
✍キーワード:AVG GROUP name PHP SELECT
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● kn1967
●300ポイント ベストアンサー

分析関数を備えた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

◎質問者からの返答

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

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

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


2 ● chuken_kenkou
●300ポイント

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

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
◎質問者からの返答

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

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

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

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ