时间序列中按24小时时间桶提取各机器ID最新数据的方法

以下是两种常用场景的具体实现方法：

1. SQL数据库场景 #

假设数据表包含 machine_id（机器ID）、record_time（记录时间）、data（数据字段），核心思路是：先把每条记录映射到对应的24小时时间桶，再按机器ID和时间桶分组，筛选出每组内时间最晚的记录。

MySQL示例代码 #

SELECT t1.*
FROM your_table t1
INNER JOIN (
    SELECT 
        machine_id,
        DATE_FORMAT(record_time, '%Y-%m-%d') AS time_bucket,
        MAX(record_time) AS latest_time
    FROM your_table
    GROUP BY machine_id, DATE_FORMAT(record_time, '%Y-%m-%d')
) t2 ON t1.machine_id = t2.machine_id 
    AND t1.record_time = t2.latest_time;

PostgreSQL示例代码 #

SELECT t1.*
FROM your_table t1
INNER JOIN (
    SELECT 
        machine_id,
        DATE_TRUNC('day', record_time) AS time_bucket,
        MAX(record_time) AS latest_time
    FROM your_table
    GROUP BY machine_id, DATE_TRUNC('day', record_time)
) t2 ON t1.machine_id = t2.machine_id 
    AND t1.record_time = t2.latest_time;

如果需要自定义24小时桶的起始时间（比如从每天6点开始），可以调整时间计算逻辑，比如MySQL中用DATE_FORMAT(record_time - INTERVAL 6 HOUR, '%Y-%m-%d')偏移时间桶的基准。

2. Python Pandas场景 #

若数据存储在Pandas DataFrame中，且包含machine_id、record_time、data列：

实现代码 #

import pandas as pd

# 确保时间列是datetime类型
df['record_time'] = pd.to_datetime(df['record_time'])

# 按机器ID分组，每组按天重采样，取每个时间桶的最后一条（最新）记录
result = df.groupby('machine_id').resample('D', on='record_time').last().reset_index()

• resample('D')按自然日划分24小时桶，若要自定义起始时间，可添加offset参数，比如resample('D', on='record_time', offset='6H')表示从每天6点开始计算时间桶。
• last()方法直接提取每个时间桶内的最新数据点。

内容的提问来源于stack exchange，提问作者Anders Sewerin Johansen