Создание базы данных для маркетплейса с помощью SQL

SQL (Structured Query Language) - это язык программирования, предназначенный для управления данными в реляционных базах данных. Он позволяет извлекать, вставлять, обновлять и удалять данные из баз данных, а также создавать и изменять структуры баз данных, такие как таблицы, индексы и представления.

Одна из простых причин изучения SQL заключается в том, что это широко используемый язык для работы с данными. Независимо от того, являетесь ли вы аналитиком данных, разработчиком программного обеспечения, специалистом по бизнес-аналитике или любым другим специалистом, работающим с данными, навыки SQL помогут вам эффективно управлять большими объёмами данных и анализировать их.

SQL важен, поскольку он является основой многих бизнес-приложений и процессов принятия решений, основанных на данных. Обладая знаниями SQL, вы можете извлекать ценную информацию из данных и принимать обоснованные решения на основе этих данных. SQL также необходим для управления и поддержки баз данных, критически важных компонентов многих программных систем и приложений. В целом, SQL - важный навык для любого, кто хочет работать с данными или создавать приложения, управляемые данными.

Чтобы узнать больше о SQL и реляционных базах данных, мы можем создать проект для практики. В этом проекте перед вами стоит задача создать реляционную базу данных для веб-сайта, предлагающего продажу подержанных автомобилей. Общее описание этого проекта заключается в том, что любой желающий может предлагать свои товары (подержанные автомобили) в виде рекламы, а потенциальные покупатели могут осуществлять поиск по нескольким категориям. клиент определяет несколько параметров, которым мы должны соответствовать при создании базы данных, которая включает:

пользователь может продать более одного автомобиля, но сначала должен предоставить личные данные (имя пользователя, контактные данные, место жительства).
Пользователи могут размещать объявления о продаже автомобилей при условии, что в объявлении должна содержаться информация об автомобиле (марка, модель, тип, коробка передач, год выпуска и т.д.).
Каждый пользователь может выполнить поиск предлагаемого автомобиля на основе местоположения пользователя продавца, марки автомобиля и типа кузова.
Существует функция ставок для пользователей, которые разрешают “делать ставки” на автомобили, которые они продают.
транзакции совершаются вне Сети, поэтому они не включены в объём работ

На основе параметров, предоставленных пользователем, мы создадим базу данных, которая сможет вместить необходимые таблицы, столбцы и данные. Для базы данных необходимо несколько таблиц, которые включают таблицы для размещения пользователей, таблицы для размещения транзакций, таблицы для хранения информации о проданных автомобилях, таблицы, связанные с информацией о местоположении, и таблицы для функций ставок.

Ниже приведен ERD, который мы ранее представляли себе на основе параметров, предоставленных клиентом. Он содержит 5 реляционных таблиц. Первая строка - это название таблицы. Остальные строки представляют столбцы в таблице.

Как мы можем видеть, в ERD есть 5 таблиц.

пользователи: в этой таблице содержится информация, относящаяся к пользователям, зарегистрированным в базе данных, такая как user_id, местоположение, контакт и т.д. Поскольку user_id уникален, user_id будет использоваться в качестве первичного ключа.

коммерция: содержит информацию об автомобилях, проданных пользователем, такую как описания, предложения, цены и т.д. Поскольку для представления каждой продажи требуется уникальный ключ, в качестве первичного ключа используется commerce_id.

ставки: содержит информацию о функции ставок.

car_details : Эта таблица содержит информацию и данные об автомобилях, проданных пользователем, поскольку каждый автомобиль уникален, car_id используется в качестве первичного ключа.

Город: содержит информацию, относящуюся к местоположению.

Чтобы создать таблицу из базы данных, нам нужно выполнить запрос. Мы можем использовать этот запрос для создания базы данных, подобной приведённой выше ERD:

