Диагноз как регулярное выражение: MATCH_RECOGNIZE в Snowflake

Диагноз — это последовательность: почему регулярные выражения побеждают агрегацию

Представьте: две собаки прошли одинаковое количество шагов за день. Одна из них хромает, но шагомер показывает идентичные цифры. Средние значения скрывают порядок событий, а именно в порядке — вся диагностическая информация. В этой статье вы узнаете, как использовать MATCH_RECOGNIZE в Snowflake для поиска клинических синдромов в потоковых данных с датчиков, превращая диагноз в регулярное выражение над строками.

Ветеринар не ставит диагноз по среднему числу. Он видит последовательность: собака идёт, останавливается, идёт снова, нюхает, крутится, не может улечься. Если усреднить эти данные, сигнал исчезнет. Именно поэтому в SQL:2016 появилась функция row pattern recognition, реализованная в Snowflake как MATCH_RECOGNIZE. Она позволяет сопоставлять паттерны с упорядоченными строками, как регулярные выражения — с текстом.

Проект TELLTAIL: 10 миллионов строк данных с собачьих ошейников

Мы взяли открытый датасет Kumpulainen, Vehkaoja et al. (2021): 45 собак, 27 пород, видеозаписи с разметкой и два инерциальных датчика (IMU) на ошейнике и на спине. 10.6 миллионов строк данных с частотой 100 Гц были загружены в Snowflake. Вместо того чтобы считать «активные минуты», мы ищем шесть клинических синдромов, каждый из которых — это последовательность поведенческих состояний.

Ключевая идея: ошейник не может отличить ходьбу от тряски головой — в обоих случаях шея активно движется. Но если на спине есть второй датчик, то при ходьбе оба датчика движутся синхронно, а при тряске — только шея. Корреляция между сигналами падает. Это видно в SQL:

-- warehouse/04_staging_dt.sql — одна функция SQL, возможная только
-- потому что в данных два датчика
SELECT
  CORR(vm_neck, vm_back) AS neck_back_corr,  -- корреляция между ошейником и спиной
  STDDEV(vm_neck) / NULLIF(STDDEV(vm_back), 0) AS neck_dominance -- доминирование шеи
FROM raw_collar_data
GROUP BY epoch_id;

Эти два показателя — фундамент для всех последующих паттернов. Если корреляция высокая — собака двигается целиком, если низкая — она трясёт головой или чешется.

Как работает MATCH_RECOGNIZE: паттерн как регулярное выражение

Синдром S2 «перемежающаяся хромота» выглядит как регулярное выражение над строками:

-- warehouse/07_syndromes.sql — каждый синдром это PATTERN и DEFINE
SELECT *
FROM epoch_data
MATCH_RECOGNIZE (
  PARTITION BY dog_id
  ORDER BY epoch_time
  MEASURES
    MATCH_NUMBER() AS match_num,
    FIRST(epoch_time) AS start_time,
    LAST(epoch_time) AS end_time
  PATTERN (stride{3,} halt+ stride2{1,3} halt2+ stride3{1,3} halt3+)
  DEFINE
    stride AS state = 'WALK',
    halt AS state = 'PAUSE',
    stride2 AS state = 'WALK',
    halt2 AS state = 'PAUSE',
    stride3 AS state = 'WALK',
    halt3 AS state = 'PAUSE'
) AS S2;

Читается это так: «собака идёт минимум 3 секунды, останавливается, идёт немного, снова останавливается, идёт немного, снова останавливается». Это и есть хромота, выраженная через последовательность. Среднее количество шагов не изменится, но частота остановок растёт — это то, что видит владелец, когда говорит «он вроде нормальный, но часто останавливается».

Другой синдром, S6 — желудочно-кишечный дискомфорт — описывается как PATTERN (probe{5,} turn{2,} probe2{5,}), где probe — это нюхание, turn — кружение, а probe2 — повторное нюхание. Нюхание — обычное поведение, но если собака нюхает, кружится и снова нюхает без завершения — это признак проблемы.

Честная оценка точности: почему 77% — это хорошо

Мы получили точность 77.46% и F1-меру 0.650 на новых собаках (dog-disjoint split). Это ниже, чем 91%, которые дают модели, обученные на отдельных собаках, но те результаты — самообман: они запоминают индивидуальные особенности. Мы специально разделили данные так, чтобы собаки из обучающей выборки не встречались в тестовой. Публикации на этом датасете показывают падение с 91% до 70-74% при переносе на новых собак. Наши 77% — выше этой планки, и мы честно печатаем это число крупно на дашборде.

Лестница состояний: как классифицировать каждую секунду

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

1. QUALITY — если данных слишком мало, помечаем как UNKNOWN.
2. GEOMETRY — по сигнатуре поворота определяем CIRCLE или PACE.
3. NECK — если меток для тряски нет, выводим SHAKE/SCRATCH эвристически.
4. CONTEXT — если собака неподвижна между периодами движения — это PAUSE.
5. MODEL — иначе используем предсказание нейросети.

Важно, что каждый эпизод имеет атрибут state_source, который показывает, какой уровень сработал. Это обеспечивает прозрачность: 3.65% всех эпох помечены как HEURISTIC, и это видно в данных и на дашборде.

Практический вывод: как применить MATCH_RECOGNIZE в своих проектах

Если вы работаете с временными рядами и ищете паттерны, а не просто агрегаты — попробуйте MATCH_RECOGNIZE в Snowflake. Начните с простого: возьмите данные о кликах пользователей или логи сервера и опишите последовательность действий, которая означает проблему. Например, «пользователь залогинился, но не совершил действие» — это тоже паттерн.

Прямо сейчас: откройте свою среду Snowflake и выполните запрос с MATCH_RECOGNIZE на небольшом наборе данных. Убедитесь, что синтаксис работает, и поэкспериментируйте с паттернами. Затем подумайте, какие последовательности в ваших данных могут быть диагностически значимыми. Это может стать вашим следующим проектом.