0Pricing
SQL Interview Prep · Урок

Приём с разностью номеров строк

Вычитайте ROW_NUMBER из последовательности, чтобы объединять последовательные значения в острова.

«Приём с разностью номеров строк» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Самый изящный ключ острова

Приём с разностью номера строки — это техника, которую собеседники особенно хотят увидеть при работе с островами последовательных целых чисел или дат. Она создаёт ключ группы одним вычитанием, без LAG и без накопительной суммы.

Идея проста: вычесть ROW_NUMBER из самого значения. В любой последовательности значений, идущих подряд, и значение, и номер строки на каждом шаге увеличиваются ровно на 1, поэтому их разность постоянна на протяжении всей последовательности. Эта постоянная величина и есть ключ острова.

Почему разность остаётся постоянной

Рассмотрим две соседние строки в последовательности. При переходе от одной к другой значение увеличивается на 1, и номер строки увеличивается на 1. При вычитании эти прибавления на 1 сокращаются, поэтому value - row_number не меняется.

Но как только появляется разрыв, значение увеличивается больше чем на 1, тогда как номер строки по-прежнему увеличивается только на 1. Разность меняется на новую постоянную величину. Именно это изменение отделяет один остров от следующего.

На наших данных

Вспомним дни входа: 1, 2, 3, 7, 8, 10. Расположим рядом номер строки и разность:

  • день 1, номер строки 1, разность 0
  • день 2, номер строки 2, разность 0
  • день 3, номер строки 3, разность 0
  • день 7, номер строки 4, разность 3
  • день 8, номер строки 5, разность 3
  • день 10, номер строки 6, разность 4

Разности (0, 0, 0, 3, 3, 4) идеально разделяют строки на три острова. Одинаковая разность означает один и тот же остров.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Свёртывание в острова

Используя разность как ключ группы, выполните стандартное свёртывание. Оберните вычисление разности в CTE и сгруппируйте по ней с помощью GROUP BY:

Этот запрос возвращает те же три острова, что и раньше, но SQL получается короче и понятнее, чем вариант с LAG и накопительной суммой. Для последовательностей целых чисел или значений с равномерным шагом к этому решению следует обращаться в первую очередь.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

Важное ограничение: значения должны увеличиваться на единицу

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

  • Даже значения 2,4,6,8 будут выглядеть как пропуски при вычитании номера строки из значения.
  • Дубликаты нарушают выравнивание: номер строки продолжает увеличиваться, а значение — нет.

Именно понимание этого ограничения и способов его устранения отличает заученный приём от настоящего понимания.

Последовательности с фиксированным шагом

Если значения увеличиваются на известную постоянную величину k, а не на 1, сначала выполните нормализацию: разделите значение на k (или используйте value / k для целых чисел), чтобы каждый шаг снова стал равен 1, а затем вычтите номер строки.

Например, для чётных чисел с шагом 2 используйте day_no / 2 - ROW_NUMBER(). Нормализованное значение теперь увеличивается на 1 для каждого элемента, и свойство постоянной разности восстанавливается.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Применение к датам

Даты — самый распространённый практический случай. Календарные даты нельзя напрямую вычитать из номера строки, поэтому сначала преобразуйте дату в количество дней. В PostgreSQL вычтите фиксированную опорную дату, чтобы получить целое число дней, а затем примените тот же приём.

Поскольку соседние календарные дни отличаются на 1, разность между количеством дней и номером строки снова постоянна внутри острова.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Вычисление разностей дат в разных диалектах

Шаг преобразования даты в целое число зависит от СУБД, и интервьюеры ценят знание особенностей разных диалектов:

  • PostgreSQL: вычтите литерал даты: login_date - DATE '2000-01-01' возвращает целое число.
  • MySQL: используйте DATEDIFF(login_date, '2000-01-01').
  • SQL Server: используйте DATEDIFF(day, '2000-01-01', login_date).

На некоторых СУБД есть ещё более удобный способ: вычесть из даты ROW_NUMBER дней напрямую с помощью арифметики интервалов, а затем сгруппировать строки по полученной опорной дате с помощью GROUP BY.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Добавление разбиения по группам

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

Поэтому используйте GROUP BY и для user_id, и для вычисленной разности. Если забыть user_id в итоговом GROUP BY, получится тонкая ошибка, которую интервьюеры любят обнаруживать.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Приём или LAG: что выбрать

Теперь в вашем арсенале есть два надёжных приёма. Выбирайте осознанно:

  • Разность с номером строки: самый короткий и понятный вариант для последовательностей со стабильным шагом (плотных целых чисел, идущих подряд дат). Это первый выбор, когда соседство означает «разность равна постоянной величине».
  • LAG плюс накопительная сумма: более гибкий вариант, когда соседство не определяется фиксированным числовым шагом, например для правила «тот же статус, что и в предыдущей строке» или других нерегулярных пользовательских правил.

На собеседовании объясните свой выбор и его причину: ход рассуждений впечатляет сильнее, чем синтаксис.

Надёжная обработка дубликатов

Если значение может повторяться, но для каждого последовательного участка всё равно нужен отдельный остров, сначала удалите дубликаты с помощью DISTINCT или группировки, чтобы номер строки однозначно соответствовал значениям. Другой вариант — использовать DENSE_RANK вместо ROW_NUMBER, чтобы одинаковые значения получали один ранг.

Всегда уточняйте у интервьюера, могут ли встречаться дубликаты: правильная защита зависит от того, должны ли дубликаты расширять участок или игнорироваться внутри него.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Быстрая проверка

Убедитесь, что понимаете, почему этот приём работает.

Итоги: приём разности

Теперь у вас есть самый простой ключ острова:

  • Формула ключа: value - ROW_NUMBER() OVER (ORDER BY value) постоянна для каждого последовательного участка.
  • Сгруппируйте строки с помощью GROUP BY по разности, чтобы получить начало, конец и длину.
  • Для последовательностей с фиксированным шагом сначала выполните нормализацию (разделите значение на шаг).
  • Для дат преобразуйте их в целое количество дней с помощью функции вычисления разности, зависящей от диалекта.
  • Для каждой группы: используйте PARTITION BY для номера строки и добавьте столбец группы в итоговый GROUP BY.
  • Защищайтесь от дубликатов с помощью DISTINCT или DENSE_RANK.

Теперь переключим внимание с островов на пустые места — поиск пропусков.

Часто задаваемые вопросы

Урок «Приём с разностью номеров строк» бесплатный?

Да — полный текст урока «Приём с разностью номеров строк» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Приём с разностью номеров строк»?

Вычитайте ROW_NUMBER из последовательности, чтобы объединять последовательные значения в острова. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Приём с разностью номеров строк»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Распознавание задачи о пропусках и островах
  2. Приём с разностью номеров строк
  3. Поиск пропусков в последовательности
  4. Острова при изменении даты и статуса
← Назад к SQL Interview Prep