SQL, 데이터들로부터 인사이트에 맞게 가공하기
[코드스테이츠 PMB 7기] SQL을 사용해 데이터 추출하기
오늘의 과제는 SQL을 활용한 데이터 추출입니다. w3schools에 있는 SQL TryitEditor를 활용해서 예시로 간단하게 주어진 데이터를 추출해보는 작업입니다.
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
*w3schools는 온라인상에서 손쉽게 손쉽게 sql을 비롯한 웹기술을 배우는 교육용 웹사이트입니다.
고객 데이터 살펴보기
전체 데이터베이스를 살펴보면 총 8개의 테이블로 구성되어 있음을 볼 수 있습니다. 각 테이블별로는 배송형 서비스에 관한 데이터들이 저장되어 있음을 알 수 있습니다. 이 내용들을 토대로 각 테이블별 관계와 내용들을 정리하고자 합니다.
본 예시에서는 각 테이블별로 Primary Key에 대해서 "XXX"ID라는 형태로 네이밍을 해서 보다 원활하게 데이터간 관계를 파악할 수 있도록 제시해두고 있습니다. 실제 데이터를 가공할 때에도 PI를 식별할 수 있도록 네이밍을 지정하면 각 테이블별 관계를 파악할 때에 보다 용이하게 데이터를 분석할 수 있을 것으로 보입니다.
DB 스키마 정리하기
DB 스키마(Schema)는 각 데이터베이스 간의 관계를 한 눈에 볼 수 있도록 정리한 표를 이야기합니다. 전체 데이터베이스의 구조도를 파악할 수 있기 때문에 이를 토대로 의사결정과 데이터 분석을 할 때에 용이하게 사용이 가능합니다.
DB 스키마의 작성은 ERD(Entity Relationship Diagram, 개체-관계 다이아그램)의 방식으로 많이 표현하며, 이를 그릴 수 있는 툴은 아래 사이트를 활용하였습니다.
Flowchart Maker & Online Diagram Software
위의 스키마를 살펴보면 기본적으로 데이터 안에 'XXID'형태의 키가 들어간 데이터들이 다른 테이블들의 Primary Key를 활용해서 데이터를 만들어내는 부분임을 알 수 있습니다. 여기서는 Orders와 OrderDetails겠죠. 따라서 이들을 맨 앞에 배치합니다.
이후 각 키들이 어느 테이블들을 참조하는지를 파악해서 각 부분들에 대한 관계도를 작성합니다. Orders의 경우 CustomerID와 EmployeeID, 그리고 ShipperID를 통해 데이터가 만들어지기 때문에 각 테이블들과 관계도를 작성합니다.
OrderDetails의 경우, OrderID와 ProductID를 참조하기 때문에 Orders와 Product의 테이블과 관계를 맺지만, 다시 Product는 SupplierID와 CategoryID를 참조함을 알 수 있습니다. 따라서 1 depth를 더 들어가서 Suppliers와 Categories 테이블로부터 값을 불러냅니다. 총 3단계 형태의 스키마가 완성됩니다.
SQL 문장 실행
이렇게 만들어진 DB스키마를 토대로 데이터를 추출해서 원하는 내용만 확인할 수 있는 명령어를 만들 수 있습니다.
1. 미국에 사는 고객들의 주문일자
고객들의 국적이 미국(USA)일 경우 이 사람들의 주문일자는 어떻게 확인할 수 있을까요?
이 경우 먼저 고객들의 국적을 추출하고 이 중 USA에 살고 있는 고객들의 OrderDate를 확인하도록 테이블을 만들면 됩니다.
(1) 국적과 주문일자 추출하기
국적(Country)과 주문일자(OrderDate)는 각각 고객(Customers)와 주문(Orders) 테이블에 위치해 있습니다. 따라서 먼저는 각 국적별로 주문일자가 어떻게 되는지를 파악하는 것이 필요합니다.
앞선 스키마에서 주문(Orders)는 CustomerID를 키값으로 가지고 있기 때문에 고객(Customers) 테이블과 연결된다는 사실을 알 수 있었습니다. 이를 활용해서 명령어를 만들면 다음과 같습니다.
Customers의 country와 orders의 orderdate를 출력하되,
customers가 orders 안에 합쳐져 있는데
customers의 CustomerID가 orders의 CustomersID와 동일하다
라는 명령어 입니다.
이 경우, SQL은 customers의 CustomerID와 orders의 CustomerID를 같은 값으로 인식하여 두 테이블을 연결시키고, 그렇게 연결된 테이블에서 각각 country와 orderdate값을 추출하게 됩니다.
위의 명령어만 수행할 경우 아래와 같은 결과가 도출됩니다.
196개의 데이터가 추출되었습니다.
(2) 미국에 사는 고객들의 주문일자 추출하기
이제 이 중에서 미국(USA)에 사는 고객들만을 추출해서 해당 고객들의 주문일자를 확인하면 됩니다.
미국에 사는 고객들의 경우, 조건절 Where을 활용하여 구분하면 됩니다.
따라서 최종 명령어는 다음과 같습니다.
customers의 country가 'USA'일 경우 country와 orderdate값을 추출하는 겁니다. 이 경우 값은 아래와 같이 출력됩니다.
2. 음료를 구매한 사람들의 국적별 수량
이 항목은 크게 3가지로 나누어서 나타낼 수 있습니다.
1-Categories에서 'Beverage'에 해당하는 값들 중에서
2-Customers의 'Country'를 추출하고
3-OrderDetails의 'Quantity'를 확인합니다
1번 항목의 경우 조건절 Where가 들어가고 2번 항목의 Country와 3번 항목의 Quantity를 출력하면 됩니다.
(1) CategoryName과 Country, Quantity출력하기
해당 항목들의 경우, Categories, Products, OrderDetails, Orders, Customers로 이어지는 총 5개의 테이블을 확인해야 합니다. 따라서 먼저 각 테이블들에 대한 연결관계를 설명해줘야 합니다.
3개 이상의 테이블을 합치기 위해서는 join 명령어를 통해 테이블들을 합쳐줍니다. 이때 on 명령어를 통해 각 키값이 어떻게 연결되는지를 설정해줍니다.
따라서 명령어는 아래와 같습니다.
총 5개의 테이블이 어떻게 서로 연결되는지를 보여주고, 그 중에서 각각의 열들을 추출하는 명령어를 사용합니다.
이 경우 아래와 같은 값이 출력됩니다.
518개의 값이 출력됨을 알 수 있습니다.
(2) 음료구매자에 대한 조건절 삽입
이 중 Where 명령어를 통해 categoryname이 beverage인 값들만 추출해내는 명령어를 삽입합니다.
최종적으로 아래와 같은 명령어를 수행하면
다음과 같은 값이 출력됩니다.
총 93개의 값이 출력됨을 알 수 있습니다.
3. 공급자와 소비자가 같은 국적일 때의 선박기업
이는 다음과 같은 단계로 진행됩니다.
1-공급자와 소비자, 국적, 선박기업을 추출합니다.
2-공급자와 소비자가 같은 국적일 조건을 삽입합니다.
스키마 상에서 연결되는 데이터들은 Customers, Shippers, Suppliers이고 이들을 연결시키기 위해서는 Orders, OrderDetails, Products 테이블이 필요합니다.
(1) 공급자의 국가, 소비자의 국가, 선박이름 추출하기
이는 앞서 진행했던 것과 같이 각 테이블들의 연결관계를 설정하고 그 가운데에서 공급자 국가, 소비자 국가, 선박이름을 추출합니다.
이 중 공급자 국가와 소비자 국가의 경우, 동일하게 country라는 이름을 사용하고 있기 때문에 customers_country와 suppliers_country라는 이름으로 바꿔줍니다. 바꿀때는 as 명령어를 사용합니다.
그 결과값은 다음과 같습니다.
(2) 같은 국가 조건 붙이기
이후 조건절에 customers의 country와 suppliers의 country가 같은 경우를 where 명령어를 통해 삽입합니다. 따라서 명령어는 다음과 같습니다.
이 경우 결과값은 다음과 같습니다.
총 29개 결과값이 나왔고, 각각이 country가 같을 경우의 선박이름이 추출됨을 알 수 있습니다.
나가며
SQL의 경우, 여러 개의 데이터들로부터 원하는 결과값을 간편하게 추출할 수 있다는 점에 있어서 방대한 데이터를 다룰 때에 특히나 중요하게 사용될 수 있는 툴입니다.
특히 이번 과제에서는 각 데이터들을 어떻게 연결시키고, 그로부터 어떤 값들을 추출해서 그 값들을 조건에 따라 나타내는 방식들을 주로 표현했습니다.
여러가지 내용들을 추출할 경우, 명령어를 작성할 때에도 여러 명령어가 동시에 들어가게 되는데 이 경우 명령어들을 어떻게 정렬하고 최소화해서 보다 간편하게 확인할 수 있도록 하느냐가 이후 데이터 분석 등을 위해 명령어를 수정할 때에 보다 용이하게 작업을 진행할 수 있을 것으로 보입니다.
실제 데이터를 수집하고 데이터 내용들을 분석하는 작업들은 다음 과제들에서 수행해보도록 하겠습니다.
