Как решать задачи на SQL

Примеры решений

Разберем на нескольких примерах последовательность шагов для решения задач. С разрешения Федора Самородова разберем первые две задачи, придуманные им.

Задача 1

Какие продавцы продали в 1997 году более 30 штук товара №1?

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

• результат: продавцы
• критерий:
  • продажи за 1997 год
  • товар №1
  • более 30 штук проданного товара
    • показатель: 30 штук проданного товара

Анализ данных. Далее мы смотрим схему базы данных и отображаем на нее логические компоненты задачи. По схеме мы видим, что напрямую между продавцами (Employees) и проданными товарами (Order Details) нет прямой связи, но обе таблицы связаны с таблицей (Orders), которая также нам требуется, чтобы выбрать данные на 1997 год. Это значит, что продавцы имеют косвенную связь с проданными товарами, которая, с точки зрения SQL, принципиально не отличается от прямой связи. В формализованном виде можно представить данные для задачи в следующем виде:

• продавцы: таблица Employees
• проданные товары: таблица [Order Details]
  • критерий:
    • товар №1: ProductId = 1
    • более 30 штук проданного товара: sum(Quantity) > 30
• связь продавцов с продажами товаров: через таблицу Orders
  • критерий:
    • продажи за 1997 год: year(OrderDate) = 1997

Для простоты выведем в качестве результата колонку с фамилией продавцов (LastName). В данном случае количество колонок не влияет на решение задачи.

Поскольку в задаче у нас есть показатель, который считается с помощью агрегатной функции, то в запросе нам потребуется сгруппировать данные с помощью GROUP BY. В качестве аналитики, соответственно мы укажем колонку LastName:

select LastName --, sum(od.Quantity)
from Employees as e
join Orders as o on e.EmployeeID = o.EmployeeID
join [Order Details] as od on o.OrderID = od.OrderID
where od.ProductID = 1
  and year(o.OrderDate) = 1997
group by LastName
having sum(od.Quantity) > 30

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

Для этой задачи использование JOIN и GROUP BY является простой и оптимальной техникой. Давайте в учебных целях рассмотрим вариант решения с подзапросами. В новом варианте у нас будут использоваться такие же таблицы, критерии и показатель. Но по синтаксису мы заменяем  каждый JOIN на подзапрос.

При использовании подзапросов можно вначале написать общую структуру основного запроса с фейковыми подзапросами (SELECT NULL), чтобы мы поняли общую логику решения:

select LastName
from Employees as e
where (select null) > 30

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

Давайте вспомним как считается показатель продажи товара — важно преобразовать условия соединения из JOIN ON в критерий выбора данных WHERE. Поскольку Order Details связан с Orders как «многие к одному» (несколько товаров в одном заказе), то нам нужен предикат IN:

select sum(od.Quantity)
from [Order Details] as od
where od.ProductID = 1 
  -- Соединение с таблицей Orders
  and od.OrderID IN (SELECT NULL)

Ключевым моментом для выбора синтаксической конструкции является понимание схемы базы данных. Если мы не понимаем, как соединять подзапросы между собой, значит нужно еще раз внимательно изучить схему, и тогда мы вспомним, что продажи товаров у нас связаны с сотрудниками через таблицу Orders, тем более, что нам еще нужно реализовать критерий по году продаж, который вычисляется из поля OrderDate этой таблицы.

С помощью подзапросов у нас каждая подзадача решается с помощью простого запроса. Нам осталось добавить в подзапрос критерий для фильтрации заказов. Для этого нам нужен еще один подзапрос 2-го уровня. При тестировании подзапроса можно написать частный случай для сотрудника №1, а в конечном решении заменить код сотрудника на поле EmployeeID из таблицы Employees.  Поскольку таблица Orders связана с таблицей Employees как «один к одному» (у каждого заказа один продавец), то нам нужен не IN  а операция «=»:

select o.OrderID
from Orders as o
where year(o.OrderDate) = 1997 
  -- Соединение с таблицей Employees
  and EmployeeID = 1

Собираем наши простые запросы в конечный рабочий запрос:

