php - 按unix时间戳+24小时分组

Question

我正在编写一个报告程序，它使用 MySQL 作为数据库，使用 php 作为脚本语言。

我想做的是按时间对记录进行分组，并按结果集中过去 24 小时的小时对它们进行分组。

当前查询

SELECT count(*),time FROM `service_data` GROUP BY time

上面的查询不会按过去 24 小时对它们进行分组，它只是将类似的 unixtimestamp 分组在一起

表结构

CREATE TABLE IF NOT EXISTS `service_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_id` varchar(255) NOT NULL,
  `ip_address` varchar(255) NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

在结果集中，如果特定小时内没有报告，我希望 24 条记录为空。

像这样

| hour |  reports |
|------|----------|
|   1  |    5     |
|   2  |    10    |
|   3  |    12    |
|   4  |    25    |

任何帮助将不胜感激!

谢谢!

Answer 1

要获取最近 24 小时的时间，请将 1 天前的时间转换为 unix 时间戳:

UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))

要将时间戳转换回小时，请首先将其转换为时间，然后使用 HOUR 函数:

HOUR(FROM_UNIXTIME(time))

结合这些，你可以做到:

SELECT HOUR(FROM_UNIXTIME(time)) AS hour, COUNT(*)
FROM service_data
WHERE time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY hour

如果表非常大，请在 time 列上添加索引，这样就不必执行完整扫描。

要获取没有数据的小时结果，您需要对包含所有小时数字的表使用 LEFT JOIN:

SELECT t1.hour, COUNT(s.id) AS reports
FROM (SELECT 0 AS hour UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 23) AS t1
LEFT JOIN service_data AS s
ON t1.hour = HOUR(FROM_UNIXTIME(s.time)) AND s.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY))
GROUP BY t1.hour
ORDER BY t1.hour