SQL查询:在给定时间间隔内处于活动状态的所有ID的列表，按其开始时间排序 [英] SQL query: list of all IDs that were active during a given time interval, sorted by their start-time

问题描述

我有一个MySQL表，其中包含轨道的点(x/y坐标).每行都包含TrackID，时间戳，以及该轨道在给定时间点的X和Y位置.

I have a MySQL table containing the points (x/y coordinates) of tracks. Each row contains the TrackID, a Timestamp, and the X and Y Positions for that track at that given point in time.

我想要的是一个在给定时间间隔(tmin ... tmax)内处于活动状态的所有TrackID的列表，按其开始时间进行排序，即使该开始时间不在该时间间隔内.

What I want is a list of all TrackIDs that were active during a given time interval (tmin...tmax), sorted by their start-time, even if that start time is outside the interval.

一些小插图可能会有所帮助:

A little illustration might help:

例如:从t11到t12，磁道1处于活动状态，这意味着我的表中有很多行，ID = 1，且时间戳从t11到t12.

As an example: Track 1 is active from t11 till t12, which means I have many rows in my table with ID=1 and with timestamps ranging from t11 to t12.

所需的输出将是:

TrackID | StartTime
--------+-----------
    7   |    t71
    1   |    t11
    2   |    t21
    6   |    t61

我尝试过这样的事情:

SELECT TrackID, MIN(Timestamp) AS StartTime FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax GROUP BY TrackID ORDER BY StartTime;

但是，在上面的示例中，我没有获得轨道1和轨道7的实际开始时间，因为根本不考虑所有时间戳小于tmin的行.

However, in the example above I don't get the real start times for tracks 1 and 7, since all rows with timestamps less than tmin are not considered at all.

当然，我可以第一步获得所有活动的TrackID，

Of course I could in a first step just get all active TrackIDs with

SELECT TrackID FROM Tracks WHERE Timestamp BETWEEN tmin AND tmax GROUP BY TrackID;

然后通过单独的查询找到所有这些曲目的开始时间，然后将它们按我的应用程序代码进行排序.

and then with separate queries find the start times of all these tracks and then sort them in my application code.

但是我敢肯定有一种方法可以通过一个SQL查询来做到这一点.我的表包含数百万行，因此效率是一个问题.

But I'm sure there is a way to do this with one SQL query. My table contains millions of rows, so efficiency is an issue here.

推荐答案

一种思考方法是构造逻辑以处理图中的四种特殊情况.这两个规则就足够了.

One way to think about it is to construct the logic to handle your four special cases in your diagram. These two rules should suffice.

1. tend> tmin AND
2. tstart< tmax

如果这两个条件中的任何一个为真，则应包括该轨道.与第二个查询一样，您将需要一个轨道列表及其最小值和最大值，然后执行比较:

If any of these two conditions are true, then the track should be included. You will need a list of tracks as in your second query with their min and max values, and then perform the comparisons:

SELECT T.TrackID
  FROM (SELECT TrackID, MIN(Timestamp) AS StartTime, MAX(Timestamp) AS EndTime
        FROM Tracks GROUP BY TrackID) T
 WHERE T.EndTime > tmin AND T.StartTime < tmax

这篇关于SQL查询:在给定时间间隔内处于活动状态的所有ID的列表，按其开始时间排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！