CarSales 프로젝트 SQL 분석[Part 1]

안녕하세요! 이번 프로젝트는 CarSales 데이터를 사용하여 SQL 쿼리와 파이썬을 활용한 분석을 진행하는 프로젝트입니다. 차량 판매 데이터는 여러 변수들, 예를 들어 차량 가격, 연식, 주행 거리, 연료 종류, 차체 형태 등의 정보를 포함하고 있습니다. 이러한 데이터들을 분석하여 유용한 인사이트를 도출하고, 이를 시각적으로 표현하는 것이 주요 목표입니다.

 

이 프로젝트의 첫 번째 단계에서는 SQL을 이용해 데이터를 로딩하고, 여러 테이블을 생성한 후, 다양한 분석 쿼리를 실행하여 데이터의 특성을 파악합니다. 또한, 데이터 분석 후에는 Tableau를 사용해 시각화를 진행하여 더욱 직관적으로 분석 결과를 전달하고자 합니다. 이 글에서는 첫 번째 단계인 데이터 로딩과 SQL 쿼리 실행, 그리고 Tableau에서의 시각화 작업을 다룰 것입니다.

 

데이터셋:

데이터:	Kaggle AutoTrader (AT) Car Sale Adverts Dataset
개수	387,764

 

첫째, SQL을 사용하여 데이터를 테이블로 나누어 정리했어요. 데이터가 더 명확해지고 분석에 더욱 도움이 되서 테이블로 나눴어요.

우선 CSV 데이터를 MySQL에 AML 테이블로 업로드했습니다. 이후 데이터를 보다 체계적으로 관리하기 위해 다음과 같은 4개의 테이블을 생성했습니다:

1. FuelType
2. BodyType
3. VehicleCondition
4. Cars

각 테이블에 Primary Key와 Foreign Key를 설정했으며, ID 컬럼에는 AUTO_INCREMENT를 적용했습니다. 마지막으로 원본 테이블(aml)에서 필요한 데이터를 각각의 테이블로 옮겼습니다. 이제 각 단계와 테이블 생성 과정을 자세히 살펴보겠습니다~~ 

 

1. FuelType 테이블

USE CarSales;

-- create table fueltypes
CREATE TABLE FuelTypes (
    fuel_type_id  INTEGER AUTO_INCREMENT PRIMARY KEY,
    fuel_type VARCHAR(128) NOT NULL
);

-- insert into fueltypes
INSERT INTO FuelTypes (fuel_type)
VALUES ('Diesel');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Diesel Hybrid');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Diesel Plug-in Hybrid');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Electric');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Natural Gas');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Petrol');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Petrol Hybrid');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Petrol Plug-in Hybrid');

INSERT INTO FuelTypes (fuel_type)
VALUES ('Bi Fuel');

 

2. BodyType 테이블

-- create table bodytype
create table BodyTypes
(body_type_id  INTEGER AUTO_INCREMENT PRIMARY KEY,
    body_type VARCHAR(128) NOT NULL);

-- insert into bodytypes
INSERT INTO BodyTypes (body_type)
VALUES ('Saloon');

INSERT INTO BodyTypes (body_type)
VALUES ('Combi Van');

INSERT INTO BodyTypes (body_type)
VALUES ('MPV');

INSERT INTO BodyTypes (body_type)
VALUES ('Car Derived Van');

INSERT INTO BodyTypes (body_type)
VALUES ('Camper');

INSERT INTO BodyTypes (body_type)
VALUES ('Hatchback');

INSERT INTO BodyTypes (body_type)
VALUES ('SUV');

INSERT INTO BodyTypes (body_type)
VALUES ('Chassis Cab');

INSERT INTO BodyTypes (body_type)
VALUES ('Panel Van');

INSERT INTO BodyTypes (body_type)
VALUES ('Limousine');

INSERT INTO BodyTypes (body_type)
VALUES ('Coupe');

INSERT INTO BodyTypes (body_type)
VALUES ('Pickup');

INSERT INTO BodyTypes (body_type)
VALUES ('Convertible');

INSERT INTO BodyTypes (body_type)
VALUES ('Estate');

INSERT INTO BodyTypes (body_type)
VALUES ('Minibus');

INSERT INTO BodyTypes (body_type)
VALUES ('Window Van');

 

3. VehicleConditions 테이블

-- create table vehicle conditions

CREATE TABLE VehicleConditions (
    vehicle_condition_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    vehicle_condition VARCHAR(128) NOT NULL
);

-- insert into vehicleconditions
insert into vehicleconditions (vehicle_condition) values ('USED');
insert into vehicleconditions (vehicle_condition) values ('NEW');

 

4. Cars 테이블

