MySQL检索距离当前最近的7个小时内，靠近每个时间点数据信息

MySQL检索距离当前最近的7个小时内，靠近每个时间点数据信息

如果你想在最近7个小时内找到每个时间点最接近的数据，即使某些时间点没有数据，你可以使用子查询和窗口函数。以下是一个示例查询：

sqlCopy codeSELECT
  t.time_point,
  COALESCE(y.your_column1, 0) AS your_column1,
  COALESCE(y.your_column2, 0) AS your_column2,
  ...
FROM (
  SELECT
    CURDATE() + INTERVAL (HOUR(NOW()) - n) HOUR AS time_point
  FROM
    (SELECT 0 AS n
     UNION SELECT 1
     UNION SELECT 2
     UNION SELECT 3
     UNION SELECT 4
     UNION SELECT 5
     UNION SELECT 6) AS hours
) t
LEFT JOIN (
  SELECT
    your_column1,
    your_column2,
    ...,
    your_datetime_column,
    ROW_NUMBER() OVER (PARTITION BY HOUR(your_datetime_column) ORDER BY ABS(TIMESTAMPDIFF(SECOND, your_datetime_column, NOW()))) AS row_num
  FROM
    your_table
  WHERE
    your_datetime_column >= NOW() - INTERVAL 7 HOUR
) y ON t.time_point = DATE_FORMAT(y.your_datetime_column, '%Y-%m-%d %H:00:00') AND y.row_num = 1
ORDER BY
  t.time_point DESC;

在这个查询中，我们首先创建了一个包含过去7个小时内每个小时的时间点的临时表 t。然后，我们使用一个子查询从 your_table 中选择最近7个小时内每个小时内最接近当前时间的数据，并为每个小时的数据分配了一个 row_num。

最后，我们使用 LEFT JOIN 将时间序列表和筛选后的数据连接，确保每个时间点都包含最接近的数据。ORDER BY 子句按照时间点降序排列。

请根据你的实际表和列名称进行适当的替换。这个查询假设每个小时内最多只有一条数据，如果有多条数据，可能需要根据具体需求进行调整。

实践

SELECT
	t.time_point ,
	y.id,
	y.NAME,
	y.creatime 
FROM
	(
	SELECT
		CURDATE() + INTERVAL ( HOUR ( NOW()) - n ) HOUR AS time_point 
	FROM
		( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) AS hours 
	) t
	LEFT JOIN (
	SELECT
		id,
		NAME,
		creatime,
		ROW_NUMBER() OVER (
			PARTITION BY HOUR ( creatime ) 
		ORDER BY
			ABS(
				TIMESTAMPDIFF(
					SECOND,
					creatime,
				NOW()))) AS row_num 
	FROM
	USER 
	WHERE
		creatime >= NOW() - INTERVAL 7 HOUR 
	) y ON t.time_point = DATE_FORMAT( y.creatime, '%Y-%m-%d %H:00:00' ) 
	AND y.row_num = 1 
ORDER BY
	t.time_point DESC;