PostgreSQL для аналитика данных

Основы SQL: структура запроса SELECT, фильтрация и сортировка

SQL (Structured Query Language) — это основной инструмент аналитика для взаимодействия с базами данных. В отличие от Excel, где вы видите данные сразу, в SQL вы должны явно описать, какие данные хотите получить. Основой этого взаимодействия является оператор SELECT.

Анатомия таблицы базы данных

Прежде чем писать запросы, важно понимать, как хранятся данные. В реляционных базах данных, таких как PostgreSQL, информация организована в таблицы.

Таблица состоит из двух измерений:

Колонки (столбцы, поля) — определяют структуру данных. Каждая колонка имеет имя и тип данных (число, текст, дата).

Строки (записи) — содержат конкретные данные. Каждая строка — это отдельный объект или событие.

!Структура таблицы: пересечение строк и колонок

Базовая структура запроса

Любой запрос на выборку данных начинается с двух ключевых слов: SELECT и FROM.

* SELECT указывает базе данных, какие колонки вы хотите увидеть. * FROM указывает, из какой таблицы брать данные.

Выборка всех колонок

Чтобы получить всё содержимое таблицы, используется символ звёздочки *:

Этот запрос вернет полную копию таблицы employees. В реальной аналитической работе использовать SELECT * не рекомендуется, особенно если в таблице миллионы строк или сотни колонок. Это создает избыточную нагрузку на базу данных и сеть.

Выборка конкретных колонок

Профессиональный подход — перечислять только необходимые поля через запятую:

Результатом будет таблица, содержащая только два столбца: имя сотрудника и его зарплату.

Фильтрация данных: оператор WHERE

Аналитику редко нужна вся таблица целиком. Чаще всего требуется найти конкретный сегмент: продажи за прошлый месяц, клиентов из определенного города или транзакции с ошибками. Для этого используется оператор WHERE.

WHERE добавляется после FROM и содержит логическое условие. Если условие истинно для строки, она попадает в результат. Если ложно — отбрасывается.

Операторы сравнения

PostgreSQL поддерживает стандартные математические операторы:

* = — равно * <> или != — не равно * > — больше * < — меньше * >= — больше или равно * <= — меньше или равно

Логические операторы: AND, OR, NOT

Часто одного условия недостаточно. Для комбинирования условий используются логические операторы.

Оператор AND (И) Требует выполнения обоих условий одновременно. Это сужает выборку.

В результат попадут только те сотрудники, которые работают в маркетинге И получают 60000 или больше.

Оператор OR (ИЛИ) Требует выполнения хотя бы одного из условий. Это расширяет выборку.

Запрос вернет всех сотрудников IT-отдела и всех сотрудников отдела продаж.

Приоритет операций Как и в математике, у оператора AND приоритет выше, чем у OR. Чтобы изменить порядок выполнения, используйте круглые скобки.

Пример ошибки логики:

Из-за приоритета AND, база данных сначала найдет продавцов с высокой зарплатой, а потом добавит к ним всех айтишников (даже с низкой зарплатой).

Правильный запрос для поиска высокооплачиваемых сотрудников в обоих отделах:

Работа с NULL

NULL в SQL означает отсутствие значения. Это не ноль и не пустая строка. Это "неизвестно".

Главное правило: никогда не используйте = или != для сравнения с NULL. Поскольку значение неизвестно, оно не может быть равно или не равно чему-либо.

Для проверки на пустоту используются специальные конструкции: * IS NULL — если значение отсутствует. * IS NOT NULL — если значение заполнено.

Сортировка данных: ORDER BY

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

Он всегда ставится в конце запроса (после WHERE).

Типы сортировки: * ASC (Ascending) — по возрастанию (от А до Я, от 0 до 9). Это значение по умолчанию, его можно не писать. * DESC (Descending) — по убыванию (от Я до А, от 9 до 0).

Сортировка по нескольким колонкам

Можно сортировать данные по нескольким критериям последовательно. Например, сначала отсортировать сотрудников по отделам (по алфавиту), а внутри каждого отдела — по зарплате (от самой высокой).

