SQL-запросы

Описание базы данных

Описание БД

Скачать базу данных SQLite

Задание

Постройте SQL-запросы для получения следующей информации из базы данных.

  1. Список первых 3 клиентов, купивших товаров на максимальную сумму в заданном диапазоне дат
  2. Список первых 3 клиентов, купивших максимальное количество товара в заданном диапазоне дат
  3. Товар, который покупали чаще всего в заданном диапазоне дат
  4. Товар, который покупали чаще всего в заданном диапазоне дат по филиалам
  5. Средний чек в диапазоне дат
  6. Средний чек в диапазоне дат по филиалам
  7. Суммарная стоимость проданных товаров каждого филиала в диапазоне дат
  8. Суммарная стоимость проданных товаров в диапазоне дат
  9. Список филиалов по убыванию объема продаж (суммарная стоимость) до заданной даты
  10. Список филиалов по убыванию количества проданного товара до заданной даты
  11. Список товаров с указанием проданного количества товара в каждом магазине до заданной даты
  12. Список товаров, проданных в заданном филиале до заданной даты
  13. Список клиентов, покупавших любой товар в заданном магазине до заданной даты
  14. Список клиентов, покупавших любой товар в двух магазинах сети
  15. Список клиентов, покупавших любой товар во всех магазинах сети
  16. Количество товара каждой категории, проданных в филиале до заданной даты
  17. Количество товара каждой категории, проданных во всех филиалах до заданной даты
  18. Суммарная стоимость товаров по филиалам на заданную дату
  19. Суммарная стоимость товаров во всех филиалах на заданную дату
  20. Количество товаров в каждом филиале на заданную дату
  21. Какого товара меньше всего осталось в каждом из филиалов на заданную дату?
  22. Какого товара больше всего осталось в каждом из филиалов на заданную дату?
  23. Количество товаров каждого наименования во всех филиалах на заданную дату
  24. Остатки товаров каждого наименования в филиале на заданную дату
  25. Список пяти самых дешевых товаров в филиале на заданную дату
  26. Список пяти самых дорогих товаров в филиале на заданную дату
  27. Дата, в которую поступило товара на максимальную сумму в заданный филиал
  28. Суммарная стоимость поступивших товаров на каждую дату поступления в заданный филиал
  29. Суммарная стоимость товаров каждого производителя в филиале на дату
  30. Суммарное количество товаров каждого производителя в филиале на дату
  31. Суммарное количество проданных товаров каждого производителя в филиале до заданной даты
  32. Суммарная стоимость проданных товаров каждого производителя в филиале до заданной даты
  33. Суммарная стоимость проданных товаров каждого производителя во всех филиалах до заданной даты
  34. Суммарное количество проданных товаров каждого производителя во всех филиалах до заданной даты
  35. Суммарное количество поступивших товаров каждого производителя во всех филиалах до заданной даты
  36. Суммарное количество поступивших товаров каждого производителя в филиал до заданной даты
  37. Суммарная стоимость поступивших товаров каждого производителя в филиал до заданной даты
  38. Изменение суммарной стоимости товаров заданного производителя в филиале в диапазоне дат

Методические рекомендации

При работе с базой данных SQLite необходимо учитывать, что даты в таблицах SQLite хранятся в виде целого числа, которое представляет собой количество секунд прошедших с начала (по Гринвичу) первого января 1970 года – Unix-время (этот момент называют Unix-эпохой). Эту особенность представления даты и времени в SQLite следует учитывать при выводе (форматировании) и сравнении дат.

Например, следующий сценарий выводит таблицу имен покупателей с датами совершенных ими покупок до 20 января 2019 года.

select 
	customers.customer_name, strftime("%d.%m.%Y", purchase_date, 'unixepoch') as purchase_date_string
from 
	purchases
left join 
	customers on customers.customer_id  = purchases.customer_id 
where 
	purchase_date < CAST(strftime('%s', '2019-01-20') AS INTEGER)
order by 
	purchase_date;

Результат

customer_name                |purchase_date_string|
-----------------------------|--------------------|
Гусев Пров Дмитриевич        |06.01.2019          |
Борисова Евдокcия Григорьевна|06.01.2019          |
Дорофеев Митофан Демидович   |10.01.2019          |

В этом сценарии для преобразования из внутреннего Unix-времени в “читаемый” текстовый формат даты ГГГГ.ММ.ДД используется функция strftime, которая преобразует числовое значение даны в строку в соответствии с заданным форматом, который передается первым аргументом функции. Второй аргумент функции strftime – столбец таблицы, который содержит дату, “закодированную” целым числом. Третий аргумент – формат хранения даты ‘unixepoch’.

Для того, чтобы сравнить заданную текстовым представлением дату (в примере это ‘2019-01-20’) со значением в таблице, например, в условии WHERE, необходимо преобразовать строку в ГГГГ-ММ-ДД в строку, представляющую целое число секунд с Unix-эпохи, а затем преобразовать эту строку в целое число, которое и будет сравниваться со значениями в столбце purchase_date таблицы. Для преобразования из строки в целое число используется функция CAST(СТРОКА AS INTEGER), например результатом работы функции CAST(‘123’ AS INTEGER) будет целое число 123.

При работа с базой данных из среды Python, преобразование даты в формат Unix-времени и обратно можно выполнять в коде, используя метод объекта datetimedatetime.timestamp(), например:

import sqlite3
import datetime
from tabulate import tabulate 

path = "./drive/My Drive/databases/goods.db"

conn = sqlite3.connect(path)
cursor = conn.cursor()

sql  = """
SELECT 
  customers.customer_name, purchases.purchase_id, COUNT(purchase_items.product_id)
FROM
  purchase_items 
LEFT JOIN 
  purchases on purchase_items.purchase_id = purchases.purchase_id
LEFT JOIN 
  customers on customers.customer_id = purchases.customer_id
WHERE 
  purchases.purchase_date BETWEEN ? AND ? 
GROUP BY 
  purchases.purchase_id
ORDER BY 
  customer_name
"""

cursor.execute(sql,(datetime.datetime(2019,8,1).timestamp(),  
                    datetime.datetime(2020,1,1).timestamp() ))

rows = cursor.fetchall()
print(tabulate(rows))

cursor.close()
conn.close()