MySQL 8.0：获取指定市场每日最接近04:00的数据

问题背景 #

使用MySQL 8.0，目标表结构如下：

CREATE TABLE `sentiments` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `market_id` int NOT NULL,
  `customer_long` decimal(4,2) unsigned NOT NULL,
  `customer_short` decimal(4,2) unsigned NOT NULL,
  `vol_long` decimal(4,2) unsigned NOT NULL,
  `vol_short` decimal(4,2) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sentiments_market_id_created_at_index` (`market_id`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=24040526 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

需要查询market_id=1且created_at在'2023-01-01'至'2023-01-31'之间，每日最接近04:00的数据。

原SQL的问题 #

你编写的SQL存在逻辑缺陷：

SELECT
    * 
FROM
    (
    SELECT
        *,
        ABS( ( UNIX_TIMESTAMP( created_at ) - UNIX_TIMESTAMP( CONCAT( DATE_FORMAT( created_at, "%Y-%m-%d" ), " 04:00:00" ) ) ) ) AS math_sub,
        DATE_FORMAT( created_at, "%Y-%m-%d" ) AS days 
    FROM
        sentiments 
    WHERE
        DATE( created_at ) BETWEEN '2023-01-01' 
        AND '2023-01-14' 
        AND market_id = 1 
    ORDER BY
        math_sub ASC 
    ) AS b 
GROUP BY
    b.days

MySQL的分组逻辑不会保留子查询的排序结果，优化器可能会忽略子查询的ORDER BY，导致最终取到的是分组内任意一条记录，而非最接近04:00的目标记录。

正确解法 #

利用MySQL 8.0支持的窗口函数ROW_NUMBER()，按日期分组后根据时间差排序，精准获取每日最接近04:00的记录：

SELECT 
    id, market_id, customer_long, customer_short, vol_long, vol_short, created_at, updated_at
FROM (
    SELECT 
        *,
        ABS(TIMESTAMPDIFF(SECOND, created_at, CONCAT(DATE(created_at), ' 04:00:00'))) AS time_diff,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(created_at) 
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, created_at, CONCAT(DATE(created_at), ' 04:00:00'))) ASC,
                     created_at DESC -- 若两条记录时间差相同，优先取04:00之后的记录，可按需改为ASC取之前的
        ) AS rn
    FROM sentiments
    WHERE 
        market_id = 1 
        AND created_at >= '2023-01-01 00:00:00' 
        AND created_at < '2023-02-01 00:00:00' -- 避免DATE()函数导致索引失效，提升查询效率
) t
WHERE rn = 1;

关键说明 #

1. 用TIMESTAMPDIFF(SECOND, ...)直接计算时间差的秒数绝对值，比转UNIX_TIMESTAMP更直观高效。
2. PARTITION BY DATE(created_at)将同一天的记录划为一组，ORDER BY time_diff ASC确保每组内最接近04:00的记录排在第一位。
3. 条件中使用created_at >= '2023-01-01' AND created_at < '2023-02-01'，可以利用sentiments_market_id_created_at_index索引，避免全表扫描。

内容的提问来源于stack exchange，提问作者Fan Yang