PHP＋MySQLで月ごとの数量を表示したい

質問

MySQLに以下のような形でデータが入っています。

連番｜販売日｜商品名｜単価｜数量
1｜2014/01/15｜たわし｜100｜5
2｜2014/02/10｜スポンジ｜50｜2
3｜2014/03/05｜タオル｜200｜3
・・・

このようなデータが大量に保存されていて、それをPHPで以下のように出力したい場合
（数量でも合計金額でも）
2013年度
商品名｜4月｜5月｜6月｜・・・12月｜1月｜2月｜3月｜合計
たわし｜5｜6｜12｜・・・7｜14｜21｜15｜210
スポンジ｜・・・

どのようにデータを抽出し、どのように表示すればよいでしょうか？

回答No.3

agunuz · ベストアンサー

アプローチは二つあります。

（１）SQL文で集計して集計結果をphpで受け取って処理する

集計したい開始年月と終了年月が既知であれば、手っ取り早い方法です。

下記ソースではリテラルで書いていますが、phpスクリプトで動的に生成すればいいですね。

$sql = <<<EOD
select `商品名`,
sum(if (date_format(`販売日`, '%Y%m')='201401', `数量`, 0)) as `１月`,
sum(if (date_format(`販売日`, '%Y%m')='201402', `数量`, 0)) as `２月`,
sum(if (date_format(`販売日`, '%Y%m')='201403', `数量`, 0)) as `３月`,
sum(if (date_format(`販売日`, '%Y%m')='201404', `数量`, 0)) as `４月`,
sum(if (date_format(`販売日`, '%Y%m')='201405', `数量`, 0)) as `５月`,
sum(if (date_format(`販売日`, '%Y%m')='201406', `数量`, 0)) as `６月`,
sum(if (date_format(`販売日`, '%Y%m')='201407', `数量`, 0)) as `７月`,
sum(if (date_format(`販売日`, '%Y%m')='201408', `数量`, 0)) as `８月`,
sum(if (date_format(`販売日`, '%Y%m')='201409', `数量`, 0)) as `９月`,
sum(if (date_format(`販売日`, '%Y%m')='201410', `数量`, 0)) as `１０月`,
sum(if (date_format(`販売日`, '%Y%m')='201411', `数量`, 0)) as `１１月`,
sum(if (date_format(`販売日`, '%Y%m')='201412', `数量`, 0)) as `１２月`
from data
group by `商品名`
EOD;

$stmt = $pdo->query($sql);
// あくまでも「例」なので一気に読み込んでみます（汗
// 実際の出力は、この取得内容から勘案してください
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
print '<pre>';
print_r($arr);
print '</pre>';

（２）順次読み込んだデータをphpで集計する

集計範囲が決まっていなくてもいいです。「キーブレーク処理」に慣れていればさほど面倒ではありませんが、速度的に劣ると思います（集計はMySQLに任せた方がphpよりも速い）。

$stmt = $pdo->query("select *, date_format(`販売日`, '%Y%m') as yymm from data order by `商品名`");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$yymm = $row['yymm'];
if (isset($svhin)&&$svhin === $row['商品名']) { // 同じ商品だったら
if (isset($svarr[$yymm])) {
$svarr[$yymm] += $row['数量'];
} else {
$svarr[$yymm] = $row['数量'];
}
} else { // キーブレーク
if (isset($svhin)) {
print $svhin . '<br>';
// このprint部分は「決まっている年月範囲」があるときはそれに合わせて行う
foreach ($svdat as $key=>$val) { printf('%s --- %d<br>', $key, $val); }
}
$svhin = $row['商品名'];
$svdat = array();
$svdat[$yymm] = $row['数量'];
}
}
if (isset($svhin)) {
print $svhin . '<br>';
foreach ($svdat as $key=>$val) { printf('%s --- %d<br>', $key, $val); }
}

お礼

思った挙動になりました。
丁寧にありがとうございます。
PHPで集計する方法は、一度やってみたのですが、データが多くて表示に時間がかかったので、SQLで可能な方法を模索していました。
ちなみに、1月、2月と抽出していますが、12月のあとに1～12月の合計を表示する場合はどうすればいいでしょうか？
sum(…)+sum(…)+・・・とやっていると効率が悪いですよね。せっかく1月、2月とラベルがあるのを使うことはできないのでしょうか？

回答No.4

yambejp

＞12月のあとに1～12月の合計を表示する場合
あれ？スクールカレンダーじゃないの？

#3さんのロジックを使うのであれば
//1～12月
sum(if (date_format(`販売日`, '%Y')='2014', `数量`, 0)) as `合計`
//4～翌3月
sum(if (date_format(`販売日`, '%Y%m') BETWEEN '201304' AND '201403', `数量`, 0)) as `合計`

