MySQL计算门交替开关事件每小时总开门时长及异常处理

Got it, let's tackle this problem head-on. You need to compute the total time a door stays open each hour, and also handle messy edge cases like consecutive duplicate actions (thanks to sensor glitches) or unclosed open events (like a sensor dying mid-open). Here's a robust SQL solution tailored to your needs:

Step-by-Step SQL Query #

WITH ordered_events AS (
    SELECT 
        ID,
        door,
        Action,
        date,
        -- Grab the next event's action and timestamp for pairing
        LEAD(Action) OVER (PARTITION BY door ORDER BY date) AS next_action,
        LEAD(date) OVER (PARTITION BY door ORDER BY date) AS next_date,
        -- Check the previous event to filter duplicates
        LAG(Action) OVER (PARTITION BY door ORDER BY date) AS prev_action
    FROM events
),
valid_open_events AS (
    SELECT 
        door,
        date AS open_time,
        -- Determine the correct close time: use next Close if exists, else handle unclosed opens
        CASE 
            WHEN next_action = 'Close' THEN next_date
            -- For unclosed opens: use current time if it's the current hour, else the hour's end
            ELSE CASE 
                WHEN DATE_FORMAT(date, '%Y-%m-%d %H') = DATE_FORMAT(NOW(), '%Y-%m-%d %H') THEN NOW()
                ELSE DATE_ADD(DATE_FORMAT(date, '%Y-%m-%d %H:00:00'), INTERVAL 1 HOUR)
            END
        END AS close_time
    FROM ordered_events
    WHERE 
        Action = 'Open'
        -- Skip consecutive duplicate Open events (ignore if last action was also Open)
        AND (prev_action IS NULL OR prev_action != 'Open')
),
duration_per_open AS (
    SELECT 
        door,
        open_time,
        close_time,
        -- Calculate duration in seconds (adjust unit if needed)
        TIMESTAMPDIFF(SECOND, open_time, close_time) AS open_duration
    FROM valid_open_events
    -- Guard against invalid data where close time is earlier than open time
    WHERE close_time > open_time
)
-- Final hourly aggregation (matches your original grouping logic with 30-minute offset)
SELECT 
    door,
    DATE_FORMAT(DATE_ADD(open_time, INTERVAL 30 MINUTE), '%Y-%m-%d %H:00:00') AS hour,
    SUM(open_duration) AS total_open_seconds,
    -- Optional: Format seconds into readable HH:MM:SS
    SEC_TO_TIME(SUM(open_duration)) AS total_open_time
FROM duration_per_open
GROUP BY door, hour
ORDER BY hour;

How This Handles Edge Cases #

Let's walk through the key problem-solving parts:

• Consecutive Open events: The prev_action check skips any Open that follows another Open (since it's a glitch, we only keep the first valid Open in a sequence).
• Unclosed Open events: If an Open has no matching Close (like your example's ID 19), we calculate duration up to either:
  • The end of the hour if it's a past hour, or
  • The current time if it's the ongoing hour.
• Consecutive Close events: These are automatically ignored because we only process Open events, and a Close without a preceding Open won't be picked up in our valid Open set.
• Invalid time order: The close_time > open_time filter ensures we don't count negative durations from bad sensor data.

Customization Tips #

• Adjust grouping logic: If you don't need the 30-minute offset (your original query uses this to split hours at the 30-minute mark), replace the DATE_ADD(open_time, INTERVAL 30 MINUTE) part with just open_time in the final SELECT.
• Change duration units: Swap TIMESTAMPDIFF(SECOND, ...) for MINUTE or HOUR if you want totals in those units instead of seconds.
• Handle multi-door setups: The PARTITION BY door clause ensures calculations are per-door, so this works if you have multiple doors in your table.

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