Exasol数据库补全缺失日期并填充最近里程值的SQL问题

问题根源 #

你之前的SQL核心问题是没有生成「所有车辆+所有日期」的完整组合，左连接仅保留了日期匹配的车辆记录，缺失日期对应的car_id直接为null，导致后续LAG函数无法基于车辆分区进行有效填充。

正确SQL实现 #

WITH date_series AS (
    -- 生成最近2年的所有日期序列
    SELECT ADD_DAYS(CURRENT_DATE - INTERVAL '2' YEAR, LEVEL - 1) AS dates
    FROM DUAL
    CONNECT BY LEVEL <= DAYS_BETWEEN(CURRENT_DATE, CURRENT_DATE - INTERVAL '2' YEAR) + 1
),
unique_cars AS (
    -- 获取所有唯一车辆ID
    SELECT DISTINCT car_id FROM mileage_event
),
car_date_combo AS (
    -- 生成每个车辆对应所有日期的笛卡尔积，确保无缺失
    SELECT u.car_id, d.dates
    FROM unique_cars u
    CROSS JOIN date_series d
),
filled_data AS (
    SELECT
        c.car_id,
        c.dates,
        -- 用LAST_VALUE向前填充最近的非空里程值，IGNORE NULLS跳过空值
        LAST_VALUE(m.car_mileage IGNORE NULLS) OVER (
            PARTITION BY c.car_id 
            ORDER BY c.dates 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS car_mileage
    FROM car_date_combo c
    LEFT JOIN mileage_event m 
        ON c.car_id = m.car_id AND c.dates = m.created_at
)
SELECT * FROM filled_data ORDER BY car_id, dates;

关键说明 #

• 笛卡尔积生成完整组合：通过unique_cars和date_series的交叉连接，确保每辆车在最近2年的每一天都有一条记录，从根源避免car_id为null的情况。
• LAST_VALUE填充逻辑：使用IGNORE NULLS参数，让窗口函数自动跳过空值，取当前行之前最近的非空里程值填充，完美解决日期缺失后的里程补全需求。
• 可扩展调整：如果需要处理车辆首次记录日期之前的里程（比如默认0），可以在LAST_VALUE外层嵌套COALESCE(..., 0)。

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