Разбор ключевых функций SQL, которые нужны аналитику на работе и на собеседовании: оконные, обработка NULL, условия, даты, агрегация и строки. У каждой функции — синтаксис, два примера с результатом, типичная ошибка и что по ней спрашивают. Диалект — PostgreSQL и SQLite (движок тренажёра); где функция специфична, указан аналог для другой СУБД.
ROW_NUMBER() — ROW_NUMBER() — оконная функция, которая присваивает каждой строке уникальный порядковый номер (1, 2, 3, …) внутри окна, заданного PARTITION BY, в порядке ORDER BY.
RANK() — RANK() — оконная функция ранжирования: строки с одинаковым значением ORDER BY получают одинаковый ранг, а следующий ранг «перепрыгивает» на число совпадений.
DENSE_RANK() — DENSE_RANK() — оконная функция ранжирования, которая при одинаковых значениях присваивает одинаковый ранг, но следующий ранг не пропускает.
LAG() — LAG() — оконная функция, которая возвращает значение из предыдущей строки в пределах окна (ORDER BY).
LEAD() — LEAD() — оконная функция, которая возвращает значение из следующей строки в пределах окна (ORDER BY).
NTILE() — NTILE(n) — оконная функция, которая делит отсортированные строки окна на n примерно равных по размеру групп и присваивает каждой строке номер группы от 1 до n.
COALESCE() — COALESCE() принимает список аргументов и возвращает первый из них, который не равен NULL.
NULLIF() — NULLIF(a, b) возвращает NULL, если оба аргумента равны, и значение a в остальных случаях.
CASE — CASE — условное выражение SQL, аналог if-else: оно проверяет условия WHEN по порядку и возвращает значение первого сработавшего THEN (иначе ELSE).
Даты и время
date_trunc() — date_trunc(поле, дата) — функция PostgreSQL, которая «обрезает» дату или время до заданной точности: месяца, недели, дня, часа.
EXTRACT() — EXTRACT(поле FROM дата) достаёт из даты или времени одно числовое поле: год, месяц, день, день недели (dow), час, квартал.
strftime() — strftime(формат, дата) — функция SQLite для форматирования даты по строке-шаблону: %Y — год, %m — месяц, %d — день, %w — день недели, %H — час.
Агрегация и подсчёт
COUNT() — COUNT() считает строки, но ведёт себя по-разному в зависимости от аргумента.
STRING_AGG() — STRING_AGG(выражение, разделитель) — агрегатная функция PostgreSQL, которая объединяет значения из всех строк группы в одну строку через заданный разделитель.
Строки и типы
SUBSTR() — SUBSTR(строка, старт, длина) — она же SUBSTRING — вырезает часть строки, начиная с позиции старт (нумерация с 1) заданной длины.
CAST() — CAST(значение AS тип) приводит значение к указанному типу данных: текст в число, число в текст, строку в дату.
ROUND() — ROUND(число, знаки) округляет число до заданного количества знаков после запятой (по умолчанию до целого).
FAQ по функциям SQL
Какие функции SQL нужны аналитику?
Оконные (ROW_NUMBER, RANK, LAG, SUM OVER), обработка NULL (COALESCE, NULLIF), условия (CASE), даты (date_trunc, strftime), агрегация (COUNT, STRING_AGG) и приведение типов (CAST). Этого набора хватает для большинства задач и собеседований.
Чем справочник функций отличается от тем SQL?
Темы (JOIN, оконные, CTE) — это широкие разделы. Справочник разбирает конкретную функцию: синтаксис, 2 примера с результатом, типичную ошибку и что спрашивают на собеседовании.
Эти функции работают в PostgreSQL и SQLite?
Большинство — в обоих. Где функция диалектная (date_trunc и STRING_AGG — PostgreSQL, strftime — SQLite), это отмечено в тексте с аналогом для другой СУБД.