Mysql按时间段分组查询来统计会员的个数

今天碰到个需求需要在log日志表中按照时间段来统计会员的个数,经努力找到了两种解决方案,记录下来以备使用.

1.使用case when方法,不建议使用,代码如下:

1. SELECT
2. COUNT(DISTINCTuser_id)user_count,
3. CASE
4. WHENcreate_time>1395046800ANDcreate_time<1395050400THEN'17:00-18:00'
5. WHENcreate_time>1395050400ANDcreate_time<1395054000THEN'18:00-19:00'
6. WHENcreate_time>1395054000ANDcreate_time<1395057600THEN'19:00-20:00'
7. WHENcreate_time>1395057600ANDcreate_time<1395061200THEN'20:00-21:00'
8. ELSE'unknown'
9. ENDAS`date`www.phpfensi.com
10. FROM
11. tb_user_online_log
12. WHEREcreate_time>1395046800ANDcreate_time<1395061200
13. GROUPBY
14. `date`
15. ORDERBYcreate_time

Mysql按时间段分组查询来统计会员的个数.

2.使用时间戳转换后分组,代码如下:

1. SELECT
2. COUNT(DISTINCTuser_id)user_count,
3. FROM_UNIXTIME(
4. create_time,
5. '%Y-%m-%d%H:00:00'
6. )AShours,
7. CONCAT(FROM_UNIXTIME(create_time,'%Y-%m-%d%H:00'),'-',FROM_UNIXTIME(create_time,'%H')+1,":00")AS`date`
8. FROM
9. tb_user_online_log
10. GROUPBY
11. hours
12. ORDERBYcreate_time