Основы SQL, от начала до конца

1. Таблицы, строки и одно слово, которое предотвращает большинство ошибок.

Перед пояснением: две таблицы содержат по 500 строк. Означает ли это, что они содержат одинаковое количество информации об одних и тех же вещах?

A Таблица представляет собой сетку. Столбцы — это поля, и каждое значение в столбце — это одно и то же. Строки — это записи. А база данных представляет собой набор таблиц, которые знают друг о друге.

Теперь слово, которое предотвращает большинство ошибок SQL: grain. Уровень детализации таблицы — это то, что означает одна строка. Скажите это как предложение, начинающееся с «одна строка на».

• Таблица клиентов содержит одну строку на каждого клиента.
• Таблица заказов содержит одну строку на заказ.
• Таблица строк заказа содержит одну строку на каждый продукт в заказе.

Все эти три таблицы могут иметь по 500 строк и означать совершенно разные вещи. Уровень детализации — это ответ на вопрос «что я считаю», и каждый счет, сумма и среднее значение, которые вы пишете, — это ответ на какое-то уровень детализации. Если число оказывается неправильным, оно обычно оказывается верным в той степени, которую вы не предполагали.

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

2. Шесть пунктов и порядок их выполнения

Перед пояснением: вы называете столбец revenue в твоем SELECT, затем попробуйте отфильтровать revenue в твоем WHERE, а в базе данных указано, что такого столбца нет. Это прямо здесь, на строке выше. Почему база данных его не видит?

Вот запрос, использующий каждое предложение. В этом вся суть SQL, а все остальное — его вариации.

SELECT   region, COUNT(*) AS orders
FROM     orders
WHERE    order_date >= '2026-01-01'
GROUP BY region
HAVING   COUNT(*) > 100
ORDER BY orders DESC
LIMIT    10;

Прочтите в порядке ввода: покажите мне эти вещи, отсюда, где это правда, сгруппированы вот так, оставьте только такие группы, отсортированы вот так и остановитесь после десяти.

Но база данных не запускает его в таком порядке. Он работает FROM, затем WHERE, затем GROUP BY, затем HAVING, затем SELECT, затем ORDER BY, затем LIMIT. Именно такой порядок показан на картинке выше, и он объясняет три вещи, которые люди иначе запоминают как произвольные правила.

Первое – это ваш вопрос. WHERE бежит раньше SELECT, так что на данный момент WHERE оценивается псевдоним revenue еще не существует. Вот почему вам придется повторить все выражение в WHEREили оберните запрос в другой.

Второй WHERE против HAVING, который является самым часто задаваемым вопросом на собеседовании по SQL после присоединения. WHERE запускается перед группировкой, поэтому фильтрует отдельные строки. HAVING запускается после группировки, поэтому он фильтрует группы и является единственным, кто может видеть агрегат типа COUNT(*). «Заказы на сумму более 500 долларов» — это WHERE. «Регионы с более чем 100 заказами» — это HAVING.

Третий заключается в том, что ORDER BY бежит за SELECT, вот почему это может используйте свой псевдоним. ORDER BY orders DESC работает в приведенном выше запросе с именем, которое WHERE не мог видеть. Тот же запрос, тот же псевдоним, другой ответ исключительно из-за того, когда выполняется каждое предложение.

Прежде чем читать дальше, скажите это вслух: почему можно ORDER BY используйте псевдоним, который WHERE не может?

3. Фильтрация и что с ней делает NULL

Перед пояснением: в таблице 100 строк. Вы запускаете один фильтр и получаете 60 строк, затем запускаете противоположный фильтр и получаете 30. Где остальные 10?

WHERE сохраняет строки, которые делают условие истинным. Строительные блоки небольшие.

Это подводит нас к ответу на предварительный вопрос. NULL не означает ноль и не означает пустой текст. Это значит неизвестно. И сравнение с неизвестным не оказывается ложным, оно возвращает неизвестное, что является третьим состоянием. Строка, значение которой неизвестно, не проходит оба region = 'North' и region <> 'North', потому что SQL не может сказать, что ни одно из них истинно. Этих десяти строк нет ни в одном результате.

