mysql 按半小时分组_mysql – 按半小时间隔分组

如果格式不太重要,则可以为间隔返回两列.您甚至可能只需要间隔的开始,可以通过以下方式确定：

date_format(created_at - interval minute(created_at)%30 minute, '%H:%i') as period_start

别名可以在GROUP BY和ORDER BY子句中使用.如果您还需要间隔结束,则需要进行一些小修改：

SELECT

date_format(created_at - interval minute(created_at)%30 minute, '%H:%i') as period_start,

date_format(created_at + interval 30-minute(created_at)%30 minute, '%H:%i') as period_end,

COUNT(*)

FROM urls

GROUP BY period_start

ORDER BY period_start ASC;

当然你也可以连接这些值：

SELECT concat_ws('-',

date_format(created_at - interval minute(created_at)%30 minute, '%H:%i'),

date_format(created_at + interval 30-minute(created_at)%30 minute, '%H:%i')

) as period,

COUNT(*)

FROM urls

GROUP BY period

ORDER BY period ASC;

Another thing, is that, if it there is half an hour with no results, I

would like it to return 0

如果以过程语言使用结果,则可以在循环中初始化所有48行零,然后从结果中“注入”非零行.

但是 – 如果需要在SQL中完成它,则需要一个至少有48行的LEFT JOIN表.这可以用“巨大的”UNION ALL语句内联完成,但(恕我直言)这将是丑陋的.所以我更喜欢让序列表有一个整数列,这对于报告非常有用.要创建该表,我通常使用information_schema.COLUMNS,因为它可以在任何MySQL服务器上使用,并且至少有几百行.如果您需要更多行 – 只需将其与自身连接即可.

现在让我们创建该表：

drop table if exists helper_seq;

create table helper_seq (seq smallint auto_increment primary key)

select null

from information_schema.COLUMNS c1

, information_schema.COLUMNS c2

limit 100; -- adjust as needed

现在我们有一个从1到100的整数表(虽然现在你只需要48 – 但这是为了演示).

使用该表我们现在可以创建所有48个时间间隔：

select time(0) + interval 30*(seq-1) minute as period_start,

time(0) + interval 30*(seq) minute as period_end

from helper_seq s

where s.seq <= 48;

我们将得到以下结果：

period_start | period_end

00:00:00 | 00:30:00

00:30:00 | 01:00:00

...

23:30:00 | 24:00:00

现在我们可以将它用作派生表(FROM子句中的子查询)和LEFT JOIN你的urls表：

select p.period_start, p.period_end, count(u.created_at) as cnt

from (

select time(0) + interval 30*(seq-1) minute as period_start,

time(0) + interval 30*(seq) minute as period_end

from helper_seq s

where s.seq <= 48

) p

left join urls u

on time(u.created_at) >= p.period_start

and time(u.created_at) < p.period_end

group by p.period_start, p.period_end

order by p.period_start

最后一步(如果真的需要)是格式化结果.我们可以在外部选择中使用CONCAT或CONCAT_WS和TIME_FORMAT.最后的查询是：

select concat_ws('-',

time_format(p.period_start, '%H:%i'),

time_format(p.period_end, '%H:%i')

) as period,

count(u.created_at) as cnt

from (

select time(0) + interval 30*(seq-1) minute as period_start,

time(0) + interval 30*(seq) minute as period_end

from helper_seq s

where s.seq <= 48

) p

left join urls u

on time(u.created_at) >= p.period_start

and time(u.created_at) < p.period_end

group by p.period_start, p.period_end

order by p.period_start

结果如下：

period | cnt

00:00-00:30 | 1

00:30-01:00 | 0

...

23:30-24:00 | 3