sorting-and-limiting

Сортировка и ограничение

Предпосылки: SELECT и фильтрация (SELECT, FROM, WHERE, логический порядок выполнения).

← SELECT и фильтрация | Агрегация →

Данные выбраны и отфильтрованы — теперь менеджер хочет их презентовать: показать топ по зарплате, разбить на страницы, убрать дубликаты. Результат запроса без ORDER BY — неупорядоченная коллекция строк: в реляционной модели отношение не имеет порядка, и без явной инструкции СУБД ничего не гарантирует. Порядок вывода произволен и может меняться. Подробнее о пагинации см. в отдельной заметке пагинация.

Топ-3 по зарплате — ORDER BY

ORDER BY (англ. «упорядочить по») сортирует результат:

SELECT name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary;

 name     | salary
----------+--------
 Евгений  |  55000
 Борис    |  60000
 Глеб     |  70000
 Вера     |  85000
 Анна     |  90000

По умолчанию сортировка по возрастанию — ASC (от ascending, «восходящий»). Для убывания — DESC (descending, «нисходящий»):

SELECT name, salary
FROM employees
ORDER BY salary DESC;

 name     | salary
----------+--------
 Дина     |  NULL
 Анна     |  90000
 Вера     |  85000
 Глеб     |  70000
 Борис    |  60000
 Евгений  |  55000

NULL наверху — NULLS LAST

Дина с NULL-зарплатой оказалась первой при DESC. NULL при сортировке в PostgreSQL ведёт себя как если бы был наибольшим значением: при ASC — в конце, при DESC — в начале. Это конвенция PostgreSQL, не математическое свойство NULL. Поведение можно переопределить:

ORDER BY salary DESC NULLS LAST

Это поставит NULL в конец даже при убывающей сортировке.

По отделам, внутри по зарплате — несколько столбцов

SELECT name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

 name     | department_id | salary
----------+---------------+--------
 Дина     |             1 |   NULL
 Анна     |             1 |  90000
 Вера     |             1 |  85000
 Глеб     |             2 |  70000
 Борис    |             2 |  60000
 Евгений  |          NULL |  55000

Сначала по отделу (по возрастанию), внутри каждого отдела — по зарплате (по убыванию). Два эффекта NULL: department_id сортируется ASC → NULL в конце (NULLS LAST по умолчанию). salary сортируется DESC → NULL в начале группы (NULLS FIRST по умолчанию для DESC), поэтому Дина стоит выше Анны и Веры.

Псевдонимы в ORDER BY

ORDER BY выполняется после SELECT, поэтому может использовать псевдонимы:

SELECT name, salary * 12 AS annual
FROM employees
ORDER BY annual DESC NULLS LAST;

 name     |  annual
----------+---------
 Анна     | 1080000
 Вера     | 1020000
 Глеб     |  840000
 Борис    |  720000
 Евгений  |  660000
 Дина     |    NULL

Это работает, потому что к моменту сортировки столбец annual уже вычислен.

Только первые 3 — LIMIT

LIMIT (англ. «предел, ограничение») обрезает результат до указанного числа строк:

SELECT name, salary
FROM employees
ORDER BY salary DESC NULLS LAST
LIMIT 3;

 name | salary
------+--------
 Анна |  90000
 Вера |  85000
 Глеб |  70000

LIMIT без ORDER BY почти всегда бессмыслен: без сортировки нет гарантии, какие именно строки попадут в результат.

Страница 2 — OFFSET

OFFSET (англ. «смещение») пропускает указанное число строк:

SELECT name, salary
FROM employees
ORDER BY salary DESC NULLS LAST
LIMIT 2 OFFSET 2;

 name     | salary
----------+--------
 Глеб     |  70000
 Борис    |  60000

OFFSET 2 пропустил Анну и Веру, LIMIT 2 взял следующие две. В порядке выполнения LIMIT/OFFSET — последний шаг:

1. FROM         -- берём строки
2. WHERE        -- фильтруем
3. SELECT       -- вычисляем выражения
4. ORDER BY     -- сортируем все строки
5. LIMIT/OFFSET -- обрезаем результат

Все предыдущие шаги выполняются полностью, прежде чем LIMIT/OFFSET отбросит лишнее. Сортировка больших объёмов данных требует памяти — в PostgreSQL механизм выделения и сброса на диск описан в memory и spill. Это объясняет проблему пагинации через OFFSET: страница 1 — LIMIT 10 OFFSET 0, страница 2 — LIMIT 10 OFFSET 10. Чтобы показать страницу 1000, PostgreSQL должен пройти шаги 1–4 целиком — отсортировать все строки — и лишь затем пропустить 9990. Чем глубже страница, тем дороже запрос. Для больших таблиц существует keyset pagination — см. пагинация.

Видимость псевдонимов

Псевдонимы из SELECT видны не во всех секциях запроса — SELECT вычисляется позже WHERE:

Псевдоним	WHERE	SELECT	ORDER BY
Столбца (SELECT … AS x)	нет	да	да
Таблицы (FROM t AS x)	да	да	да

Если нужно использовать вычисленное выражение в WHERE — придётся повторить его: WHERE salary * 12 > 500000, а не WHERE annual > 500000.

Какие отделы есть? — DISTINCT

Менеджер спрашивает: «какие отделы представлены в таблице?» Без DISTINCT результат содержит дубликаты — каждый department_id повторяется столько раз, сколько сотрудников в отделе.

DISTINCT (англ. «различный, уникальный») убирает дубликаты из результата:

SELECT DISTINCT department_id
FROM employees;

 department_id
--------------
             1
             2
          NULL

Три уникальных значения: 1, 2 и NULL. DISTINCT считает два NULL одинаковыми (дубликатами). Это отличается от поведения =, где NULL = NULL даёт NULL — одна из непоследовательностей, описанных в таблице контекстов NULL. DISTINCT использует специальный механизм «IS NOT DISTINCT FROM».

DISTINCT стоит ресурсов — PostgreSQL сравнивает строки через сортировку или хеш-таблицу. Не ставьте DISTINCT «на всякий случай».

DISTINCT выполняется после SELECT, но до ORDER BY:

1. FROM         -- берём строки
2. WHERE        -- фильтруем
3. SELECT       -- вычисляем столбцы
4. DISTINCT     -- убираем дубликаты
5. ORDER BY     -- сортируем
6. LIMIT/OFFSET -- обрезаем

Задача «первая строка в каждой группе» (например, самый высокооплачиваемый в каждом отделе) потребует инструментов, которые рассмотрены в оконных функциях.

Sources

• PostgreSQL Documentation (v16): ORDER BY, LIMIT, DISTINCT. https://www.postgresql.org/docs/16/sql-select.html

---

← SELECT и фильтрация | Агрегация →