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

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

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

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

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

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

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

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

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

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

Как мы можем видеть, в 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-файл с именем “город”, содержащий необходимые нам данные внутри.

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

Для этой базы данных мы будем использовать оба метода. Первый метод – это метод импорта. во-первых, мы должны убедиться, что тип данных (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"
Создание базы данных для маркетплейса с помощью SQL

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

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

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

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

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

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

Помимо использования функции импорта файла, мы также можем использовать запрос 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;

Результат:

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

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')

До:

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

После:

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

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';

Результат:

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

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;

Результат:

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

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;

Результат:

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

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;
Создание базы данных для маркетплейса с помощью SQL

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

Результат:

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

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;

Результат:

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

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;

Результат:

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

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';

Результат:

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

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

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

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

+1
0
+1
6
+1
0
+1
1
+1
0
Просмотры: 2 288

Похожие записи

Ответить

Ваш адрес email не будет опубликован. Обязательные поля помечены *