Как решать задачи на 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