MYSQL-统计查询

获取当前时间截

select UNIX_TIMESTAMP(NOW())

获取当天零时时间截

select UNIX_TIMESTAMP(curdate())

统计当天每个小时段的记录数量

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")

统计当天每个小时段的用户数，去重复用户记录数 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")

统计某月每天的记录数量

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")

统计某月每天的人均记录数量

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