-- this queries will be used to create a marketplace to sell car -- some tables will be filled by datas from csv files and other tables will be filled with datas from queries -- we start by creating needed tables by using DDL CREATE TABLE "users" ( "user_id" varchar(30) NOT NULL, "first_name" varchar(50) NOT NULL, "last_name" varchar(50) NOT NULL, "city_id" int NOT NULL, "phone_num" varchar(50) NOT NULL, "email" varchar(50) NOT NULL, CONSTRAINT "pk_user" PRIMARY KEY ( "user_id" ) ); CREATE TABLE "commerce" ( "commerce_id" serial NOT NULL, "user_id" varchar(30) NOT NULL, "car_id" int NOT NULL, "city_id" int NOT NULL, "description" text, "price" int NOT NULL, "bid" bool NOT NULL, "post_date" date NOT NULL, CONSTRAINT "pk_commerce" PRIMARY KEY ( "commerce_id" ) ); CREATE TABLE "car_details" ( "car_id" serial NOT NULL, "brand" varchar(50) NOT NULL, "type" text NOT NULL, "model" varchar(50) NOT NULL, "year" int NOT NULL, "transmision" text NOT NULL, "kilometer" int NOT NULL, "fuel_type" text, "engine_size" text, CONSTRAINT "pk_car_details" PRIMARY KEY ( "car_id" ) ); CREATE TABLE "city" ( "city_id" int NOT NULL, "city_name" text NOT NULL, "latitude" decimal(8,6) NOT NULL, "longitude" decimal(9,6) NOT NULL, CONSTRAINT "pk_city" PRIMARY KEY ( "city_id" ) ); CREATE TABLE "bid" ( "bid_id" serial NOT NULL, "car_id" int NOT NULL, "user_id" varchar(30) NOT NULL, "bid_date" date NOT NULL, "bid_price" int NOT NULL, "bid_status" text NOT NULL, CONSTRAINT "pk_bid" PRIMARY KEY ( "bid_id" ) );

Мы создали таблицы, но оставили их пустыми. Теперь мы заполним эти таблицы соответствующими данными, а затем запустим запросы для извлечения данных из них. Чтобы заполнить базу данных, нам нужно вставить наши данные или датасет в таблицы.

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

Для этой базы данных мы будем использовать данные, сгенерированные на Python.

#import liblary import csv #create headers for city table #since we already know what the header, the headers wont make it to the file. it's just for a reference headers = ['city_id', 'city_name', 'latitude', 'longitude'] #create data lists for city table #insert required data including latitude and longitude coordinates city = [ [3171, 'Kota Jakarta Pusat', -6.186486, 106.834091], [3172, 'Kota Jakarta Utara', -6.121435, 106.774124], [3173, 'Kota Jakarta Barat', -6.1352, 106.813301], [3174, 'Kota Jakarta Selatan', -6.300641, 106.814095], [3175, 'Kota Jakarta Timur', -6.264451, 106.895859], [3573, 'Kota Malang', -7.981894, 112.626503], [3578, 'Kota Surabaya', -7.289166, 112.734398], [3471, 'Kota Yogyakarta', -7.797224, 110.368797], [3273, 'Kota Bandung', -6.9147444, 107.6098111], [1371, 'Kota Padang', -0.95, 100.3530556], [1375, 'Kota Bukittinggi', -0.3055556, 100.3691667], [6471, 'Kota Balikpapan', -1.2635389, 116.8278833], [6472, 'Kota Samarinda', -0.502183, 117.153801], [7371, 'Kota Makassar', -5.1333333, 119.4166667], [5171, 'Kota Denpasar', -8.65629, 115.222099], ] with open('city.csv', 'w', encoding='utf-8', newline='') as f: writer = csv.writer(f) #write multiple rows writer.writerows(city) #sql file will be created at the destination folder

Мы используем коды для команды Python для создания CSV-файлов с желаемыми данными. Возможно, сделать это с помощью Excel было бы намного, намного проще. После того, как мы выполним код, автоматически будет создан CSV-файл с именем "город", содержащий необходимые нам данные внутри.

Для этой базы данных мы будем использовать оба метода. Первый метод - это метод импорта. во-первых, мы должны убедиться, что тип данных (string, integer, boolean и т.д.) соответствует параметрам, которые мы создали в запросе на создание таблицы.

