在mysql获得每天最接近凌晨4点0分0秒的数据示例代码

数据库  /  管理员 发布于 2星期前   28

如何在mysql中取出market_id为 1，created_at指定日期段(2023-01-01 至 2023-02-22)每天中created_at最接近凌晨4点0分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

sql代码示例：

SELECT
    * 
FROM
    (
    SELECT
        *,
        row_number() over (
            PARTITION BY market_id,
            date( created_at ) 
        ORDER BY
            ABS(
                UNIX_TIMESTAMP( created_at ) - UNIX_TIMESTAMP(
                CONCAT( DATE_FORMAT( created_at, '%Y-%m-%d' ), ' 04:00:00' ))) 
        ) AS rn 
    FROM
        sentiments 
    WHERE
        created_at >= TIMESTAMP ( '2014-01-01' ) 
        AND created_at < TIMESTAMP ( '2014-02-01' ) 
        AND market_id = 1 
    ) d 
WHERE
    rn = 1