基于时间戳按日统计设备各特定状态累计停留时长方法咨询

核心思路 #

• 第一步：按设备+时间戳对所有记录升序排序
• 第二步：识别连续相同的状态段，状态未发生变化的连续记录归为同一段
• 第三步：对每个状态段计算「最大时间戳 - 最小时间戳」得到该段停留时长
• 第四步：按天+状态维度汇总所有段的时长，得到单日各状态的累计停留时长

SQL 实现示例（适用于Hive/Spark SQL/MySQL 8.0+等支持窗口函数的场景） #

假设你的表名为device_status_log，核心字段为：device_id（设备ID）、status（设备状态）、record_time（记录时间戳，datetime类型）

WITH step1 AS (
    -- 排序+标记和上一行状态是否变化
    SELECT 
        device_id,
        status,
        record_time,
        -- 状态和上一行不同时标记为1，相同为0
        IF(status != LAG(status,1,'') OVER(PARTITION BY device_id ORDER BY record_time),1,0) AS status_change_flag
    FROM device_status_log
    -- 可按需加时间范围过滤
    -- WHERE record_time >= '2024-01-01' AND record_time < '2024-01-08'
),
step2 AS (
    -- 对变化标记累加，得到连续状态段的唯一ID
    SELECT 
        *,
        SUM(status_change_flag) OVER(PARTITION BY device_id ORDER BY record_time) AS status_block_id
    FROM step1
),
step3 AS (
    -- 计算每个连续状态段的停留时长，单位为分钟
    SELECT 
        device_id,
        status,
        DATE(record_time) AS stat_date,
        TIMESTAMPDIFF(MINUTE, MIN(record_time), MAX(record_time)) AS duration
    FROM step2
    GROUP BY device_id, status, DATE(record_time), status_block_id
)
-- 按天+状态汇总累计时长
SELECT 
    stat_date,
    status,
    SUM(duration) AS total_duration_min
FROM step3
GROUP BY stat_date, status
ORDER BY stat_date, status;

Python Pandas 实现示例 #

假设你已经把数据集读入为名为df的DataFrame，核心字段同上

import pandas as pd

# 1. 先按设备ID+记录时间排序
df = df.sort_values(by=['device_id', 'record_time']).reset_index(drop=True)

# 2. 打状态变化标记，生成连续状态段ID
df['status_change_flag'] = df.groupby('device_id')['status'].diff().ne(0).astype(int)
df['status_block_id'] = df.groupby('device_id')['status_change_flag'].cumsum()

# 3. 计算每个状态段的停留时长
block_duration = df.groupby(['device_id', 'status', df['record_time'].dt.date, 'status_block_id']).agg(
    start_time=('record_time', 'min'),
    end_time=('record_time', 'max')
).reset_index()
block_duration['duration_min'] = (block_duration['end_time'] - block_duration['start_time']).dt.total_seconds() / 60

# 4. 按天+状态汇总累计时长
result = block_duration.groupby(['record_time', 'status'])['duration_min'].sum().reset_index()
result.columns = ['stat_date', 'status', 'total_duration_min']

注意事项 #

• 如果数据存在跨天的连续状态段（比如前一天23点到第二天1点都是RUNNING状态），上面的实现会把跨天的时长拆分到对应日期统计，如果需要完整归到状态开始的日期，去掉分组逻辑里的日期拆分条件，统计时取状态段开始时间的日期即可
• 时间单位可以根据需求调整，SQL里把MINUTE换成SECOND/HOUR，Pandas里把除以60的逻辑对应调整即可

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