Введение

В одной телеком‑компании, где я работал, возникла проблема класса «тихих» деградаций: абоненты массово жаловались, что при отличном уровне сигнала невозможно совершить или принять голосовой вызов.

Вендорские системы мониторинга при этом не фиксировали аварий, а оборудование не сигнализировало о сбоях. С точки зрения всех стандартных метрик сектор выглядел полностью работоспособным.

Однако в реальности на отдельных секторах базовых станций голосовой трафик внезапно исчезал, тогда как сам сектор продолжал оставаться «зеленым» во всех системах наблюдения.

Проблема обнаруживалась только постфактум, по обращениям пользователей. Это означало, что часть деградаций могла оставаться незамеченной в течение длительного времени.

Так сформировалась задача: автоматически выявлять подобные скрытые аномалии на десятках тысяч объектов, используя только данные временных рядов, уже хранящиеся в ClickHouse без вынесения вычислений во внешние системы.

Исходные условия системы:

• более 50 000 объектов мониторинга;

• дискретность измерений — 15 минут;

• существенно различающиеся профили нагрузки между объектами.

В таких условиях ручной анализ невозможен, а использование фиксированных порогов приводило бы к пропуску инцидентов, либо к большому количеству ложных срабатываний.

В статье я покажу, как удалось решить эту задачу средствами SQL, используя исторические профили нагрузки, медианные бейслайны и классический паттерн Islands & Gaps.

Почему нельзя просто искать нулевой трафик

На первый взгляд задача выглядит тривиально: если проблема проявляется как отсутствие трафика, можно детектировать интервалы, в которых метрика равна нулю.

Если бы профиль нагрузки всегда был стабильным и непрерывным, задача действительно решалась бы элементарно.

Но на практике это предположение оказывается неверным: для большинства объектов нулевой трафик — часть абсолютно нормального поведения.

Вот три классических примера из телеком реальности:

1. Ночной провал. Даже на городских секторах в ночные часы трафик может естественно падать до нуля.

2. Бизнес‑центры (Indoor‑покрытие). Активность есть только в рабочее время. По ночам, в выходные и праздники трафик может отсутствовать часами или даже сутками. И это норма.

3. Автомобильные трассы. Объекты вдоль дорог или в малонаселенных районах характеризуются нерегулярным, «рваным» трафиком с длительными периодами полного затишья.

Таким образом, правило вида «если трафик отсутствует N интервалов подряд — поднять аварию» неизбежно будет ошибаться, либо требуемое значение N окажется слишком большим для своевременного выявления проблем.

Единый статический порог для всей сети неприменим. Каждый сектор должен сравниваться не с глобальной константой, а со своим собственным историческим поведением. Именно эта идея легла в основу нашего алгоритма, который мы реализовали внутри ClickHouse.

Исторический профиль как эталон нормального поведения

Трафик в телеком‑сети обладает выраженной суточной и недельной сезонностью, поэтому поведение сектора является периодическим с недельным циклом.

Для каждого сектора строился собственный исторический профиль с учетом дня недели и времени суток. При дискретности 15 минут один недельный цикл разбивается на 672 характерные точки.

4 интервала в час × 24 часа × 7 дней недели = 672 временные точки

После построения профиля задача сводится к сравнению ожидаемого и фактического поведения.

Интервал считается потенциально аномальным, если выполняются оба условия:

1. по историческому профилю ожидается ненулевой трафик;

2. фактический трафик равен нулю.

На рисунке ниже показан принцип такого сравнения. Нулевой трафик в субботу утром является нормальным поведением объекта, тогда как аналогичное значение в понедельник днём выглядит аномалией.

Однако единичный аномальный интервал еще не означает проблему. В реальных данных встречаются отдельные выбросы, а для малонагруженных объектов подобный подход привел бы к большому количеству ложных срабатываний.

Кроме того, использование только предыдущей недели в качестве эталона поведения недостаточно надежно: в ней могут присутствовать праздники, аномалии или разовые события, искажающие профиль.

Поэтому в качестве базового значения использовалась медиана по историческому окну в интервале от 10 до 2 недель назад, что снижает влияние выбросов и краткосрочных аномалий.

Последние две недели намеренно исключались из расчета, чтобы уменьшить влияние «заражения бейслайна», ситуации, когда длительная авария начинает восприниматься как нормальное поведение. Именно на этих двух неделях впоследствии выполнялся поиск аномалий.

Такой подход имеет свои недостатки. Например, при постепенном изменении профиля нагрузки медиана адаптируется не сразу. Однако на практике это не стало проблемой: мы фокусируемся на сценарии полного исчезновения трафика.

