Раздел 35 · SQLite с нуля

Сортировка, LIMIT, DISTINCT

junior~20 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

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

Колода карточек

Вернёмся к бариста с карточками напитков. После отбора у него в руках стопка. Дальше с ней можно сделать три вещи:

  1. Разложить по порядку: по цене, по алфавиту, сначала по категории, а внутри по цене.
  2. Взять сверху несколько: «покажи первые три».
  3. Убрать дубли: если нужен список категорий, пять карточек с надписью «кофе» превращаются в одну.

В SQL это ORDER BY, LIMIT и DISTINCT.

ORDER BY: порядок по столбцу

Без ORDER BY база вправе вернуть строки в любом порядке. Сегодня они идут по номеру, а после добавления индекса или новой версии SQLite могут пойти иначе. Если порядок важен, задай его явно:

sqlite> SELECT name, price FROM drinks ORDER BY price;
╭────────────────┬───────╮
│      name      │ price │
╞════════════════╪═══════╡
│ Матча-латте    │ NULL  │
│ Эспрессо       │   120 │
│ Чай с чабрецом │   130 │
│ Американо      │   150 │
│ Какао          │   180 │
│ Капучино       │   190 │
│ Тархун         │   200 │
│ Латте          │   210 │
│ Раф            │   240 │
╰────────────────┴───────╯

По умолчанию сортировка идёт по возрастанию, это ASC. Матча-латте без цены оказалась первой: в SQLite NULL при сортировке меньше любого значения. В PostgreSQL наоборот, там NULL по возрастанию идут в конце. О том, что такое NULL, подробно в следующем уроке.

Чтобы перевернуть порядок, добавь DESC:

sqlite> SELECT name, price FROM drinks ORDER BY price DESC;
╭────────────────┬───────╮
│      name      │ price │
╞════════════════╪═══════╡
│ Раф            │   240 │
│ Латте          │   210 │
│ Тархун         │   200 │
│ Капучино       │   190 │
│ Какао          │   180 │
│ Американо      │   150 │
│ Чай с чабрецом │   130 │
│ Эспрессо       │   120 │
│ Матча-латте    │ NULL  │
╰────────────────┴───────╯

Несколько столбцов

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

sqlite> SELECT category, name, price FROM drinks ORDER BY category, price DESC;
╭──────────┬────────────────┬───────╮
│ category │      name      │ price │
╞══════════╪════════════════╪═══════╡
│ другое   │ Какао          │   180 │
│ кофе     │ Раф            │   240 │
│ кофе     │ Латте          │   210 │
│ кофе     │ Капучино       │   190 │
│ кофе     │ Американо      │   150 │
│ кофе     │ Эспрессо       │   120 │
│ холодное │ Тархун         │   200 │
│ чай      │ Чай с чабрецом │   130 │
│ чай      │ Матча-латте    │ NULL  │
╰──────────┴────────────────┴───────╯

Категории идут по алфавиту, внутри кофе цена падает от Рафа к Эспрессо. DESC относится только к price, к category он не применился.

Про алфавит есть оговорка. SQLite сравнивает текст по кодам символов, это называется правилом сравнения BINARY. Для обычных русских букв порядок совпадает со словарным, но буква ё уйдёт в самый конец, а заглавные встанут раньше строчных.

Сортировать можно и по псевдониму из списка SELECT:

sqlite> SELECT name, price * 2 AS double FROM drinks ORDER BY double DESC LIMIT 2;
╭───────┬────────╮
│ name  │ double │
╞═══════╪════════╡
│ Раф   │    480 │
│ Латте │    420 │
╰───────┴────────╯

NULLS FIRST и NULLS LAST

Напиток без цены во главе дешёвого меню выглядит странно. Явно указать место NULL можно словами NULLS FIRST и NULLS LAST после направления:

sqlite> SELECT name, price FROM drinks ORDER BY price NULLS LAST LIMIT 3;
╭────────────────┬───────╮
│      name      │ price │
╞════════════════╪═══════╡
│ Эспрессо       │   120 │
│ Чай с чабрецом │   130 │
│ Американо      │   150 │
╰────────────────┴───────╯

