БИЗНЕС-ЗАДАЧА по курсу 1Т Архитектор данных
Михаил -- индивидуальный предприниматель (ИП), и его заказчики -- такие же ИП и частные лица. Но сегодня Михаил узнал, что один из его заказчиков, Григорий (тоже ИП), смог получить заказ, размещенный на сайте госзакупок. До этого Михаил полагал, что на рынке госпоставщиков работают в основном крупные компании с большим опытом, но сейчас он задумался. Григорий поделился с Михаилом базой данных заказов и контрактов с сайта госзакупок, которую он напарсил сам и которой пользовался для поиска подходящих заказов. Вернувшись домой, Михаил просит свою дочь Лидию, только что окончившую курс 1Т, посчитать, сколько контрактов выигрывают физические лица в каждом из регионов РФ.
Вот точная формулировка задания: Сколько КОНТРАКТОВ в каждом регионе выигрывают физические лица из общего числа контрактов в этом регионе. Обрати внимание, что в задании две отдельные сущности: заказы и контракты, и мы считаем именно контракты. При этом регион определяется местоположением организатора закупки из таблицы заказов.
В результирующей таблице каждая строка будет соответствовать одному региону РФ -- местоположению компании-организатора закупки, и содержать количество контрактов, заключенных с поставщиками-физическими лицами.
Подсказки: Для скрипта создания таблиц может потребоваться адаптация к используемому тобой диалекту SQL. Как узнать, является ли поставщик физическим лицом? Очень просто: ИНН физических лиц содержит 12 цифр, а ИНН юридических -- только 10.
Как узнать код региона? Это -- первые 2 цифры ИНН. Для извлечения кода из ИНН используй функцию SUBSTR.
Помоги Лидии решить эту задачу, включающую развертывание БД и сопутствующие шаги.
Датасет для создания таблицы заказов (orders) Датасет для создания таблицы контрактов (contracts) Список регионов РФ (regions)
ПОЛНОЕ ЗАДАНИЕ
- Мы предполагаем, что по итогам первого модуля у тебя уже есть виртуальная или физическая машина с Linux, на которой установлена одна из СУБД (PostgreSQL, ClickHouse или MySQL) и подходящий клиент (напр. DBeaver). Еще нужно будет создать папку для ответов, в которую ты будешь складывать требуемые скриншоты и скрипты.
- Просмотреть журнал, чтобы убедиться, что за последнее время на машине не было ошибок (err). Сделать скриншот screen1.jpg, сохранить в папку.
- Проверить, что у СУБД включен автозапуск и сервис работает (при необходимости включить автозапуск и сервис), вывести статус сервиса. Сделать скриншот screen2.jpg, сохранить в папку.
- В списке процессов найти процесс(ы), соответствующие сервису СУБД. Сделать скриншот screen3.jpg, сохранить в папку.
- При помощи cURL загрузить датасеты orders.csv и contracts.csv и вывести подробное содержимое директории с этими файлами. При помощи подходящей утилиты подсчитать число строк в каждом из файлов. Сделать скриншот работы команды cURL (можно для одного файла), листинга и подсчета строк, желательно одним скриншотом. Скриншот назвать screen4.jpg, сохранить в папку.
- Создать соответствующие таблицы в БД, импортировать датасеты, сравнить количество строк в получившихся таблицах со значениями, полученными в предыдущем пункте.
Сделать аналитический запрос для решения нашей бизнес-задачи. Результаты запроса сохранить в csv-файл table5.csv, а SQL-скрипт -- в файл code5.sql. Оба файла сохранить в папку. 6*) Тот же запрос сделать в PySpark SQL, сравнить результаты с п.5, выводы написать в комментарии к коду. Код сохранить в файл code6.py (или code6.ipynb). Сделать скриншот screen6.jpg результатов выполнения кода. Оба файла сохранить в папку. 7*) Провести визуализацию полученного в п.5 csv-файла в Matplotlib/Seaborn -- построить столбчатую диаграмму для топ-10 регионов, или любой другой график на свой вкус с описанием. Код сохранить в файл code7.py (или code7.ipynb). Сделать скриншот screen7.jpg результатов выполнения кода. Оба файла сохранить в папку. 8**) Запрос как в п.5 (бизнес-задача), но с подсчетом не абсолютного количества контрактов, а процента контрактов в каждом регионе, которые выигрывают физические лица, из общего числа контрактов в этом регионе. Результаты запроса сохранить в csv-файл table8.csv, а SQL-скрипт -- в файл code8.sql. Оба файла сохранить в папку
Задания 6, 7 и 8 -- необязательные.