MYSQL-統計クエリー

現在のタイムスライスの取得

select UNIX_TIMESTAMP(NOW())

取得当日0時終了

select UNIX_TIMESTAMP(curdate())

当日の1時間あたりの記録数を集計する

SELECT COUNT(*) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%H') as time FROM qxm30_point_log WHERE created > unix_timestamp(curdate()) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%H')

当日の1時間あたりのユーザ数を集計し、ユーザ記録数DISTINCT修飾を繰り返して繰り返す

SELECT COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%H') as time FROM qxm30_point_log WHERE created > unix_timestamp(curdate()) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%H')

ある月の1日あたりの記録数を集計する

SELECT COUNT(*) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')

月ごとのユーザー数の統計

SELECT COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')

ある月の1日あたりの1人当たりの記録数を統計する

SELECT count(*)/COUNT(distinct uid) as statnum, DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d') as time FROM qxm30_point_log WHERE created BETWEEN  unix_timestamp('2013-9-11') AND (unix_timestamp('2013-9-11')+3600*24*30) GROUP BY  DATE_FORMAT(FROM_UNIXTIME(created),'%m-%d')

グラフプラグインhighchartsも添付
ダウンロード先:http://www.highcharts.com/download