这是我的查询:
$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