MySQL中如何对非连续ID的连续行计算DATEDIFF时间差？

Got it, let's work through this. First, here's your raw Logs table output formatted as a clear table to visualize the expected Secs values:

The key issue here is that you need to:

1. Group records by E.ID
2. Reset the time calculation every time S.INDEX = 1 appears within an E.ID group

Using plain DATEDIFF won't work because it doesn't account for these reset points. Instead, we can use window functions to first create sub-groups within each E.ID (resetting on S.INDEX=1), then calculate the time difference from the previous record in the same sub-group.

Here's a SQL query that does this (works with SQL Server, PostgreSQL, MySQL 8.0+, and other databases supporting window functions):

WITH LogsSubGroups AS (
    SELECT
        S.ID AS S_ID,
        S.[INDEX] AS S_INDEX,
        E.ID AS E_ID,
        TIME,
        -- Create a sub-group ID that increments every time S.INDEX=1 is found
        SUM(CASE WHEN S.[INDEX] = 1 THEN 1 ELSE 0 END)
            OVER (PARTITION BY E.ID ORDER BY TIME) AS SubGroupID
    FROM Logs
),
LogsWithPreviousTime AS (
    SELECT
        *,
        -- Get the TIME value from the previous record in the same E_ID and SubGroupID
        LAG(TIME) OVER (PARTITION BY E_ID, SubGroupID ORDER BY TIME) AS PreviousTime
    FROM LogsSubGroups
)
SELECT
    S_ID,
    S_INDEX,
    E_ID,
    TIME,
    -- Calculate the seconds difference between current and previous time
    DATEDIFF(SECOND, PreviousTime, TIME) AS Secs
FROM LogsWithPreviousTime
ORDER BY E_ID, TIME;

How this works: #

1. LogsSubGroups CTE: We partition the data by E.ID and order by TIME. Using SUM() with a conditional, we generate a SubGroupID that increases by 1 every time we hit a record where S.INDEX=1. This effectively creates separate "chunks" of records for each reset point.
2. LogsWithPreviousTime CTE: The LAG() function pulls the TIME value from the immediately preceding record in the same E_ID and SubGroupID—this is what we need to calculate the time difference against.
3. Final query: We use DATEDIFF to compute the number of seconds between the current record's TIME and the PreviousTime from the sub-group.

If you're using MySQL, replace DATEDIFF(SECOND, PreviousTime, TIME) with TIMESTAMPDIFF(SECOND, PreviousTime, TIME) since MySQL's DATEDIFF only works with dates, not timestamps.

内容的提问来源于stack exchange，提问作者João Amaro