Firebird与LibreOffice Base中倒数第三个事件查询结果异常排查

问题背景 #

我正在研究Firebird数据库，使用LibreOffice Base工具优化SQL查询。已创建名为Data Entry的表，结构及数据如下：

CREATE TABLE "Data Entry"(
  ID int,
  Date date,
  "Vehicle Type" varchar,
  events int,
  "Hours 1" int,
  "Hours 2" int
);

INSERT INTO "Data Entry" VALUES
(1, '31/12/22', 'A', '1', '0', '1'),
(2, '31/12/22', 'A', '1', '0', '1'),
(3, '29/12/22', 'A', '3', '0', '1'),
(4, '25/06/22', 'B1', '1', '0', '1'),
(5, '24/06/22' , 'B1', '1', '1', '0'),
(6, '24/06/22' , 'B1', '1', '1', '0'),
(7, '31/12/22' , 'B2', '7', '0', '1'),
(8, '29/12/22' , 'C', '1', '0', '1'),
(9, '29/12/22' , 'C', '2', '0', '1'),
(10, '19/01/22' , 'D1', '5', '1', '0'),
(11, '23/01/22' , 'D2', '6', '1', '1'),
(12, '29/07/19' , 'D3', '5', '0', '1'),
(13, '21/12/22' , 'D4', '1', '0', '1'),
(14, '19/12/22' , 'D4', '1', '1', '1'),
(15, '19/12/22' , 'D4', '1', '0', '1'),
(16, '28/12/22' , 'E', '2', '0', '1'),
(17, '24/12/22' , 'E', '3', '0', '1'),
(18, '14/07/07' , '1', '0', '0', '1'),
(19, '22/12/22' , '2', '1', '0', '1');

原有正确查询（仅获取倒数第三个事件间隔天数） #

之前编写的SQL可正确获取各Vehicle Type距离倒数第三个事件的天数，语句及输出如下：

SELECT
    "Vehicle Type",
    DATEDIFF(DAY, "Date", CURRENT_DATE) AS "Days Since 3rd Last Event"
FROM
    (
        SELECT
            "Date",
            "Events",
            "Vehicle Type",
            "Event Count",
            ROW_NUMBER() OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "rn"
        FROM
            (
                SELECT
                    "Date",
                    "Events",
                    "Vehicle Type",
                    SUM("Events") OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "Event Count"
                FROM "Data Entry"
            )
        WHERE "Event Count" >= 3
    )
WHERE "rn" = 1

查询结果 #

当前问题查询（组合统计总时长与间隔天数） #

现在需要同时统计各Vehicle Type的Total Hours和距离倒数第三个事件的天数，编写的新SQL如下，但部分Vehicle Type的Days Since 3rd Last Event字段未正确显示数值：

SELECT
    "Vehicle Type",
    SUM("Hours 1" + "Hours 2") AS "Total Hours",
    MAX(CASE
            WHEN
                "Total Events" = 3
            THEN
                DATEDIFF(DAY, "Date", CURRENT_DATE)
        END
    ) "Days Since 3rd Last Event"
FROM
    (
        SELECT
            "Vehicle Type",
            "Date",
            "Hours 1",
            "Hours 2",
            CASE
                WHEN
                    "Events" > 0
                THEN
                    SUM(    "Events")
                OVER(
                    PARTITION BY "Vehicle Type"
                    ORDER BY "Date" DESC
                )
            END
            "Total Events"
        FROM
            "Data Entry"
    )
GROUP BY "Vehicle Type"
ORDER BY "Vehicle Type"

问题分析 #

1. 原查询通过SUM("Events") OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC)按日期倒序累加事件数，筛选Event Count >=3后取第一条记录，这是正确匹配“倒数第三个事件”的逻辑（累加值首次达到≥3时的对应日期）。
2. 新查询的逻辑缺陷：
   • 用CASE WHEN "Events">0 THEN SUM(...) OVER(...) END计算Total Events，导致Events=0的记录对应值为NULL，干扰后续条件匹配。
   • 直接判断Total Events=3，无法覆盖累加值跳过3的情况（比如车辆B2的单条记录Events=7，累加值直接到7，永远不会等于3），因此无法正确获取对应日期。

修正后的SQL #

结合原查询的正确逻辑，通过LEFT JOIN将总时长统计与倒数第三个事件的计算结果关联，确保所有Vehicle Type都能显示总时长，同时匹配正确的间隔天数：

SELECT
    de."Vehicle Type",
    SUM(de."Hours 1" + de."Hours 2") AS "Total Hours",
    third_last."Days Since 3rd Last Event"
FROM "Data Entry" de
LEFT JOIN (
    -- 保留原有的倒数第三个事件查询逻辑
    SELECT
        "Vehicle Type",
        DATEDIFF(DAY, "Date", CURRENT_DATE) AS "Days Since 3rd Last Event"
    FROM
        (
            SELECT
                "Date",
                "Events",
                "Vehicle Type",
                "Event Count",
                ROW_NUMBER() OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "rn"
            FROM
                (
                    SELECT
                        "Date",
                        "Events",
                        "Vehicle Type",
                        SUM("Events") OVER (PARTITION BY "Vehicle Type" ORDER BY "Date" DESC) AS "Event Count"
                    FROM "Data Entry"
                ) sub1
            WHERE "Event Count" >= 3
        ) sub2
    WHERE "rn" = 1
) third_last ON de."Vehicle Type" = third_last."Vehicle Type"
GROUP BY de."Vehicle Type", third_last."Days Since 3rd Last Event"
ORDER BY de."Vehicle Type"

说明 #

• 对于事件总数不足3的Vehicle Type（比如示例中的'1'和'2'），Days Since 3rd Last Event会显示NULL，符合实际数据情况。
• 保留原查询中累加事件数、筛选首次达到≥3的记录逻辑，确保间隔天数计算准确。
• 通过LEFT JOIN保证所有Vehicle Type的总时长都能被统计到，不会遗漏数据。

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