Вот почему = NULL никогда не работает и IS NULL это отдельная часть синтаксиса. Вот почему NOT IN против списка, содержащего NULL вообще ничего не возвращает, что в первый раз становится действительно неприятным днем.

Повседневная привычка, которая вас спасает: всякий раз, когда вы пишете фильтр, исключающий что-то, спрашивайте, что происходит со строками, где столбец неизвестен, и решайте специально. Часто вы хотите WHERE region <> 'North' OR region IS NULL.

4. Агрегация, группировка и наличие

Перед объяснением: COUNT(*) и COUNT(customer_id) в одной и той же таблице могут возвращаться разные числа. В чем разница между тем, что задают эти два вопроса?

Ан агрегатная функция принимает много строк и возвращает одно значение. Есть пять, которые вы будете использовать постоянно: COUNT, SUM, AVG, MIN, MAX.

GROUP BY решает, что означает «много строк». Без него агрегат сворачивает всю таблицу в одну строку. С GROUP BY region, он сжимается до одной строки на регион, и внутри каждого из них вычисляется агрегат. Это та же идея, что и уровень детализации вида в Tableau, и если вы сталкивались с этим, то это точно такая же механика в другой одежде. Прохождение этого есть в Руководство по концепциям Tableau.

Следующее правило: каждый столбец в вашем SELECT должно быть либо в GROUP BY или быть обернутым в совокупность. Третьего варианта нет, потому что нет разумного ответа на вопрос «дайте мне имя клиента для этой группы из 400 клиентов».

Теперь на счет три, это и есть предварительный вопрос.

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

AVG Пропускает NULL тоже, что имеет большее значение, чем кажется. Если 40 из 100 строк не имеют значения, AVG делится на 60, а не на 100. Если пропущенное значение действительно означает ноль, сначала замените его на COALESCE(column, 0), который возвращает первый объект в своем списке, который не является NULL.

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

5. Объединение двух таблиц

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

A Объединение таблиц (JOIN) объединяет две таблицы путем сопоставления строк по общему значению, называемому ключом. Механика — это одно предложение, и его стоит придерживаться: для каждой строки слева база данных находит каждую строку справа, ключ которой совпадает, и записывает по одной выходной строке на каждую пару.

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

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

Типы, кратко. INNER JOIN сохраняет только совпадающие пары. LEFT JOIN сохраняет каждую строку из левой таблицы, заполняя правую часть NULL где ничего не совпало. RIGHT JOIN делает то же самое наоборот. FULL OUTER JOIN сохраняет несовпадающие строки с обеих сторон. CROSS JOIN сочетает все со всем и почти никогда не бывает тем, что вы хотите случайно.

В соединениях возникает наибольшая доля реальных ошибок аналитиков, и оба режима сбоя невидимы в тексте запроса и очевидны при подсчете строк. Это целое руководство: как объединить две таблицы, не теряя и не удваивая строки работает с обоими на 41 миллионе реальных строк, с двумя счетчиками, которые перехватывают каждую из них.

6. Формирование вывода: DISTINCT, ORDER BY, CASE, псевдонимы.

Перед пояснением: в вашем результате один и тот же клиент указан девять раз. Является DISTINCT исправление или это способ скрыть вопрос, который вам следует задать?

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

ORDER BY сорта. По возрастанию по умолчанию, DESC по убыванию, и вы можете сортировать по нескольким столбцам в порядке приоритета. LIMIT занимает первые н строк и является вашим лучшим другом на столе, который вы никогда раньше не открывали, потому что SELECT * FROM big_table LIMIT 10 покажет вам, с чем вы имеете дело, за секунду.

псевдонимы переименовывать вещи. AS avg_hours называет выходной столбец и FROM customers AS c называет таблицу, чтобы вы могли писать c.name. Двум объединенным таблицам почти всегда нужны эти псевдонимы, поскольку обе могут содержать столбец с одинаковым именем. Вся привычка заключена в Псевдонимы SQL и чтение запроса вслух.

