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

SQL文について質問です。

前回質問させて頂いた内容と似ていてしつこい感じで気が引けるのですが、完成型がようやく自分でもわかってきたので再度質問させて頂きます。

【tableA:商品一覧テーブ】
id, name
1 , りんご
2 , みかん
3 , いちご


【tableB:売上表テーブル】
id, date, price
1 , 1265375594 ,50
1 , 1260265023 ,57
2 , 1265375594 ,150
2 , 1260265023 ,160


【抽出したいデータ】
id, name, date, price, price2, price3
1, りんご,,,,
2, みかん,,,,
3, いちご,,,,
4, ×××,,,,
5, ○○○,,,,
6, △△△,,,,


date = IDごとの最新日付
price = IDごとの最新日付の値段
price2 = IDごとの最新日付から数えて20件のpriceの平均値
price3 = IDごとの最新日付から数えて20件のpriceの標準偏差

・各IDごとのデータがほしい
・商品によっては売上の無い日もある

MYSQLは集計が弱いということですが、売上表が20万件くらいありますので、パフォーマンス優先で可能であればよろしくお願いします。

(参考)
仕様:MYSQL5.1
日付:UNIXTIMEのINT型

●質問者: dekapurio
●カテゴリ:ウェブ制作
✍キーワード:MySQL name SQL いちご みかん
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● chuken_kenkou
●100ポイント

まず、「どんな基のデータから、どういう結果を得たい」ということが、これまでの質問では具体的に提示されていません。一部分を限定的に提示されたりもしているのですが、それが限定された情報の範囲で、要件を判断するには不十分なものになっています。

そのために、アドバイスする側も「こんな結果を得たいのだろうか?」と推察して回答することとになっているし、質問する側も「それで期待した通り、動いた」と誤った判断をすることになっています。

今回提示された要件が正しいなら、「サブクエリ中でlimitを指定する場合、結果は1件以下でなければならない」というMySQLの制約に引っ掛からないようにselect文を書けます。

select
 A.`id`
 ,A.`name`
 ,from_unixtime(x.max_date,'%Y-%m-%d') as `date`
 ,(select `price` from `tableB`
 where `id`=x.`id`
 and `date`=x.max_date) as `price`
 ,(select avg(price) from `tableB`
 where `id`=x.`id`
 and `date`<=x.max_date
 order by `date` desc
 limit 0,20
 ) as `price2`
 ,(select std(price) as avg_price from `tableB`
 where `id`=x.`id`
 and `date`<=x.max_date
 order by `date` desc
 limit 0,20
 ) as `price3`
 from `tableA` as A
 left join
 (select
 `id`
 ,max(`date`) as max_date
 from `tableB`
 group by `id`) as x
 on A.`id`=x.`id`
 order by x.`id`


売上表が20万件くらいありますので、パフォーマンス優先で

これまでの質問で、私が知っている限り、「どういうインデクス定義をしているか」といった情報が、まったく提示されていません。

まず、現状、どういうインデクス定義になっているか、どのくらいselect文で時間が掛かっているかといった情報も提示してください。


MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.3.1 DESCRIBE 構文

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.5.4.17 SHOW INDEX 構文

※古いMySQLでは、「show indexes」という構文であったが、日本語マニュアルでは「show index」だけ記載されている。英文マニュアルでは、両方記載されている。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 21 INFORMATION_SCHEMA データベース

※情報スキーマ:

表名、列名などの管理方式は、各RDBMSによりそれぞれ独自で、その参照方法も特別なコマンドを用いたり、SQLで検索できたりと様々であった、標準SQLで定義情報の参照方法を規定したものが、情報スキーマであり、各RDBMSでも実装が進んでいる。例えば、表名一覧を得たいなら、information_schemaのtables表のtable_name列に格納されている。ただし、例えば「データベース」というオブジェクトの扱いが各RDBMSにより異なるので、実際にどういう値がどの表のどの列に入っているか、あるいは入っていないかといった仕様差がある。


2 ● chuken_kenkou
●100ポイント

ストアド・プロシジャにより、集計用のための作業用の表に、対象行をコピー&集計する例です。


1.集計のための作業用の表の定義

