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型

回答の条件
  • 1人2回まで
  • 登録:2010/02/07 01:16:06
  • 終了:2010/02/13 15:08:12

ベストアンサー

id:kn1967 No.3

kn1967回答回数2915ベストアンサー獲得回数3012010/02/10 03:43:10

ポイント200pt

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

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

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


下記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の指定部分を参考に、

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

id:dekapurio

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

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

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

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

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

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

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

2010/02/10 18:17:42

その他の回答(3件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542010/02/08 01:24:44

ポイント100pt

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

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

今回提示された要件が正しいなら、「サブクエリ中で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 5.0以降なら、情報スキーマが実装されている

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

※情報スキーマ:

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

id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542010/02/09 00:05:39

ポイント100pt

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


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前はインデクスを削除しておき、集計前にインデクスを定義するといった方法も考えられます。

id:dekapurio

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

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

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

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

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

2010/02/09 02:49:04
id:kn1967 No.3

kn1967回答回数2915ベストアンサー獲得回数3012010/02/10 03:43:10ここでベストアンサー

ポイント200pt

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

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

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


下記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の指定部分を参考に、

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

id:dekapurio

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

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

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

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

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

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

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

2010/02/10 18:17:42
id:km1967 No.4

km1967回答回数541ベストアンサー獲得回数402010/02/13 15:02:08

長い・・・

  • id:kn1967
    前回質問( http://q.hatena.ne.jp/1265347102 )の、
    条件2を外したいだけであれば、私のコードなら、
      HAVING price1 >= price2 + price3
    id:chuken_kenkou さんのコードなら、
      and B.`price` >= price2 + price3
    をそれぞれ削るだけです。

    ところで・・・、毎度毎度集計させるのも無駄な話ですから、
    tableC(id, name, date, price, price2, price3)を作って、
    集計結果を格納しておいてはいかが?

    日付が変わって新しいデータが入ってきたら、
    その分だけを計算させて tableC に追記という形にしていけば、
    日々少しの集計時間だけで、参照時に計算は発生せず、
    ハードディスクの容量はその分食いますが、応答性は抜群ですよ。

    いろんな人の意見も聞いてみたほうがいいと思うので、
    今日は、このあたりにしておきます。
    (このヒントだけで、出来てしまいそうなスキルは既に・・・?)
  • id:dekapurio
    kn1967様、お世話になってます。

    >tableC(id, name, date, price, price2, price3)を作って、集計結果を格納しておいてはいかが?
    確かに呼び出すだけでいいので楽ですよね。
    とりあえずはこの計算ができたら、そちらの方向にもっていくつもりでいます。

    一応レスポンスが少し早かったという理由で、chuken_kenkouさんのコードを使って出来たと思っていたのですが、
    よくよく個別に計算してみるとAVGの計算が違っている(最新日付から順にとってきていない)ことに気づいたので(たぶん標準偏差も)、
    色々といじってみたものの解らずで・・・ちょっと困っています。
  • id:kn1967
    お気づきかと思ってたのですが・・・、採用されたほうのコードは
    20日前といった要素がまったくなく移動平均には対応してません。
    (商取引や株取引などを行っていれば当然のように使う指標ですが、
     そういった事とは無関係な場に居られる人だと、移動平均なんて、
     使う機会も無いでしょうし、がんばってコードを書いたのでしょうから、
     責める訳にはいきませんね。)

    かのコードを対応させようとしても、私が前回回答で書きましたように、
    「サブクエリではLIMITが使えない」などの諸問題があるため無理なんです。

    何かトリッキーな方法でもあればと私も探してたりはするのですが・・・。
  • id:chuken_kenkou
    >MYSQLは集計が弱いということですが、

    出典は何ですか?
    オープンソースであり、商用RDBMSに比べれば、機能的な不足はあります。
    しかし、ちゃんとした設計をすれば、それなりに性能は出せます。



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


    性能優先のため、テーブル設計を変えてもいいですか?
    適当に設計した「テーブルの形を維持し続ける」ことが大前提であるなら、仮に高価な商用RDBMSを導入しても、出せる性能には限界があります。
  • id:dekapurio
    集計が弱いというのは、サブクエリでLIMITが使えない等の制約があるという意味で書きました。
    よく理解せずに書いてしまいまして^^;

    >性能優先のため、テーブル設計を変えてもいいですか?

    テーブルの設計が変えれないんですよね・・。
    このテーブル構成では、ちょっと厳しいかもしれませんね。
  • id:chuken_kenkou
    登録:2010-02-07 01:16:06
    の回答は、サブクエリ中で limit を使えているのですが、avg関数 や std関数した結果をソートする方法になっているので、”正しい動作”になっていません。

    訂正版を検討しますので、締め切るのは保留してください。

    MySQL 5.0以降とのことでしたので、ストアドプロシジャで集計してしまうとか、集計済の表や最新の行だけの表をトリガで作ってしまうといった方法もあります。

    なお、性能を気にするのであれば、登録:2010-02-07 01:16:06 の回答で求めたように、インデクスの定義等の提示をお願いします。
  • id:dekapurio
    chuken_kenkou様 

    回答ありがとうございます。
    わたしが未熟なばかりに、なかなか理解できずに思うようなデータを渡せず申し訳ないです。

    インデクスの定義ですが、データ型の指定と数値の長さ以外は何もしていないという状況です。
    性能といいましてもわたしの場合、60秒タイムアウトにならない程度ということで、無視してもらって結構です。
  • id:chuken_kenkou
    2010-02-09 00:05:39の回答で、FETCHしながらINSERTする方法を提示しましたが、INSERT~SELECTの形にできましたね。

    ちょっと考えてみます。
  • id:chuken_kenkou
    >コードの多さにちょっと圧倒されていますが・・

    デバッグ用の機能とか、パラメタが省略された場合の仮定値を決めるとか、機能を入れすぎましたね。

    >ストアドプロシジャの部分ですが、調べてみたのですが、ちょっと使い方がわからないです。

    MySQL Command Line Clientを使えるなら、MySQLに接続し、データベースを選択した後は、表やインデクスの定義と同じように、create procedureでストアドプロシジャを定義します。
    MySQL Command Line Clientの場合、「;」が区切り文字と認識されますが、ストアドプロシジャのボディ部分の文も「;」を区切りと認識されてしまうので、create procedure実行前に、

    delimiter // -- 終端記号を // に変更

    create procedure実行後に、

    delimiter ; -- 終端記号を ; に戻す

    といった作業が必要になります。

    提示済のストアドプロシジャを、デバッグ等のオプションをやめ、INSERT SELECT化したコードを以下に示します。

    -- ストアド ここから
    create procedure sel_ex1
    (in pCDate int -- 対象とする最新日付
    )
    begin
    --
    declare fEod tinyint; -- end of dataチェックフラグ
    declare fOn tinyint default 1;
    declare fOff tinyint default 0;
    --
    declare xMaxDate int;
    declare xId1 int;

    -- カーソル宣言
    --
    -- id毎に、最新の日付を得るカーソル
    declare cr1 cursor for
    select
    `id`
    ,max(`date`)
    from `tableB`
    where `date`<=pCDate
    group by `id`
    ;

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

    -- 作業用の表を空にする
    truncate table `tmp_tableB`;

    set fEod=fOff;
    open cr1;
    fetch cr1 into xId1,xMaxDate;

    while fEod=fOff do

    set @Seq=0; -- ユーザ変数(@変数名)を使う 

    insert into `tmp_tableB`
    select
    `id`
    ,@Seq:=@Seq+1 -- id毎に通番を付与
    ,`date`
    ,`price`
    from `tableB` as B
    where `id`=xId1
    and `date`<=xMaxDate
    order by `date` desc
    limit 5 -- id毎に何件
    ;
    fetch cr1 into xId1,xMaxDate;

    end while;
    close cr1;
    end;
    -- ストアド ここまで

    これを、MySQL Command Line Clientで実行する場合は、

    delimiter // -- 終端記号を変更

    <<<この部分にストアドのコードを入れる>>>

    //
    delimiter ; -- 終端記号を元に戻す

    といった方法になります。
    既に同じ名前のストアドを登録していて、それを置換(一旦、削除して再定義)したい場合は、

    drop procedure if exists sel_ex1; -- 存在したら削除

    といった文を事前に実行すると便利です。if existsの指定がないと、存在しなかった場合はエラーになってしまいますが、if existsがあれば、無視してくれます。

    ストアドとphpから定義する場合は、提示済のコードを流用すると、こんな感じです。

    // 既にストアドが存在していれば、削除
    $res = mysql_query("
    drop procedure if exists sel_ex1;
    ") or die(mysql_error());

    // ストアドの定義
    $res = mysql_query("

    <<<この部分にストアドのコードを入れる>>>

    ") or die(mysql_error());

    // ストアドの定義済を確認
    echo comm_table("
    show procedure status like 'sel_ex1';
    ", "定義確認");


    また、ストアドの実行方法は、パラメタを簡素化して個数も減らしたので、次のようになります。

    <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(
    unix_timestamp(current_timestamp())
    );
    ") or die(mysql_error());

    // 作業用の表の内容を出力!! この時点でのtableAのジョインはやめ
    echo comm_table("
    select
    *
    ,from_unixtime(`date`,'%Y-%m-%d') as hizuke
    from `tmp_tableB`
    order by `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>

  • id:chuken_kenkou
    一箇所、誤解を招く可能性があったので訂正

    これを、MySQL Command Line Clientで実行する場合は、



    これを、MySQL Command Line Clientで定義する場合は、


    ストアドプロシジャの実行は、MySQL Command Line Clientでもphpでも、MySQLのSQLであるcallを使います。
    今回提示したストアドでは、テストすることも考え、集計対象とする”最新日付”をパラメタで与えるようにしました。

    call sel_ex1(最新日付)

    最新日付は、int型でunixtime形式です。

    -- 現在日時を最新として集計
    call sel_ex1(
    unix_timestamp(current_timestamp())
    );

    -- 2月9日を最新日付とする場合
    call sel_ex1(
    unix_timestamp('2010-02-09')
    );
  • id:dekapurio
    chuken_kenkou 様

    コメント有難うございます。

    早速やってみまして、
    Table 'kabu.tmp_tableb' doesn't exist と出ましたが、
    転載ミスだと思うので、これからじっくり試させて頂きます。
  • id:chuken_kenkou
    >Table 'kabu.tmp_tableb' doesn't exist

    まずは、tryされたようで、正直いって、少しほっとしました!

    「デバッグ」機能や「いろいろなオプション」を(ある意味、趣味で)盛り込んでしまったことで、逆に混乱させてしまったようで、ちょっと心配してしまいました。

    コメントで示しましたが、最低限の仕様を実装するだけなら、大幅にコードを省略できました。

    ただ、「どうなれば、正しいか?」ということが曖昧だったこともあり、デバッグ機能を組み込んでおけば、質問者側も回答者側も便利だと思いまして。

    MySQLでは、テーブル、ストアドプロシジャも、「データベース」というオブジェクトの配下資源という扱いになります。
    したがって、kabu というデータベースを選択した後に、今回提示した作業用のtmp_tableBを作成したり、ストアドプロシジャを定義&実行してもらえれば、要件を満たすことができたり、あるいは前進できると思います。

    もし、実際に扱うデータベースは kabu だけれども、それを扱うアプリ群は異なるデータベース群であるといった場合は、

    tableB を参照する部分では、

    kabu.tableB

    というように、表名をデータベース名で参照する方法があります。

    データベースは kabu だけど、ストアドの定義ユーザでは、 kabu に対してアクセス権限がないといった場合は、また別の対応(そちらの運用、定義の問題なのか、あるいは利用者側の定義で可能か)が必要なのかといったことは、現状では判断できません。

    後述したストアドは、

    tableB → tmp_tableB

    に、コピーする処理を実装しています。
    したがって、事前に最初のコードでも示した tmp_tableB を定義した後で、ストアドを実行してください。

    この作業用の表を、空にしたり、対象行の格納&集計は、ストアドでやっています。


    -- 集計対象の行を保存する表
    -- 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`)
    );



  • id:chuken_kenkou
    tableBの各id毎の行数で、20行未満しか存在しないものはないでしょうか?

    kn1967さんの 2010-02-10 03:43:10 提示の ストアドファンクションですが、仮にid毎の対象データが20行未満だった場合、20番目に古い日付を拾えませんよね?
  • id:kn1967
    >1万件くらいのデータで試しているのですが、
    >どうしても呼び出しでタイムアウトになってしまう

    こちらは余剰マシンを使ったVineLinux5.0サーバなのですが、
    CPUは10年以上前のMMX-Pentium200MHzでメモリもSD-RAM256MBです。
    (HDDはSATAタイプを繋いでるので20MB/秒の転送速度を何とか保ってます。)
    これは昨今の安いマシンに比べても数百分の一の能力しかないものです。

    一万件ものテストデータは作ってないので、テストはしてませんが、
    同一サーバ上で動かしているPostgreSQLで、もっともっと複雑なSQLで、
    数万件のデータを集計させても、数秒で終わる性能は有しているので、
    いくらMySQLが遅くても、私の環境でタイムアウトすることは無いでしょう。


    >データを全部読ませなければならない

    一万件でタイムアウトするような状況で20万件だと・・・下手すると、
    100回以上繰り返さなければならないということになりそうですね。

    さて、原因についてですが、それは index が適正ではないからだと思われます。
    indexが適正でないと、ストアドファンクションが一回実行される度に、
    1万件全てのデータから該当データを探し出そうとしたり、同様の理由で、
    JOINするのも非常に時間(最悪の組み合わせの場合1万件x1万件・・・)が、
    かかってしまいます。

    tableA は id がプライマリキーになっている(いますよね?)のでOK
    tableB は id + date がプライマリキーになっていると思いますので、
    ストアドのほうはindexを使ってくれているものと思いますが、
    comm_table内で行っているクエリのほうは、別途 id だけの index を
    作ってやると、MySQLが使ってくれて高速化するはずです。
    (PostgreSQLだと、EXPLAINすれば実行時間の予測も立ててくれるのですが、
    MySQLのEXPLAINだとそこまで詳細なものは得られないため、やってみるしかない。)


    >id毎の対象データが20行未満だった場合、20番目に古い日付を拾えません

    はい。拾いません。下手に計算してしまうより、20日分のデータが揃わない時は、
    単純にprice2やprice3の部分がNullになるほうが、誤算を防げるとの考えです。
    どうしても必要だという事であればストアドファンクションの中で、
    (CASE date WEHN Null THEN 'すごく古い日付' ELSE date END) AS date
    とでもすれば良いでしょう。
  • id:kn1967
    肝心の事を書き忘れてました。一番時間のかかる処理はTABLE化の部分です。
    数千レコードに及ぶTABLEを表示しようとすると、作成にかかる時間も長ければ、
    その容量によってブラウザのほうが落ちるかもしれません。いえ、多分落ちます。

    TABLE化は必要な分だけ処理させて結果を確認する用途で作成してますので、
    一度に大量の処理をさせるのであれば、一番最初のコメントに戻りますが、
    結合&出力の部分を、一時保存用のtableCにINSERTするように書き換え、
    comm_tableルーチンではなく他と同様にmysql_queryを使い、結果件数を、
    mysql_affected_rows で得るといったように書き換えないといけないでしょう。
    (もう対処をはじめてるかな・・・?)

    なお、
    if(!mysql_query('SET NAMES SJIS;')) { exit; }
    は私の環境用がそのまま残ってました。xamppだと文字コード変換で、
    余計なオーバーヘッドが発生する可能性がありますので、削除しておいてください。
  • id:dekapurio
    kn1967様 コメント有難うございます。

    >数千レコードに及ぶTABLEを表示しようとすると、作成にかかる時間も長ければ、
    >その容量によってブラウザのほうが落ちるかもしれません。いえ、多分落ちます。

    それで落ちてたんですね。xamppがイカれてしまったかなと思ってました。

    >結合&出力の部分を、一時保存用のtableCにINSERTするように書き換え、
    >comm_tableルーチンではなく他と同様にmysql_queryを使い、結果件数を、
    >mysql_affected_rows で得るといったように書き換えないといけないでしょう。

    chuken_kenkou様やkn1967様のコードを今日いじっていたのですが、うまく出来なかったのでまた後日ということになりそうです。
    いいヒントを頂いたので、それを頼りに頑張ってみます。

  • id:kn1967
    もう一つ・・・

    その都度、必要な分だけ計算させて、結果を見たいという場合であれば、
    その場で計算&TABLE化させれば、すぐに結果が見れて便利なのですが、
    どうしても大量に一括計算させなけらばならないという事であれば、
    そもそもの問題としてapacheやphpやブラウザを通すという事すら無駄。

    一度でもプログラムを動かしたのであれば、
    ストアドファンクション fn1 はデータベースに登録されてますから、
    MySQLにコマンドラインから接続して、tableCを作り、下記のような具合に、

    INSERT INTO tableC AS
    結合&出力のSQLをここに書く
    ;
    (この場合、ORDER BY句は不要。)

    データを流し込めば、タイムアウトなどありませんし、
    apacheやphpを通さない分、パソコンの能力も使えスピードアップです。
    適宜使い分けましょう。

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

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

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

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