0Pricing
SQL Interview Prep · Урок

FIRST_VALUE, LAST_VALUE и границы рамки

Извлекайте крайние значения и разбирайтесь в тонкости рамки LAST_VALUE.

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

Извлечение граничных значений

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

Для этого используются функции FIRST_VALUE и LAST_VALUE. Они кажутся простыми, но LAST_VALUE скрывает одну из самых известных ловушек, связанных с оконными рамками в SQL. В этом уроке обе функции рассматриваются надёжно.

Основы FIRST_VALUE

FIRST_VALUE(col) возвращает значение col из первой строки окна и добавляет его к каждой строке. При сортировке по дате каждой строке возвращается самое раннее значение в её разделе.

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

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

Рамка окна по умолчанию

Вот главное. Когда Вы добавляете ORDER BY к окну, рамка по умолчанию имеет вид RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Это означает, что окно для каждой строки простирается только от начала раздела до текущей строки, а не до его конца. На FIRST_VALUE это не влияет (первая строка всегда попадает в диапазон), а вот LAST_VALUE страдает очень сильно.

Подводный камень LAST_VALUE

Если запустить LAST_VALUE, указав только ORDER BY, большинство кандидатов ожидают получить последнее значение раздела. Но поскольку рамка заканчивается на текущей строке, «последним значением в рамке» оказывается значение самой текущей строки.

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

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

Исправление LAST_VALUE с помощью полной рамки

Исправление состоит в том, чтобы расширить рамку на весь раздел: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Теперь окно для каждой строки охватывает весь раздел, поэтому LAST_VALUE возвращает настоящее последнее значение. На собеседовании явно назовите это исправление: так Вы покажете, что понимаете рамки, а не просто помните названия функций.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

Более простой вариант

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

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) возвращает самую позднюю дату, и для этого не требуется указывать предложение рамки. Это простой и легко запоминающийся приём, о котором стоит упомянуть.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

ROWS и RANGE в рамках

Рамки бывают двух видов. ROWS считает физические строки, а RANGE объединяет строки с одинаковыми значениями ORDER BY (равные строки).

В рамке по умолчанию используется RANGE, поэтому строки с одинаковыми значениями сортировки имеют общую границу рамки. Для исправления LAST_VALUE предпочитайте явную запись ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы избежать неожиданностей при одинаковых значениях.

NTH_VALUE для произвольных позиций

Помимо первого и последнего значения, NTH_VALUE(col, n) извлекает значение на позиции n внутри рамки, например цену, занимающую второе место среди самых высоких.

Эта функция подчиняется тем же правилам рамки, что и LAST_VALUE, поэтому используйте её с полной рамкой, если нужно получить значение на позиции n во всём разделе, а не только до текущей строки.

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

Пример: первое и последнее значения вместе

В распространённом отчёте рядом с каждой транзакцией указываются суммы первой и последней транзакций клиента. Объедините обе функции и не забудьте явно указать рамку для LAST_VALUE.

Теперь каждая строка содержит первое и последнее значения для всего раздела — их можно использовать для вычисления разницы или на этапе маркировки.

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Именованные окна не нарушают принцип DRY

Обратите внимание: в предыдущем запросе использовалось предложение WINDOW w AS (...), а ссылка OVER w встречалась дважды. Определение окна в одном месте избавляет от повторения длинного описания рамки и не позволяет настройкам двух функций разойтись.

Большинство крупных баз данных поддерживает именованные окна. Их использование — аккуратная деталь, которую интервьюеры ценят, когда несколько столбцов используют одно окно.

Пример: разница между первым и последним значениями

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

Так объединяются исправление с полной рамкой и простая арифметика — именно такой аккуратно собранный ответ от начала до конца интервьюеры хотят видеть.

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

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

Классический подводный камень LAST_VALUE.

Итоги

Функции для получения граничных значений зависят от рамки:

  • FIRST_VALUE работает с рамкой по умолчанию, а LAST_VALUE — нет.
  • Рамка по умолчанию заканчивается на текущей строке, поэтому исправляйте LAST_VALUE с помощью ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING либо измените порядок сортировки и используйте FIRST_VALUE.
  • NTH_VALUE(col, n) извлекает значения на произвольных позициях, а именованные окна помогают соблюдать принцип DRY при описании нескольких столбцов.

На этом завершается набор инструментов для LAG, LEAD, NTILE и функций граничных значений.

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

Урок «FIRST_VALUE, LAST_VALUE и границы рамки» бесплатный?

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

Чему я научусь в уроке «FIRST_VALUE, LAST_VALUE и границы рамки»?

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

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

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

Сколько времени занимает урок «FIRST_VALUE, LAST_VALUE и границы рамки»?

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

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

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

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

  1. LAG и LEAD для соседних строк
  2. Изменение от периода к периоду
  3. NTILE для разбиения на группы
  4. FIRST_VALUE, LAST_VALUE и границы рамки
← Назад к SQL Interview Prep