素人でもできたデータ処理の技

まぐまぐ！メルマガ＜素人でもできたデータ処理の技＞

　第12号 2005/09/13

＜EXCEL＞

当サイトの情報をご利用になる場合は、全て自己責任でお願いします

第12号 2005/09/13

＜MySQL編　第８回目＞

　今回は、クロス集計の前段階として、年月を横方法に集計する方法を紹介させ
て頂きます。

2004年の販売データを月ごとに集計し、横方向にならべます。その際、地区コード
を地区マスターを使って地区名に読み替えます。

PHPプログラム

<?php

$con = mysql_connect("localhost","ユーザー名","パスワード");
mysql_select_db("データベース名", $con);

// テーブルを１つにまとめるための、一時的なテーブル(テンポラリーテーブル)を作成します。

$sql="CREATE TEMPORARY TABLE TTBL01(
tiku varchar(254),
kingaku01 bigint,
kingaku02 bigint,
kingaku03 bigint,
kingaku04 bigint,
kingaku05 bigint,
kingaku06 bigint,
kingaku07 bigint,
kingaku08 bigint,
kingaku09 bigint,
kingaku10 bigint,
kingaku11 bigint,
kingaku12 bigint,
INDEX I_tiku (tiku)
)";

mysql_query($sql, $con);

// 2004年の販売実績をデータをテンポラリーテーブルの該当する月に入れます。

$sql="INSERT INTO TTBL01
SELECT tiku,
IF(MONTH(ymd)=1,kingaku,0),
IF(MONTH(ymd)=2,kingaku,0),
IF(MONTH(ymd)=3,kingaku,0),
IF(MONTH(ymd)=4,kingaku,0),
IF(MONTH(ymd)=5,kingaku,0),
IF(MONTH(ymd)=6,kingaku,0),
IF(MONTH(ymd)=7,kingaku,0),
IF(MONTH(ymd)=8,kingaku,0),
IF(MONTH(ymd)=9,kingaku,0),
IF(MONTH(ymd)=10,kingaku,0),
IF(MONTH(ymd)=11,kingaku,0),
IF(MONTH(ymd)=12,kingaku,0)
FROM uriage_2004
";

mysql_query($sql, $con);

// テンポラリーテーブルTTBL01を集計した結果を入れるためのテンポラリーテーブルを作成します。

$sql="CREATE TEMPORARY TABLE TTBL02(
tiku varchar(254),
kingaku01 bigint,
kingaku02 bigint,
kingaku03 bigint,
kingaku04 bigint,
kingaku05 bigint,
kingaku06 bigint,
kingaku07 bigint,
kingaku08 bigint,
kingaku09 bigint,
kingaku10 bigint,
kingaku11 bigint,
kingaku12 bigint,
INDEX I_tiku (tiku)
)";

mysql_query($sql, $con);

// TBL01を集計した結果をテンポラリーテーブルTTBL02に入れます。

$sql="INSERT INTO TTBL02
SELECT tiku,
SUM(kingaku01),
SUM(kingaku02),
SUM(kingaku03),
SUM(kingaku04),
SUM(kingaku05),
SUM(kingaku06),
SUM(kingaku07),
SUM(kingaku08),
SUM(kingaku09),
SUM(kingaku10),
SUM(kingaku11),
SUM(kingaku12)
FROM TBL01
GROUP BY tiku
";

mysql_query($sql, $con);

// TBL02の地区コードを地区名に読み替えます。

$sql = "SELECT tiku_mast.tikumei,
TTBL02.kingaku01,
TTBL02.kingaku02,
TTBL02.kingaku03,
TTBL02.kingaku04,
TTBL02.kingaku05,
TTBL02.kingaku06,
TTBL02.kingaku07,
TTBL02.kingaku08,
TTBL02.kingaku09,
TTBL02.kingaku10,
TTBL02.kingaku11,
TTBL02.kingaku12
FROM TTBL02
LEFT JOIN tiku_mast
ON TTBL02.tiku = tiku_mast.tikucode
";

$rs = mysql_query($sql, $con);
$n = mysql_numrows($rs);
$m = mysql_numfields($rs);
mysql_close($con);

// 集計結果を表示します。

$itigyou .= "地区\t1月\t2月\t3月\t4月\t5月\t6月\t7月\t8月\t9月\t10月\t11月\t12月\t\n";
print($itigyou);
for ($i=0; $i<$n; $i++) {
$itigyou="";

for ($j=0; $j<$m; $j++) {
$itigyou .= mysql_result($rs,$i,$j)."\t";
}

$itigyou .= "\n";
print($itigyou);
}
?>
(プログラム終わり)

　前回と同じように、このファイルのURLを、Excelの“データ”　→　
“外部データの取り込み”　→“データの取り込み”のファイル名に入れてやる
と集計結果がExcelで取り込めます。
　例えば、このphpファイルをshukei.phpとした場合、ファイル名に
http:>//（URL名）/shukei.php　をいれて、タブ区切りのテキストデータを読み込む
設定をすれば取り込めます。

************************************************************************
＜編集後記＞

　今回の集計処理はExcelの集計機能やAccessの集計クエリでもできます。ただし、
Excelでは処理に時間がかかりすぎますし、Accessではクエリの作成に結構な手間
がかかります。今回のPHPもプログラム作成に時間がかかりますが、あとの修正が
簡単にできますので、その点ではAccessより優れていると思います。

　今回の集計は、あらかじめ横の列の項目がわかっている場合しか使えません。
また、存在しないデータも集計してしまいます。(例えば、５月のデータが存在
しない場合にも、集計結果は0として表示されます。)
　横の項目がわかっていない場合や存在しないデータの項目を表示したくない場
合は、Accessではクロス集計という機能で集計できます。
（Excelではピボットテーブルで出来ます。）
ということで、次回は、PHPでのクロス集計を紹介させて頂きます。

************************************************************************