使用mysql查询以epoch格式确定基于开始和结束日期时间的并发性

I have a table in the MySQL database, shown below, in which each line represents session data. Each session has a start- and stop date-time in epoch format and is connected to a specific server where a asset has been used.

我在MySQL数据库中有一个表,如下所示,其中每一行代表会话数据。每个会话都有一个epoch格式的开始和结束日期时间,并连接到已使用资产的特定服务器。

 +--------------------------------------------+
 | Asset + Server   +  Start     + End        | 
 +--------------------------------------------+ 
 | 1     | 10.0.0.1 | 1397606535 | 1397606511 |
 | 1     | 10.0.0.2 | 1397606534 | 1397606311 |
 | 2     | 10.0.0.2 | 1397606533 | 1397606612 |
 | 1     | 10.0.0.1 | 1397606534 | 1397606511 |
 | 3     | 10.0.0.1 | 1397606531 | 1397609555 |
 | 1     | 10.0.0.3 | 1397606531 | 1397606511 |
 | 1     | 10.0.0.4 | 1397606525 | 1397606511 |
 | 4     | 10.0.0.3 | 1397606515 | 1397606411 |
 | 1     | 10.0.0.3 | 1397606135 | 1397606581 |
 | 6     | 10.0.0.1 | 1397606135 | 1397606511 |
 | 1     | 10.0.0.2 | 1397606525 | 1397606511 |
 | 5     | 10.0.0.1 | 1397606135 | 1397606511 |
 | 1     | 10.0.0.4 | 1397606535 | 1397606511 |
 +--------------------------------------------+

The main problem is that I'm trying to solve three problems:

主要问题是我正试图解决三个问题:

What is the maximum amount of concurrent sessions?

并发会话的最大数量是多少?

What is the maximum amount of concurrent sessions per asset?

每个资产的最大并发会话数是多少?

What is the maximum amount of concurrent sessions per server?

每台服务器的最大并发会话数是多少?

The three problems all have the same problem: how do i compare the start and end times while taking into account all the time in between?

这三个问题都有同样的问题:我如何比较开始和结束时间,同时考虑到它们之间的所有时间?

Hopefully there is somebody who can help me out with this.

希望有人可以帮助我解决这个问题。

2 个解决方案

#1

I think you will have to take each row at a time and compare the dates with the others (except itself) to see how many times the interval overlaps and update your max number for each case when necesarry

我认为你必须一次取每一行并将日期与其他行(除了它自己)进行比较,以查看间隔重叠的次数,并在必要时更新每个案例的最大数量。

DECLARE maxSessions INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT Asset,Server,Start,End FROM yourtable;
OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO CurrAsset,CurrServer,CurrStart,CurrEnd;

    SELECT COUNT(*) FROM yourtable
    WHERE (Start >= CurrStart && Start <= CurrEnd) || (End >= CurrStart && End <= CurrEnd)
    INTO number;

    IF (number > maxSessions) THEN
       maxSessions = number;
    END IF;
END LOOP;

maxSessions--;  //Since one time it will be compared to itself

It can be adapted for the other 2 situations depending if you need to find out the max concurent times for each server/asset in particular or the max in general

它可以适用于其他两种情况,具体取决于您是否需要查找特定每个服务器/资产的最大连续时间或一般最大值

#2

I solved the issue by fetching the entire data-set and processing it in code. I didn't find the cursor solution elegant and didn't give me the freedom i was looking for.

我通过获取整个数据集并在代码中处理它来解决了这个问题。我没有找到光标解决方案优雅,并没有给我我寻找的*。

#1