PostgreSQL中如何将低于0的engn_hrs_contdwn倒计时列重置为初始值

数据老陈

2026-10-7

解决方案

完整实现代码

WITH RECURSIVE flight_ranked AS (
    -- 先对目标飞机的飞行日志按飞行时间排序，生成序号方便递归逐行处理
    SELECT
        f.fleet_id,
        (fl.date || ' ' || fl.tkof_01_time)::timestamp AS flight_time,
        fl.hobs_total,
        ROW_NUMBER() OVER (PARTITION BY f.fleet_id ORDER BY (fl.date || ' ' || fl.tkof_01_time)::timestamp) AS rn
    FROM flt_log fl
    JOIN fleet f ON fl.aircraft_id = f.fleet_id
    WHERE f.fleet_id = 2
),
recursive_calc AS (
    -- 递归起始节点：计算第一条飞行记录的剩余发动机时长
    SELECT
        fleet_id,
        flight_time,
        hobs_total,
        rn,
        1200 - hobs_total AS engn_hrs_contdwn
    FROM flight_ranked
    WHERE rn = 1
    UNION ALL
    -- 递归迭代节点：逐行计算后续所有记录的剩余时长
    SELECT
        fr.fleet_id,
        fr.flight_time,
        fr.hobs_total,
        fr.rn,
        CASE
            -- 剩余时长扣减本次飞行时长后为负，自动加1200模拟更换发动机重置阈值
            WHEN rc.engn_hrs_contdwn - fr.hobs_total < 0 THEN (rc.engn_hrs_contdwn - fr.hobs_total) + 1200
            ELSE rc.engn_hrs_contdwn - fr.hobs_total
        END AS engn_hrs_contdwn
    FROM flight_ranked fr
    JOIN recursive_calc rc ON fr.rn = rc.rn + 1 AND fr.fleet_id = rc.fleet_id
)
-- 输出最终计算结果
SELECT
    fleet_id,
    flight_time AS date,
    hobs_total,
    engn_hrs_contdwn
FROM recursive_calc
ORDER BY flight_time;