Как последовательно писать аналитические SQL-запросы за 8 шагов

Используйте Crunchy Playground, чтобы следовать по материалу поста с помощью терминала Postgres:

Песочница Postgres с примерами данных.

Рассмотрим пример запроса. Похоже, что здесь ищется сводная сумма сумм счетов по командам. Если присмотреться, можно увидеть, что объединение приведет к увеличению ежегодных расходов команды на каждого члена команды.

SELECT
	teams.id,
	json_agg(accounts.email),
	SUM(invoices.amount)
FROM teams
	INNER JOIN team_members ON teams.id = team_members.team_id
	INNER JOIN accounts ON teams.id = team_members.team_id
	INNER JOIN invoices ON teams.id = invoices.team_id
WHERE lower(invoices.period) > date_trunc('year', current_date)
GROUP BY 1;

Запрос объединяет счета с командами после уже присоединения team_members к командам. Если в команде несколько участников и несколько счетов, сумма каждого счета может быть учтена несколько раз в расчете SUM(invoices.amount).

Построение SQL-запроса с нуля

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

Написание сложного SQL — это не столько «написание», сколько «построение» запроса. Комбинируя строительные блоки, вы берете данные, которые, по вашему мнению, получаете. Чтобы написать сложный запрос, выполните следующие шаги, пока не дойдете до нужных данных:

1. Словесно определите данные.
2. Изучите доступные данные.
3. Верните простейшие данные.
4. Подтвердите простые данные.
5. Дополните данные с помощью объединений.
6. Выполните суммирования.
7. Дополните деталями или агрегатами.
8. Отладьте запрос.

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

Шаг 1. Напишите человеческими словами, чего вы хотите

Напишите описание и знайте: ничего страшного, если оно изменится. Исследование данных может означать, что данные отличаются от ожидаемых. Но это отправная точка. Обычно я пишу описание на естественном языке в виде комментария вверху редактора:

-- Возвращает все команды, адреса электронной почты команды и
-- общие расходы за текущий год

Шаг 2. Изучите данные в таблицах

Даже будучи знакомым с набором данных, я трачу время на то, чтобы убедиться, что данные не изменились. Во-первых, если вы используете psql, перечислите таблицы:

psql> \dt
psql> \d invoices

Есть множество SQL-клиентов, и все они должны поддерживать вывод и просмотр таблиц и их структур. Для дальнейшего погружения напишите простой запрос выборки данных:

SELECT * FROM invoices;

Попробуйте это на нескольких разных таблицах. Проверяя имена и данные столбцов, я могу увидеть закономерность взаимоотношений. При изучении набора данных, созданного кем-то другим, может быть сложно определить взаимосвязи. Данные не всегда чистые. Столбцы не могут быть названы неправильно. «Магические строки» и «магические целые числа» могут не иметь смысла. А многие разработчики приложений реализуют разные подходы к структурам данных.

Чтобы проверить структуру таблицы, я использую двухэтапный подход: 1) сравниваю ее с известными данными и 2) опрашиваю людей, участвующих в проекте. Когда человека спрашивают о структуре данных, он никогда не ответит «да» или «нет» — у структуры данных всегда есть история. Важно проверить связи — возможно объединить два несвязанных поля.

Шаг 3. Сначала найдите самые простые данные

В нашем случае самые простые данные — это возврат счета. Еще хочется рассчитать расходы команды за год. Сначала упростим до счетов-фактур, которые должны идти в калькуляции:

SELECT
	*
FROM invoices
WHERE lower(period) > date_trunc('year', current_date);

Просмотрите данные и убедитесь, что возвращенные строки соответствуют ожидаемым данным — включенным и исключенным. Просматривая данные, добавьте условие WHERE для атрибутов, которые следует исключить. Распространенная проблема c пропущенными в данных строками внутри условных выражений — значения NULL. Следующее условие также исключает данные, где deleted_at имеет значение NULL:

AND deleted_at < date_trunc('year', current_date)

Чтобы включить значения NULL, условие необходимо расширить до:

AND (deleted_at < date_trunc('year', current_date) OR
deleted_at IS NULL)

Шаг 4. Проверьте простые данные

При работе со сложными запросами, требующими точности, например с финансовыми отчетами, вам может потребоваться проверить результаты построчно. Пройдитесь по каждой строке и подтвердите результаты. Затем просмотрите известный набор достоверных данных и убедитесь, что данные не пропущены. Эта двусторонняя верификация обнаруживает множество неверно написанных SQL-запросов.

Шаг 5. Добавьте объединения (джоины), но не добавляйте вычисления

Начните с наиболее разумных объединений. Это пример: идея о том, что данных мы не знаем, ложная, а в реальном мире этот шаг требует дополнительных экспериментов и проверки данных от членов команды:

SELECT
	*
FROM invoices
	INNER JOIN teams ON invoices.team_id = teams.id
WHERE lower(period) > date_trunc('year', current_date);

После добавления объединений запустите запрос и проверьте данные. Объединив данные, запрос возвращает больше столбцов. Начните ограничивать ответ столбцами, с которыми будете работать. Удалите * и перейдите к именам столбцов:

SELECT
	invoices.period,
	invoices.amount,
	teams.id,
	teams.name
FROM invoices
	INNER JOIN teams ON invoices.team_id = teams.id
WHERE lower(period) > date_trunc('year', current_date);

Как только это сработает, добавляйте дополнительные объединения, пока запрос не сломается. В этом примере поэкспериментируйте — добавьте team_members:

SELECT
	invoices.period,
	invoices.amount,
	teams.id,
	teams.name
FROM invoices
	INNER JOIN teams ON invoices.team_id = teams.id
	INNER JOIN team_members ON teams.id = team_members.team_id
