MYSQL-统计查询

最新推荐文章于 2024-03-20 18:26:31 发布

iteye_264

最新推荐文章于 2024-03-20 18:26:31 发布

阅读量146

点赞数

文章标签：数据库

获取当前时间截

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