select LastName
from Employees as e
where (
  select sum(od.Quantity)
  from [Order Details] as od
  where od.ProductID = 1 and od.OrderID in (
    select o.OrderID
    from Orders as o
    where year(o.OrderDate) = 1997 and e.EmployeeID = o.EmployeeID)
) > 30

Задача 2

Еще одна задача от Федора Самородова, как всегда, очень изящная, не столько на технику, сколько на мышление.

Для каждого покупателя (имя, фамилия) найти два товара (название), на которые покупатель потратил больше всего денег в 1997-м году.

Логика задачи. В этой задаче важно не запутаться: очевидно, какой у нас должен получиться результат, мы сразу же видим, что нам нужен показатель по продажам и критерий по году продаж. Тонкость заключается в том, чтобы понять: «два товара» это ограничение результата на основе рейтинга по сумме продаж. Формализация задачи:

• результат: покупатель (имя, фамилия), товар (название)
  • ограничение: 2 товара
• критерий:
  • продажи за 1997 год
• рейтинг:
  • максимальная сумма продаж
    • показатель: сумма продаж
      • аналитика: покупатель, товар

Анализ данных. По схеме данных понимаем, что нам нужно связать справочник заказчиков (Customers), заказы (Orders) и проданные товары (Order Details). Формализация данных:

• заказчики: таблица Customers
  • результат: имя покупателя (ContactName)
• заказы: таблица Orders
  • критерий:
    • продажи за 1997 год: year(OrderDate) = 1997
• продажа товаров: таблица Order Details
  • рейтинг:
    • максимальная сумма продаж
      • показатель: сумма продаж: sum(Quantity * UnitPrice * (1 — Discount))
        • аналитика: имя покупателя (ContactName), название товара (ProductName)
• справочник товаров: таблица Products
  • результат: название товара (ProductName)

Рейтинг данных строится с помощью ORDER BY, а ограничение реализуется с помощью TOP в SELECT. Поскольку у нас есть показатель, который мы считаем с помощью агрегатной функции sum, то нам потребуется GROUP BY и задать с его помощью аналитику по ContactName и ProductName. Начальный вариант решения:

SELECT c.ContactName, p.ProductName, SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS Amt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName

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

SELECT c.ContactName, p.ProductName
FROM Customers c
CROSS APPLY (
  SELECT TOP 2 p.ProductName
  FROM Orders o
  JOIN [Order Details] od ON od.OrderID = o.OrderID
  JOIN Products p ON p.ProductID = od.ProductID
  WHERE YEAR(o.OrderDate) = 1997
    -- Соединение с внешним запросом
    AND o.CustomerID = c.CustomerID
    GROUP BY p.ProductName
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC) p

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

Для данной задачи решение с APPLY является классическим, но можно решить эту задачу с помощью оконных функций, поскольку они также позволяют решать задачи с рейтингами. Давайте используем оконную функцию ROW_NUMBER, чтобы сделать рейтинг продаж товаров для каждого покупателя:

SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
    PARTITION BY c.ContactName 
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName

Поскольку оконные функции работают только в SELECT, для фильтрации данных с рейтингом 1 и 2 (по столбцу RatingByAmt) нужно использовать обертку как технику преодоления синтаксических ограничений в SQL:

SELECT ContactName, ProductName FROM (
SELECT c.ContactName, p.ProductName
, ROW_NUMBER() OVER (
    PARTITION BY c.ContactName
    ORDER BY SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) DESC
) AS RatingByAmt
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN [Order Details] od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
WHERE YEAR(o.OrderDate) = 1997
GROUP BY c.ContactName, p.ProductName
) t
WHERE RatingByAmt < 3

Задача 3

Сколько товаров нужно заказать у поставщиков для выполнения текущих заказов.

Важно при решении задач понимать бизнес-логику компании, в которой мы работаем и при неопределенной постановке задачи нужно задавать вопросы и уточнять требования. Если внимательно посмотреть на таблицу справочника товаров (Products), то мы увидим, что наличие товара это поле UnitsInStock. Но есть еще интересное поле ReorderLevel, в котором задается уровень запаса для обеспечения надежности поставок товара. И необходимо уточнить требование о необходимости его учитывать.