WHERE lower(period) > date_trunc('year', current_date);

Но здесь есть повторяющиеся строки: раньше запрос возвращал 602 строки, а теперь — 3749 строк. Почему? При присоединении к командам и team_members отношения «один-ко-многим» добавляют одну строку для каждого дополнительного члена команды. В этом случае мы бы сделали шаг назад, чтобы идти вперед. Удалите последнее значение и инкапсулируйте его.

Распространенные проблемы на этом этапе включают:

• опечатки в условии объединения — при работе с таблицами со схожими именами легко вставить неправильное условие обьединения. Например, следующий запрос будет выполнен и вернет совершенно неверные данные. Сможете ли вы обнаружить ошибку?

SELECT
	invoices.period, invoices.amount, teams.id, teams.name
FROM invoices
	INNER JOIN teams ON invoices.id = teams.id
WHERE lower(period) > date_trunc('year', current_date);

Другой вопрос: какой тип объединения следует использовать? Освежим в памяти:

• INNER JOIN — это исключающее объединение, строки только с совпадающими строками в объединенной таблице, тогда значение не возвращается.
• LEFT JOIN — немонопольное объединение, возвращает все строки из ранее запрошенной таблицы, а также объединенную таблицу, если она существует.
• OUTER JOIN вернет все строки из всех таблиц; если они не найдены, другая таблица вернет NULL.

Шаг 6. Выполните суммирование

Вернемся к тому, что работает, и упакуем это в общее табличное выражение — CTE, которое можно использовать в качестве объединения. Изменяя запрос, вы делаете какие-то неправильные шаги – это обычное дело. Знайте, как вернуться к рабочему запросу. Часто для этого требуется отменить изменения до работающего состояния.

Как только я перехожу в этому состоянию, я упаковываю кусочек данных в CTE:

WITH team_yearly_spend AS (
	SELECT
		invoices.period AS invoice_period,
		invoices.amount AS invoice_amount,
		teams.id AS team_id,
		teams.name AS team_name
	FROM invoices
		INNER JOIN teams ON invoices.team_id = teams.id
	WHERE lower(period) > date_trunc('year', current_date)
)

SELECT * FROM team_yearly_spend;

Обратите внимание на использование AS, чтобы объявить уникальные имена столбца. При построении сложных запросов я предпочитаю многословность, чтобы сократить количество ошибок.

Добавим в CTE агрегации:

WITH team_yearly_spend AS (
	SELECT
		teams.id AS team_id,
		teams.name AS team_name,
		SUM(invoices.amount) AS team_yearly_spend
	FROM invoices
		INNER JOIN teams ON invoices.team_id = teams.id
	WHERE lower(period) > date_trunc('year', current_date)
	GROUP BY 1
)

SELECT
	*
FROM team_yearly_spend;

Шаг 7. Наконец, дополним данные подробностями

Чтобы включить адреса электронной почты членов команды, как указано вначале, присоединим членов команды к оператору вне CTE:

WITH team_yearly_spend AS (
	SELECT
		teams.id AS team_id,
		teams.name,
		SUM(invoices.amount) AS spend
	FROM invoices
		INNER JOIN teams ON invoices.team_id = teams.id
	WHERE lower(period) > date_trunc('year', current_date)
	GROUP BY 1
)

SELECT
	team_yearly_spend.team_id,
	team_yearly_spend.spend,
	COUNT(DISTINCT accounts.id) AS accounts_count,
	JSON_AGG(accounts.email) AS account_emails
FROM team_yearly_spend
LEFT JOIN team_members ON team_yearly_spend.team_id = team_members.team_id
LEFT JOIN accounts ON team_members.account_id = accounts.id
GROUP BY 1, 2
;

Шаг 8. Отладьте запрос

Для отладки ошибок вывода мне проще удалить вычисления, чтобы получить необработанные данные. При использовании инструмента редактирования запросов, который позволяет выполнять выбранный запрос визуально (например, DBeaver), я комментирую агрегаты и добавляю *, чтобы вернуть больше значений:

-- WITH team_yearly_spend AS (
	SELECT
		teams.id AS team_id,
		teams.name,
		*
--		SUM(invoices.amount) AS spend
	FROM invoices
		INNER JOIN teams ON invoices.team_id = teams.id
	WHERE lower(period) > date_trunc('year', current_date)
--	GROUP BY 1
--)

В этом ответе сервера найдите:

• строки, дублированные объединениями;
• строки, которые должны присутствовать, но из-за плохого условия их нет;
• включенные строки, которые следует отфильтровать каким-то условием;

Отладка SQL-запросов — простой, но не легкий процесс. Для отладки требуется аудит данных, обычно лучше всего сравнивать с известным значением.

Почему SQL сложен?

Схема приведенного выше примера представляла собой пример структуры данных приложения с учетом OLTP. Только что написанный нами SQL-запрос может использовать эту схему и генерировать значения для создания отчетов или отображения пользователям приложения. Это самое замечательное в SQL — независимо от того, как представлена ​​основная структура, мы можем получить из нее те данные, которые хотим получить.

SQL является мощным инструментом, поскольку он построен на простых стандартизированных блоках логики.

Написание SQL-запроса — нелинейный процесс. Я никогда не видел, чтобы кто-то начинал длинный запрос с начала и доводил его до конца. Этот процесс включает несколько уровней извлечения, верификации и подведения итогов.

Читайте также:

• NestJS и PostgreSQL: руководство по настройке 
• Уязвимости для SQL-инъекций
• Освойте оконные функции SQL раз и навсегда

Читайте нас в Telegram, VK и Дзен

---

Перевод статьи Christopher Winslett: 8 Steps in Writing Analytical SQL Queries