Ограничение количества строк: LIMIT

При анализе больших данных часто нужно посмотреть только на примеры данных или на топ-N записей. Для этого используется LIMIT.

Найти 5 сотрудников с самой высокой зарплатой:

Порядок выполнения запроса

Для аналитика критически важно понимать не только то, как писать запрос, но и как база данных его выполняет. Порядок написания кода отличается от порядка его логической обработки движком PostgreSQL.

Вы пишете:

SELECT

FROM

WHERE

ORDER BY

LIMIT

База данных выполняет:

FROM — сначала определяется источник данных (берется вся таблица).

WHERE — затем отсеиваются лишние строки (фильтрация).

SELECT — только после фильтрации вычисляются и возвращаются указанные колонки.

ORDER BY — полученный результат сортируется.

LIMIT — отрезается нужное количество строк.

!Порядок выполнения SQL-запроса движком базы данных

Почему это важно? Потому что вы не можете использовать в блоке WHERE псевдонимы (alias) колонок, которые вы создали в блоке SELECT, так как на момент работы WHERE блок SELECT еще не был выполнен.

Итоги

* Запрос всегда начинается с SELECT (что выбрать) и FROM (откуда выбрать). * WHERE используется для фильтрации строк. Для сравнения с пустыми значениями используйте только IS NULL, а не =. * Логический оператор AND сужает выборку (оба условия верны), а OR расширяет её (хотя бы одно верно). * ORDER BY сортирует результаты. ASC — по возрастанию, DESC — по убыванию. * Порядок выполнения запроса отличается от порядка написания: фильтрация (WHERE) происходит до выбора колонок (SELECT).

Работа с несколькими таблицами: типы JOIN и объединение данных

Реляционные базы данных называются «реляционными» (от англ. relation — связь) именно потому, что данные в них распределены по множеству таблиц, связанных между собой логическими ключами. Хранить всю информацию в одной огромной таблице неэффективно: это приводит к дублированию данных, ошибкам при обновлении и увеличению размера базы.

Аналитику необходимо уметь собирать эти разрозненные данные в единый отчет. Для этого в SQL существуют два основных механизма: горизонтальное объединение (JOIN) и вертикальное объединение (UNION).

Логика связей: Primary Key и Foreign Key

Чтобы соединить две таблицы, между ними должна быть общая точка соприкосновения. Обычно это специальные колонки — ключи.

Primary Key (Первичный ключ) — уникальный идентификатор строки в таблице. Например, client_id в таблице клиентов. Он не может повторяться и не может быть пустым.

Foreign Key (Внешний ключ) — ссылка на первичный ключ другой таблицы. Например, client_id в таблице заказов.

!Связь таблиц через первичный и внешний ключи

Когда мы пишем запрос с JOIN, мы явно указываем базе данных: «Возьми строку из таблицы А и найди соответствующую ей строку в таблице Б, где значения этих ключей совпадают».

Горизонтальное объединение: JOIN

Оператор JOIN добавляет к колонкам одной таблицы колонки из другой таблицы, расширяя результат по горизонтали. Существует несколько типов присоединения, которые определяют, какие именно строки попадут в итоговую выборку.

INNER JOIN (Внутреннее присоединение)

Это самый распространенный тип объединения. Он возвращает только те строки, для которых нашлось совпадение в обеих таблицах. Если клиент есть в базе, но не сделал ни одного заказа, при INNER JOIN с таблицей заказов он не попадет в результат.

Синтаксис:

Обратите внимание на использование псевдонимов (alias) AS c и AS o. Это сокращает код и делает его более читаемым, особенно когда нужно указывать, из какой таблицы берется колонка (например, c.id).

LEFT JOIN (Левое присоединение)

LEFT JOIN возвращает все строки из левой таблицы (той, что указана первой, сразу после FROM) и сопоставленные строки из правой таблицы (указанной после JOIN).

Если для строки из левой таблицы не нашлось совпадения в правой, база данных не удаляет эту строку, а заполняет колонки правой таблицы значением NULL.

