如何计数数据组每6小时间隔在每一天的时间戳从数据库在PHP - How to count data group by per 6 hours interval in every day in timestamp from database in PHP?

How to count data group by per 6 hours interval in every day in timestamp from database in PHP?

这是我的查询:

$sql = "SELECT DATE( pm_timestamp ) AS d, HOUR( pm_timestamp ) AS h, 
       COUNT( * ) AS c FROM pm_impr_tracker WHERE campaign_id = '$camp_id' GROUP BY h,d order by `pm_timestamp`";

这给出了每小时的数据，但我想要每6小时。我的结果:

    d       h   c
2015-08-23  6   6
2015-08-23  7   11
2015-08-23  8   26 
2015-08-23  9   57
2015-08-23  10  36
2015-08-23  11  19
2015-08-23  12  21
2015-08-23  13  18
2015-08-23  14  18
2015-08-23  15  17
2015-08-23  16  107
2015-08-23  17  55
2015-08-24  8   26
2015-08-24  9   57
2015-08-24  10  36
2015-08-24  11  19
2015-08-24  12  21
2015-08-24  13  18
2015-08-24  14  18

您发布的代码按小时对记录进行分组。如果你需要它们以6小时为间隔进行分组，那么你所要做的就是让h成为一天中这样一个6小时组的数量。例如，h = 0为0..5(即HOUR(pm_timestamp)在0到5之间)的小时组，h = 1为6..11的小时组，以此类推。

这可以很容易地实现，如果你将小时除以6，忽略余数。这就是MySQL DIV operator的工作。

查询为:

SELECT DATE(pm_timestamp) AS d,
    HOUR(pm_timestamp) DIV 6 AS h, 
    COUNT(*) AS c
FROM pm_impr_tracker
WHERE campaign_id = '$camp_id'
GROUP BY h, d