CASE — это оператор «если-то» SQL, который преобразует столбец в категории.

SELECT name,
       CASE WHEN tenure_months < 12 THEN 'First year'
            WHEN tenure_months < 36 THEN 'Established'
            ELSE 'Loyal'
       END AS segment
FROM customers;

Он читает сверху вниз и останавливается на первом совпадении, поэтому для каждого из приведенных выше диапазонов не требуется верхняя граница. Все, что не соответствует, попадает в ELSE, и без ELSE это становится NULL. CASE также работает внутри агрегата, то есть подсчета подмножества в том же запросе: SUM(CASE WHEN churned = 1 THEN 1 ELSE 0 END).

7. Подзапросы, CTE и оконные функции

Прежде чем объяснить: вам нужно сравнить расходы каждого клиента со средними показателями по его региону. Для этого нужны две разные группы в одном результате. Как один запрос может иметь два зерна одновременно?

Три инструмента, и это шаг от написания запросов к их структурированию.

A подзапрос это запрос внутри другого запроса. Он может сидеть в FROM, где он действует как временная таблица, или в WHERE, где он предоставляет значение или список для сравнения.

A CTE, обычное табличное выражение, представляет собой ту же идею, написанную так, чтобы человек мог ее прочитать. Вы заранее называете результат с помощью WITH, затем используйте это имя ниже.

WITH regional AS (
  SELECT region, AVG(spend) AS region_avg
  FROM customers
  GROUP BY region
)
SELECT c.name, c.spend, r.region_avg
FROM customers AS c
JOIN regional AS r ON c.region = r.region;

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

A оконная функция получает тот же результат без соединения. Он выполняет вычисления по набору связанных строк, сохраняя при этом каждую строку.

SELECT name, spend,
       AVG(spend) OVER (PARTITION BY region) AS region_avg
FROM customers;

В этом вся идея: OVER говорит: «Посмотрите в окно рядов», и PARTITION BY говорит, какие строки принадлежат одному и тому же окну. Обычный GROUP BY сворачивает ваши строки. Оконная функция выполняет ту же самую математику и сохраняет их. Другие распространенные из них: ROW_NUMBER, RANK и DENSE_RANK для нумерации строк внутри каждой группы и LAG и LEAD для достижения предыдущей или следующей строки, именно так вы рассчитываете ежемесячные изменения.

8. Ключи, индексы и что означает реляционный характер

Прежде чем объяснить: почему адрес одного и того же клиента хранится в одной таблице, а не повторяется в каждом его заказе?

A первичный ключ столбец, который однозначно идентифицирует строку. Один идентификатор клиента, один клиент, никогда не повторяется, никогда NULL. А внешний ключ тот же самый идентификатор появляется в другой таблице, чтобы указать назад, поэтому заказ содержит идентификатор клиента того, кто его разместил.

Эта пара и есть полный ответ на заданный вопрос. Адрес живет в одном месте, поэтому его изменение меняет его повсюду, и две копии не могут не согласиться. Разделение данных на таблицы, которые ссылаются друг на друга по ключу, называется нормализацией, и на практике именно это означает реляционность.

Ан индекс — это структура поиска, которую база данных хранит в столбце, поэтому ей не нужно читать каждую строку, чтобы найти значение. Стоимость - мало места и чуть медленнее пишет. Выгода для большой таблицы огромна: сопоставление 125 855 строк с 41 миллионом позволяет перейти от полного сканирования к поиску. Индексируйте столбцы, к которым вы присоединяетесь, и фильтруйте их. руководство по работе с большими наборами данных охватывает остальную часть работы в этом размере.

И последнее, что стоит знать, прежде чем вы встретитесь с ним. SQL является стандартом, но каждая база данных расширяет его. Функции даты, строковые функции и способы ограничения строк различаются в PostgreSQL, MySQL, SQLite, SQL Server и БигЗапрос. Суть этого руководства везде одинакова. Когда функция не существует, вы обычно говорите на другом диалекте, а не ошибаетесь.