Важно, что исторический профиль позволяет не только фиксировать факт аномалии, но и измерять ее масштаб. Для каждого интервала рассчитывалось отклонение фактического трафика от ожидаемого значения. Затем отклонения суммировались по всему периоду инцидента. Это позволяет приоритизировать инциденты: объекты с максимальным накопленным отклонением требуют первоочередного внимания. Например, два часа нулевого трафика на загруженном городском секторе являются значительно более серьезным инцидентом, чем те же два часа на малонагруженном объекте.

В результате критерий аварии определяется не только длительностью периода нулевого трафика, но и суммарным отклонением от ожидаемого профиля.

Подготовка данных и структура хранения в ClickHouse

Чтобы последующие аналитические запросы выполнялись эффективно, необходимо корректно спроектировать таблицы в ClickHouse.

В реальной продакшн‑системе мы отслеживали десятки KPI, но для демонстрации работы алгоритма нам вполне хватит трех базовых полей:

• DATETIME_ID временная метка измерения;

• CELL_NAME уникальный идентификатор сектора базовой станции;

• TRAFFIC значение трафика.

Для экспериментов создадим отдельную базу данных:

CREATE DATABASE IF NOT EXISTS test;

Чтобы не тратить процессорное время на вычисление календарных атрибутов при каждом аналитическом запросе, вынесем день недели DAY_OF_WEEK и номер 15-минутного интервала SLOT_15M в MATERIALIZED колонки. Они рассчитываются автоматически один раз в момент вставки данных с помощью встроенных функций ClickHouse.

Вот DDL основной таблицы с сырыми метриками:

CREATE TABLE test.raw_data
(
    DATETIME_ID DateTime CODEC(DoubleDelta, LZ4),
    CELL_NAME String CODEC(ZSTD(3)),
    TRAFFIC Float32 CODEC(Gorilla(4), LZ4),   
    
    DAY_OF_WEEK UInt8 MATERIALIZED toDayOfWeek(DATETIME_ID) CODEC(T64, LZ4),
    SLOT_15M UInt16 MATERIALIZED (toHour(DATETIME_ID) * 4) + intDiv(toMinute(DATETIME_ID), 15) CODEC(T64, LZ4)
) ENGINE = MergeTree()
PARTITION BY toYearWeek(DATETIME_ID, 1)
ORDER BY (CELL_NAME, DATETIME_ID);

Для проверки алгоритма нам понадобятся синтетические данные. Сгенерируем профили нагрузки для разных типов объектов (город, бизнес‑центр, трасса), которые мы обсуждали в предыдущих разделах. На этом этапе данные создаются очищенными — без аномалий и падений трафика.

INSERT INTO test.raw_data
WITH
    -- 10 недель * 7 дней * 24 часа * 4 интервала в часе = 6720 интервалов
    time_grid AS (
        SELECT toDateTime('2026-03-23 00:00:00') + (number * 900) AS dt
        FROM numbers(6720)
    ),
    -- Кросс-джойн времени и 9 демонстрационных секторов
    cross_join AS (
        SELECT dt, CELL_NAME
        FROM time_grid
        CROSS JOIN (
            SELECT arrayJoin([
                'XXXX1', 'XXXX2', 'XXXX3', 
                'YYYY1', 'YYYY2', 'YYYY3', 
                'ZZZZ1', 'ZZZZ2', 'ZZZZ3'
            ]) AS CELL_NAME
        ) cj
    ),
    profiles AS (
        SELECT 
            dt AS DATETIME_ID,
            CELL_NAME AS CELL_NAME,
            toDayOfWeek(dt) AS dow,
            toHour(dt) AS hr,
            toYearWeek(dt, 1) AS yw,
            (intHash32(toUInt32(dt) + cityHash64(CELL_NAME)) % 100) / 100.0 AS noise,            
            50.0 + 40.0 * sin((hr - 6) * 3.14159 / 12.0) AS base_wave,

            CASE
                -- Профиль 1: Городские станции
                WHEN CELL_NAME IN ('XXXX1', 'YYYY1', 'YYYY2', 'ZZZZ1') THEN 
                    CASE
                        WHEN hr >= 1 AND hr <= 5 THEN 0.0
                        WHEN dow >= 6 THEN (base_wave * 0.7) + (noise * 10.0)
                        ELSE base_wave + (noise * 20.0)
                    END
                
                -- Профиль 2: Бизнес-центр
                WHEN CELL_NAME IN ('XXXX2', 'ZZZZ2') THEN 
                    CASE 
                        WHEN hr >= 20 OR hr <= 7 OR dow >= 6 THEN 0.0
                        ELSE (base_wave * 0.4) + (noise * 15.0)
                    END
                
                -- Профиль 3: Трасса
                WHEN CELL_NAME IN ('XXXX3', 'YYYY3', 'ZZZZ3') THEN 
                    CASE 
                        WHEN hr IN (12, 15, 17, 18, 21) THEN (randUniform(0, 1) < 0.25) * ((base_wave * 0.1) + (noise * 5.0))
                        ELSE 0.0
                    END
                ELSE 0.0
            END AS raw_traffic

        FROM cross_join
    )
