История вопроса. Необходимость в расчетах уникальных подсчетов возникла из аналитических нагрузок, отслеживающих такие метрики, как кумулятивные уникальные приобретения клиентов или введение уникальных SKU с течением времени. До расширений функций окон ANSI SQL:2003 аналитики полагались на самосоединения или коррелированные подзапросы, что приводило к квадратичной сложности времени, неприемлемой для современных объемов данных. Стандартизация функций окон предоставила линейный, основанный на наборе механизм для поддержания текущей кардинальности без процедурных циклов.
Проблема. ANSI SQL явно запрещает использование ключевого слова DISTINCT внутри агрегатных функций окон (например, COUNT(DISTINCT col) OVER (...)). Это ограничение не позволяет напрямую вычислять уникальные значения в кумулятивной или скользящей рамке. Основная задача заключается в идентификации первого появления каждой сущности в порядке сортировки раздела и суммировании этих бинарных флагов (первое появление = 1, иначе = 0) постепенно.
Решение. Канонический подход сочетает ROW_NUMBER() для пометки первых появлений с условной функцией агрегирования SUM(). Разделив ROW_NUMBER() по идентификатору сущности, хронологически первое появление получает значение 1; последующие появления получают последовательные целые числа. Внешний запрос затем суммирует условное выражение, выдающее 1 только тогда, когда номер строки равен 1, оценивается по непрерывной предшествующей рамке.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- event_id в качестве разрешающего фактора ) AS rn FROM user_activity ) flagged;
Описание проблемы. Финансовый стартап нуждался в мониторинге соблюдения нормативных требований, отслеживая кумулятивное количество уникальных торговцев, подключенных по регионам продаж на протяжении финансового года. Их таблица merchant_signups содержала 120 миллионов строк с region_code, merchant_id и signup_timestamp. Существующие задания пакетной обработки на Python занимали 35 минут для вычисления этих метрик каждую ночь, вызывая задержки в отчетности и устаревшие данные на панелях мониторинга. Требование заключалось в получении реального времени кумулятивных подсчетов с соблюдением строгого ANSI SQL для обеспечения портативности между облачными хранилищами данных.
Решение A: Метод самосоединения. Этот метод соединяет таблицу сама с собой по совпадающим регионам и более ранним временным меткам, подсчитывая уникальных торговцев для каждой внешней строки. Плюсы: не требует поддержки функций окон и работает на движках SQL-92. Минусы: алгоритм имеет сложность O(n²); для миллионов строк это генерирует промежуточные декартовы произведения, потребляющие терабайты временного хранилища и не успевает завершиться в течение нескольких часов, что делает его операционно неприемлемым.
Решение B: Коррелированный скалярный подзапрос. Здесь оператор SELECT включает подзапрос: (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Плюсы: это декларативно и логически прозрачно для чтения. Минусы: подзапрос выполняется один раз для каждой строки (120 миллионов раз), что предотвращает применение предикатов и вызывает массовый произвольный ввод/вывод; оптимизаторы баз данных не могут декоррелировать уникальные агрегаты по различным временным диапазонам, что приводит к оценкам времени выполнения более 90 минут.
Решение C: Техника функций окон ANSI SQL. Использование ROW_NUMBER() для идентификации первых появлений, за которым следует выполняемая SUM(), как показано в приведенном выше примере. Плюсы: это выполняет одно сканирование таблицы с сортировкой, используя возможности спулинга оконного оптимизатора для сложности O(n log n) и ограниченного использования памяти. Минусы: требует внимательной обработки временных связок; если два подключения имеют идентичные временные метки, недетерминированный порядок может привести к двойному подсчету, если не добавить уникальный разрешающий фактор (например, event_id) к предложению ORDER BY.
Выбранное решение и результат. Решение C было внедрено. Включив event_id в ORDER BY для обеспечения детерминированного определения первого появления, запрос выполнялся за 4 минуты на существующем кластере — улучшение в 9 раз. Это позволило создать панели мониторинга в реальном времени для соблюдения нормативных требований, позволяя операторам по рискам контролировать разнообразие привлечения без задержек ETL, и запрос оказался полностью портативным для PostgreSQL, Snowflake и BigQuery без изменений.
Почему COUNT(DISTINCT column) OVER (ORDER BY ...) вызывает синтаксическую ошибку в строгом ANSI SQL?
Стандарт SQL явно запрещает использовать ключевое слово DISTINCT в аргументе агрегатной функции окна, такой как COUNT, SUM или AVG. Хотя некоторые поставщики (например, PostgreSQL 16+, Oracle) предлагают это как проприетарное расширение, ANSI SQL:2011 и более ранние версии ограничивают агрегаты окон работать со всеми строками в определенной рамке. Это ограничение существует, потому что поддержание хеш-таблицы уникального набора для каждого возможного оконного фрейма при потоковой оценке не является обязательным по стандартной грамматике. Кандидаты должны осознавать, что DISTINCT разрешен только в стандартных агрегатных функциях, не имеющих предложений OVER, или в инверсных функциях распределения, таких как PERCENTILE_CONT, но никогда как оконный уникальный подсчет.
Как вы обрабатываете дублирующие временные метки при определении "первого" появления сущности?
ROW_NUMBER() назначает произвольные значения среди связок, если только предложение ORDER BY не указывает полное упорядочение. Если у торговца есть две записи с идентичными временными метками, обе строки могут потенциально получить rn = 1, если порядок недетерминирован, что приведет к неправильному увеличению кумулятивного подсчета дважды. Разрешение состоит в том, чтобы добавить уникальный первичный ключ или автоинкрементный ID к предложению ORDER BY: ORDER BY signup_timestamp, merchant_signup_id. Это обеспечивает детерминированную последовательность, где ранее назначенный ID считается первым появлением, сохраняя математическую целостность кумулятивного уникального подсчета.
Можно ли адаптировать эту технику для скользящего уникального подсчета зафиксированного диапазона строк (например, последние 100 транзакций) вместо неограниченной предшествующей?
Нет, неэффективно с чистым ANSI SQL. Метод неограниченного предыдущего успешно работает, потому что уникальность монотонна — как только сущность появляется, она остается "подсчитанной" навсегда. В скользящем окне (например, ROWS BETWEEN 100 PRECEDING AND CURRENT ROW) сущность, выходящая из окна, должна уменьшить подсчет, что требует знания, представляет ли уходящая строка единственный экземпляр этой сущности в текущем фрейме. ANSI SQL не обладает операторами агрегирования массивов или разностей множеств в оконных рамках, чтобы эффективно отслеживать такие выходы. Реализация требует либо рекурсивных CTE (которые деградируют до O(n²) для этого сценария), либо проприетарных расширений, таких как ARRAY_AGG, в сочетании с операциями над множествами, которые оба нарушают строгую совместимость с ANSI.