Выведи отсортированные значения парами в две колонки

Эту задачку любят на собеседованиях как проверку на знание оконных функций: с виду перекладывание значений, а на деле нужно сообразить про LEAD и фильтр по чётности.

Задача

Есть таблица с одним столбцом id, значения отсортированы, количество строк может быть нечётным:

id: 1, 2, 3, 4, 5, 7

Выведи значения парами в две колонки — следующее значение рядом с предыдущим. Если строк нечётное число, последнему без пары поставь NULL:

1 | 2
3 | 4
5 | NULL   (если строк нечётное число)

Схема данных

CREATE TABLE ids (
  id int
);

В песочнице шесть значений: 1, 2, 3, 4, 5, 7 — чётное число, так что последняя пара получится полной.

Решение

WITH t AS (
  SELECT id,
         LEAD(id) OVER (ORDER BY id) AS next_id,
         ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM ids
)
SELECT id, next_id
FROM t
WHERE rn % 2 = 1
ORDER BY id;

Разбор

Идея в двух оконных функциях, которые считаются на одном и том же порядке ORDER BY id. LEAD(id) подтягивает в текущую строку значение следующей строки — так рядом с каждым числом оказывается его сосед справа. ROW_NUMBER() нумерует строки по порядку: 1, 2, 3, ...

Теперь ключевой ход — фильтр WHERE rn % 2 = 1. Он оставляет только строки с нечётным номером: первую, третью, пятую. Именно они — «левые» элементы пар, а LEAD уже приклеил к ним «правые». Строки с чётными номерами выкидываем, иначе пары задвоятся.

Откуда берётся NULL? LEAD у самой последней строки не находит следующей и возвращает NULL. Это срабатывает автоматически, когда строк нечётное число: последний нечётный rn не имеет соседа справа. На данных песочницы строк шесть (чётно), поэтому последняя пара получается полной — 5 | 7, без NULL. Про то, как оконные функции считают значения соседних строк и почему не схлопывают набор, — в вопросе что такое оконные функции.

Ожидаемый результат

id | next_id
--- | ---
1 | 2
3 | 4
5 | 7