SELECT 
    DATETIME_ID,
    CELL_NAME,
    if(raw_traffic< 0, 0, raw_traffic) AS TRAFFIC
FROM profiles;

Рассчитывать медианные значения «на лету» при каждом поиске аномалий — непозволительная роскошь для системы с 50 000+ объектов. Поэтому будем материализовать исторический профиль в отдельной таблице и обновлять его раз в сутки. Подходящий инструмент для этого — Refreshable Materialized Views.

Создаем таблицу‑таргет для хранения профилей и саму материализованную вьюху с автообновлением:

CREATE TABLE test.baselines_15m	
(
    CELL_NAME String CODEC(ZSTD(3)),
    DAY_OF_WEEK UInt8 CODEC(T64, LZ4), 
    SLOT_15M UInt16 CODEC(T64, LZ4),
    TRAFFIC Float32 CODEC(Gorilla(4), LZ4)
) ENGINE = MergeTree()
ORDER BY (CELL_NAME, DAY_OF_WEEK, SLOT_15M);

CREATE MATERIALIZED VIEW test.mv_baselines_15m
REFRESH EVERY 1 DAY OFFSET 0 SECOND
TO test.baselines_15m
AS 
SELECT 
    CELL_NAME, 
    DAY_OF_WEEK, 
    SLOT_15M, 
    quantile(0.5)(TRAFFIC) AS TRAFFIC
FROM test.raw_data

WHERE 
    (DATETIME_ID >= (today() - toIntervalWeek(10))) 
    AND (DATETIME_ID < (today() - toIntervalWeek(2)))

GROUP BY CELL_NAME, DAY_OF_WEEK, SLOT_15M;

Подбор порогов детекции

Когда исторический профиль готов, необходимо определить критерии аварии: сколько подряд аномальных интервалов должна зафиксировать система и какое минимальное отклонение нужно накопить для триггера.

Заложим следующую логику:

• скорость реакции: чем больше трафика теряет сеть, тем быстрее должна подняться авария;

• защита от ложных срабатываний: на малонагруженных объектах системе требуется больше интервалов для подтверждения аномалии.

Чтобы не плодить громоздкие ветвления CASE WHEN под разные профили нагрузки, применим линейную интерполяцию порогов между накопленным отклонением и временем детекции.

Базовое условие аварии — это накопление не менее 20 последовательных интервалов с нулевым трафиком при минимальном суммарном отклонении в размере 60 условных единиц.

Однако для высоконагруженных секторов введем механизм ускоренного выявления проблемы, чтобы не ждать 20 интервалов.

• На тестовом датасете медианный трафик за 15 минут в периоды активности равен 31 условной единице.

• Если затронут высоконагруженный сектор, мы хотим поймать аварию уже через 4 интервала (1 час). За это время накопленная потеря составит 4 × 31 = 124 единицы. Это наша стартовая точка ускоренного реагирования: (4 интервала, 124 ед. отклонения).

• По мере увеличения длительности простоя требования к накопленному отклонению линейно снижаются вплоть до базового условия (20 интервалов, 60 ед. отклонения).

• Базовое условие аварии с отклонением в 60 условных единиц сохраняется на всем двухнедельном окне анализа (1344 интервала).

На практике координаты этих точек подбираются эмпирически через итерационные тесты на исторических данных: выставляются стартовые параметры, запускается расчет, и если на выходе получается лавина ложных алармов, пороги сдвигаются. Итерации повторяются, пока доля ложноположительных сигналов не снизится до приемлемого уровня.

Реализация алгоритма детекции в SQL

Поиск непрерывных аварийных периодов реализуем внутри ClickHouse с помощью классического аналитического паттерна Islands & Gaps. Наша задача — склеить последовательные аномальные 15-минутные интервалы в единые инциденты («островки»), посчитать для каждого длительность и накопленное отклонение, а затем отфильтровать по графику интерполяции.

Весь пайплайн обработки данных внутри итогового SQL‑запроса разобьем на 6 последовательных шагов:

