Informix自连接实现开关时间戳差值计算（替代MariaDB含LIMIT子查询）

Hey there, let's fix this query issue for your Informix environment. The key challenge here is working around Informix's restriction on subqueries with LIMIT, but luckily we can use window functions (supported in 11.70) to achieve exactly what you need: pairing consecutive ON/OFF timestamps and calculating their duration.

Assumptions About Your Table Structure #

First, let's assume your table (let's call it device_status) has these columns:

• device_id: (optional) If you're tracking multiple devices
• timestamp_col: The timestamp of the status change (could be DATETIME or Unix epoch integer)
• status: The switch state, e.g., 'ON' or 'OFF'

Step 1: Filter Out Redundant Consecutive Statuses #

First, we need to eliminate consecutive rows with the same status (e.g., multiple ON entries in a row) — we only care about the points where the status changes. We'll use the LAG() window function to compare each row to the previous one.

Step 2: Pair ON/OFF Timestamps #

Once we have only status change events, we can assign row numbers to each event (per device, if applicable) and join each ON event with the immediately following OFF event.

Solution Using CTE (Recommended, if your 11.70 version supports it) #

Informix 11.70 FC2 and later supports Common Table Expressions (CTEs), which make the query cleaner:

WITH status_changes AS (
    SELECT 
        device_id,
        timestamp_col,
        status,
        -- Assign row numbers to each status change event, ordered by time
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp_col) AS event_num
    FROM (
        SELECT 
            device_id,
            timestamp_col,
            status,
            -- Get the status of the previous row to filter out duplicates
            LAG(status) OVER (PARTITION BY device_id ORDER BY timestamp_col) AS prev_status
        FROM device_status
    ) filtered
    -- Keep only rows where status changed (or the first row)
    WHERE prev_status IS NULL OR prev_status != status
)
-- Join ON events with the next OFF event
SELECT 
    sc_on.device_id,
    sc_on.timestamp_col AS on_timestamp,
    sc_off.timestamp_col AS off_timestamp,
    -- Calculate duration: adjust based on your timestamp type
    -- For DATETIME columns, direct subtraction gives an INTERVAL
    sc_off.timestamp_col - sc_on.timestamp_col AS duration_interval,
    -- To get total seconds (for DATETIME):
    EXTEND(sc_off.timestamp_col - sc_on.timestamp_col, SECOND) AS duration_seconds,
    -- For Unix epoch integers (e.g., bigint), just subtract:
    -- sc_off.timestamp_col - sc_on.timestamp_col AS duration_seconds
FROM status_changes sc_on
JOIN status_changes sc_off
    ON sc_on.device_id = sc_off.device_id
    AND sc_on.event_num + 1 = sc_off.event_num
WHERE sc_on.status = 'ON' 
  AND sc_off.status = 'OFF';

Solution Without CTE (For Older 11.70 Builds) #

If your Informix version doesn't support CTEs, we can rewrite it with nested subqueries:

SELECT 
    sc_on.device_id,
    sc_on.timestamp_col AS on_timestamp,
    sc_off.timestamp_col AS off_timestamp,
    sc_off.timestamp_col - sc_on.timestamp_col AS duration_interval,
    EXTEND(sc_off.timestamp_col - sc_on.timestamp_col, SECOND) AS duration_seconds
FROM (
    SELECT 
        device_id,
        timestamp_col,
        status,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp_col) AS event_num
    FROM (
        SELECT 
            device_id,
            timestamp_col,
            status,
            LAG(status) OVER (PARTITION BY device_id ORDER BY timestamp_col) AS prev_status
        FROM device_status
    ) filtered1
    WHERE prev_status IS NULL OR prev_status != status
) sc_on
JOIN (
    SELECT 
        device_id,
        timestamp_col,
        status,
        ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp_col) AS event_num
    FROM (
        SELECT 
            device_id,
            timestamp_col,
            status,
            LAG(status) OVER (PARTITION BY device_id ORDER BY timestamp_col) AS prev_status
        FROM device_status
    ) filtered2
    WHERE prev_status IS NULL OR prev_status != status
) sc_off
    ON sc_on.device_id = sc_off.device_id
    AND sc_on.event_num + 1 = sc_off.event_num
WHERE sc_on.status = 'ON' 
  AND sc_off.status = 'OFF';

Key Notes #

• Handling Single Devices: If you don't have a device_id (tracking only one device), remove all PARTITION BY device_id clauses from the window functions.
• Timestamp Types: Adjust the duration calculation based on whether your timestamp_col is a DATETIME type or integer epoch timestamp.
• Edge Cases: If your data ends with an ON status (no corresponding OFF), that row will be excluded from the results (which is probably what you want, since there's no end time to calculate duration).

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