使用PostgreSQL窗口函数计算时序数据中的发动机时长

针对车辆追踪的时序数据，利用SQL窗口函数（LAG()/LEAD()）即可实现低CPU开销的指标计算，完全无需遍历数据集。以下是核心指标的具体实现：

1. 里程计算 #

假设数据表包含vehicle_id、latitude、longitude、record_time字段，通过相邻记录的地理距离累加得到总里程：

WITH segment_distances AS (
    SELECT
        vehicle_id,
        -- 使用Haversine公式计算当前记录与上一条记录的距离（单位：公里）
        6371 * 2 * ASIN(
            SQRT(
                POWER(SIN(RADIANS(latitude - LAG(latitude) OVER (PARTITION BY vehicle_id ORDER BY record_time))/2), 2) +
                COS(RADIANS(LAG(latitude) OVER (PARTITION BY vehicle_id ORDER BY record_time))) * COS(RADIANS(latitude)) *
                POWER(SIN(RADIANS(longitude - LAG(longitude) OVER (PARTITION BY vehicle_id ORDER BY record_time))/2), 2)
            )
        ) AS segment_distance
    FROM vehicle_tracking_data
)
SELECT
    vehicle_id,
    SUM(segment_distance) AS total_mileage
FROM segment_distances
WHERE segment_distance IS NOT NULL
GROUP BY vehicle_id;

逻辑说明：通过LAG()窗口函数获取同一车辆的上一条经纬度记录，计算两点地理距离后按车辆累加所有路段距离。

2. 发动机时长计算 #

假设数据表包含vehicle_id、engine_status（1=启动，0=熄火）、record_time字段，通过分组连续状态计算总运行时长：

WITH status_groups AS (
    SELECT
        vehicle_id,
        engine_status,
        record_time,
        -- 标记连续相同状态的分组：状态切换时分组ID递增
        SUM(CASE WHEN engine_status != LAG(engine_status) OVER (PARTITION BY vehicle_id ORDER BY record_time) THEN 1 ELSE 0 END) OVER (PARTITION BY vehicle_id ORDER BY record_time) AS status_group_id
    FROM vehicle_tracking_data
),
group_durations AS (
    SELECT
        vehicle_id,
        MAX(record_time) - MIN(record_time) AS group_duration
    FROM status_groups
    WHERE engine_status = 1
    GROUP BY vehicle_id, status_group_id
)
SELECT
    vehicle_id,
    SUM(group_duration) AS total_engine_running_time
FROM group_durations
GROUP BY vehicle_id;

逻辑说明：先将连续相同的发动机状态归为同一分组，再计算每个启动分组的时间跨度，最后累加得到总发动机运行时长。

3. 行驶时长计算 #

假设数据表包含vehicle_id、speed、record_time字段，按速度阈值分组计算行驶时长：

WITH driving_groups AS (
    SELECT
        vehicle_id,
        speed,
        record_time,
        -- 标记连续行驶/停车分组：速度跨越阈值时分组ID递增
        SUM(CASE WHEN (speed > 1) != (LAG(speed) OVER (PARTITION BY vehicle_id ORDER BY record_time) > 1) THEN 1 ELSE 0 END) OVER (PARTITION BY vehicle_id ORDER BY record_time) AS driving_group_id
    FROM vehicle_tracking_data
),
driving_durations AS (
    SELECT
        vehicle_id,
        MAX(record_time) - MIN(record_time) AS group_duration
    FROM driving_groups
    WHERE speed > 1
    GROUP BY vehicle_id, driving_group_id
)
SELECT
    vehicle_id,
    SUM(group_duration) AS total_driving_time
FROM driving_durations
GROUP BY vehicle_id;

逻辑说明：将连续的行驶（速度>1）状态归为同一分组，计算每个分组的时间跨度后累加，得到总行驶时长。

性能优化建议 #

• 给vehicle_id和record_time创建联合索引，窗口函数的PARTITION BY和ORDER BY会依赖该索引提升效率
• 针对实时更新的数据流，可结合数据库物化视图或流处理引擎（如Flink）实现增量计算，避免全表扫描
• 所有计算在数据库层完成，无需将全量数据拉取到应用层处理，大幅降低CPU和网络资源消耗

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