Это критически важно для аналитики, например, при поиске клиентов без покупок:

Если у клиента нет заказов, в колонке order_id будет NULL. Вы можете отфильтровать таких клиентов, добавив WHERE o.order_id IS NULL.

!Визуализация логики работы основных типов JOIN

RIGHT JOIN (Правое присоединение)

Работает зеркально LEFT JOIN: возвращает все строки из правой таблицы и совпадающие из левой. На практике используется крайне редко, так как любой RIGHT JOIN можно переписать как LEFT JOIN, просто поменяв таблицы местами. Читать код слева направо (от главной таблицы к присоединяемой) когнитивно проще.

FULL OUTER JOIN (Полное присоединение)

Возвращает строки, если есть совпадение хотя бы в одной из таблиц. Это объединение множеств: «все из левой» + «все из правой». Там, где данные не совпали, проставляются NULL.

Пример использования: сверка двух списков (план и факт), чтобы найти расхождения с обеих сторон.

CROSS JOIN (Декартово произведение)

Этот тип соединения не требует условия ON. Он соединяет каждую строку первой таблицы с каждой строкой второй таблицы.

Если в таблице sizes (размеры) 5 строк, а в таблице colors (цвета) 3 строки, результат CROSS JOIN вернет строк.

Это полезно для генерации справочников или матриц всех возможных комбинаций параметров.

Вертикальное объединение: UNION

Если JOIN расширяет таблицу вширь (добавляет колонки), то UNION увеличивает её в длину (добавляет строки). Это операция склеивания результатов двух и более запросов.

Представьте, что у вас есть таблица sales_january и sales_february. Структура у них одинаковая, но данные разные. Чтобы получить общий отчет, их нужно объединить.

Требования к UNION

Количество колонок в запросах должно совпадать.

Типы данных в соответствующих колонках должны быть совместимы (нельзя объединить текст с числом).

Порядок колонок важен: SQL объединяет первую колонку первого запроса с первой колонкой второго запроса.

UNION vs UNION ALL

Существует два варианта оператора:

UNION — объединяет данные и удаляет дубликаты. Если одна и та же строка встречалась в обеих таблицах, в результате она останется в единственном экземпляре. Это требует дополнительных ресурсов на сортировку и проверку уникальности.

UNION ALL — просто «склеивает» таблицы, сохраняя все строки, включая дубликаты. Это работает значительно быстрее.

В 95% случаев аналитику нужен именно UNION ALL, так как мы редко хотим терять данные о транзакциях, даже если они выглядят одинаково.

Распространенные ошибки при объединении

Проблема дублирования строк (Fan-out)

Самая опасная ловушка JOIN. Если в правой таблице одному ключу соответствует несколько строк (связь «один-ко-многим»), то строки левой таблицы размножатся.

Пример: Вы хотите посчитать выручку по клиентам. У вас есть таблица клиентов (100 человек) и таблица заказов. Если вы сделаете JOIN, количество строк станет равно количеству заказов, а не клиентов. Если после этого вы просто просуммируете зарплаты или бюджеты, которые хранились в таблице клиентов, вы получите завышенные цифры, так как данные клиента продублировались для каждого его заказа.

Фильтрация NULL в WHERE при LEFT JOIN

Если вы сделали LEFT JOIN, чтобы сохранить все данные из левой таблицы, но затем в блоке WHERE наложили условие на колонку из правой таблицы, ваш запрос превратится в INNER JOIN.

Неправильно (теряем клиентов без заказов):

Правильно (условие переносится в ON):

Итоги

* INNER JOIN оставляет только пересечение данных (совпадения в обеих таблицах). * LEFT JOIN сохраняет все данные из левой таблицы, заполняя пропуски справа значением NULL. * CROSS JOIN создает все возможные комбинации строк (декартово произведение). * UNION объединяет результаты запросов вертикально (удаляя дубликаты), а UNION ALL делает то же самое, но сохраняет все строки и работает быстрее. * При использовании JOIN всегда следите за количеством строк в результате: связь «один-ко-многим» может неожиданно увеличить объем выборки.