按组检测累计里程：用SQL替代Excel宏实现1小时超20英里检测

需求说明 #

需要从卡车行驶里程记录表中，识别出每辆卡车首次在1小时窗口内累计行驶里程超过20英里的时间点，输出对应时间、卡车编号及累计里程。现有Excel逐行扫描宏处理数千行数据时效率低下，改用SQL提升处理速度。

示例输入数据 #

Date                Truck   Dist
05/11/2023 03:12:40 A       9.6
05/11/2023 03:43:25 A       6.5
05/11/2023 04:14:24 A       5.6
05/11/2023 04:43:55 A       7.4
05/11/2023 05:14:10 A       8.7
05/11/2023 05:24:39 A       8.9
05/11/2023 06:41:40 A       12.1
05/11/2023 04:13:33 B       11.5
05/11/2023 04:53:49 B       16.6
05/11/2023 06:14:04 B       7.4
05/11/2023 06:34:19 B       9.2
05/11/2023 07:04:34 B       9.5
05/11/2023 08:24:49 B       0.8

期望输出结果 #

Date                Truck   Dist
05/11/2023 04:14:24 A       21.7
05/11/2023 05:24:39 A       25
05/11/2023 04:53:49 B       28.1
05/11/2023 07:04:34 B       26.1

现有Excel VBA宏代码 #

For i = 2 To lastrow
        t = i
        cont = Sheets("Data").Range("C" & i)
        Do While Sheets("Data").Range("B" & i) = Sheets("Data").Range("B" & i + 1) And DateDiff("s", Sheets("Data").Range("A" & i).Value, Sheets("Data").Range("A" & t).Value) <= 3600
            cont = cont + Sheets("Data").Range("C" & t)
            t = t + 1
            If cont >= 20 Then
            Sheets("Data").Range("D" & t) = cont
            i = t
            Exit Do
            End If
        Loop
Next

SQL解决方案 #

通用版（适用于所有支持SQL的数据库） #

通过自连接计算1小时窗口内的累计里程，并筛选首次达标记录：

WITH truck_sorted AS (
    -- 按卡车分组、时间排序，添加行号
    SELECT 
        Date,
        Truck,
        Dist,
        ROW_NUMBER() OVER (PARTITION BY Truck ORDER BY Date) AS rn
    FROM your_table_name
),
window_totals AS (
    -- 计算每个时间点对应的1小时窗口内的累计里程
    SELECT
        t1.Date,
        t1.Truck,
        SUM(t2.Dist) AS total_dist,
        t1.rn
    FROM truck_sorted t1
    JOIN truck_sorted t2
        ON t1.Truck = t2.Truck
        -- 确保t2的时间在t1的前1小时内
        AND t2.Date >= DATEADD(HOUR, -1, t1.Date)
        AND t2.Date <= t1.Date
    GROUP BY t1.Date, t1.Truck, t1.rn
),
trigger_candidates AS (
    SELECT
        Date,
        Truck,
        total_dist,
        -- 标记是否为首次达标：当前累计≥20，且窗口内前一个记录的累计<20
        CASE
            WHEN total_dist >= 20
                 AND COALESCE(
                     (SELECT total_dist FROM window_totals wt 
                      WHERE wt.Truck = window_totals.Truck 
                        AND wt.rn = window_totals.rn - 1
                        AND wt.Date >= DATEADD(HOUR, -1, window_totals.Date)),
                     0
                 ) < 20
            THEN 1
            ELSE 0
        END AS is_first_trigger
    FROM window_totals
)
-- 输出最终结果
SELECT
    Date,
    Truck,
    total_dist AS Dist
FROM trigger_candidates
WHERE is_first_trigger = 1;

优化版（适用于支持时间范围窗口函数的数据库：SQL Server、PostgreSQL等） #

利用窗口函数直接计算滚动累计，代码更简洁高效：

WITH rolling_totals AS (
    SELECT
        Date,
        Truck,
        SUM(Dist) OVER (
            PARTITION BY Truck
            ORDER BY Date
            RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW
        ) AS total_dist
    FROM your_table_name
),
valid_triggers AS (
    SELECT
        Date,
        Truck,
        total_dist,
        -- 筛选首次达标的记录
        LAG(total_dist, 1, 0) OVER (PARTITION BY Truck ORDER BY Date) AS prev_total
    FROM rolling_totals
)
SELECT
    Date,
    Truck,
    total_dist AS Dist
FROM valid_triggers
WHERE total_dist >= 20 AND prev_total < 20;

针对MySQL的适配版 #

MySQL不支持RANGE BETWEEN INTERVAL，可改用自连接方式：

WITH truck_sorted AS (
    SELECT 
        Date,
        Truck,
        Dist,
        ROW_NUMBER() OVER (PARTITION BY Truck ORDER BY Date) AS rn
    FROM your_table_name
),
window_totals AS (
    SELECT
        t1.Date,
        t1.Truck,
        SUM(t2.Dist) AS total_dist,
        t1.rn
    FROM truck_sorted t1
    JOIN truck_sorted t2
        ON t1.Truck = t2.Truck
        AND t2.Date >= DATE_SUB(t1.Date, INTERVAL 1 HOUR)
        AND t2.Date <= t1.Date
    GROUP BY t1.Date, t1.Truck, t1.rn
),
trigger_candidates AS (
    SELECT
        Date,
        Truck,
        total_dist,
        CASE
            WHEN total_dist >= 20
                 AND COALESCE(
                     (SELECT total_dist FROM window_totals wt 
                      WHERE wt.Truck = window_totals.Truck 
                        AND wt.rn = window_totals.rn - 1
                        AND wt.Date >= DATE_SUB(window_totals.Date, INTERVAL 1 HOUR)),
                     0
                 ) < 20
            THEN 1
            ELSE 0
        END AS is_first_trigger
    FROM window_totals
)
SELECT
    Date,
    Truck,
    total_dist AS Dist
FROM trigger_candidates
WHERE is_first_trigger = 1;

逻辑说明 #

1. 排序分组：先按卡车分组、时间排序，为每条记录添加行号，方便后续关联计算。
2. 窗口累计：通过自连接或窗口函数，计算每个时间点往前1小时内的累计行驶里程。
3. 筛选触发点：识别出累计里程首次超过20英里的时间点，避免重复输出同一连续窗口内的多条达标记录。

内容的提问来源于stack exchange，提问作者David Ricardo Menacho Vadillo