统计最多在线人数的sql

[guanshanwuxia]

有一个需求,需要统计类似最大的在线人数的功能,表设计如下:

其中 id为主键 user_id为用户id start_time为开始时间 end_time为结束时间
我现在需要统计最大的在线人数和时间段
参考了http://blog.csdn.net/zhanghongju/article/details/8392817这篇文章
我的sql是:

SELECT
	t1.*, count(t1.`user_id`)
FROM
	testcy t1,
	testcy t2
WHERE
	t1.`start_time` < t2.`end_time`
AND t1.`end_time` > t2.`start_time`
GROUP BY
	t1.`user_id`,
	t1.`start_time`,
	t1.`end_time`;

得到的查询结果为:
实际应该得到的最大在线人数是3,但是由于用户2在用户1的时间段里出现了两次在线的情况,导致最大值为5
请教各位大佬,如何获取正确的查询结果

[二月十六]

加一个distinct试试。

SELECT
    t1.*, count(distinct t1.`user_id`)
FROM
    testcy t1,
    testcy t2
WHERE
    t1.`start_time` < t2.`end_time`
AND t1.`end_time` > t2.`start_time`
GROUP BY
    t1.`user_id`,
    t1.`start_time`,
    t1.`end_time`;

[「已注销」]

引用 3 楼 sinat_28984567 的回复:

看看那这样对不

SELECT
	t1.*, (SELECT count(DISTINCT `user_id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`end_time`) as 在线人数
FROM
	testcy t1

再请教一下大神 对于这种数据:

不去管user_id这个字段 我想要统计得到按group_id分组后的在某一时间段的最大值,类似场景为最大并发数,
比如按图中的数据我期望得到的结果为aa的最大并发数为4 bb的为2 但实际应用sql查询结果为:

我的sql是

select t1.*,count(t2.`id`) 
from testcy t1,testcy t2
where  t1.`group_id`=t2.group_id 
and t1.`start_time` <= t2.`end_time`
and t1.`end_time` >= t2.`start_time`
group by t1.`id`,t1.`start_time`,t1.`end_time`;

原因在aa中有部分时间有重叠的情况,请教下我该如何做呢

[二月十六]

额，你试试这样。

SELECT
	t1.*, (SELECT count(t2.`id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`end_time` and t1.group_id =  t2.group_id ) as count
FROM
	testcy t1

PS：最好能贴数据，贴图的话测试数据还得自己录入。。。

[「已注销」]

引用 6 楼 sinat_28984567 的回复:

额，你试试这样。

SELECT
	t1.*, (SELECT count(t2.`id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`end_time` and t1.group_id =  t2.group_id ) as count
FROM
	testcy t1

PS：最好能贴数据，贴图的话测试数据还得自己录入。。。

好的好的 是我2了 数据如下:

create table `testcy` (
	`id` int (11),
	`group_id` varchar (255),
	`user_id` int (11),
	`start_time` datetime ,
	`end_time` datetime 
); 
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('1','aa','1','2017-04-05 14:00:00','2017-04-05 15:30:01');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('2','aa','1','2017-04-05 13:30:00','2017-04-05 14:20:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('3','aa','1','2017-04-05 13:40:00','2017-04-05 14:40:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('4','aa','1','2017-04-05 15:00:00','2017-04-05 16:00:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('5','aa','1','2017-04-05 14:20:00','2017-04-05 15:00:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('6','aa','1','2017-04-05 15:10:00','2017-04-05 16:10:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('7','bb','1','2017-04-05 14:15:00','2017-04-05 15:15:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('8','bb','1','2017-04-05 14:30:00','2017-04-05 15:00:00');
insert into `testcy` (`id`, `group_id`, `user_id`, `start_time`, `end_time`) values('9','bb','1','2017-04-05 15:30:00','2017-04-05 16:00:00');

因为我要算的是并发数,我之前参考了一篇帖子http://blog.csdn.net/zhanghongju/article/details/8392817 比较赞同 当前一条数据的开始时间小于等于后一条数据的结束时间 并且 前一条数据的结束时间大于等于后一条数据的开始时间 就认为这两条数据出现了时间重叠 因此我的sql为(结合您的写法):

select
    t1.*, (select count(t2.`id`) 
from testcy t2 where  t1.`start_time` <= t2.`end_time`
and t1.`end_time` >= t2.`start_time` and t1.group_id =  t2.group_id ) as count
from
    testcy t1;

这样得到的结果为

    id  group_id  user_id  start_time           end_time              count  
------  --------  -------  -------------------  -------------------  --------
     1  aa              1  2017-04-05 14:00:00  2017-04-05 15:30:01         6
     2  aa              1  2017-04-05 13:30:00  2017-04-05 14:20:00         4
     3  aa              1  2017-04-05 13:40:00  2017-04-05 14:40:00         4
     4  aa              1  2017-04-05 15:00:00  2017-04-05 16:00:00         4
     5  aa              1  2017-04-05 14:20:00  2017-04-05 15:00:00         5
     6  aa              1  2017-04-05 15:10:00  2017-04-05 16:10:00         3
     7  bb              1  2017-04-05 14:15:00  2017-04-05 15:15:00         2
     8  bb              1  2017-04-05 14:30:00  2017-04-05 15:00:00         2
     9  bb              1  2017-04-05 15:30:00  2017-04-05 16:00:00         1

我期望的结果应该是:aa最大并发数为4 bb为2 对于aa组来说最大并发出现在id=1,4,5,6这四条数据里 请指教我该如何写这样的sql

[二月十六]

你看看是不是这个意思

SELECT
	t1.*, (SELECT count(t2.`id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`start_time` and t1.group_id =  t2.group_id ) as count
FROM
	testcy t1

[「已注销」]

引用 8 楼 sinat_28984567 的回复:

你看看是不是这个意思

SELECT
	t1.*, (SELECT count(t2.`id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`start_time` and t1.group_id =  t2.group_id ) as count
FROM
	testcy t1

感谢感谢 是我先入为主的把时间重叠的判断条件定死了 谢谢大神的指教

[「已注销」]

引用 1 楼 sinat_28984567 的回复:

加一个distinct试试。

SELECT
    t1.*, count(distinct t1.`user_id`)
FROM
    testcy t1,
    testcy t2
WHERE
    t1.`start_time` < t2.`end_time`
AND t1.`end_time` > t2.`start_time`
GROUP BY
    t1.`user_id`,
    t1.`start_time`,
    t1.`end_time`;

感谢大神的回复,不过貌似不行,实测运行得到的结果是:

[二月十六]

看看那这样对不

SELECT
	t1.*, (SELECT count(DISTINCT `user_id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`end_time`) as 在线人数
FROM
	testcy t1

[「已注销」]

引用 3 楼 sinat_28984567 的回复:

看看那这样对不

SELECT
	t1.*, (SELECT count(DISTINCT `user_id`) 
from testcy t2 where  t1.`start_time` <= t2.`start_time`
AND t1.`end_time` >= t2.`end_time`) as 在线人数
FROM
	testcy t1

厉害了word神 感谢感谢 或许这就是大佬吧...