Логика задачи.  Сама по себе задача несложная: нужно посчитать остатки товара (с учетом нормы запаса), вычесть количество проданных штук и выбрать товары с дефицитом. Формализация логики:

• результат: поставщик, товар, дефицит товара
  • показатель: [дефицит товара] = [к-во продаж] — [остаток на складе] — [норма запаса]
• критерий:
  • текущие (неотгруженные) товары

Анализ данных. Вначале разберемся, что такое неотгруженные товары. При анализе таблицы заказов мы видим поле ShippedDate, и, если внимательно изучить сами данные, то становится понятным, что если поле пустое, это значит, что товары по этому заказы еще не отгружены. Ну а дальше все просто: показатель по количеству проданного товара считаем по полю Quantity  в таблице номенклатуры заказа (Order Details). А показатели остатков товара и нормы запаса хранятся в справочнике товаров (Products). Формализация данных:

• поставщики: таблица Suppliers
• справочник товаров: Products
  • показатель:
    • наличие товара на складе: UnitsInStock
    • норма запаса: ReorderLevel
• заказы: таблица Orders
  • критерий: заказ не отгружен (ShippedDate IS NULL)
• проданные товары: таблица Order Details
  • показатель: количество штук товара: sum(Quantity)

Вначале давайте посчитаем количество единиц товара в актуальных заказах — двигаемся от известного к неизвестному:

select od.ProductID, sum(od.Quantity)
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
where o.ShippedDate is null
group by od.ProductID

Далее мы уже можем посчитать дефицит товара — важный нюанс, что показатели по наличию товара и нормы запаса уже имеются в готовом виде в таблице Products, поэтому нужно сделать соединение с таблицей Products и добавить их как аналитику в GROUP BY. В результате запроса мы можем вывести название продукта и, соответственно, поменять аналитику. Также нам нужно вывести название поставщика товара, для чего мы делаем соединение с таблицей Suppliers и добавляем название поставщика как аналитику в GROUP BY.

Поскольку в критерии у нас имеется показатель, который мы вычисляем с помощью агрегатной функции, то он работает через HAVING, а не через WHERE.

Окончательное решение:

select s.CompanyName, p.ProductName, sum(od.Quantity) + p.ReorderLevel — p.UnitsInStock as ToOrder
from Orders o
join [Order Details] od on o.OrderID = od.OrderID
join Products p on od.ProductID = p.ProductID
join Suppliers s on p.SupplierID = s.SupplierID
where o.ShippedDate is null
group by s.CompanyName, p.ProductName, p.UnitsInStock, p.ReorderLevel
having p.UnitsInStock < sum(od.Quantity) + p.ReorderLevel

Основы SQL на примере задачи и Решение

Основы SQL на примере задачи

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

Постановка задачи

Дана база данных, в ней 3 таблицы следующего вида:




table1: user_id (INT(5), PRIMARY KEY), username (VARCHAR(50), INDEX)
table2: phone_id (INT(5), PRIMARY KEY), user_id (INT(5), INDEX), phone_number (INT(10), INDEX)
table3: room_id (INT(5), PRIMARY KEY), phone_id (INT(5), INDEX), room_number(INT(4) INDEX)

Необходимо выбрать номер комнаты в которой сидит пользователь с ником qux…

Подготовка данных для задачи

Для создания баз данных и таблиц используются операторы CREATE DATABASE и CREATE TABLE, соответственно (для удаления DROP DATABASE и DROP TABLE). В конце каждой команды ставится точка с запятой (;). Сначала создадим базу с именем article:

CREATE DATABASE IF NOT EXISTS article;

Мы используем ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная база данных или таблица уже существует (в дальнейшем IF NOT EXISTS для простоты будем опускать).
Теперь необходимо создать таблицы:

CREATE TABLE `table1` (`user_id` INT(5) NOT NULL AUTO_INCREMENT, `username` VARCHAR(50), PRIMARY KEY(`user_id`), INDEX(`username`));
CREATE TABLE `table2` (`phone_id` INT(5) NOT NULL AUTO_INCREMENT, `user_id` INT(5) NOT NULL, phone_number INT(10) NOT NULL, PRIMARY KEY (`phone_id`), INDEX(`user_id`, `phone_number`));
CREATE TABLE `table3` (`room_id` INT(5) NOT NULL AUTO_INCREMENT, `phone_id` INT(5) NOT NULL, `room_number` INT(4) NOT NULL, PRIMARY KEY(`room_id`), INDEX(`phone_id`, `room_number`));

Разберём эти команды по порядку. После CREATE TABLE указывается имя таблицы, далее в скобках следуют имена полей с типами и атрибутами, перечисленные через запятую и указания ключей. Первой командой мы создаём таблицу с именем table1 и полями user_id, username. Поле user_id имеет целочисленный тип (INT) и длину 5-ть знаков, не может равняться нулю и обладает атрибутом auto_increment (при создании каждой записи, значение в этом поле создаётся автоматически и увеличивается на единицу), к тому же оно является первичным ключём. [ Первичный ключ (primary key) представляет собой один из примеров уникальных индексов и применяется для уникальной идентификации записей таблицы. Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа. ] Поле username имеет символьный тип (длина 255 символов) и является индексом. Вторая и третья команды аналогичны первой.

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

SHOW TABLES; 

Теперь необходимо добавить данные в таблицы. Для добавления записей используется оператор INSERT.

INSERT INTO table1 (username) VALUE ('foo'); 

