时间戳

首页 > 解决方案 > 从最接近选定日期的数据库中获取唯一数据

mysql - 从最接近选定日期的数据库中获取唯一数据

问题描述

我想获得最接近特定日期的数据，无论是过去还是未来，以更接近日期为准。但我也需要它distinct由type. 例如：

id |type |data | date                
 1 |0    |1904 |2018-08-19 00:14:32
 2 |0    |1904 |2018-08-19 00:24:47
 3 |0    |1904 |2018-08-19 05:02:30
 4 |0    |1904 |2018-08-22 00:05:58
 5 |0    |1904 |2018-08-22 00:08:34
 6 |4    |1903 |2018-08-19 00:14:31
 7 |6    |1926 |2018-08-19 00:14:32
 8 |6    |1926 |2018-08-19 04:44:10
 9 |6    |1926 |2018-08-19 04:52:58
10 |6    |1926 |2018-08-19 04:59:23
11 |6    |1926 |2018-08-22 00:05:58
12 |6    |1926 |2018-08-22 00:07:52
13 |6    |1926 |2018-08-22 00:08:34
14 |7    |1564 |2018-08-19 00:14:32
15 |8    |1900 |2018-08-19 00:14:32

如果我指定日期时间：2018-08-19 00:00:00。我应该得到以下结果：

id | type
 1 | 0    
 6 | 4
 7 | 6
14 | 7
15 | 8

或者，如果我指定日期时间：2018-09-03 00:00:00。我预计：

id | type
 5 | 0    
 6 | 4
13 | 6
14 | 7
15 | 8

或者最后：2018-08-22 00:05:58。

id | type
 4 | 0    
 6 | 4
11 | 6
14 | 7
15 | 8

我尝试了一些没有真正意义的查询......比如：

SELECT *
FROM history 
WHERE (ABS(TIMESTAMPDIFF(DAY, date, "2018-08-19 00:08:34")) < 4) AND user_id = 1299
GROUP BY type
ORDER BY date

无论如何，这可能仅使用 MySQL 查询吗？我是否需要使用一些应用程序逻辑来实现这一点？我如何查询这个？

SQL小提琴

标签： mysqlsql

解决方案

这采用标准查询模式来查找具有每种类型极值的行。在这种情况下，极端意味着最接近目标值。

首先，您需要一个聚合子查询来获取极值：每种类型的行与您的目标日期之间的最小增量时间。（http://sqlfiddle.com/#!9/b51a7a/26/1）

      SET @target := '2018-08-20 00:00:00';
      SELECT type, MIN(ABS(TIMESTAMPDIFF(MINUTE, date, @target))) delta
        FROM history
       GROUP BY type

然后你将它加入到你的原始表中，使用该ON子句来匹配类型和增量时间。把它们放在一起（http://sqlfiddle.com/#!9/b51a7a/27/1）

SET @target := '2018-08-20 00:00:00';
SELECT history.*
  FROM history
  JOIN (
          SELECT type, MIN(ABS(TIMESTAMPDIFF(MINUTE, date, @target))) delta
            FROM history
           GROUP BY type
      ) delta ON history.type=delta.type 
              AND ABS(TIMESTAMPDIFF(MINUTE, date, @target)) = delta

编辑更容易只获取最近的未来或最近的过去项目到日期。子查询（最近的过去）是

         SELECT type, MAX(date) date
           FROM history
         WHERE date <= @target
         GROUP BY date

那么整体查询是

  SELECT history.*
    FROM history
    JOIN (
             SELECT type, MAX(date) date
               FROM history
             WHERE date <= @target
             GROUP BY date
         ) m ON history.type = m.type AND history.date = m.date

推荐阅读