如何在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是UniqueId AT-2的最新ID，基于日期，与下一个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

- Vector JX

---

你的 MySQL 版本是什么？ - Fahmi

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
)

- Salman A

---

只要 OP 不需要实际的天数差异，这就是一个更简洁的解决方案。 - Nick

Answer 2

这个查询将会给你想要的结果。它首先将表连接到每个唯一ID的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 上的演示

- Nick

---

如何将UniqueId的计数作为单独的列获取（即对于T-3，UniqueId的计数将为3）。 - Vector JX

@VectorJX请查看此更新的演示：https://www.db-fiddle.com/f/BjwoQprDKwtvRfvcGSy3p/1 - Nick