Привычка писать NULLS LAST полезна ещё и тем, что запрос ведёт себя одинаково в SQLite и в PostgreSQL, где порядок NULL по умолчанию другой.

LIMIT: первые несколько строк

LIMIT обрезает ответ после сортировки. «Три самых дорогих» это сортировка по убыванию и LIMIT 3:

sqlite> SELECT name, price FROM drinks ORDER BY price DESC LIMIT 3;
╭────────┬───────╮
│  name  │ price │
╞════════╪═══════╡
│ Раф    │   240 │
│ Латте  │   210 │
│ Тархун │   200 │
╰────────┴───────╯

Без ORDER BY слово LIMIT даст «какие-нибудь три строки», и это почти всегда ошибка в запросе.

OFFSET: страницы

Сайт кофейни показывает меню по три напитка на страницу. Первая страница это LIMIT 3, вторая пропускает три строки и берёт следующие три. Пропуск задаёт OFFSET:

sqlite> SELECT id, name FROM drinks ORDER BY id LIMIT 3 OFFSET 3;
╭────┬───────╮
│ id │ name  │
╞════╪═══════╡
│  4 │ Латте │
│  5 │ Раф   │
│  6 │ Какао │
╰────┴───────╯

Третья страница:

sqlite> SELECT id, name FROM drinks ORDER BY id LIMIT 3 OFFSET 6;
╭────┬────────────────╮
│ id │      name      │
╞════╪════════════════╡
│  7 │ Чай с чабрецом │
│  8 │ Матча-латте    │
│  9 │ Тархун         │
╰────┴────────────────╯

Для страниц порядок тем более обязателен: без ORDER BY одна и та же строка может попасть на две страницы, а другая не попасть ни на одну. На больших таблицах OFFSET работает медленно, потому что базе всё равно приходится пройти пропущенные строки. Для меню из девяти позиций это неважно.

DISTINCT: без повторов

Какие категории есть в меню? Простой запрос вернёт категорию каждого напитка, и кофе повторится пять раз. DISTINCT после SELECT оставляет только уникальные строки ответа:

sqlite> SELECT DISTINCT category FROM drinks ORDER BY category;
╭──────────╮
│ category │
╞══════════╡
│ другое   │
│ кофе     │
│ холодное │
│ чай      │
╰──────────╯

DISTINCT смотрит на всю строку ответа, а не на первый столбец. Если выбрать два столбца, уникальными должны быть пары:

sqlite> SELECT DISTINCT staff_id, paid FROM orders ORDER BY staff_id;
╭──────────┬──────────╮
│ staff_id │   paid   │
╞══════════╪══════════╡
│        1 │ наличные │
│        2 │ карта    │
│        2 │ наличные │
│        3 │ карта    │
│        3 │ наличные │
│        4 │ карта    │
╰──────────┴──────────╯

Сотрудник 2 встречается дважды: он принимал и карту, и наличные. Тонкости DISTINCT подробнее разбирает урок из подготовки к собеседованиям.

В каком порядке это работает

Запрос пишут в одном порядке, а база выполняет в другом:

  1. FROM: взять таблицу.
  2. WHERE: отобрать строки.
  3. SELECT: вычислить столбцы.
  4. DISTINCT: убрать повторы.
  5. ORDER BY: отсортировать.
  6. LIMIT и OFFSET: отрезать нужный кусок.

Отсюда понятно, почему ORDER BY видит псевдоним из SELECT, а LIMIT всегда применяется к уже отсортированному ответу.

Попробуй сам

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

IS NOT NULL убирает напитки без цены. Почему здесь нельзя написать price <> NULL, разберём в следующем уроке.

Проверь себя

Задание

Дальше

Матча-латте без цены весь урок путалась под ногами: вставала первой при сортировке, требовала NULLS LAST и IS NOT NULL. В следующем уроке разбираемся, что такое NULL и почему с ним нельзя сравнивать через =.

домашка

Домашка