В поле user_id мы ничего не добавляем так как оно автоматически создаётся при каждом INSERT`е (вспоминаем про магический атрибут auto_increment). После названия таблицы в скобках (далее будем называть эти скобки кортежём) указывается список полей, которым мы будем присваивать значения. После VALUE указываются сами значения. Они должны стоять на соответствующих позициях в кортеже.
Такими же командами добавляем пользователей bar, baz, qux.
Для проверки используем команду:

[1]

SELECT * FROM table1; 

Саму команду SELECT мы рассмотрим подробнее позже.
Далее заполним таблицы table2 и table3.

[2]

INSERT INTO table2 (user_id, phone_number) VALUE ('2','200'); 

Здесь полю user_id присваивается значение 2, а полю phone_number — 200. Если поменять местами названия полей или значения в кортежах, то результат измениться. Например:

[3]

INSERT INTO table2 (user_id, phone_number) VALUE ('200','2'); 

Теперь полю user_id присваивается значение 200, а phone_number – 2.
Предположим, мы ошиблись при добавлении значений (использовали команду [3] вместо [2]), не надо рваться удалять таблицу или всю базу — значение можно изменить с помощью оператора UPDATE.

UPDATE table2 SET user_id='2', phone_number='200' WHERE phone_id='1';

После SET мы указываем поля, значения которых необходимо изменить, и соответственно новые значения через знак равно. Оператор WHERE мы видим впервые. Он необходимо для наложения ограничений на запрос. В данном случае изменения будут применяться не ко всем строкам таблицы, а только к тем у которых значение поля phone_id равно '1'.
Остальные данные добавляются по аналогии (что добавлять можно посмотреть вверху страницы).

Решение

Базу данных и таблицы мы создали. Теперь можно заняться решением самой задачи. Выборка в базе данных производится с помощью оператора SELECT, с которым мы немного знакомы по команде [1]. Рассмотрим его подробнее. В общем виде он выглядит так:
SELECT названия_полей FROM названия_таблиц WHERE условие [ORDER BY, LIMIT]
Где ORDER BY и LIMIT дополнительные опции.
Попробуем применить его. Выберем все значения поля username из таблицы table1.

SELECT username FROM table1;

и отсортируем их

SELECT username FROM table1 ORDER BY username;

Как видно, ORDER BY используется для сортировки по одному из полей, указанных после оператора SELECT. По умолчанию делается возрастающая сортировка, если хотим использовать сортировку в обратном порядке то после поля необходимо добавить DESC:

SELECT username FROM table1 ORDER BY username DESC;

Так как нам нужны все значения, то оператор WHERE можно не использовать. Ещё один пример: выбираем значения полей phone_id и user_id из таблицы table2, где phone_number равен '200'.

SELECT phone_id, user_id FROM table2 WHERE phone_number=200;
SELECT phone_id, user_id FROM table2 WHERE phone_number=200 LIMIT 1, 3;

LIMIT выводит строки в указанном диапазоне (нижняя граница не включается). Если первый аргумент не указан, то он считается равным 0.

Как мы можем видить, все три наши таблицы связаны. table1 и table2 через поле user_id, а table2 и table3 через phone_id. Для того, чтобы связать их в одно целое по указанным столбцам, необходимо воспользоваться оператором JOIN. JOIN, в переводе на великий и могучий, означает «объединять», то есть собирать из нескольких кусочков единое целое. В базе данных MySQL такими «кусочками» служат поля таблиц, которые можно объединять при выборке. Объединения позволяют извлекать данные из нескольких таблиц за один запрос. В зависимости от требований к результату, MySQL позволяет производить три разных типа объединения:
1. INNER JOIN (CROSS JOIN) — внутреннее (перекрёстное) объединение
2. LEFT JOIN — левостороннее внешнее объединение
3. RIGHT JOIN — правостороннее внешнее объединение

INNER JOIN позволяет извлекать строки, которые обязательно присутсвуют во всех объединяемых таблицах.
Попробуем написать запрос:

[4]

SELECT table3.room_number FROM table1 INNER JOIN table2 USING(user_id) INNER JOIN table3 USING(phone_id) WHERE table1.username = 'qux'; 

С помощью оператора USING мы указываем поле по которому будут связаны таблицы. Его использование возможно только если поля имеют одинаковое название. В противном случае необходимо использовать ON, так как показано ниже:

SELECT table3.room_number FROM table1 INNER JOIN table2 ON table1.user_id = table2.user_id INNER JOIN table3 ON table2.phone_id = table3.phone_id WHERE table1.username = 'qux';

LEFT/RIGHT JOIN позволяют извлекать данные из таблицы, дополняя их по возможности данными из другой таблицы. Чтобы показать разницу с INNER JOIN нам сначала необходимо будет добавить ещё одно поле в таблицу table1.

INSERT INTO table1 (username) VALUE ('quuz');

А теперь используем команду [4], только заменим INNER JOIN на LEFT JOIN, а qux на quuz:

SELECT table3.room_number FROM table1 LEFT JOIN table2 USING(user_id) LEFT JOIN table3 USING(phone_id) WHERE table1.username = 'quuz';

Мы получим следующий результат:


Новый пользователь получил user_id=5. Это значение отсутствует в других таблицах, поэтому в результате мы получили NULL. При INNER JOIN результат был бы пустой, так как выводятся только значения, которые есть во всех таблицах. Здесь же таблицы table1 и table2 дополняются значением из table3, даже если его и нет.

Аппендикс

Ниже приводятся примеры команд с небольшими пояснениями:

Удалить строку с user_id равным 1 из таблицы table1:

DELETE FROM table1 WHERE user_id = 1; 

Переименовываем таблицу table1 в nya:

RENAME TABLE table1 TO nya; 

Переименовать поле user_id в id (таблица table1):

ALTER TABLE table1 CHANGE user_id id INT; 

Меняем тип и атрибут поля phone_number:

ALTER TABLE table2 MODIFY phone_number VARCHAR(100) NOT NULL; 

Просмотр описания таблицы table1:

DESCRIBE table1; 

Добавляем поле abra типа DATE:

ALTER TABLE table3 ADD abra  DATE; 

Выбираем из table3 все значения поля room_id, для которых room_number начинается с цифры 3 (% означает любое количество любых символов; like проверяет совпадение символьной строки с заданным шаблоном):

SELECT room_id FROM table3 WHERE room_number LIKE '3%';

P.S.

1. Часть материала про join`ы взята из статьи MySQL немного о JOIN'ах.
2. Задача встретилась на одном из собеседований, которые я проходил. Она достаточно синтетическая, но хорошо подходит для описания материала.
3. Описания конструкций операторов намеренно упрощены для более лёгкого восприятия новичками. Для всех остальных есть Справочное руководство по MySQL