Пример построения сложного запроса к базе данных


Составить запрос к базе данных, формирующий список производителей товаров, проданных до заданной даны во всех филиалах (магазинах), с указанием суммарной стоимости проданных товаров для каждого производителя.

Начнём с простого запроса к таблице покупок. Выведем номер (идентификатор) покупки, дату и идентификатор покупателя – вся эта информация есть в таблице 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 года?

2020

2019

2018