Understanding the Problem: Gaps-and-Islands in Apache IoTDB

When monitoring industrial equipment, a common requirement is to detect threshold violations and group contiguous alarm samples into a single, cohesive incident ID. In SQL, this pattern is widely recognized as a Gaps-and-Islands problem.

While attempting to solve this in Apache IoTDB (version 2.0.x and higher using the relational Table Model), you might attempt to evaluate the start of a run using LAG() and calculate a running total with SUM() OVER(...) within a single projection:

SELECT 
    motor,
    time,
    temperature,
    SUM(
        CASE 
            WHEN temperature > 85 AND LAG(temperature) OVER (PARTITION BY motor ORDER BY time) <= 85 
            THEN 1 
            ELSE 0 
        END
    ) OVER (PARTITION BY motor ORDER BY time) AS incident_id 
FROM motor_samples 
ORDER BY time;

Executing this query triggers the following error:

Msg: org.apache.iotdb.jdbc.IoTDBSQLException: 701: Cannot nest window functions or row pattern measures inside window function arguments

Why Does This Error Happen?

In standard ANSI SQL—as well as Apache IoTDB's relational table engine—window functions cannot be directly nested inside other window functions within the same SELECT clause. Because LAG() is a window function, wrapping it inside SUM(...) OVER (...) creates a nested window expression, which query planners explicitly disallow.

Therefore, yes: you must split the calculation into at least two query layers when computing run-based identifiers using window functions.

The Recommended Solution: Two-Stage Query with CTE

The standard, robust, and documented method to handle this behavior is to split the query into two phases using a Common Table Expression (CTE) or subquery:

  1. Stage 1 (Marking Island Starts): Identify the boundaries of a new incident using LAG() to check if the previous record was below the threshold (or null at the start of the partition).
  2. Stage 2 (Accumulating IDs): Calculate the cumulative sum of these markers over time to create a unique, sequential incident ID.

Example SQL Implementation

If you want samples outside the incident to not carry the prior incident ID (or to cleanly separate normal state from alarm state), you can handle the assignment with CASE statements:

WITH marked_starts AS (
    SELECT 
        motor,
        time,
        temperature,
        CASE 
            WHEN temperature > 85 
                 AND COALESCE(LAG(temperature) OVER (PARTITION BY motor ORDER BY time), -1.0) <= 85 
            THEN 1 
            ELSE 0 
        END AS is_run_start
    FROM motor_samples
),
accumulated_runs AS (
    SELECT 
        motor,
        time,
        temperature,
        SUM(is_run_start) OVER (PARTITION BY motor ORDER BY time) AS run_id
    FROM marked_starts
)
SELECT 
    motor,
    time,
    temperature,
    CASE 
        WHEN temperature > 85 THEN run_id 
        ELSE NULL 
    END AS incident_id
FROM accumulated_runs
ORDER BY time;

Key Takeaways for Apache IoTDB Table Model

  • No Nested Window Functions: Window aggregations (like SUM() OVER(...)) cannot evaluate row-level analytical functions (like LAG() or LEAD()) within the same layer.
  • CTE Portability: Using a CTE is the standard, future-proof approach for gaps-and-islands queries in IoTDB 2.0.x and other distributed SQL analytical databases.
  • Handling Boundary Conditions: Always account for the first record in a partition by using COALESCE() or explicit NULL checks on the result of LAG().