每15分钟一次GROUP BY时间戳,包括缺失条目 [英] GROUP BY timestamp every 15 minutes including missing entries
问题描述
我想每隔15分钟将名为'reports'的给定表格中的所有报告进行分组,包括没有填充报告的时间间隔。示例:
I want to group all reports from a given table named 'reports' by every 15 minutes, including intervals where no reports where filled. Example:
id timestamp
---------------------------
1 2015-04-16 20:52:04
2 2015-04-16 20:53:04
3 2015-04-16 20:54:04
4 2015-04-16 19:52:04
5 2015-04-17 22:24:56
6 2015-04-17 22:27:09
7 2015-04-18 06:48:41
选择查询后,我应该有:
After the select query I should have:
timestamp count
----------------------
20:52:04 3
21:07:04 0
21:22:04 0
21:37:04 0
21:52:04 0
22:07:04 0
22:22:04 2
22:37:04 0
22:52:04 0
......
06:52:04 1
07:07:04 0
我尝试的是以下查询,但这不包括缺少的15分钟间隔:
What I tried is the following query but this doesn't include the missing 15-minute-intervals:
select created_at , count(id) AS count
from `reports`
where `company_id` = '3'
group by UNIX_TIMESTAMP(created_at) DIV 900
order by `created_at` asc
推荐答案
如果你真的想在mysql中这样做,这应该在一定程度上 - 取决于你想如何组织15分钟的时间间隔。我已经从|输出了的时间时间| |报告数量
,因为我认为它没有意义,甚至有点欺骗性,不能输出特定的时间戳,并将时间范围的计数与它关联在一起。
If you really want to do it in mysql, this should work to some extent - depending on how you want to organise your 15 minute intervals. I've given the output as time from | time to | number of reports
because i think its meaningless and even a little deceptive to output a specific timestamp and associate a count of a time range with it.
SELECT CONCAT (
lpad(hours.a, 2, "0"),
":",
lpad(minutes.a * 15, 2, "0"),
":00"
) AS 'from',
CONCAT (
lpad(hours.a, 2, "0"),
":",
lpad((minutes.a * 15) + 14, 2, "0"),
":59"
) AS 'to',
count(r.id)
FROM (
SELECT 0 AS a
UNION
SELECT 1 AS a
UNION
SELECT 2 AS a
UNION
SELECT 3 AS a
UNION
SELECT 4 AS a
UNION
SELECT 5 AS a
UNION
SELECT 6 AS a
UNION
SELECT 7 AS a
UNION
SELECT 8 AS a
UNION
SELECT 9 AS a
UNION
SELECT 10 AS a
UNION
SELECT 11 AS a
UNION
SELECT 12 AS a
UNION
SELECT 13 AS a
UNION
SELECT 14 AS a
UNION
SELECT 15 AS a
UNION
SELECT 16 AS a
UNION
SELECT 17 AS a
UNION
SELECT 18 AS a
UNION
SELECT 19 AS a
UNION
SELECT 20 AS a
UNION
SELECT 21 AS a
UNION
SELECT 22 AS a
UNION
SELECT 23 AS a
) hours
INNER JOIN (
SELECT 0 AS a
UNION
SELECT 1 AS a
UNION
SELECT 2 AS a
UNION
SELECT 3 AS a
) minutes
LEFT JOIN reports r ON hours.a = hour(r.t)
AND minutes.a = floor(minute(r.t) / 15)
GROUP BY hours.a,
minutes.a;
这篇关于每15分钟一次GROUP BY时间戳,包括缺失条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!