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

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

　第11号 2005/09/10

＜EXCEL＞

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

第11号 2005/09/10

＜MySQL編　第７回目＞

　今回から実際の抽出・集計プログラムを紹介させて頂きます。

2004年1月～12月の販売データと2005年1月～12月の販売データを集計して、
2004年4月～2005年3月までの月ごとの集計を作成します。その際、地区コード
を地区マスターを使って地区名に読み替えます。

PHPプログラム

<?php

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

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

$sql="CREATE TEMPORARY TABLE TTBL01(
nen int,
tuki int,
tiku varchar(254),
kingaku bigint,
INDEX I_nen (nen),
INDEX I_tuki (tuki),
INDEX I_tiku (tiku)
)";

mysql_query($sql, $con);

// 2004年の販売実績から必要なデータをテンポラリーテーブルに入れます。

$sql="INSERT INTO TTBL01
SELECT YEAR(ymd), MONTH(ymd), tiku, kingaku
FROM uriage_2004
WHERE ymd>='20040401'
";

mysql_query($sql, $con);

// 2005年の販売実績から必要なデータをテンポラリーテーブルに入れます。

$sql="INSERT INTO TTBL01
SELECT YEAR(ymd), MONTH(ymd), tiku, kingaku
FROM uriage_2005
WHERE ymd<'20050401'
";

mysql_query($sql, $con);

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

$sql="CREATE TEMPORARY TABLE TTBL02(
nen int,
tuki int,
tiku varchar(254),
kingaku bigint,
INDEX I_nen (nen),
INDEX I_tuki (tuki),
INDEX I_tiku (tiku)
)";

mysql_query($sql, $con);

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

$sql="INSERT INTO TTBL02
SELECT nen, tuki, tiku, SUM(kingaku)
FROM TBL01
GROUP BY nen, tuki, tiku
";

mysql_query($sql, $con);

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

$sql = "SELECT TTBL02.nen, TTBL02.tuki, tiku_mast.tikumei, TTBL02.kingaku
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);

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

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　をいれて、タブ区切りのテキストデータを読み込む
設定をすれば取り込めます。

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

以上が基本的な集計処理です。
簡単な処理でも、結構多い行になります。Select文を工夫すれば複数の処理を
１つでする事もできますが、エラーが出たときに原因が分かりにくいのと、追加
の処理が必要になったときの追加がやりにくくなることから、行は多くなります
ができるだけ少しずつ処理をするようにしています。
また、行が多くなっても、ほとんどが前の行をコピー・ペーストして変更する作
業ですので大して手間はかかりません。

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

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