-- 集計対象の行を保存する表
-- MySQLの一時表(temporary table)は、join等の制限があるので、
-- ここでは通常の表で定義
drop table if exists `tmp_tableB`;
create table `tmp_tableB`
(`id` int
,`seq` int
,`date` int
,`price` int
,primary key(`id`,`seq`,`date`)
);

2.tableBから集計用の表に、対象データをコピーするストアド・プロシジャの定義

-- tableBから、集計対象の行をコピー
drop procedure if exists sel_ex1; -- 存在したら削除

delimiter // -- 終端記号の変更
create procedure sel_ex1
(in pRunMode varchar(5) -- null:normal ,'d':debug(for MySQL Command Line Client)
,in pTruncTbl varchar(3) -- null:truncate ,'n':not truncate
,in pCDate int -- 最新日 unixtime
,in pRank int -- id毎に何番目まで対象か
,out pRslt varchar(30))
--
begin
-- 
 declare fEod tinyint; -- end of dataチェックフラグ
 declare fOn tinyint default 1;
 declare fOff tinyint default 0;
 declare RunDebug char(1) default 'd';
 declare RunTruncTbl char(1) default 't';
 declare cNull char(4) default 'null';
--
 declare xCDate int;
 declare xMaxDate int;
 declare xId1 int;
 declare xId2 int;
 declare xDate int;
 declare xPrice int;

--
 declare vRank int;
 declare vRCnt int;
 
-- カーソル宣言
--
-- id毎に、最新の日付を得るカーソル
 declare cr1 cursor for
 select
 `id`
 ,max(`date`)
 from `tableB`
 where `date`<=xCDate
 group by `id`
 ;
-- id毎に最新日付以前の行を、指定数だけ検索するカーソル
 declare cr2 cursor for
 select
 `id`
 ,`date`
 ,`price`
 from `tableB`
 where `id`=xId1
 and `date`<=xMaxDate
 order by `date` desc
 ;

-- 例外宣言
 declare continue handler for not found set fEod=fOn;

 if pTruncTbl is null or pTruncTbl<>'n' then
 if pRunMode like concat('%',RunDebug,'%') then
 select '*** truncate table start ***' as msg;
 end if;
 truncate table `tmp_tableB`;
 if pRunMode like concat('%',RunDebug,'%') then
 select '*** truncate table end ***' as msg;
 end if;
 else
 if pRunMode like concat('%',RunDebug,'%') then
 select '*** truncate table skip ***' as msg;
 end if;
 end if;

 set pRslt=null;
 set fEod=fOff;

-- 引数がnullなら、仮定値を設定
 if pCDate is null then
 set xCDate=unix_timestamp(current_timestamp());
 else
 set xCDate=pCDate;
 end if;
 if pRank is null or pRank<=0 then
 set vRank=20;
 else
 set vRank=pRank;
 end if;

 if pRunMode like concat('%',RunDebug,'%') then
 select concat('pCDate=',coalesce(pCDate,cNull)
 ,',pRank=',coalesce(pRank,cNull)
 ,',xCDate=',coalesce(xCDate,cNull)
 ,',vRank=',coalesce(vRank,cNull)) as msg;
 end if;

 open cr1;
 fetch cr1 into xId1,xMaxDate;

 while fEod=fOff do
 if pRunMode like concat('%',RunDebug,'%') then
 select concat('xId1=',xId1
 ,',xMaxDate=',xMaxDate) as msg;
 end if;

 set vRCnt=0;
 open cr2;
 fetch cr2 into xId2,xDate,xPrice;

 while fEod=fOff and vRCnt<vRank do
 if pRunMode like concat('%',RunDebug,'%') then
 select concat('xId2=',xId2
 ,',xPrice=',xPrice) as msg;
 end if;
 set vRCnt=vRCnt+1;
-- 集計対象の行をコピー
 insert into `tmp_tableB`
 values(xId2,vRCnt,xDate,xPrice);
--
 fetch cr2 into xId2,xDate,xPrice;

 end while;
 close cr2;

 set fEod=fOff; -- cr1の検索継続のため、eodフラグをリセット
 fetch cr1 into xId1,xMaxDate;

 end while;
 close cr1;
