Mysql group queries by time period to count the number of members and the number of mysql members

Mysql group queries by time period to count the number of members and the number of mysql members

1. Use the case when method (not recommended)
-

The Code is as follows:

Copy code

SELECT COUNT (DISTINCT user_id) user_count, CASE WHEN create_time> 1395046800 AND create_time <1395050400 THEN '17: 00' WHEN create_time> 1395050400 AND create_time <1395054000 THEN '18: 00' WHEN create_time> 1395054000 AND create_time <1395057600 THEN '19: 00' WHEN create_time> 1395057600 AND create_time <1395061200 THEN '20: 00' ELSE 'unknown' End as 'date' www.111cn.net FROM Tb_user_online_log WHERE create_time> 1395046800 AND create_time <1395061200 GROUP 'Date' Order by create_time

The result is as follows:

2. group after time stamp Conversion

The Code is as follows:	Copy code
SELECT COUNT (DISTINCT user_id) user_count, FROM_UNIXTIME ( Create_time, '% Y-% m-% d % H: 00: 00' ) AS hours, CONCAT (FROM_UNIXTIME (create_time, '% Y-% m-% d % H: 00'),'-', FROM_UNIXTIME (create_time,' % H') + 1 ,": 00 ") AS 'date' FROM Tb_user_online_log GROUP Hours Order by create_time

The result is www.111cn.net.

Note: case when is not efficient and is not recommended when the data volume is large. Here, only the solution is listed, which is for reference only.

From: http://www.111cn.net/database/mysql/58986.htm

How to count the number of records after the mysql group, count after gourp

Select count (*) from
(SELECT count (*) FROM table name WHERE condition group by id);

Mysql time group statistics

SELECT date_format (FROM_UNIXTIME ('time'), '% Y-% m-% D') AS time, count (*) as count FROM 'table name' WHERE 1 group by time