MySQL4.0系でUNIONとGROUP BYを使うには

Read More

はぁ疲れた…。
これに4時間以上はまった…。


テーブルを縦結合して集計したかったんだけど、
本番環境で思わぬエラーが返ってきた。


本番環境までこのエラーに気が付かなかったわけは、ローカルと本番のMySQLのVerが違ったから。

ローカルのVerは5.1.37。
本番のVerは4.0.25-standardだった。
本番のがかなり古いね。


SQLのVerを確認するには
SELECT VERSION();

と実行すればよい。



どちらの環境でもエラーが出ないように色々と試行錯誤した結果、うまくいったのでそれをメモとして残しておく。


まず結論から言うと
MySQL4.0系でテーブルの縦結合を行うにはTEMPORARY TABLEを使う。


でもTEMPORARY TABLEを使えば何も問題ないかと言えばそうでもなくて、新旧どちらのVerでも動かすようにするには少しコツがいる。



さて、ここに2つのテーブルがある。

page_ua_07テーブルとpage_ua_06テーブルだ。


そしてそれぞれのテーブルにはrefererというtext型のカラムが1つだけあるものとする。

このrefererにはURLの文字列が格納される。

そう、refererというカラム名をみればわかる人もいるかも知れないが、これはアクセス解析用のテーブルだ。

テーブル名の07というのは7月のことで、これと同じものが全部で12個ある。


今回は月をまたいだ集計をする必要があったので、
この2つのテーブルを縦結合し、referer毎にGROUP化して集計をしたいというわけ。



Verが5系ならばTEMPORARY TABLEなど使わずとも集計を行うことができるのだが、4系となるとそうもいかず、ましてや4.0系ともなると制限が色々ある。


そう、まだ4.1系ならば良かったのに…。

まあ嘆いても始まらない。



さて、TEMPORARY TABLEを使うことを決めてまず5系で動作をチェックした。


以下のSQLは5系ならば正しく動作する。

■5系で正しく動く集計SQL
CREATE TEMPORARY TABLE tmp (referer text, cnt int) 
( 
SELECT referer, count( 1 ) AS cnt 
FROM page_ua_07 
GROUP BY referer 
) 
UNION 
(
SELECT referer, count( 1 ) AS cnt 
FROM page_ua_06 
GROUP BY referer 
);

SELECT referer, sum( cnt ) FROM tmp
GROUP BY referer ORDER BY cnt DESC;



それぞれのテーブルでまずreferer毎の重複カウントを取り、
二つのテーブルを縦結合している。

その後tmpテーブルにぶち込んで、
最後にもう一度referer毎にする為にGROUP BYを行っている。



ところが4.0でこれを実行するとこれでもエラーが返ってくる。


■エラーメッセージ
MySQLのメッセージ: ドキュメント
#1060 - Duplicate column name 'referer' 


デュープリケート…、カラムが重複していることを怒っている。



カラム名と型の指定を外すと…動いた。


■4.0系で動く集計SQL
CREATE TEMPORARY TABLE tmp 
( 
SELECT referer, count( 1 ) AS cnt 
FROM page_ua_07 
GROUP BY referer 
) 
UNION 
(
SELECT referer, count( 1 ) AS cnt 
FROM page_ua_06 
GROUP BY referer 
);

SELECT referer, sum( cnt ) FROM tmp
GROUP BY referer ORDER BY cnt DESC;





はー、ここに辿り着くまでが長かった…。


ちなみに動くには動くけど時間がすごーくかかる。
1対20くらいの差がでる。



一番いいのはMySQLのVerを上げることなんだけどねえ…。