SQL统计机器每小时距离的跨小时时间戳计算问题

核心逻辑是通过原生SQL的时间序列生成能力，自动拆分跨小时的速度区间，不需要手动插入人工行，可适配多SQL方言，实现复杂度低。

完整实现步骤（以PostgreSQL为例） #

步骤1：生成原始速度区间 #

先用LEAD函数生成每条速度记录对应的有效时间区间：

WITH raw_intervals AS (
    SELECT
        machine,
        CAST(timestamp AS TIMESTAMP) AS start_ts,
        LEAD(CAST(timestamp AS TIMESTAMP)) OVER (PARTITION BY machine ORDER BY CAST(timestamp AS TIMESTAMP)) AS end_ts,
        speed
    FROM machine_speed_data
    WHERE speed IS NOT NULL
)

步骤2：生成区间覆盖的所有小时边界 #

对每个原始区间，生成它所跨的所有整小时时间点：

, hour_boundaries AS (
    SELECT
        ri.machine,
        ri.start_ts,
        ri.end_ts,
        ri.speed,
        -- 生成从区间开始所属小时到结束所属小时的所有整小时节点
        generate_series(
            DATE_TRUNC('hour', ri.start_ts),
            DATE_TRUNC('hour', ri.end_ts),
            INTERVAL '1 hour'
        ) AS stat_hour
    FROM raw_intervals ri
    WHERE ri.end_ts IS NOT NULL -- 过滤最后一条无结束时间的记录，如有特殊统计需求可单独处理
)

步骤3：拆分跨小时区间 #

计算每个原始区间在对应小时内的实际有效时间片段：

, split_intervals AS (
    SELECT
        machine,
        stat_hour,
        GREATEST(start_ts, stat_hour) AS split_start,
        LEAST(end_ts, stat_hour + INTERVAL '1 hour') AS split_end,
        speed
    FROM hour_boundaries
)

步骤4：按小时聚合总行驶距离 #

SELECT
    machine,
    stat_hour,
    -- 时间差转分钟数乘以速度，累加得到小时总距离
    SUM(EXTRACT(EPOCH FROM (split_end - split_start)) / 60 * speed) AS total_distance_meters
FROM split_intervals
GROUP BY machine, stat_hour
ORDER BY machine, stat_hour;

样例验证结果 #

针对给出的测试数据，计算结果如下：

完全修正了原逻辑跨小时区间统计错误的问题。

其他SQL方言适配 #

只需替换generate_series部分为对应方言的时间序列生成函数即可：

• MySQL 8.0+：用递归CTE生成小时序列
• Spark SQL：用sequence函数：sequence(DATE_TRUNC('hour', start_ts), DATE_TRUNC('hour', end_ts), INTERVAL 1 hour)
• Hive：用explode(sequence(...))组合实现

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