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ランキング
-
【PL/SQL】DATE型の時刻の表示...
-
日替わりメッセージを表示させ...
-
EXCELマクロのSQL文での引用符...
-
PHP+MySQLで月ごとの数量を表...
-
SQLローダーCSV取込で、囲み文...
-
select文の実行結果に空白行を...
-
Oracleで「文字が無効です」の...
-
複数のテーブルから値を合計出...
-
単一グループのグループ関数で...
-
group byの並び順を変えるだけ...
-
count関数の値をwhere句で使用...
-
【PL/SQL】FROM区に変数を使う方法
-
テーブルの最後(最新)のレコー...
-
SELECTした合計値をそのまま割...
-
AccessのSQL文で1件のみヒット...
-
SELECT FOR UPDATE で該当レコ...
-
2つの列が同じ値の行を取得するSQL
-
BLOB型項目をSQLの検索条件に指...
-
where句中のtrim関数について
-
「数字で始まらない」ものを抽...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
PHP+MySQLで月ごとの数量を表...
-
【PL/SQL】DATE型の時刻の表示...
-
PHPとMysqlを使用した集計表の...
-
データ更新用のphpの記述でデー...
-
PHPでMySQL内のデータの合計値...
-
postgresのカレンダーについて
-
EXCELマクロのSQL文での引用符...
-
日替わりメッセージを表示させ...
-
bash内のaws cli cloudwatch Lo...
-
SQLローダーCSV取込で、囲み文...
-
単一グループのグループ関数で...
-
select文の実行結果に空白行を...
-
テーブルの最後(最新)のレコー...
-
count関数の値をwhere句で使用...
-
【PL/SQL】FROM区に変数を使う方法
-
SELECT FOR UPDATE で該当レコ...
-
2つの列が同じ値の行を取得するSQL
-
レコードの登録順がおかしい
-
<SQL>重複しているデータの場合...
-
AccessのSQL文で1件のみヒット...
おすすめ情報