Пример построения сложного запроса к базе данных
Составить запрос к базе данных, формирующий список производителей товаров, проданных до заданной даны во всех филиалах (магазинах), с указанием суммарной стоимости проданных товаров для каждого производителя.
Начнём с простого запроса к таблице покупок. Выведем номер (идентификатор) покупки, дату и идентификатор покупателя – вся эта информация есть в таблице purchases:
SELECT
purchases.purchase_id,
DATE(purchases.purchase_date,'unixepoch') as purchase_date,
purchases.customer_id
FROM
purchases
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER)
Результат выполнения запроса, из которого следует, что до 30 января 2019 года (некоторая произвольно заданная дата) было совершено 7 покупок:
purchase_id|purchase_date|customer_id|
-----------|-------------|-----------|
12| 2019-01-24| 9|
32| 2019-01-25| 2|
37| 2019-01-10| 1|
46| 2019-01-06| 10|
60| 2019-01-24| 4|
75| 2019-01-06| 6|
83| 2019-01-29| 8|
В полученном результате отсутствует информация о товарах, которые были куплены в каждой покупке. Эта информация есть в таблице purchase_items – “купленные товары”.
В таблице purchase_items есть поле (столбец) purchase_id, который представляет собой ссылку на идентификатор покупки в таблице покупок purchases, т.е. purchase_items.purchase_id является внешним ключом.
Чтобы увидеть список товаров для каждой покупки необходимо для каждой строки полученного выше результата, т.е. каждой покупки, найти и вывести соответствующую строку из таблицы purchase_items: нужно найти строки у которых purchase_items.purchase_id = purchases.purchase_id. Такое соединение двух таблиц выполняется при помощи оператора LEFT JOIN:
SELECT
purchases.purchase_id,
DATE(purchases.purchase_date,'unixepoch') as purchase_date,
purchases.customer_id,
purchase_items.product_id
FROM
purchases
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN purchase_items
ON purchase_items.purchase_id = purchases.purchase_id
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER)
Внешнее соединение LEFT JOIN: означает, что помимо строк, для которых выполняется условие предиката (ON УСЛОВИЕ), в результирующий набор попадут все остальные строки из первой таблицы (левой). При этом отсутствующие значения полей из правой таблицы будут заполнены NULL-значениями.
В рассматриваемом случае покупка не может не содержать списка товаров, поэтому для записи из левой таблицы purchases всегда найдётся хотя бы одно соответствие (товар) в правой таблице purchase_items.
Результат запроса
purchase_id|purchase_date|customer_id|product_id|
-----------|-------------|-----------|----------|
12|2019-01-24 | 9| 3|
32|2019-01-25 | 2| 11|
32|2019-01-25 | 2| 46|
37|2019-01-10 | 1| 35|
37|2019-01-10 | 1| 43|
46|2019-01-06 | 10| 19|
46|2019-01-06 | 10| 28|
46|2019-01-06 | 10| 30|
60|2019-01-24 | 4| 28|
60|2019-01-24 | 4| 47|
75|2019-01-06 | 6| 48|
83|2019-01-29 | 8| 21|
В полученном новом результате больше строк, чем первом запросе, поскольку некоторым покупкам соответствует несколько проданных товаров. Например, запись о покупке с номером 32 повторяется два раза, следовательно в таблице purchase_items нашлось две записи с таким номером и это означает, что в этой покупке было куплено 2 товара с номерами 11 и 46.
Номер покупки 46 повторяется три раза, т.е. в рамках этой покупки было куплено 3 товара с номерами 19, 28 и 30.
В полученном результате больше информации, но она не наглядна, поскольку вместо имен покупателей, наименований товаров, указаны их номера. Для того чтобы увидеть вместо номера товара его наименование необходимо найти в таблице товаров имя, которой соответствует номеру товара в последнем запросе. Это делается также при помощи внешнего соединения LEFT JOIN таблицы products с условием ON products.product_id = purchase_items.product_id:
SELECT
purchases.purchase_id,
DATE(purchases.purchase_date,'unixepoch') as purchase_date,
purchases.customer_id,
-- Вместо идентификатора продукта выведем его имя из присоединенной таблицы товаров --
products.product_name
FROM
purchases
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN purchase_items
ON purchase_items.purchase_id = purchases.purchase_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN products
ON products.product_id = purchase_items.product_id
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER)
Результат
purchase_id|purchase_date|customer_id|product_name |
-----------|-------------|-----------|---------------------------------------------|
12|2019-01-24 | 9|Ноутбук HP ENVY 13-ad000 [13-AD008UR 1WS54EA]|
32|2019-01-25 | 2|Швейная машина, оверлок Pfaff Expression 150 |
32|2019-01-25 | 2|Электрочайник Winner WR-126 |
37|2019-01-10 | 1|Мультиварка Marta MT-1981 |
37|2019-01-10 | 1|Микроволновая печь Daewoo KOR-8A4R |
46|2019-01-06 | 10|Пылесос Redmond RV-350 |
46|2019-01-06 | 10|Электрочайник Vitesse VS-118 |
46|2019-01-06 | 10|Весы Unit UBS-2156 |
60|2019-01-24 | 4|Электрочайник Vitesse VS-118 |
60|2019-01-24 | 4|Весы Energy EN-426 |
75|2019-01-06 | 6|Монитор HP V214a |
83|2019-01-29 | 8|Ноутбук HP ProBook 470 G5 [470G5 2UB59EA] |
Добавим к результату столбец производителя, выполнив LEFT JOIN с таблицей manufacturers по условию manufacturers.manufacturer_id = products.manufacturer_id
SELECT
purchases.purchase_id,
DATE(purchases.purchase_date,'unixepoch') as purchase_date,
purchases.customer_id,
products.product_name,
-- Добавим имя производителя и назо вем его для краткости brand --
manufacturers.manufacturer_name as brand
FROM
purchases
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN purchase_items
ON purchase_items.purchase_id = purchases.purchase_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN products
ON products.product_id = purchase_items.product_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ manufacturers --
LEFT JOIN manufacturers
ON manufacturers.manufacturer_id = products.manufacturer_id
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER)
Результат запроса
purchase_id|purchase_date|customer_id|product_name |brand |
-----------|-------------|-----------|---------------------------------------------|-------|
12|2019-01-24 | 9|Ноутбук HP ENVY 13-ad000 [13-AD008UR 1WS54EA]|HP |
32|2019-01-25 | 2|Швейная машина, оверлок Pfaff Expression 150 |Pfaff |
32|2019-01-25 | 2|Электрочайник Winner WR-126 |Winner |
37|2019-01-10 | 1|Мультиварка Marta MT-1981 |Marta |
37|2019-01-10 | 1|Микроволновая печь Daewoo KOR-8A4R |Daewoo |
46|2019-01-06 | 10|Пылесос Redmond RV-350 |Redmond|
46|2019-01-06 | 10|Электрочайник Vitesse VS-118 |Vitesse|
46|2019-01-06 | 10|Весы Unit UBS-2156 |Unit |
60|2019-01-24 | 4|Электрочайник Vitesse VS-118 |Vitesse|
60|2019-01-24 | 4|Весы Energy EN-426 |Energy |
75|2019-01-06 | 6|Монитор HP V214a |HP |
83|2019-01-29 | 8|Ноутбук HP ProBook 470 G5 [470G5 2UB59EA] |HP |
В соответствии с заданием необходимо получить суммарную стоимость проданных товаров каждого производителя до заданной даты. Учитывая это исключим из запроса лишние для решения поставленной задачи столбцы, кроме наименования товара (для ясности) и добавим столбцы с ценой товара и количества. Информация для двух новых столбцов есть в первой присоединенной таблице purchase_items:
SELECT
products.product_name,
manufacturers.manufacturer_name as brand,
purchase_items.product_price,
purchase_items.product_count
FROM
purchases
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN purchase_items
ON purchase_items.purchase_id = purchases.purchase_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN products
ON products.product_id = purchase_items.product_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ manufacturers --
LEFT JOIN manufacturers
ON manufacturers.manufacturer_id = products.manufacturer_id
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER);
Результат выполнения запроса
product_name |brand |product_price|product_count|
---------------------------------------------|-------|-------------|-------------|
Ноутбук HP ENVY 13-ad000 [13-AD008UR 1WS54EA]|HP | 54890| 1|
Швейная машина, оверлок Pfaff Expression 150 |Pfaff | 91850| 1|
Электрочайник Winner WR-126 |Winner | 818.4| 1|
Мультиварка Marta MT-1981 |Marta | 2855.6| 1|
Микроволновая печь Daewoo KOR-8A4R |Daewoo | 5481.3| 2|
Пылесос Redmond RV-350 |Redmond| 5377.9| 1|
Электрочайник Vitesse VS-118 |Vitesse| 5149.1| 2|
Весы Unit UBS-2156 |Unit | 647.9| 2|
Электрочайник Vitesse VS-118 |Vitesse| 5149.1| 1|
Весы Energy EN-426 |Energy | 625.9| 1|
Монитор HP V214a |HP | 22544.5| 2|
Ноутбук HP ProBook 470 G5 [470G5 2UB59EA] |HP | 60159| 2|
Получен список товаров с наименованием производителя каждого товара, цены товара и его количества, проданных до 30 января 2019 года. Если теперь исключить из результата первый столбцец, то получим список производителей проданных товаров
brand |product_price|product_count|
-------|-------------|-------------|
HP | 54890| 1|
Pfaff | 91850| 1|
Winner | 818.4| 1|
Marta | 2855.6| 1|
Daewoo | 5481.3| 2|
Redmond| 5377.9| 1|
Vitesse| 5149.1| 2|
Unit | 647.9| 2|
Vitesse| 5149.1| 1|
Energy | 625.9| 1|
HP | 22544.5| 2|
HP | 60159| 2|
Чтобы найти суммарную стоимость проданных товаров каждого производителя необходимо сложить стоимость всех товаров каждого производителя (с учетом количества в последнем столбце). Для этого необходимо сгруппировать значения первого столбца или, другими словами, объеденить одинаковые значения в первом столбце, используя оператор GROUP BY, а к объединямым произведениям цены на количество применить агрегатную функцию SUM:
SELECT
manufacturers.manufacturer_name as brand,
SUM(purchase_items.product_price*purchase_items.product_count) as total
FROM
purchases
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN purchase_items
ON purchase_items.purchase_id = purchases.purchase_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ purchase_items --
LEFT JOIN products
ON products.product_id = purchase_items.product_id
-- ВНЕШНЕЕ СОЕДИНЕНИЕ ТИПА LEFT JOIN ТАБЛИЦЫ manufacturers --
LEFT JOIN manufacturers
ON manufacturers.manufacturer_id = products.manufacturer_id
WHERE
purchases.purchase_date < CAST(strftime('%s', '2019-01-30') AS INTEGER)
GROUP BY
brand;
Результат
brand |total |
-------|------------------|
Daewoo | 10962.6|
Energy | 625.9|
HP | 220297|
Marta | 2855.6|
Pfaff | 91850|
Redmond| 5377.9|
Unit | 1295.8|
Vitesse|15447.300000000001|
Winner | 818.4|
Найдена суммарная стоимость проданных товаров каждого производителя до 30 января 2019 года.
Вопрос: Как нужно изменить запрос, чтобы выводилась информация для всех производителей, которые есть в базе данных с указанием нулевой суммы для тех производителей, товары которых не были проданы до 30 января 2020 года?