CREATE TABLE "commerce" ( "commerce_id" serial NOT NULL, "user_id" varchar(30) NOT NULL, "car_id" int NOT NULL, "city_id" int NOT NULL, "description" text, "price" int NOT NULL, "bid" bool NOT NULL, "post_date" date NOT NULL, CONSTRAINT "pk_commerce" PRIMARY KEY ( "commerce_id"

Во-вторых, щёлкните правой кнопкой мыши по таблице, из которой мы хотим ввести данные, и выберите импорт.

В-третьих, выберите файл для загрузки и нажмите кнопку ОК.

После успешной загрузки должно появиться уведомление, подобное этому.

Помимо использования функции импорта файла, мы также можем использовать запрос INSERT для вставки данных в базу данных. Для таблицы commerce мы введём данные, используя метод INSERT. Ниже представлен синтаксис:

INSERT INTO table (column1, column2,...) VALUES (value1), (value2), .... (value-n);

Поскольку нам нужно настроить имена таблиц и столбцов, синтаксис для таблицы commerce будет следующим:

insert into commerce (user_id, car_id, city_id, description, price, bid, post_date) values('samarindamobil', 1, 6472, 'WTS Yaris 2016 bekas dokter. kondisi interior eksterior rapih. hubungi kontak yang tersedia!!', 125000000, True, '2020-03-01'), (value2) ..... (value-n);

После того, как мы наполним базу данных созданным нами фиктивным набором данных, мы можем добавить связи между таблицами, чтобы она выглядела как ERD выше, где есть линии, соединяющие таблицы. Чтобы создать эти отношения, мы должны вставить внешний ключ между таблицами. Чтобы сделать это, мы добавим ограничения внешнего ключа к этим таблицам и свяжем их с исходной таблицей следующим образом:

ALTER TABLE users ADD CONSTRAINT fk_city FOREIGN KEY(city_id) REFERENCES city(city_id); ALTER TABLE commerce ADD CONSTRAINT fk_users FOREIGN KEY(user_id) REFERENCES users(user_id); ALTER TABLE commerce ADD CONSTRAINT fk_car_details FOREIGN KEY(car_id) REFERENCES car_details(car_id);

Наконец-то мы создали рабочую базу данных! Но мы ещё не закончили. Нам нужно выполнить несколько запросов, чтобы убедиться, что созданная нами база данных и введённый нами фиктивный файл достаточны для нашего клиента. Давайте реализуем 10 запросов.

1. Поиск автомобилей 2015 года выпуска и старше:

select car_details.car_id, car_details.brand, car_details.type, car_details.model, car_details.year, commerce.price FROM car_details INNER JOIN commerce ON car_details.car_id=commerce.car_id WHERE year > 2015;

Результат:

2. Добавление одной новой записи о заявке на товар:

INSERT INTO bid (bid_id, car_id, user_id, bid_date, bid_price, bid_status) VALUES (34, 76, 'rangers123', '2021-09-02', 50000000, 'on')

До:

После:

3. Просмотр всех проданных автомобилей в 1 аккаунте, начиная с самого последнего (используя в качестве примера идентификатор пользователя ‘kolektormobil’)

select car_details.car_id, car_details.brand, car_details.type, car_details.model, car_details.year, commerce.price, commerce.post_date, commerce.user_id FROM car_details INNER JOIN commerce ON car_details.car_id=commerce.car_id WHERE user_id = 'kolektormobil';

Результат:

4. Поиск самых дешевых подержанных автомобилей по ключевым словам (ключевое слово: Yaris)

select car_details.car_id, car_details.brand, car_details.model, car_details.year, commerce.price, commerce.post_date FROM car_details INNER JOIN commerce ON car_details.car_id=commerce.car_id WHERE model = 'Yaris' order by price asc;

Результат:

5. Поиск ближайшего подержанного автомобиля на основе идентификатора города, кратчайшее расстояние рассчитывается на основе широты и долготы (city_id 3173)

SELECT commerce.commerce_id AS product_id, car_details.brand, car_details.model, car_details.year, commerce.price, SQRT((city.latitude - given_city.latitude)^2 + (city.longitude - given_city.longitude)^2) AS distance FROM commerce JOIN car_details ON commerce.car_id = car_details.car_id JOIN users ON commerce.user_id = users.user_id JOIN city ON commerce.city_id = city.city_id CROSS JOIN (SELECT latitude, longitude FROM city WHERE city_id = 3173) AS given_city -- change given_city_id to 3173 WHERE car_details.year <= extract(year from now()) - 3 -- used car criteria, assuming car age greater than or equal to 3 years is used ORDER BY distance ASC LIMIT 10;

Результат:

6. Аналитический запрос: Рейтинг популярности модели автомобиля на основе суммы ставки:

SELECT c.model, COUNT(DISTINCT c.car_id) AS car_count, COUNT(b.bid_id) AS bid_count FROM car_details c LEFT JOIN bid b ON c.car_id = b.car_id GROUP BY c.model ORDER BY SUM(b.bid_price) asc;

7. Аналитический запрос: Сравнение цен на автомобили на основе средней цены по городу:

SELECT city.city_name, car_details.brand, car_details.model, car_details.year, commerce.price, ROUND(AVG(commerce.price) OVER (PARTITION BY city.city_name, car_details.brand, car_details.model, car_details.year), 2) as avg_car_city FROM commerce JOIN city ON commerce.city_id = city.city_id JOIN car_details ON commerce.car_id = car_details.car_id ORDER BY city_name ASC, brand ASC, model ASC, year ASC

Результат:

8. Аналитический запрос: требуется сравнить дату, когда пользователь сделал ставку, со следующей ставкой, а также цену предложения, указанную в предложении для модели автомобиля (пример модели "Camry").

SELECT c.model, b1.user_id, b1.bid_date AS first_bid_date, b2.bid_date AS next_bid_date, b1.bid_price AS first_bid_price, b2.bid_price AS next_bid_price FROM bid b1 JOIN car_details c ON b1.car_id = c.car_id LEFT JOIN bid b2 ON b1.car_id = b2.car_id AND b2.bid_date > b1.bid_date WHERE c.model = 'Camry' AND b2.bid_id = ( SELECT MIN(bid_id) FROM bid WHERE car_id = b1.car_id AND bid_date > b1.bid_date ) ORDER BY b1.bid_date ASC;

Результат:

9. Аналитический запрос: Сравнение процентной разницы в средней цене автомобиля по моделям и средней цене предложения, предложенной клиентами за последние 6 месяцев:

SELECT cd.model, ROUND(AVG(c.price)::numeric, 2) AS avg_price, ROUND(AVG(b.bid_price)::numeric, 2) AS avg_bid_6month, ROUND((AVG(c.price) - AVG(b.bid_price))::numeric, 2) AS difference, ROUND(((AVG(c.price) - AVG(b.bid_price)) / AVG(c.price) * 100)::numeric, 2) AS difference_percent FROM car_details cd INNER JOIN commerce c ON cd.car_id = c.car_id INNER JOIN bid b ON c.car_id = b.car_id WHERE b.bid_date >= (NOW() - INTERVAL '6 months') GROUP BY cd.model ORDER BY difference_percent DESC;

Результат:

10. Аналитический запрос: Создайте функцию отображения средней цены предложения марки и модели автомобиля за последние 6 месяцев:

SELECT brand, model, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '6 months') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 6 PRECEDING AND 1 PRECEDING) AS m_min_6, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '5 months') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 5 PRECEDING AND 0 PRECEDING) AS m_min_5, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '4 months') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING) AS m_min_4, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '3 months') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 3 PRECEDING AND 0 PRECEDING) AS m_min_3, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '2 months') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS m_min_2, AVG(bid_price) FILTER (WHERE bid_date >= NOW() - INTERVAL '1 month') OVER (PARTITION BY brand, model ORDER BY bid_date ASC ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING) AS m_min_1 FROM car_details JOIN bid ON car_details.car_id = bid.car_id WHERE brand = 'honda' AND model = 'Civic';

Результат:

Наконец, мы смогли создавать базы данных, заполнять базы данных, выстраивать связи с базами данных и выполнять базовые запросы к более сложным запросам.

Спасибо вам за чтение!

Доступ ко всем кодам и наборам данных можно получить по этой ссылке: https://github.com/frhnkl/Database-for-Car-Marketplace

Статья была взята из этого источника:

uproger.com

Создание базы данных для маркетплейса с помощью SQL