时间戳

首页 > 解决方案 > 计算 SQL 中的周期计数，其中某些周期的计数可能为零

mysql - 计算 SQL 中的周期计数，其中某些周期的计数可能为零

问题描述

我有一些数据，例如：

id  tstamp
1   2021-06-04 09:00:00
2   2021-06-04 09:01:00
3   2021-06-04 10:00:00
4   2021-06-04 12:00:00
5   2021-06-04 14:00:00
6   2021-06-04 14:20:00

我希望计算某些时期之间的行数。

例如，如果我想按小时计算，我可以这样做：

SELECT HOUR(tstamp), COUNT(*) from logs GROUP BY HOUR(tstamp)

但是，这会返回：

HOUR(tstamp)    COUNT(*)
9   2
10  1
12  1
14  2

but I would actually like:
9   2
10  1
11  0
12  1
13  0
14  2

此外，在实际使用情况下，可以指定任何时间段。它可能是：

所以我需要找到一种处理任意时期的好方法，如果这甚至可能（？），或者至少如果有一种我可以针对特定时期修改的通用方法，那也很好。

这是一个 dbfiddle

编辑：根据@georgeos 的评论，我已经解决了第一部分：

WITH
periods (p) AS (SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14),
logs1 AS (SELECT HOUR(tstamp) as Hour, COUNT(*) AS Count from logs GROUP BY HOUR(tstamp))

SELECT periods.p, logs1.Count from periods
  LEFT JOIN logs1 on periods.p = logs1.Hour

虽然这并不是一个优雅的解决方案，但我仍然想知道如何很好地处理其他时期。

标签： mysqlsql

解决方案

样本数据生成：

create table mytable (id int, tstamp timestamp);

insert into mytable values (1  , '2021-06-04 09:00:00');
insert into mytable values (2 ,  '2021-06-04 09:01:00');
insert into mytable values (3  , '2021-06-04 10:00:00');
insert into mytable values (4   ,'2021-06-04 12:00:00');
insert into mytable values (5   ,'2021-06-04 14:00:00');
insert into mytable values (6   ,'2021-06-04 14:20:00');
insert into mytable values (7  , '2021-06-05 11:00:00');
insert into mytable values (8 ,  '2021-06-05 11:01:00');
insert into mytable values (9  , '2021-06-05 12:00:00');
insert into mytable values (10   ,'2021-06-05 15:00:00');
insert into mytable values (11  ,'2021-06-05 16:00:00');
insert into mytable values (12  ,'2021-06-05 23:20:00');
insert into mytable values (13  ,'2021-06-05 09:20:00');

让我们一步一步地完成这一步，这将帮助您开始其他场景，如分钟、5 分钟、一天等。

  1. 这里的 CTE 正在制作你想要计算的单位的超级集，这里是小时。所以 CTE 只是生成一个 24 小时的单列表，它将作为我们的超集。我使用了递归 CTE，因为我不喜欢用很多联合编写静态值。

  2. CTE 之后的第一个连接是在表中具有不同日期的交叉连接。这是强制性的，因为每天重复小时，并且您的表可能包含多天的数据。

  3. 最后左连接只是将实际表数据与每天的每个小时进行匹配，然后我们从表中计算 tstamp 列的匹配值。

最终查询：

with recursive RecCTE
as
(select 0 as hours
union all
select x.hours + 1
from RecCTE x
where x.hours <= 22 
)
Select x.hours ,z.dist_date, count(y.tstamp) cnt
from RecCTE x
cross join (select distinct date(tstamp) dist_date from mytable) z
left join mytable y on x.hours = hour(y.tstamp) and date(y.tstamp) = z.dist_date
group by x.hours,z.dist_date
order by z.dist_date, x.hours;

您可以概括这些步骤以将此查询移植到其他场景，例如月、周、分钟等。

推荐阅读