1. константы — фиксация пороговых значений для детекции.

2. current — фильтрация сырых метрик за анализируемое окно.

3. joined — джойн с историческим профилем.

4. flagged — разметка признаков (флагов).

5. islands — группировка последовательностей через разность оконных функций.

6. финальный SELECT — расчет метрик инцидента и применение формулы интерполяции.

Разберем процесс шаг за шагом.

Шаг 1: Пороговые значения (Константы)

Для удобства вынесем пороговые значения графика интерполяции в константы.

WITH 
    124.0   AS max_sum_dev_threshold,        -- отклонение, при котором аномалия выявляется быстро
    60.0    AS min_sum_dev_threshold,        -- минимальное отклонение
    4.0     AS min_intervals_for_max_dev,    -- минимальное количество интервалов для выявления при max отклонении
    20.0    AS min_intervals_for_min_dev     -- минимальное количество интервалов для выявления при min отклонении

Шаг 2: Сырые данные (current)

Достанем сырые значения за последние 2 недели для анализа. Чтобы в финале отделить актуальные аварии от архивных, в этом же блоке вычислим максимальную временную метку измерений по каждому сектору.

,current AS (
    SELECT 
        DATETIME_ID,
        CELL_NAME,
        TRAFFIC,
        DAY_OF_WEEK,
        SLOT_15M,
        MAX(DATETIME_ID) OVER (PARTITION BY CELL_NAME) AS LAST_DT
    FROM test.raw_data
    WHERE DATETIME_ID >= today() - INTERVAL 14 DAY
)

Шаг 3: Обогащение историческим профилем (joined)

Сджойним сырые метрики с таблицей медиан.

,joined AS (
    SELECT 
        c.*,
        b.TRAFFIC AS MEDIAN_TRAFFIC 
    FROM current AS c
    LEFT JOIN test.baselines_15m AS b 
        USING (CELL_NAME, DAY_OF_WEEK, SLOT_15M)
)

Шаг 4: Разметка флагами (flagged)

Теперь нам нужно разметить данные. Важный нюанс: мы ищем последовательные интервалы деградации, но обычный ночной нуль трафика (который совпадает с нулем по историческому профилю) не должен разрывать текущую цепочку интервалов. Прерывать серию должен только положительный трафик.

Для этого введем два независимых флага:

• IS_ANOMALY — выставляем в 1, если фактический трафик равен нулю, хотя по профилю ожидалось значение больше нуля;

• IS_SUSPECTED — выставляем в 1 при любом нулевом трафике (как аномальном, так и плановом ночном). Именно по этому флагу мы и будем сшивать непрерывные «островки» тишины.

,flagged AS (
    SELECT *,
           (TRAFFIC = 0 AND MEDIAN_TRAFFIC > 0) AS IS_ANOMALY,
           (TRAFFIC = 0) AS IS_SUSPECTED
    FROM joined
)

Шаг 5: Склеивание островков через ROW_NUMBER (islands)

Переходим к магии паттерна Islands & Gaps. Чтобы объединить идущие подряд нули трафика в единую группу, воспользуемся классическим трюком — разностью двух оконных функций ROW_NUMBER:

1. RN₁ — сквозной порядковый номер строки для каждого сектора;

2. RN₂ — порядковый номер строки в разрезе сектора и флага IS_SUSPECTED.

,islands AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY CELL_NAME ORDER BY DATETIME_ID) -
           ROW_NUMBER() OVER (PARTITION BY CELL_NAME, IS_SUSPECTED ORDER BY DATETIME_ID) AS ISLAND_ID
    FROM flagged
)

Шаг 6: Агрегация и финальная фильтрация (Финальный SELECT)

Теперь, когда каждый интервал привязан к своему «островку», нам остается сгруппировать данные по секторам и группам, рассчитать общую длительность простоя, накопить суммарное отклонение от медианы и применить формулу линейной интерполяции.

SELECT 
       CELL_NAME,
       min(DATETIME_ID) AS START_TIME,
       max(DATETIME_ID) AS END_TIME,
       sum(IS_ANOMALY) AS ANOMALY_INTERVALS,
       sum(IS_SUSPECTED) AS TOTAL_INTERVALS_15M,
       sumIf(MEDIAN_TRAFFIC - TRAFFIC, IS_ANOMALY) AS SUM_DEVIATION,
       max(DATETIME_ID) = max(LAST_DT) AS IS_RELEVANT
