如何在MySql中提取具有时间限制的数据

Question

我在上面提到的桌子下面有MySql。

ID       UniqueId     Date
T-1      AT-1         2018-04-03 15:16:48
T-2      AT-1         2018-04-10 18:21:24
T-3      AT-1         2018-04-22 13:05:16
T-4      AT-2         2018-05-05 17:12:04
T-5      AT-2         2018-05-15 05:03:01
T-6      AT-2         2018-05-25 06:09:23
T-7      AT-3         2018-06-01 06:09:23
T-8      AT-3         2018-06-25 06:09:23
T-9      AT-3         2018-07-01 06:09:23

我只想获取那些记录，其中最新的ID基础上的UniqueId是较早或等于10天。

例如，ID T3是最新的ID UniqueId AT-2 basis，即时ID T2之间的差额为12天。

此外，如果这在R中是可行的，那么也可以:)

预期产出：

ID       UniqueId     Date                  Difference
T-3      AT-1         2018-04-22 13:05:16   12
T-6      AT-2         2018-05-25 06:09:23   10

Answer 1

您可以使用EXISTS检查条件：

SELECT *
FROM t
WHERE NOT EXISTS (
    -- no newer row exists
    SELECT 1
    FROM t AS n
    WHERE n.UniqueId = t.UniqueId
    AND n.Date > t.Date
) AND NOT EXISTS (
    -- no older row exists that has difference of less than 10 days
    SELECT 1
    FROM t AS o
    WHERE o.UniqueId = t.UniqueId
    AND o.Date < t.Date
    AND o.Date >= t.Date - INTERVAL 10 DAY
)

Answer 2

此查询将给出所需的结果。它首先将表作为每个JOIN的MAX(Date)的派生表，然后使用Date值是小于最大值的最新Date的条件对其本身进行处理。最后，WHERE子句筛选出至少10天后的日期：

SELECT t1.*, DATEDIFF(t1.date, t3.date) AS Difference
FROM test t1
JOIN (SELECT UniqueId, MAX(Date) AS Date
      FROM test
      GROUP BY UniqueID) t2 ON t2.UniqueId = t1.UniqueId AND t2.Date = t1.Date
LEFT JOIN test t3 ON t3.UniqueId = t1.UniqueId
                  AND t3.Date = (SELECT MAX(Date) 
                                 FROM test t4 
                                 WHERE t4.UniqueId = t3.UniqueId
                                   AND t4.Date < t2.Date)
WHERE DATEDIFF(t1.date, t3.date) >= 10

输出：

ID      UniqueId    Date                    Difference
T-3     AT-1        2018-04-22 13:05:16     12
T-6     AT-2        2018-05-25 06:09:23     10

dbfiddle演示