按15分钟时间间隔拆分不规则开关量测量数据并统计开机时长

核心思路 #

要处理跨15分钟间隔的连续开机统计，关键是把每一段连续开机状态拆分成与15分钟区间对齐的子时段，分别计算每个子时段的时长后，再按区间汇总。

具体实现步骤（以SQL为例） #

1. 生成覆盖全时段的15分钟区间列表
   基于数据中的最小和最大时间戳，生成所有包含数据的15分钟起始时间点，比如2024-05-20 08:00:00、2024-05-20 08:15:00这类对齐的时间点。

2. 拆分连续开机时段
   先通过状态变化记录，标记每一段连续开机的起止时间：

   • 当状态从0变为1时，记录开机起始时间；状态从1变为0时，记录关机时间作为这段开机的结束时间。
   • 若最后一条记录是开机状态，需手动将结束时间设为数据覆盖的最后一个15分钟区间的结束点。
     接着判断每段开机时段与15分钟区间的重叠情况：
   • 完全在一个区间内：直接计算结束时间与起始时间的差值。
   • 跨多个区间：分别计算每个重叠区间内的有效时长，比如开机从08:10到08:25，则08:00-08:15区间贡献5分钟，08:15-08:30区间贡献10分钟。

3. 按区间汇总时长
   将所有拆分后的子时段时长，按对应的15分钟区间分组求和，得到每个区间的总开机分钟数。

示例SQL代码片段 #

-- 生成15分钟时间区间
WITH time_intervals AS (
    SELECT 
        generate_series(
            DATE_TRUNC('hour', MIN(STATS_DATE)),
            DATE_TRUNC('hour', MAX(STATS_DATE)) + INTERVAL '1 hour',
            INTERVAL '15 minutes'
        ) AS interval_start
),
-- 提取连续状态的起止时间
state_changes AS (
    SELECT 
        STATS_DATE AS start_time,
        COALESCE(LEAD(STATS_DATE) OVER (ORDER BY STATS_DATE), MAX(STATS_DATE) OVER () + INTERVAL '15 minutes') AS end_time,
        status
    FROM your_device_data
)
-- 计算每个区间的开机时长
SELECT 
    ti.interval_start,
    SUM(
        GREATEST(
            0,
            EXTRACT(EPOCH FROM (LEAST(sc.end_time, ti.interval_start + INTERVAL '15 minutes') - GREATEST(sc.start_time, ti.interval_start))) / 60
        )
    )::INT AS total_on_minutes
FROM time_intervals ti
LEFT JOIN state_changes sc 
    ON sc.status = 1
    AND sc.start_time < ti.interval_start + INTERVAL '15 minutes'
    AND sc.end_time > ti.interval_start
GROUP BY ti.interval_start
ORDER BY ti.interval_start;

注意事项 #

• 确保STATS_DATE字段为带时区的时间类型，避免跨时区计算偏差。
• 若数据中存在连续多条相同状态的记录，可先去重，只保留状态变化的节点，减少计算量。

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