mysql - 按半小时间隔分组

Question

我很幸运地在 Stack Overflow 上找到了这段很棒的代码，但是我想将其更改为每半小时显示一次而不是每小时显示一次，但是弄乱它只会导致我破坏查询哈哈.

这是SQL:

SELECT CONCAT(HOUR(created_at), ':00-', HOUR(created_at)+1, ':00') as hours,
       COUNT(*)
FROM urls
GROUP BY HOUR(created_at)
ORDER BY HOUR(created_at) ASC

我怎样才能每半小时得到一个结果？ :)

另一件事是，如果半小时没有结果，我希望它返回 0 而不是跳过该步骤。当我对查询进行统计时，它看起来有点奇怪，因为没有查询而跳过了一个小时:P

Answer 1

如果格式不是太重要，您可以为间隔返回两列。您甚至可能只需要间隔的开始，这可以通过以下方式确定:

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;

演示:http://rextester.com/RPN50688

Another thing, is that, if it there is half an hour with no results, I would like it to return 0

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

但是 - 如果您需要在 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

演示:http://rextester.com/ISQSU31450

现在我们可以将它用作派生表(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

演示:http://rextester.com/IQYQ32927

最后一步(如果确实需要)是格式化结果。我们可以在外部选择中使用 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

演示:http://rextester.com/LLZ41445