pivotを使っていれば、データの絞り込みがすでにできているので
sum(`数量`) as `合計`

をつければ大丈夫です

お礼

本来はスクールカレンダーですが、どちらでも聞けば応用できるかなと思いまして（笑）
答えを提示していただけると単純なことでしたが、助かりました。
pivotもこれから使えるようになっていきます。
ありがとうございました。

回答No.2

yambejp

こういう集計はピボットテーブルというのをつくると楽です。

//pivot作成
create table pivot (mindate date,maxdate date,`4月` tinyint not null,`5月` tinyint not null,`6月` tinyint not null,`7月` tinyint not null,`8月` tinyint not null,`9月` tinyint not null,`10月` tinyint not null,`11月` tinyint not null,`12月` tinyint not null,`1月` tinyint not null,`2月` tinyint not null,`3月` tinyint not null);
//データ投入
insert into pivot(mindate,maxdate,`4月`) values((@d:='2013-04-01') ,(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`5月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`6月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`7月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`8月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`9月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`10月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`11月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`12月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`1月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`2月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);
insert into pivot(mindate,maxdate,`3月`) values((@d:=@d+interval 1 month),(@d+interval 1 month - interval 1 day),1);

//内容確認
select * from pivot;
1が右肩下がりで投入されているのがわかります。

//元データ作成,データ投入
create table tbl(販売日 date,商品名 varchar(30),単価 int,数量 int);
insert into tbl values('2014/01/15','たわし',100,5)('2014/01/20','たわし',100,10)('2014/02/1','たわし',100,7),('2014/02/10','スポンジ',50,2),('2014/03/05','タオル',200,3);

ここから本題
//各月の品目の件数を表示
select 商品名
,sum(`4月`) as `4月`
,sum(`5月`) as `5月`
,sum(`6月`) as `6月`
,sum(`7月`) as `7月`
,sum(`8月`) as `8月`
,sum(`9月`) as `9月`
,sum(`10月`) as `10月`
,sum(`11月`) as `11月`
,sum(`12月`) as `12月`
,sum(`1月`) as `1月`
,sum(`2月`) as `2月`
,sum(`3月`) as `3月`
from tbl
inner join pivot on 販売日 between mindate and maxdate
group by 商品名;

//各月の品目ごとの数量を表示
select 商品名
,sum(`4月`*`数量`) as `4月`
,sum(`5月`*`数量`) as `5月`
,sum(`6月`*`数量`) as `6月`
,sum(`7月`*`数量`) as `7月`
,sum(`8月`*`数量`) as `8月`
,sum(`9月`*`数量`) as `9月`
,sum(`10月`*`数量`) as `10月`
,sum(`11月`*`数量`) as `11月`
,sum(`12月`*`数量`) as `12月`
,sum(`1月`*`数量`) as `1月`
,sum(`2月`*`数量`) as `2月`
,sum(`3月`*`数量`) as `3月`
from tbl
inner join pivot on 販売日 between mindate and maxdate
group by 商品名;

//各月の品目ごとの金額を表示
select 商品名
,sum(`4月`*`数量`*`単価`) as `4月`
,sum(`5月`*`数量`*`単価`) as `5月`
,sum(`6月`*`数量`*`単価`) as `6月`
,sum(`7月`*`数量`*`単価`) as `7月`
,sum(`8月`*`数量`*`単価`) as `8月`
,sum(`9月`*`数量`*`単価`) as `9月`
,sum(`10月`*`数量`*`単価`) as `10月`
,sum(`11月`*`数量`*`単価`) as `11月`
,sum(`12月`*`数量`*`単価`) as `12月`
,sum(`1月`*`数量`*`単価`) as `1月`
,sum(`2月`*`数量`*`単価`) as `2月`
,sum(`3月`*`数量`*`単価`) as `3月`
from tbl
inner join pivot on 販売日 between mindate and maxdate
group by 商品名;

お礼

ピボットテーブル。Excelでは聞いたことありましたが、MySQLでも可能なのですね。
今後の参考にさせていただきます。
ありがとうございました。

回答No.1

t_ohta

select 商品名, EXTRACT(YEAR_MONTH FROM 販売日) as 販売月, sum(数量) as 合計数量, sum(数量 * 単価) as 合計金額 from テーブル group by 商品名, 販売月;

あとは商品名と販売月でループさせながら表示する。

お礼

ありがとうございます。
今回はNo.3の方の方法を採用させていただきました。
こちらの方法も、今後の参考にさせていただきます。