CASE WHEN и оконные функции

Добавляем в запросы логику ветвления и расчёты «по окну» строк.

CASE WHEN — это условное выражение внутри SQL, аналог if/else; оконная функция считает агрегат, не сворачивая строки в одну.

CASE WHEN — ветвление в запросе

Иногда нужно превратить число в категорию: оценку в «отлично/хорошо/плохо», цену в «дёшево/дорого». Это делает CASE: проверяет условия по порядку и возвращает значение первого подошедшего, а ELSE ловит остальные.

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);

INSERT INTO students (name, score) VALUES
    ('Аня', 92), ('Борис', 75), ('Вера', 58);

SELECT name, score,
       CASE
           WHEN score >= 85 THEN 'отлично'
           WHEN score >= 70 THEN 'хорошо'
           ELSE 'плохо'
       END AS оценка
FROM students;

Вывод:

Аня|92|отлично
Борис|75|хорошо
Вера|58|плохо

Условия проверяются сверху вниз. У Бориса 75: первое условие (≥85) не прошло, второе (≥70) прошло — «хорошо». CASE можно ставить и в SELECT, и в ORDER BY, и даже внутри агрегатов.

Оконные функции — агрегат без сворачивания

Обычный GROUP BY сворачивает группу в одну строку. Но иногда хочется видеть и отдельные строки, и итог по ним рядом. Это умеют оконные функции: после агрегата пишут OVER (...), и расчёт идёт «по окну» строк, а сами строки сохраняются. SQLite поддерживает их с версии 3.25.

Самый наглядный пример — нарастающий итог. SUM(amount) OVER (ORDER BY id) суммирует значения от начала до текущей строки:

CREATE TABLE sales (id INTEGER PRIMARY KEY, day TEXT, amount INTEGER);

INSERT INTO sales (day, amount) VALUES
    ('Пн', 100), ('Вт', 150), ('Ср', 80), ('Чт', 200);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY id) AS нарастающий_итог
FROM sales;

Вывод:

Пн|100|100
Вт|150|250
Ср|80|330
Чт|200|530

Строки остались на месте, но рядом появилась накопленная сумма: 100, 100+150, +80, +200.

RANK и PARTITION BY

Функция RANK() OVER (ORDER BY ...) присваивает место в рейтинге, причём одинаковым значениям — одинаковый ранг. Найдём места участников по очкам:

CREATE TABLE scores (id INTEGER PRIMARY KEY, name TEXT, points INTEGER);

INSERT INTO scores (name, points) VALUES
    ('Аня', 92), ('Борис', 88), ('Вера', 92), ('Глеб', 75);

SELECT name, points,
       RANK() OVER (ORDER BY points DESC) AS место
FROM scores;

Вывод:

Аня|92|1
Вера|92|1
Борис|88|3
Глеб|75|4

Аня и Вера набрали поровну — обе на 1 месте, следующий ранг 3 (второе место «пропущено»). Ключевое слово PARTITION BY делит данные на независимые окна: например, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) построит отдельный рейтинг зарплат внутри каждого отдела.

Итог

CASE WHEN ... THEN ... ELSE ... END — условное выражение прямо в запросе.
Оконные функции (SUM() OVER, RANK() OVER) считают агрегаты, сохраняя отдельные строки; SQLite их поддерживает.
ORDER BY в окне задаёт порядок (нарастающий итог, ранг), PARTITION BY делит на независимые окна.