Gaps and Islands

The flag form is longer, but its rule for a new island is an ordinary CASE expression, so the rule can change while the rest of the query stays the same.

Repeated Reports

Suppose INV-7 sends its 12:30 report twice. After the INSERT below, the islands query from the subtraction form runs again unchanged and returns this:

INSERT INTO heartbeats VALUES ('INV-7', 11, '12:30');
Copy

Output:

+-------------+------------+-----------+-------+
| inverter_id | first_slot | last_slot | slots |
+-------------+------------+-----------+-------+
| INV-7       |          1 |         4 |     4 |
| INV-7       |          7 |        12 |     4 |
| INV-7       |         10 |        11 |     2 |
| INV-9       |          1 |         3 |     3 |
| INV-9       |          5 |         6 |     2 |
+-------------+------------+-----------+-------+
Copy

One run now claims slots 7 to 12, across the slot 9 outage, and a second run from 10 to 11 overlaps it. The MySQL manual gives the cause: ROW_NUMBER assigns rows with equal ORDER BY values different numbers. The two slot 11 rows take row numbers 8 and 9, so one gets key 3 and the other key 2. Slot 12 takes row number 10 and also gets key 2, the same key as slots 7 and 8.

Duplicates can also stop the query. When the slots start at 1, a repeat before the first gap pushes the row number past the slot number, so the difference falls below zero. MySQL returns the row number as an unsigned integer, and the subtraction fails with error 1690, "BIGINT UNSIGNED value is out of range".

DENSE_RANK gives equal values the same number, without gaps, so it counts distinct slots instead of rows. With DENSE_RANK and a count of distinct slots, the three islands come back:

WITH keyed AS (
  SELECT inverter_id, slot_no,
         slot_no - DENSE_RANK() OVER (
           PARTITION BY inverter_id ORDER BY slot_no
         ) AS island_key
  FROM heartbeats
)
SELECT inverter_id, MIN(slot_no) AS first_slot, MAX(slot_no) AS last_slot,
       COUNT(DISTINCT slot_no) AS slots, COUNT(*) AS reports
FROM keyed
GROUP BY inverter_id, island_key
ORDER BY inverter_id, first_slot;
Copy

Output:

+-------------+------------+-----------+-------+---------+
| inverter_id | first_slot | last_slot | slots | reports |
+-------------+------------+-----------+-------+---------+
| INV-7       |          1 |         4 |     4 |       4 |
| INV-7       |          7 |         8 |     2 |       2 |
| INV-7       |         10 |        12 |     3 |       4 |
| INV-9       |          1 |         3 |     3 |       3 |
| INV-9       |          5 |         6 |     2 |       2 |
+-------------+------------+-----------+-------+---------+
Copy

The last island of INV-7 has 3 slots but 4 reports. The flag form needs no change here, because a repeated slot has a step of 0 and does not start an island. With COUNT(DISTINCT slot_no), it returns the same islands. A third option is to remove repeats with SELECT DISTINCT before either form.

Reports at Uneven Intervals

An older inverter, INV-3, does not report in fixed slots. It reports about every 15 minutes by the clock, and its reports drift by a minute or two. Its table, old_heartbeats, stores minutes since midnight: 600, 615, 631, 645, 661, 720, 736, and 750.

The subtraction form needs values that rise by exactly 1 inside an island, and these do not. Subtracting 15 times the row number gives 600 - 15 = 585 and 615 - 30 = 585, but then 631 - 45 = 586, so one island splits into several. The flag form only needs a different test: a report more than 20 minutes after the previous one starts a new island.

WITH flagged AS (
  SELECT minute_no,
         CASE WHEN minute_no - LAG(minute_no) OVER (ORDER BY minute_no) <= 20
              THEN 0 ELSE 1 END AS is_new
  FROM old_heartbeats
), numbered AS (
  SELECT minute_no,
         SUM(is_new) OVER (
           ORDER BY minute_no
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS island_no
  FROM flagged
)
SELECT island_no, MIN(minute_no) AS first_minute, MAX(minute_no) AS last_minute,
       COUNT(*) AS reports
FROM numbered
GROUP BY island_no
ORDER BY island_no;
Copy

Output:

+-----------+--------------+-------------+---------+
| island_no | first_minute | last_minute | reports |
+-----------+--------------+-------------+---------+
|         1 |          600 |         661 |       5 |
|         2 |          720 |         750 |       3 |
+-----------+--------------+-------------+---------+
Copy

The 59-minute step from 661 to 720 is the only one above 20, so INV-3 had one outage. The 20-minute limit is a choice, not a fact in the data. A smaller limit turns every late report into an outage, and a larger one hides short outages.

Choosing a Form

• Subtraction form: shortest when values step by exactly 1. Slot numbers and day numbers fit. Its cost: a repeat or an uneven step breaks it, and the wrong result can look normal.
• Flag form: needed when the rule for a new island is not "plus 1". Drift or a time limit goes into the CASE. Its cost is a second window and a second CTE.
• Both forms: decide what a repeated value means before counting. Count distinct slots to measure time online, and count rows only when every report matters.

Practice: Find the Longest Uninterrupted Run

For each inverter in heartbeats, return its longest run of consecutive reporting slots. Return inverter_id, first_slot, last_slot, and slots, the number of distinct slots in the run. An inverter can report twice for the same slot: count that slot once, and do not let the repeat break the run. When two runs are equally long, return the one that starts first. An inverter with a single report has a run of 1 slot. Sort the result by inverter_id.