Как написать SQL-запрос для поиска аномалий и дублей на большой таблице с миллионами строк?

Короткий ответ

На больших объёмах поиск дублей и аномалий стоит делать через агрегирование и оконные функции с фильтрацией по ключевым полям и временному диапазону, избегая полных самосоединений (self-join) без ограничивающих условий, а аномалии искать через сравнение с историческими статистиками по тем же разрезам (дата, сегмент).

Как это работает подробнее

GROUP BY с HAVING COUNT(*) > 1 на индексированных ключевых полях обычно эффективнее, чем JOIN таблицы самой на себя по этим же полям, так как последний может создавать квадратичный рост промежуточного результата. Для аномалий по времени удобно агрегировать метрику по дням/часам и сравнивать с историческим средним и стандартным отклонением (или процентилями) за предыдущий сопоставимый период.

Sanity check: день с cnt, отличающимся от avg_28d более чем на 3×std_28d, стоит проверить вручную на технический сбой (дубликаты событий, задержка в пайплайне) перед тем как считать его реальной аномалией поведения.

Пример кода

example.sqlКопировать

-- BigQuery: поиск дневных аномалий относительно среднего/std за последние 28 дней того же дня недели
WITH daily AS (
  SELECT DATE(event_timestamp) AS dt, COUNT(*) AS cnt
  FROM `project.dataset.events`
  WHERE DATE(event_timestamp) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 35 DAY) AND CURRENT_DATE()
  GROUP BY dt
)
SELECT dt, cnt,
       AVG(cnt) OVER (ORDER BY dt ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING) AS avg_28d,
       STDDEV(cnt) OVER (ORDER BY dt ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING) AS std_28d
FROM daily
ORDER BY dt;

Что использовать на практике

• На очень больших таблицах важно сузить диапазон сканирования через партиционирование по дате и фильтр WHERE по этому полю до агрегации, а не сканировать всю историю. В ClickHouse для подобных задач эффективны uniqExact/countIf с группировкой по партициям
• В BigQuery — разумно ограничивать объём сканируемых данных явным указанием диапазона дат, так как оплата идёт за объём прочитанных данных. Для аномалий полезно сравнивать не просто с предыдущим днём, а с распределением последних N дней того же дня недели, чтобы не путать сезонность с аномалией.

Подводные камни

• Писать SELECT * FROM t1 JOIN t1 AS t2 ON ... без ограничения диапазона дат на таблице с миллионами строк, создавая очень дорогой запрос
• Использовать COUNT(DISTINCT ...) на огромных таблицах там, где approx-функции (approx_count_distinct в BigQuery, uniq в ClickHouse) дали бы приемлемую точность намного быстрее
• Искать аномалии по абсолютному порогу без учёта естественной сезонности метрики.