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ベストアンサー
- 回答日時:
アプローチは二つあります。
(1)SQL文で集計して集計結果をphpで受け取って処理する
集計したい開始年月と終了年月が既知であれば、手っ取り早い方法です。
下記ソースではリテラルで書いていますが、phpスクリプトで動的に生成すればいいですね。
$sql = <<<EOD
select `商品名`,
sum(if (date_format(`販売日`, '%Y%m')='201401', `数量`, 0)) as `1月`,
sum(if (date_format(`販売日`, '%Y%m')='201402', `数量`, 0)) as `2月`,
sum(if (date_format(`販売日`, '%Y%m')='201403', `数量`, 0)) as `3月`,
sum(if (date_format(`販売日`, '%Y%m')='201404', `数量`, 0)) as `4月`,
sum(if (date_format(`販売日`, '%Y%m')='201405', `数量`, 0)) as `5月`,
sum(if (date_format(`販売日`, '%Y%m')='201406', `数量`, 0)) as `6月`,
sum(if (date_format(`販売日`, '%Y%m')='201407', `数量`, 0)) as `7月`,
sum(if (date_format(`販売日`, '%Y%m')='201408', `数量`, 0)) as `8月`,
sum(if (date_format(`販売日`, '%Y%m')='201409', `数量`, 0)) as `9月`,
sum(if (date_format(`販売日`, '%Y%m')='201410', `数量`, 0)) as `10月`,
sum(if (date_format(`販売日`, '%Y%m')='201411', `数量`, 0)) as `11月`,
sum(if (date_format(`販売日`, '%Y%m')='201412', `数量`, 0)) as `12月`
from data
group by `商品名`
EOD;
$stmt = $pdo->query($sql);
// あくまでも「例」なので一気に読み込んでみます(汗
// 実際の出力は、この取得内容から勘案してください
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
print '<pre>';
print_r($arr);
print '</pre>';
(2)順次読み込んだデータを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
- 回答日時:
>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
- 回答日時:
こういう集計はピボットテーブルというのをつくると楽です。
//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でも可能なのですね。
今後の参考にさせていただきます。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA 連続する名前ごとに集計 3 2022/05/21 18:24
- Excel(エクセル) Excelグラフについて 1 2023/05/12 16:26
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- Excel(エクセル) 【エクセル関数】複数条件に該当する場合、別の列の数値を合算する。 9 2022/07/09 08:46
- 高校 日商簿記3級の勉強中なのですが 精算表が完成せず困っています。 こちらの問題の回答を教えていただきた 2 2023/03/02 09:07
- Visual Basic(VBA) VBAコードで質問があります 2 2022/10/20 15:27
- Excel(エクセル) 非表示にしたい行をグループ化して折り畳み 4 2022/09/17 20:17
- PHP PHP MySql ページング 2 2022/09/20 06:38
- Excel(エクセル) エクセルシートの合計の変動 5 2022/04/05 15:56
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
フォームで戻った際に入力済み...
-
phpでcookieがうまく保存されない
-
「基礎からのMySQL 第3版 Kindl...
-
csvファイルについて教えて下さ...
-
PHPで訪問回数を表示するカウン...
-
EC-CUBEをeclipseからコンパイ...
-
PHPで画像の渡しが上手く行きま...
-
PHPについて。
-
phpに関わる所での、form actio...
-
reuterの記事をbeautiful.soup....
-
PHPのセッション有効期限について
-
PostgreSQLからCSV形式でエクス...
-
phpinfo で MySQL のバージョン...
-
phpのheader("Location:#pos")...
-
PHPからCSVをアップロード後、m...
-
[php初心者]サイトを見てデータ...
-
ゆゆにゃ。
-
【初心者】XAMPPのapacheの(恐...
-
プログラミング言語で、使える...
-
こちらはただの直列処理ですか?
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
postgresのカレンダーについて
-
PHP+MySQLで月ごとの数量を表...
-
【PL/SQL】DATE型の時刻の表示...
-
配列の重複を削除後、削除分の...
-
掲載日と更新日の管理の仕方
-
MySQLのUPDATE文でサブクエリ
-
続・指定した時間にPHPプログラ...
-
DB登録に関する日付の認識
-
EXCELマクロのSQL文での引用符...
-
日替わりメッセージを表示させ...
-
PHPでMySQL内のデータの合計値...
-
構造体のプログラミング......
-
JavaScriptの条件文の応用
-
JOINを使った文で、date > now();
-
preg_replace, ereg_replace
-
UNIXの時間はどう変更すれ...
-
MySQL + PHP 同じテーブル内で...
-
複数のカテゴリを選択した上でL...
-
Strict Standards: date() ...
-
質問:年月日の表示方法について
おすすめ情報