CREATE TABLE Cars (
    car_id INT AUTO_INCREMENT PRIMARY KEY,  
    public_reference VARCHAR(128),
    reg_code VARCHAR(128),
    mileage INT,                            
    price DECIMAL(12,2),                      
    year_of_registration INT,                 
    fuel_type_id INT,                       
    body_type_id INT,                        
    vehicle_condition_id INT,                
    crossover_car_and_van VARCHAR(128),     
    FOREIGN KEY (fuel_type_id) REFERENCES FuelTypes(fuel_type_id),
    FOREIGN KEY (body_type_id) REFERENCES BodyTypes(body_type_id),
    FOREIGN KEY (vehicle_condition_id) REFERENCES VehicleConditions(vehicle_condition_id)
);

 

데이터 세트에서 "year_of_registration" 변수는 Vehicle Condition이 NEW인 경우 ''로 표시되어 있었습니다. 이 값을 NULL로 변환하여 데이터를 정리했어요.

INSERT INTO Cars (
    public_reference,
    reg_code,
    mileage,
    price,
    year_of_registration,
    fuel_type_id,
    body_type_id,
    vehicle_condition_id,
    crossover_car_and_van
)
SELECT 
    aml.PUBLIC_REFERENCE,
    aml.REG_CODE,
    aml.MILEAGE,
    aml.PRICE,
    CASE 
        WHEN aml.YEAR_OF_REGISTRATION = '' THEN NULL 
        ELSE aml.YEAR_OF_REGISTRATION 
    END AS year_of_registration,
    ft.fuel_type_id,
    bt.body_type_id,
    vc.vehicle_condition_id,
    aml.CROSSOVER_CAR_AND_VAN
FROM AML_dataset aml
JOIN FuelTypes ft ON ft.fuel_type = aml.FUEL_TYPE
JOIN BodyTypes bt ON bt.body_type = aml.BODY_TYPE
JOIN VehicleConditions vc ON vc.vehicle_condition = aml.VEHICLE_CONDITION;

 

데이터 세트의 year_of_registration 값을 확인한 결과, 매우 비정상적인 연도 값들이 포함되어 있었어요. 이는 잘못 입력된 값으로 판단되어, 1950년 이전의 연도 값을 가진 행들을 Cars 테이블에서 삭제했어요.

DELETE FROM Cars
WHERE year_of_registration < 1950 
   OR year_of_registration > YEAR(CURDATE());

 

이제 SQL query로 분석을 조금 하였습니다. 

 

1) 연료 유형별 평균 가격 분석

SELECT fuel_type, ROUND(AVG(price), 3) AS average_price
FROM Cars
JOIN FuelTypes ON Cars.fuel_type_id = FuelTypes.fuel_type_id
GROUP BY fuel_type;

 

 

이후, 이 결과를 Tableau에서 시각화할 것입니다. 이를 위해 SQL 쿼리를 실행한 후, 오른쪽에 있는 내보내기(Export) 아이콘을 클릭하여 데이터를 저장합니다. 저는 데이터를 CSV 파일 형식으로 저장했습니다.

 

Tableau를 열고, 왼쪽 상단의 Connect에서 Text File을 선택한 후 저장한 CSV 파일을 선택합니다. 그러면 왼쪽 패널에 데이터의 변수(컬럼)들이 표시되는데, 원하는 변수를 열(Column)이나 행(Row)으로 드래그 앤 드롭하여 차트를 생성해요!

 

 

2) 연도별 평균 가격 분석

SELECT 
    c.year_of_registration,
    AVG(c.price) AS average_price
FROM Cars c
GROUP BY c.year_of_registration
ORDER BY c.year_of_registration;

3) 자동차의 가격대 분포

SELECT 
    CASE
        WHEN c.price < 10000 THEN 'Under 10k'
        WHEN c.price BETWEEN 10000 AND 20000 THEN '10k-20k'
        WHEN c.price BETWEEN 20000 AND 30000 THEN '20k-30k'
        ELSE 'Above 30k'
    END AS price_range,
    COUNT(*) AS total_cars
FROM Cars c
GROUP BY price_range;

 

 

4) 연도별 차량 등록 수

SELECT 
    year_of_registration, 
    COUNT(*) AS registration_count
FROM Cars
GROUP BY year_of_registration
ORDER BY year_of_registration;

 

 

이렇게 첫 번째 단계에서는 데이터를 SQL 테이블에 업로드한 후, FuelTypes, BodyTypes, VehicleConditions, Cars와 같은 네 개의 테이블로 데이터를 분류했습니다. 각 테이블에 필요한 정보를 추출하여 분석을 시작했습니다. 이후, SQL 쿼리를 사용하여 데이터를 분석하고, Tableau를 통해 시각화했습니다. 다음 단계에서는 Python과 SQL을 결합하여 더 심층적인 분석을 진행할 계획입니다.