在mysql数据库中查找行和时间戳之间的交集 [英] Find intersections between rows and timestamps in a mysql db

问题描述

我有一个表，其中列出了某些对象的事件.

I have a table, where events of certain objects are listed.

有两个事件:运动"和负载".它们可以开始和结束，并且这些事件在发生时会带有时间戳列出.

There are two events: "movement" and "load". They can start and end, and these events are listed with timestamps when they happened.

现在我要计算以下三个数字:

Now i want to calculate the following three numbers:

• 运动发生的时间总和(小提琴中的值为700)
• 发生负载的时间总和(小提琴中的值为630)
• 发生移动和负载的时间总和(小提琴中的值为611)

我为这里的问题创建了一个sqlfiddle:

I have created a sqlfiddle for the problem here:

http://sqlfiddle.com/#!2/be512

推荐答案

前两列计算相应的end/start事件之间的差异. (求和是可交换的，因此我们实际上不需要匹配相应的事件.)

The first two columns compute the differences between corresponding end/start events. (Summation is commutative, so we don't need to actually match corresponding events.)

第三列搜索前一个load事件为load start事件的movement start事件，以及后一个load事件为load end事件的movement end事件.

The third colum searches for movement start events for which the preceding load event is a load start event, and for movement end events for which the following load event is a load end event.

SELECT (SELECT SUM(timestamp)
        FROM Table1
        WHERE event = 'movement end') -
       (SELECT SUM(timestamp)
        FROM Table1
        WHERE event = 'movement start') AS all_movement,

       (SELECT SUM(timestamp)
        FROM Table1
        WHERE event = 'load end') -
       (SELECT SUM(timestamp)
        FROM Table1
        WHERE event = 'load start') AS all_load,

       (SELECT SUM(timestamp)
        FROM Table1 a
        WHERE event = 'movement end' AND
              (SELECT event
               FROM Table1 b
               WHERE timestamp = (SELECT min(timestamp)
                                  FROM Table1 c
                                  WHERE c.timestamp >= a.timestamp
                                    AND c.event LIKE 'load %')
              ) = 'load end') -
       (SELECT SUM(timestamp)
        FROM Table1 a
        WHERE event = 'movement start' AND
              (SELECT event
               FROM Table1 b
               WHERE timestamp = (SELECT max(timestamp)
                                  FROM Table1 c
                                  WHERE c.timestamp <= a.timestamp
                                    AND c.event LIKE 'load %')
              ) = 'load start') AS load_movement;

这篇关于在mysql数据库中查找行和时间戳之间的交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！