mysql选择时间戳距离为3分钟的记录

debugcn 发表于 Dev

balest14

我有这样的表结构：

CREATE TABLE `trackings` (
  `id` bigint(20) NOT NULL,
  `latitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `longitude` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `trackings` (`id`, `latitude`, `longitude`, `created_at`) VALUES
(171, '45.7006124', '9.1402032', '2020-12-08 07:48:04'),
(172, '45.7008048', '9.1407833', '2020-12-08 07:49:09'),
(173, '45.7009405', '9.1418868', '2020-12-08 07:50:16'),
(174, '45.7009405', '9.1418868', '2020-12-08 07:51:16'),
(175, '45.7010856', '9.14373', '2020-12-08 07:52:26'),
(176, '45.7011537', '9.1446661', '2020-12-08 07:53:31'),
(177, '45.7012833', '9.1465588', '2020-12-08 07:54:41'),
(178, '45.70119', '9.1485754', '2020-12-08 07:55:51'),
(179, '45.7010545', '9.1507779', '2020-12-08 07:56:01'),
(180, '45.7013211', '9.1530207', '2020-12-08 07:57:11'),
(181, '45.7015063', '9.1551616', '2020-12-08 07:57:21'),
(182, '45.7016013', '9.1573392', '2020-12-08 07:57:31');

从这组数据中，我需要选择每3分钟提取一次记录才能达到此结果：

171, '45.7006124', '9.1402032', '2020-12-08 07:48:04'
174, '45.7009405', '9.1418868', '2020-12-08 07:51:16'
177, '45.7012833', '9.1465588', '2020-12-08 07:54:41'
180, '45.7013211', '9.1530207', '2020-12-08 07:57:11'

数据集仅是一个示例，数据每5-10秒记录一次，每天有数千条记录。

我不知道以最佳性能实现它的sql方法。谢谢

草莓

根据您对“ 3分钟”的精确定义，这是一个主意...

SELECT a.* 
  FROM trackings a
  JOIN 
     ( SELECT MIN(id) id
            , FLOOR(TIME_TO_SEC(TIMEDIFF(x.created_at,y.created_at))/180)*180 delta 
         FROM trackings x 
         JOIN  
             ( SELECT MIN(created_at) created_at 
                 FROM trackings
             ) y
         GROUP
             BY delta
     ) b
    ON b.id = a.id;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。