Информатика · объяснение и практика

Серии последовательных номеров: значение минус ROW_NUMBER

Серии последовательных целых чисел в SQLite: вычитаем ROW_NUMBER, группируем одинаковую разность и проверяем уникальность входа.

Синие фигурные скобки держат между собой оранжевый квадрат.

В теме Таблицы, базы данных и SQL · Учебная карта

В маршруте Запросы, ограничения и устойчивое чтение · 10 тем

Главная мысль

Для упорядоченного набора разных целых чисел разность n − ROW_NUMBER() остаётся постоянной внутри серии с шагом один. При разрыве она меняется. Этот приём позволяет получить начало, конец и длину каждой непрерывной серии, а не просто пронумеровать строки.

Подготовьте именно множество целых значений

В собственном примере получены номера наблюдений 8, 9, 10, 14, 15 и 18. Требуется найти максимальные серии, где соседние числа различаются на единицу. Это задача о последовательности значений, а не о равных оценках, последней записи или физическом порядке хранения строк.

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

Вычтите номер и найдите группы

Нумеруем числа по возрастанию: номера строк 1, 2, 3, 4, 5, 6. Разности значения и номера равны 7, 7, 7, 10, 10, 12. Одинаковая разность выделяет три серии: 8–10, 14–15 и одиночное 18. Внутри непрерывного участка обе величины растут на единицу, поэтому их разность сохраняется.

В SQLite расчёт можно разделить на подзапросы: WITH numbered AS (SELECT n, n-ROW_NUMBER() OVER (ORDER BY n) AS g FROM (SELECT DISTINCT n FROM samples WHERE n IS NOT NULL)) SELECT MIN(n), MAX(n), COUNT(*) FROM numbered GROUP BY g ORDER BY MIN(n). Внутренний шаг вычисляет ключ, внешний собирает серию. Имя samples обозначает отдельную учебную таблицу.

Пример

Для [8,9,10,14,15,18] получены строки (8,10,3), (14,15,2), (18,18,1). Что означает тройка в первой строке?

Это число разных значений в серии 8,9,10. Конец минус начало плюс один тоже равен трём при выполненных условиях целочисленности и непрерывности.

Сохраните границы независимых последовательностей

Если номера принадлежат нескольким приборам, серии нужно искать отдельно для каждого прибора. Нумерация требует PARTITION BY device_id, а итоговая группировка — и device_id, и вычисленный ключ. Совпавшая разность у разных приборов не должна склеивать их наблюдения.

Пустой вход даст отсутствие серий, одно значение — одиночную серию. Внешний ORDER BY задаёт порядок выдачи результатов независимо от нумерации внутри окна. Перед использованием проверьте повторы, разрывы и независимые группы. Приём не восстанавливает пропущенные наблюдения: он описывает, какие последовательные номера действительно присутствуют в подготовленном наборе.

От чтения к действию

А теперь — вы

В подготовленном наборе разных целых чисел [3,4,8,9,10] нумерация идёт от одного по возрастанию. Какие максимальные серии выделит разность n − ROW_NUMBER?

Частые вопросы

Почему нужно удалить повторяющиеся номера?

ROW_NUMBER увеличивается для каждой строки, а одинаковое n не увеличивается. Без удаления повторов постоянство разности уже не соответствует выбранной модели множества.

Одинаковая разность у разных приборов означает одну серию?

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

Источники и работа с материалом

Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.

Продолжить разбираться