代码之家 › 专栏 › 技术社区 › dropson

MySQL查询按输入间隔分组计数+以两个表列为间隔

mysql

dropson · 技术社区 · 15 年前

我已经断断续续地为此绞尽脑汁好几天了。真的需要任何有兴趣的人的积极投入。

输入

示例数据

ID| userID | login_time          | logout_time
------------------------------------------------------
1 | 1      | 06.11.2010 16:57:16 | 06.11.2010 16:34:11
2 | 2      | 06.11.2010 16:47:11 | 06.11.2010 19:55:15
3 | 3      | 06.11.2010 16:33:16 | 06.11.2010 16:53:33
4 | 4      | 06.11.2010 16:13:25 | 06.11.2010 18:54:54
5 | 5      | 06.11.2010 16:02:16 | 06.11.2010 16:34:11
6 | 6      | 06.11.2010 16:00:11 | 06.11.2010 17:55:19
7 | 6      | 06.11.2010 19:00:11 | 06.11.2010 22:55:19
8 | 6      | 06.11.2010 20:00:11 | 06.11.2010 23:55:19
9 | 6      | 06.11.2010 20:00:11 | 06.11.2010 21:55:19
9 | 6      | 06.11.2010 09:00:11 | 06.11.2010 10:00:19

首选结果

示例输入:2010年11月6日之间 16: 30点 16: 35点 按分钟分组()

Count | Date
---------------------------
5     | 06.11.2010 16:30:00
5     | 06.11.2010 16:31:00
5     | 06.11.2010 16:32:00
4     | 06.11.2010 16:33:00
5     | 06.11.2010 16:34:00
3     | 06.11.2010 16:35:00

示例输入:2010年11月6日之间 2010年11月6日 23:35:00

Count | Date
---------------------------
1     | 06.11.2010 09:00:00
1     | 06.11.2010 10:00:00

-->PS:如果缺少间隔(即没有数据),我不介意在这里有间隔

6     | 06.11.2010 16:00:00
3     | 06.11.2010 17:00:00
2     | 06.11.2010 18:00:00
2     | 06.11.2010 19:00:00
3     | 06.11.2010 20:00:00
3     | 06.11.2010 21:00:00
2     | 06.11.2010 22:00:00
1     | 06.11.2010 23:00:00

也许你可以举例说明一个合适而有效的查询,它将产生我所想到的预期结果?我暂时不在这篇文章里写那些乱七八糟的代码。

2 回复 | 直到 15 年前

Bill Karwin 15 年前

在SQL中,最简单的方法是创建一个包含所有要测试的时间值的临时表,然后将其连接到日志表。

drop table if exists minutes;
create temporary table minutes (date datetime not null);
set @t := '2010.11.06 16:30:00';  insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);

select count(*) as count, m.date
from minutes m join log l
on m.date <= l.logout_time and m.date + interval 1 minute >= l.login_time
group by m.date;

AndreKR 15 年前

抱歉,不能使用单一查询。因为SQL是基于集合论的,所以必须创建一组时间间隔。

可以在临时表或存储过程中执行此操作,该存储过程计算时间间隔并对每个间隔执行查询。

所以你可以:

CREATE TEMPORARY TABLE temp_intervals (
    start_datetime DATETIME NOT NULL ,
    end_datetime DATETIME NOT NULL
)

INSERT INTO temp_intervals(start_datetime, end_datetime) VALUES
    ('2010-11-06 16:30:00', '2010-11-06 16:31:00'),
    ('2010-11-06 16:31:00', '2010-11-06 16:32:00')
    ...

SELECT start_datetime, COUNT(*)
FROM temp_intervals
LEFT JOIN login_history -- LEFT JOIN if you want empty intervals in the result
WHERE login_time <= start_datetime AND logout_time > end_datetime
GROUP BY start_datetime

请记住,所有语句都必须在一个连接中完成,因为临时表只对该连接可见。

另外,您不需要任何过程语言,因为您可以让MySQL使用存储过程创建临时表。