這數天都在做一個網站流量統計的程式,其中一步是要找出每小時的人流。很直觀的,我會使用GROUP BY來處理,然而,這就出現了一個問題。如果有其中一小時沒有人來過網站的話,資料庫內就沒有那一小時的資料,那麼就會出現在每小時的人流統計中缺少了一小時的資料(那小時的人流該為0,現在卻是少了這筆資料)。要解決的方法有很多,但西杰程式的標準是高效和代碼優雅,這確然令我少了很多選擇。第一個選擇,亦是最佳的選擇,是在SELECT的時候產生一個時間列表,然後再把該小時的資料合併在內,十分直觀的選擇。當然,如果可以做到的話,就沒有這篇文章。問題就是,MySQL本身不具備generate的功能(如PgSQL的generate_series),沒有此功能這個方法就完全不可行了。

第二個選擇是按照原本的SQL來SELECT,拿到PHP後就當作是例外來處理,這個方法是高效的,然而要另外寫一個例外處理就弄污我的程式了,這只能作為後備方案。

最後的一個選擇是新增一個時間列表,包含未來十年的每一小時(總數為87600筆資料),那就避免了在SELECT時才產生一個時間列表。這個選擇的壞處是十年後要再重新產生一個時間列表,運作上是有缺憾的。然而,為了不破壞PHP程式的優雅,我還是選擇了這個方法。

使用這個方法,有兩個思路。我的第一個思路是,從時間列表找出每一個小時,然後再LEFT JOIN流量資料。很快地,我寫了一句這樣的SQL,可是效率差得很驚人,只是從一萬多筆資料中找出二百多筆資料快花費了十多秒,那怎能接受?這個效率問題相信是LEFT JOIN的效率太低了,還記得以前我也試過用LEFT JOIN,速度也是十分低。那好了,第一個思路不行,就用第二個思路。

第二個思路是,從時間列表找出每一小時的資料列,再用那條”錯”的SQL,即沒有了不存在流量的小時那條,UNION兩個SET,再GROUP在一起。寫完才發現原來UNION後是不能直接GROUP的,那就麻煩了。上官網找了一下資料,原來可以利用subquery來解決。思路就是把這條SELECT當成subquery,再在外層SELECT 一次這個暫時性列表,那就可以使用GROUP BY了。

最後的SQL是這樣的︰

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT `date`,`hour`,SUM(`count`) AS `count` FROM ((
	SELECT DATE_FORMAT(FROM_UNIXTIME(`webStatistics_stat`.`time`), '%b %d, %Y') AS `date`,
	HOUR(FROM_UNIXTIME(`webStatistics_stat`.`time`)) AS `hour`,
	COUNT(*) AS `count` FROM `webStatistics_stat`
	WHERE `webStatistics_stat`.`requestURL` LIKE 'http://hkg.westkit.net%' &&
	('1231689600' < `time` && `time` < '1231948800')
	GROUP BY `date`,`hour`
) UNION (
	SELECT DATE_FORMAT(`webStatistics_calendar`.`dateHour`, '%b %d, %Y') AS `date`,
	HOUR(`webStatistics_calendar`.`dateHour`) AS `hour`,
	0 AS `count` FROM `webStatistics_calendar`
	WHERE FROM_UNIXTIME('1231689600') < `dateHour` && `dateHour` < FROM_UNIXTIME('1231948800')
)) AS `webStat`
GROUP BY `date`,`hour`
WITH ROLLUP

花了兩晚的原因是第一,問題本身有一定的難度,上網找的全是使用折衷的方法,第二是在PHP處理和MySQL處理的取拾爭扎了很久,結果還是為了顧及程式的優美而選擇了在MySQL處理。當然,這個項目只是自己的興趣項目,這兩晚是花得起的,而且也是值得的。


Categories : MySQL, PHP, SQL