Блог

у меня есть огромная таблица с iot-данными от множества iot-устройств. Каждое устройство отправляет данные один раз в минуту, но только в том случае, если счетчик ввода получил несколько сингалов. Если нет, то никакие данные не будут отправлены. Итак, в моей базе данных данные выглядят следующим образом

Сегодня я загружаю все эти данные в свое приложение и объединяю их, повторяя и проверяя строку за строкой до 3 строк на основе смежных строк. Смежные строки — это все строки, в которых следующая строка находится на одну минуту позже. Это работает, но это не кажется умным и приятным.

Имеет ли смысл генерировать эту агрегацию на sql Server — для повышения производительности? С чего бы вы начали?

Это классическая проблема с островами и пробелами. Я все еще осваиваю острова и пробелы, поэтому я хотел бы получить любые отзывы о моем решении от других знающих людей (пожалуйста, будьте осторожны). Существует по крайней мере несколько разных способов решения проблем и пробелов, но это тот, который мне кажется самым простым. Вот как я заставил это работать:

DDL для настройки данных:

 IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp;

CREATE TABLE #tmp
    (IoT_Device INT,
    Count INT,
    TimeStamp DATETIME);

INSERT INTO #tmp
VALUES
    (1, 5, '2021-10-27 14:03'),
    (1, 4, '2021-10-27 14:04'),
    (1, 7, '2021-10-27 14:05'),
    (1, 8, '2021-10-27 14:06'),
    (1, 5, '2021-10-27 14:07'),
    (1, 4, '2021-10-27 14:08'),
    (1, 7, '2021-10-27 14:12'),
    (1, 8, '2021-10-27 14:13'),
    (1, 5, '2021-10-27 14:14'),
    (1, 4, '2021-10-27 14:15'),
    (1, 5, '2021-10-27 14:21'),
    (1, 4, '2021-10-27 14:22'),
    (1, 7, '2021-10-27 14:23');
 

Решение для островов и пробелов:

 ;WITH CTE_TIMESTAMP_DATA AS (
    SELECT
        IoT_Device,
        Count,
        TimeStamp,
        LAG(TimeStamp) OVER 
            (PARTITION BY IoT_Device ORDER BY TimeStamp) AS previous_timestamp,
        LEAD(TimeStamp) OVER 
            (PARTITION BY IoT_Device ORDER BY TimeStamp) AS next_timestamp,
        ROW_NUMBER() OVER 
            (PARTITION BY IoT_Device ORDER BY TimeStamp) AS island_location
    FROM #tmp
)
,CTE_ISLAND_START AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY IoT_Device ORDER BY TimeStamp) AS island_number,
        IoT_Device,
        TimeStamp AS island_start_timestamp,
        island_location AS island_start_location
    FROM CTE_TIMESTAMP_DATA
    WHERE DATEDIFF(MINUTE, previous_timestamp, TimeStamp) > 1
        OR previous_timestamp IS NULL
)
,CTE_ISLAND_END AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY IoT_Device ORDER BY TimeStamp) AS island_number,
        IoT_Device,
        TimeStamp AS island_end_timestamp,
        island_location AS island_end_location
    FROM CTE_TIMESTAMP_DATA
    WHERE DATEDIFF(MINUTE, TimeStamp, next_timestamp) > 1
        OR next_timestamp IS NULL
)
SELECT
    S.IoT_Device,
    (SELECT SUM(Count)
    FROM CTE_TIMESTAMP_DATA
    WHERE IoT_Device = S.IoT_Device
    AND TimeStamp BETWEEN S.island_start_timestamp AND E.island_end_timestamp) AS Count,
    S.island_start_timestamp,
    E.island_end_timestamp
FROM CTE_ISLAND_START AS S
INNER JOIN CTE_ISLAND_END AS E
ON E.IoT_Device = S.IoT_Device
AND E.island_number = S.island_number;
 

Запрос CTE_TIMESTAMP_DATA извлекает IoT_Device, количество и временную метку вместе с меткой времени до и после каждой записи с использованием LAG и LEAD и присваивает номер строки каждой записи, упорядоченной по временной метке.

Запрос CTE_ISLAND_START получает начало каждого острова.

Запрос CTE_ISLAND_END получает конец каждого острова.

Затем основной SELECT внизу использует эти данные для суммирования количества внутри каждого острова.

Это будет работать с несколькими IoT_Devices.

Вы можете прочитать больше об островах и пробелах здесь или во многих других местах в Интернете.