如何计算每架飞机最后实际航班与下一班计划航班的间隔天数？

问题说明 #

我有两张表：ACTUAL_FLIGHTS和SCHEDULED_FLIGHTS，表结构如下：

• ACTUAL_FLIGHTS：字段包括Aircraft（飞机编号）、Type（机型）、FLIGHT_DATE（实际航班日期）
• SCHEDULED_FLIGHTS：字段包括Aircraft（飞机编号）、Type（机型）、SCHEDULE_DATE（计划航班日期）

需求是计算每架飞机的最后实际航班日期与下一班计划航班日期的天数差，结果表需要包含Aircraft和DIFF_DAYS两个字段。

我试了下面的SQL，不仅没得到预期结果，运行时还卡顿：

SELECT s.AC, 
       MIN(s.SCHEDULE_DATE) - MAX(f.FLIGHT_DATE)
FROM SCHEDULE_FLIGHT s
INNER JOIN FLIGHT_DATE f ON f.AC = s.AC
HAVING MIN(s.SCHEDULE_DATE) >= MAX(f.FLIGHT_DATE)
GROUP BY s.AC;

原查询的问题 #

1. 表名/字段名不匹配：原查询用了SCHEDULE_FLIGHT、FLIGHT_DATE和AC，但实际表名是SCHEDULED_FLIGHTS、ACTUAL_FLIGHTS，字段名是Aircraft，名称不对应会导致数据读取错误。
2. 关联逻辑错误：直接按Aircraft内联两张表会生成笛卡尔积——同一架飞机的所有实际航班和计划航班都会两两配对，数据量指数级增长，这就是卡顿的核心原因，同时计算出的日期差逻辑也不符合需求。

正确的SQL写法 #

方法1：子查询分步计算 #

先分别提取每架飞机的最后实际航班日期，再筛选出该日期之后的最早计划航班，最后计算天数差：

SELECT 
    af.Aircraft,
    -- 不同数据库的日期差函数语法不同，需按需调整
    DATEDIFF(ss.NEXT_SCHEDULED_DATE, af.LAST_ACTUAL_DATE) AS DIFF_DAYS
FROM (
    -- 子查询1：获取每架飞机的最后实际航班日期
    SELECT Aircraft, MAX(FLIGHT_DATE) AS LAST_ACTUAL_DATE
    FROM ACTUAL_FLIGHTS
    GROUP BY Aircraft
) af
JOIN (
    -- 子查询2：获取每架飞机在最后实际航班之后的最早计划航班日期
    SELECT 
        Aircraft,
        MIN(SCHEDULE_DATE) AS NEXT_SCHEDULED_DATE
    FROM SCHEDULED_FLIGHTS s
    WHERE EXISTS (
        SELECT 1 
        FROM ACTUAL_FLIGHTS a
        WHERE a.Aircraft = s.Aircraft
          AND s.SCHEDULE_DATE > a.FLIGHT_DATE
    )
    GROUP BY Aircraft
) ss ON af.Aircraft = ss.Aircraft

方法2：窗口函数优化（更高效） #

用窗口函数直接标记出符合条件的计划航班，避免笛卡尔积：

WITH LastActual AS (
    -- 标记每架飞机的最后实际航班日期
    SELECT 
        DISTINCT Aircraft,
        MAX(FLIGHT_DATE) OVER (PARTITION BY Aircraft) AS LAST_ACTUAL_DATE
    FROM ACTUAL_FLIGHTS
),
NextScheduled AS (
    -- 给每架飞机的计划航班按日期排序，筛选出在最后实际航班之后的航班
    SELECT 
        s.Aircraft,
        s.SCHEDULE_DATE,
        ROW_NUMBER() OVER (PARTITION BY s.Aircraft ORDER BY s.SCHEDULE_DATE) AS rn
    FROM SCHEDULED_FLIGHTS s
    JOIN LastActual la ON s.Aircraft = la.Aircraft
    WHERE s.SCHEDULE_DATE > la.LAST_ACTUAL_DATE
)
-- 取每架飞机排序后的第一个计划航班，计算天数差
SELECT 
    Aircraft,
    DATEDIFF(SCHEDULE_DATE, (SELECT LAST_ACTUAL_DATE FROM LastActual WHERE Aircraft = ns.Aircraft)) AS DIFF_DAYS
FROM NextScheduled ns
WHERE rn = 1

注意：不同数据库的日期差函数语法不同，比如：

• MySQL：DATEDIFF(end_date, start_date)
• Oracle：TRUNC(end_date) - TRUNC(start_date)
• SQL Server：DATEDIFF(day, start_date, end_date)
  请根据你使用的数据库调整函数。

内容的提问来源于stack exchange，提问作者Саят Оразов