end;
//
delimiter ; -- 終端記号を元に戻す

3.ストアド・プロシジャを利用して、tableBからコピーして集計するphp

※前回質問の回答で、kn1967さんが作成したコードを流用させてもらいました。

<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";
 } else {
 $message = 'Invalid query=> ' . mysql_errno() . ": " . mysql_error() . "\n";
 $message .= 'Whole query=> ' . $sqlstr;
 die($message);
 }
 return($ret);
}
//---------- メインルーチン
// http://codezine.jp/article/detail/4575?p=3 参照
// 構文$con = mysql_connect('localhost','root','');ではうまくいきません。
// ストアドプロシージャからPHPに結果セットを返すためには、マルチステートメント接続
// オプションかマルチ結果オプション(またはその両方)を使う必要があるからです。
// ルーチンから結果セットが返されなければ、どちらのオプションも必要ありません。 
if ($con = mysql_connect("localhost","root","パスワード",false,65536)) {
 if((mysql_select_db("test1"))) {

 // ストアドの実行
 $res = mysql_query("
 call sel_ex1(null -- null:normal ,'d':debug(for MySQL Command Line Client)
 ,null -- 作業用の表を空にしてからコピー
 ,null -- 対象とする最新日付
 ,5 -- rows / id
 ,@msg)
 ") or die(mysql_error());
 
 // 作業用の表の内容を出力!!見やすくするため、tableAを結合
 echo comm_table("
select
 A.`name`
 ,tB.*
 ,from_unixtime(`date`,'%Y-%m-%d') as hizuke
 from `tmp_tableB` as tB
 inner join `tableA` as A
 on tB.`id`=A.`id`
 order by tB.`id`,`seq`;
 ", "作業用の表を表示");

 // 結合&結果出力!!
 echo "<br>";
 echo comm_table("
select
 A.`id`
 ,A.`name`
 ,MaxB.`date`
 ,from_unixtime(MaxB.`date`,'%Y-%m-%d') as hizuke
 ,MaxB.`price`
 ,price2
 ,price3
 from `tableA` as A
 inner join
 (select
 `id`
 ,avg(`price`) as price2
 ,std(`price`) as price3
 from `tmp_tableB`
 group by `id`) as GB
 on A.`id`=GB.`id`
 inner join
 (select
 `id`
 ,`date`
 ,`price`
 from `tmp_tableB`
 where `seq`=1) as MaxB
 on A.`id`=MaxB.`id`
 order by A.`id`
 ", "結果表示");

 }
 if ('' != $err = mysql_error($con)) { // エラー出力があった場合の出力
 echo "<br />\r\n直近のエラー : " . $err;
 }
 mysql_close();
}
?>
<hr />
<p>動作終了</p>
</body>
</html>

4.チューニング

price列まで含めたインデクスを定義すれば、集計自体は速くできますが、コピー(insert)は遅くなります。insert前はインデクスを削除しておき、集計前にインデクスを定義するといった方法も考えられます。

◎質問者からの返答

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

色々な方法があるものですね。

コードの多さにちょっと圧倒されていますが・・

ストアド・プロシジャの部分ですが、

調べてみたのですが、ちょっと使い方がわからないです。


3 ● kn1967
●200ポイント ベストアンサー

こちらはストアドプロシージャではなくストアドファンクションを使ってみました。

作業用ファイルを用いず、対象を絞る事によって高速化を図ってます。

(欲しい時に欲しい分だけ集計するというデータベース本来の方法です。)


下記phpを呼び出す時は、欲しい範囲を下記のような具合に指定します。

(この指定は結果表示する範囲の指定です。

下記の場合でも計算には2009-12-01以前のデータを当然使います。)

http://localhost/a.php?sday=2009-12-01&eday=2009-12-31

さらに対象IDも絞る事が出来ます。こちらは無くても動きます。

(下記ならばID2と3と5と8だけを計算します。

もっと長く続けることも出来ます。)

http://localhost/a.php?sday=2009-12-01&eday=2009-12-31&idlist=2+3+5+8

php本体

<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\">";
 if ($caption != "") {
 $ret .= "<caption>" . $caption . "</caption>";
 }
 $ret .= "<thead>";
 $ret .= "<tr>";
 $cols = mysql_num_fields($res);
 for ($lp1 = 0; $lp1 < $cols; $lp1++) {
 $ret .= "<th>" . mysql_field_name($res, $lp1) . "</th>";
 }
 $ret .= "</tr>";
 $ret .= "</thead>";
 $ret .= "<tbody>";
 while ($rows = mysql_fetch_array($res, MYSQL_NUM)) {
 $ret .= "<tr><td>" . join('</td><td>', $rows);
 }
 $ret .= "</tbody>";
 $ret .= "</table>";
 }
 return($ret);
}
//---------- メインルーチン
if(!isset($_GET['sday']) || !isset($_GET['eday'])) {
 echo "パラメータが足りません!!";
} elseif (!$con = mysql_connect('localhost', 'root')) {
 echo "MySQLに接続できませんでした!!";
} else {
 if(!mysql_select_db('test')) {
 echo "データベースに接続できませんでした!!";
 } else {
 if(!mysql_query('SET NAMES SJIS;')) { exit; }
 $sday = strtotime($_GET['sday'] . ' 00:00:00');
 $eday = strtotime($_GET['eday'] . ' 23:59:59');
 if (isset($_GET['idlist'])) {
 $idlist = ' AND t1.id in (' . str_replace(' ', ',', $_GET['idlist']) . ')';
 }
 $err = '';
 // ストアドファンクション作成準備(同名のものが既存であれば削除します。)
 if (!mysql_query("
 DROP FUNCTION IF EXISTS fn1
 ;")) { $err = mysql_error($con);
 // ストアドファンクション作成
 } elseif (!mysql_query("
 CREATE FUNCTION fn1 (w_id INT, w_date INT) RETURNS INT READS SQL DATA
 RETURN (
 SELECT date
 FROM tableB
 WHERE id = w_id AND date <= w_date
 ORDER BY date DESC
 LIMIT 19, 1
 );")) { $err = mysql_error($con);
 // 結合&出力
 } else {
 echo comm_table("
 SELECT t1.id
 , (SELECT DISTINCT name FROM tableA WHERE id = t1.id) AS name
 , t1.date
 , FROM_UNIXTIME(t1.date,'%Y-%m-%d') date2
 , t1.price price1
 , AVG(t2.price) price2
 , STD(t2.price) price3
 FROM tableB AS t1
 LEFT JOIN tableB AS t2 USING(id)
 WHERE t1.date BETWEEN $sday AND $eday
 $idlist
 AND t2.date BETWEEN fn1(id, t1.date) AND t1.date
 GROUP BY t1.id, t1.date, t1.price
 ORDER BY id, t1.date DESC
 ;", "結果表示"
 );
 $err = mysql_error($con);
 }
 if ($err != '') { // エラーがあった場合の出力
 echo "<br />エラー : " . $err;
 }
 }
 mysql_close();
}
?>
<hr />
<p>動作終了</p>
</body>
</html>

確かにストアド使うとスッキリしますね。

ここでのストアドファンクションの役割は、idとdateを与えれば、

当日を含め20件前のdateを探してくるというものです。

これでカウントするという面倒なロジックは一切不要になりました。

※エラー監視ロジックも少し見直しました。

※テーブル作成部分も余計な改行コードは削りました。

※price1 <= price2 + price3 にて、さらに結果を絞りたい場合は、

前回同様に HAVING を追加しても結構ですし、idlistの指定部分を参考に、

パラメータで切り替えできるようにしても良いかもしれません。

◎質問者からの返答

kn1967さま、回答有難うございます。

コードも解りやすくとても参考になります。

1万件くらいのデータで試しているのですが、

どうしても呼び出しでタイムアウトになってしまうんですよね。

やはり、データを全部読ませなければならないので、時間はかかってしまいますよね。

テーブル構造からやり直すか、毎日計算させて別テーブルにINSERTさせる方法で

少しがんばってみようと思います。


4 ● km1967
●0ポイント

長い・・・

関連質問


●質問をもっと探す●



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