Информатика · объяснение и практика
Серии последовательных номеров: значение минус 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 задаёт порядок выдачи результатов независимо от нумерации внутри окна. Перед использованием проверьте повторы, разрывы и независимые группы. Приём не восстанавливает пропущенные наблюдения: он описывает, какие последовательные номера действительно присутствуют в подготовленном наборе.
От чтения к действию
А теперь — вы
Частые вопросы
Почему нужно удалить повторяющиеся номера?
ROW_NUMBER увеличивается для каждой строки, а одинаковое n не увеличивается. Без удаления повторов постоянство разности уже не соответствует выбранной модели множества.
Одинаковая разность у разных приборов означает одну серию?
Нет. Независимые последовательности разделяют и при оконной нумерации, и при итоговой группировке.
Источники и работа с материалом
Объяснение, примеры и вопросы написаны для erudeet. Факты и определения сверены по источникам ниже; это ссылки для самостоятельной проверки.
Продолжить разбираться
RANK и DENSE_RANK: откуда берутся пропуски номеров
Чем RANK отличается от DENSE_RANK: равные баллы, группы сверстников и пропуски мест на собственном примере.
Открыть темуПлан запроса: что показывает индексный поиск без обещания секунд
Как читать EXPLAIN QUERY PLAN в SQLite: SCAN, SEARCH, покрывающий индекс и проверка гипотезы на настоящем запросе.
Открыть тему