FROM islands
WHERE IS_SUSPECTED = 1
GROUP BY CELL_NAME, ISLAND_ID
HAVING 
    SUM_DEVIATION >= min_sum_dev_threshold
    AND ANOMALY_INTERVALS >= 
       CASE 
           WHEN SUM_DEVIATION >= max_sum_dev_threshold THEN min_intervals_for_max_dev
           ELSE min_intervals_for_min_dev - 
                (min_intervals_for_min_dev - min_intervals_for_max_dev) 
                * (SUM_DEVIATION - min_sum_dev_threshold) / (max_sum_dev_threshold - min_sum_dev_threshold)
       END

Разберем логику работы финального шага:

• Фильтрация в WHERE: На этапе формирования островков оставим только подозрительные интервалы (IS_SUSPECTED = 1), внутри которых могут находиться аномалии.

• Адаптивные пороги в HAVING: В этом блоке реализуем динамические пороги. При сильном отклонении трафика (max_sum_dev_threshold) для генерации алерта достаточно собрать нижнюю планку в min_intervals_for_max_dev (4 аномальных интервала). При минимально допустимом отклонении (min_sum_dev_threshold) требования жестче: нужно накопить минимум min_intervals_for_min_dev (20 аномальных интервалов). Между этими точками строится линейная интерполяция, которая плавно изменяет планку детекции.

Что означают поля на выходе:

• START_TIME и END_TIME — задают границы инцидента.

• SUM_DEVIATION показывает масштаб бизнес‑потерь (насколько фактический трафик отклоняется от исторического профиля). При этом учитываются только интервалы, где зафиксировано аномальное отклонение (IS_ANOMALY = 1).

• ANOMALY_INTERVALS — подсчитывает «чистое» количество 15-минутных интервалов с аномалиями внутри островка.

• TOTAL_INTERVALS_15M — подсчитывает «грязное» количество 15-минутных интервалов во всей длительности инцидента (включая допустимые ночные нули, которые попали внутрь последовательности нулей).

• IS_RELEVANT — важнейший флаг, который позволяет отделить активные инциденты от архивных. Если островок заканчивается на последнем доступном измерении для сектора (LAST_DT), значит проблема наблюдается прямо сейчас. Если нет — это уже завершившееся в прошлом событие, которое отправляется в архив для последующего анализа.

Заключение и результаты внедрения решения

Масштабируемость и производительность

Чтобы проверить эффективность алгоритма на масштабе реальной сети, было проведено нагрузочное тестирование на наборе из 336 миллионов строк (50 000 объектов мониторинга с глубиной истории в 10 недель). Замеры проводились на обычном домашнем ПК (локальный однонодовый ClickHouse).

• Расчет исторического профиля: Аналитический запрос для вычисления медиан по 8-недельному историческому окну обрабатывает 269.27 млн строк (8.07 GB) за 52.65 секунды.

• Поиск аномалий (Islands & Gaps): Итоговый запрос, который сканирует последнее двухнедельное окно по всем 50 000 объектам, делает джойн и оконные функции для разметки островков, обрабатывает 67.22 млн строк (2.02 GB) за 9.48 секунд.

Чего удалось достичь на практике:

• Снижение MTTD в 28 раз. Время выявления инцидентов сократилось до нескольких часов без ожидания массовых обращений абонентов.

• Более 160 инцидентов за год. Алгоритм стабильно отлавливает реальные локальные деградации на сети, которые оставались «зелеными» для стандартных вендорских систем.

Нюансы не раскрытые в статье:

• Исключение ложных триггеров по авариям. На реальной сети из анализа дополнительно исключались объекты, на которых уже зафиксированы аппаратные проблемы. Для этого при поиске аномалий достаточно наложить фильтр по эксплуатационным KPI доступности оборудования. Чтобы не перегружать статью, в примерах кода этот шаг опущен.

• Автоматизация процессов. Внедрение алгоритма — лишь первый шаг. В дальнейшем для всех подтвержденных инцидентов с флагом IS_RELEVANT = 1 автоматически создавались тикеты в системе инцидент‑менеджмента с логикой дедупликации и подавления, но интеграция — это уже совсем другая история.

• Проблема «пустых» медиан. Для очень низкотрафиковых секторов (например, глухие проселочные дороги) может возникнуть ситуация, когда исторический профиль полностью состоит из нулей. На таких объектах искать аномалии по 15-минутным интервалам бессмысленно. В качестве развития алгоритма для них эффективнее внедрить сравнение не по 15-минутным слотам, а по агрегированным суточным значениям трафика.

Посмотреть на работу алгоритма в динамике можно в короткой видео‑демонстрации на YouTube. Для сгенерированных синтетических данных добавляются непрерывные серии нулей, алгоритм ловит такие деградации.