I see exactly where the problem is—your current query isn't partitioning the LAG window by plc_id, so when multiple PLCs send data, the query mixes events across different devices. That's why it works flawlessly for a single PLC but breaks when scaling to multiple ones.

The Root Cause #

Without partitioning by plc_id, the LAG function pulls the most recent event from all PLCs, not just the same device. You might end up pairing a downtime event from plc4 with an uptime event from plc5, leading to invalid duration calculations and messy results.

Corrected Query #

Here's the adjusted query that fixes the multi-PLC scenario:

WITH SelectPreviousEvent AS (
    SELECT *,
        -- Partition LAG by plc_id to only reference the same device's prior events
        LAG([time]) OVER (PARTITION BY [plc_id] LIMIT DURATION(hour, 24)) as previous_time,
        LAG([zero-speed]) OVER (PARTITION BY [plc_id] LIMIT DURATION(hour, 24)) as previous_speed,
        LAG([plc_id]) OVER (PARTITION BY [plc_id] LIMIT DURATION(hour,24)) as previous_plc,
        [plc_id]
    FROM [iot-input] TIMESTAMP BY [time]
)
-- Filter for transitions from downtime to uptime
SELECT
    -- Ensure start time is tied to the same PLC
    LAG(time) OVER (PARTITION BY [plc_id] LIMIT DURATION(hour, 24) WHEN previous_speed = 0 ) [started_time],
    previous_time [end_time],
    [plc_id],
    [created_by] = 'Stream Analytics',
    [updated_by] = 'Stream Analytics',
    [duration] = DATEDIFF(second, LAG(time) OVER (PARTITION BY [plc_id] LIMIT DURATION(hour, 24) WHEN previous_speed = 0 ), previous_time)
INTO [sql-output]
FROM SelectPreviousEvent
-- Add check to guarantee prior event belongs to the same PLC
WHERE [zero-speed] = 0 AND previous_speed = 1 AND [plc_id] = previous_plc

Key Changes Explained #

• PARTITION BY [plc_id] in all LAG clauses: This restricts the window function to only consider events from the same PLC, so data from different devices never gets mixed.
• Added [plc_id] = previous_plc to the WHERE clause: An extra safety net to ensure we only process event pairs that belong to the same device, even if edge cases slip past the partitioning.

How It Works #

For each individual PLC, the query will:

1. Track the previous time, speed, and device ID exclusively for that PLC
2. Detect when the device transitions from downtime (zero-speed = 1) to uptime (zero-speed = 0)
3. Calculate the exact downtime duration between the start of the downtime window and the uptime trigger
4. Write only valid, device-specific downtime records to your SQL output

This should give you the same accurate results you saw with a single PLC, but now scaled to handle multiple devices seamlessly.

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