[코드스테이츠 PMB 7기] ERD가 뭔가요? SQL실습 ERD 그려보기

당신의 컴퓨터엔 얼마나 많은 자료가 있나요?

 

 

컴퓨터나 노트북에도 수많은 폴더가 있다. 작업을 하다보면 바탕화면에 그냥 파일들을 펼쳐두는 경우도 있고, 일이 마무리 될때까지 계속해서 새로운 폴더를 생성하는 경우도 있다. 아마 '최종, 최종1, 최종2, 최종수정, 진짜최종, 진짜진짜최종, 이젠정말 수정안해 최종, 제출전 최종...' 같은 파일이나 폴더들을 생성해본 경험이 누구나 있을 것이다. 

 

 

출처 : 구글

 

 

하는일이 많아지고 복잡해질수록 폴더를 정리하는 일은 더더욱 중요해진다. 

어떤 파일이 어디에 들어가 있는지, 다음 프로젝트를 진행할 때 어떠한 자료를 찾아와 참고할 것인지, 보고서를 작성하는 양식은 어떤 폼을 가지고 진행할 것인지, 원하는 정보나 자료를 찾기 위해서는 자료를 관계가 있는 것들끼리 묶어서 정리해야 한다. 그리고, 더 좋은 방법은 각각의 파일이 어떤 형식으로 구조를 갖추고 있는지 이미지로 그려 가지고 있으면 자료를 처음 접하는 사람도 쉽게 접근할 수 있을 것이다. 

 

 

폴더정리 예시 (출처 : 네이버 이미지)

 

 

Ι ERD가 뭔데요?

 

데이터베이스(Database : DB)에 대해서는 대부분 어렴풋이라도 이해하고 있을 것이다.

집에 쌓아둔 서류철들을 데이터베이스라고 이야기하기도 하고, 회사나 조직에서 업무를 진행해 본 사람은 회사의 자료들을 모아놓은 것을 데이터베이스라고 이해하기도 한다. 모두 맞는 말이다. 데이터베이스란 쉽게 이해하자면 결국 어떤 활동을 위한 정보의 '저장고'라고 생각하면 된다. 

 

그러나 이 정보들이 날 것의 상태로(raw data) 보관되어있다면, 정보로써의 가치를 다 하지 못할 것이다. 이러한 정보들(data)을 저장고(database)에 어떻게 차곡차곡 정리할지 고민해서 구조화하고, 이 내용을 그림으로(diagram) 표현해 놓은 것이 바로 ERD다. 

 

ERD는 Entity Relationship Diagram의 약자로 한국말로는 '개체-관계 모델'이라고 한다. 의미를 풀이하자면 '실체가 있는 것들의 관계를 그림으로 표현한 것'이라고 생각하면 될 것이다. 

 

 

그럼 ERD는 어떻게 그리면 되는 걸까? 개인적으로 폴더를 정리하던 것처럼 내맘대로 폴더명을 정하고 채워넣으면 되는 걸까? 물론 아니다. 나 혼자만 보고 말 개인 폴더를 정리하는 것이라면 상관이 없지만, ERD의 목적은 많은 사람들이 자료가 저장되어있는 위치를 명확하게 파악하고 쉽게 활용할 수 있도록 돕는 것이기 때문이다.

 

ERD를 작성하는 것에는 세가지 요소가 있는데 실체(Entity), 속성(Attribute), 관계(Relationship)를 표시해야 된다는 것이다.

 

 

ERD 작성방법 (출처 : 하단게재)

 

ERD (ER 다이어그램) 작성 방법

 

 

 

 

Ι 실습을 해보자 (ERD 그리기)

(과제1. SQL을 사용해 데이터 추출하기/DB 스키마 정리하기)

 

 

SQL을 연습하기 위한 w3school이라는 좋은 온라인 서비스가 있다. 아래에 연결해 놓은 링크는 실습을 위한 참고 데이터 베이스다. 직접 그려보고 싶은 사람은 똑같이 활용이 가능하다. 

 

 

SQL Tryit Editor v1.6

 

 

위의 웹페이지에 들어가면 아래와 같이 총 8개의 테이블이 세팅되어 있는 것을 볼 수 있다. 

 

 

 

 

각각의 테이블에는 개별 속성값들이 입력되어 있고, 이 자료를 바탕으로 직접 ERD를 그려 보았다. 

 

SQL데이터로 작성해본 ERD (출처 : 직접 작성)

(ERD를 제작한 툴은 '루시드차트'(https://www.lucidchart.com/pages/)라는 웹 서비스를 이용했다.) 

 

 

크게 여덟개의 테이블속에 다양한 고객데이터가 들어있는데, 테이블과 속성의 이름들을 살펴보면 주문서 정보(orders)나 어떤 고객이 주문했는지에 대한 고객정보(customerID), 그리고 배송정보(shippers)와 같은 내용들을 확인할 수 있다. 무엇인가 물리적인 유형의 제품을 팔고 있는 회사로 보인다.

 

이렇게 DB자료들을 가지고 ERD를 작성해 보았다. 그런데 자료가 어디에 들어있는지만 정리해 둔다고 모든 일이 해결된 것은 아니다. 저장되어 있는 자료들을 적절하게 활용할 수 있어야지만 이 자료가 의미가 있어진다. 이어서 저장되어있는 DB를 가지고 SQL을 활용하여 원하는 자료를 추출해 보도록 하겠다. 

 

 

 

Ι DB에서 원하는 자료를 출력해보자 (SQL 실습) 

 

 

제품을 전 세계로 팔고 있는 이 회사는 성장을 위해 다음 시장과 타겟에 대해 깊은 이해가 필요로 한 상황이다. 

회사가 보유하고 있는 고객 데이터를 바탕으로 정보를 추출하여 분석해보자 

 

 

1. 우리 고객들은 어느지역에 많이 살고 있을까? 

 

우선 고객들의 정보가 있는 [Customers] 테이블을 살펴보면, 총 91명의 고객데이터가 있는 것을 확인할 수 있다. 전 세계에 제품을 판매하고 있는지 고객들의 거주 도시가 정말 다양한 것을 확인할 수 있다. 우리 고객들이 많이 살고 있는 지역은 어디인지 한번 살펴 보도록 하자. 

 

우선 국가별로 가장 많은 고객들이 살고 있는 국가를 추출해 보았다. 

 

 

 

가장 많은 고객들이 살고 있는 국가는 미국(USA)이었다. 총 13명의 고객이 미국에서 주문을 하였다. 데이터를 추출해 놓고 보니 영토가 큰 나라들이 꽤 많았다. 지역별로 조금더 세분화 해 보면 어떨까? 

 

같은 쿼리로 국가 표시만 추가하여 도시별로 거주하는 고객들을 추출 해 보았다. 

 

 

회사의 고객들은 총 69개의 도시에 분포하여 살고 있다. 고객들이 가장 많이 거주하고 있는 지역은 영국(UK)의 런던으로 총 6명의 고객이 주문을 했다는 것을 확인할 수 있었다. 

 

 

* Insight 

짧은 쿼리문이지만 국가와 도시별로 거주 고객들을 구분해 보면서 느낀점은 '고객 분류의 중요성'이었다. 표본이 많지 않은 데이터임에도 불구하고, 고객들을 구분하는 지점이 도시냐 국가냐에 따라서 결과값이 무척 다를 수 있다는 점이었다. 국가로만 보면 미국에 거주하는 고객이 가장 많았지만, 도시별로 다시 구분을 해 놓고 보니 미국의 경우 각각 다 다른 도시에서 주문을 진행 한 것이고, 영국의 경우는 런던에서만 6건의 주문이 있었다. 이처럼 raw한 데이터를 바라볼 때 어떤 기준으로 구분할 것인지, 어떤 가설을 세워 검증을 진행할 것인지 등 정보를 바라보는 목적이 명확히애 한다는 점을 느낄 수 있었다. 

 

* 쿼리 정리

SELECT city AS 도시, country AS 국가, COUNT (*) AS 거주고객수 
FROM customers 
GROUP BY city
ORDER BY 거주고객수 DESC;

 

 

 

2. 가장 많이 팔린 제품은 무엇일까? 

 

1) 회사가 가지고 있는 제품 중에서 가장 많이 팔린 제품은 무엇일까? 

이번 쿼리의 핵심은 두개의 다른 테이블로부터 WHERE문을 이용해 productID 번호를 기준으로 시트 정보를 통합시켜 추출했다는 점이다. 아직도 구문 쓰는것이 쉽지 않지만 생각보다 재밌다. (에러 안나고 잘 나올때만...) 

 

 

추출한 데이터를 살펴보면 가장 많이 팔린, 주문량이 가장 높은 제품은 'Gorgonzola Telino' 다. 

어떤 제품인지는 잘 모르겠으나, 제품가가 저렴한 것이 저관여 제품중에서 생활에 밀접하게 쓰이는 제품이 아닐까 짐작해 본다. 

 

 

2) 가장 매출이 높은 제품은 무엇일까? 

 

그렇다면 가장 높은 매출을 자랑하는 제품은 무엇일까? ORDER BY로 정렬만 주문수량에서 판매액으로 변경하였다. 

 

 

추출된 데이터를 살펴보면 가장 많이 팔린 제품은 'Côte de Blaye' 이다. 어떤 제품인지 궁금하여 찾아봤더니, 와인이었다. Côte de Blaye는 프랑스산 와인으로 블라예(블레유) 지방에서 나는 코트 와인을 뜻한다고 한다. 와인을 잘 모르더라도 '보르도 와인'은 한번쯤 들어보지 않았을까? 품질이 좋은 와인을 생산하기로 유명한 보르도 지역 안에 위치한 블라예 지역의 와인이라고 한다. 

블라예 지역은 지롱 강(River Gironde) 오른쪽에 위치하고 있고, 마찬가지로 와인으로 유명한 Bourg지역과 인접해 있다. Bourg지역의 'Côtes de Bourg' 와인과 같이 언급되는 것 같다.

 

 

검색 결과를 살펴보면 총 77개의 제품중에서 가장 높은 판매액을 가지고 있는 것이 이 와인이었다. 개당 단가도 저렴한 편이 아님에도 꽤 높은 주문량을 보여주고 있다. (맛있어서 그런거겠지...?) 다음에 기회가 되면 보르도 지역의 이 와인들을 마셔볼 수 있는 기회가 있으면 좋겠다. 

 

 

 

* 쿼리 정리

SELECT productname AS 제품명, price AS 가격, sum(quantity) AS 총주문수량, sum(quantity)*price AS 총판매액
FROM OrderDetails,  products
WHERE products.productid = orderdetails.productid 
GROUP BY productname
ORDER BY 총주문수량 DESC;

 

 

 

3. 가장 매출이 많은 지역은 어디일까? 

 

그럼 1번과 2번을 합쳐서 가장 높은 매출을 기록한 지역은 어디일지 살펴보자

 

 

처음 추출하고자 목표로 한 것은 지역당 매출이었으므로 국가별로 그룹(GROUP BY)을 지어 PK로 설정했으며, 매출액이 큰 순으로 살펴보기 위해 나열(ORDER BY)은 매출액으로 진행했다.

 

 

총 매출액이 가장 큰 국가는 미국(USA)였으며, 주문량또한 미국이 가장 높았다. 

추가적으로 몇번에 걸쳐서, 몇건의 주문으로 매출을 달성하였는지 확인해 보고 싶어서 결제 횟수와, 결제 건수 데이터도 함께 추출 해 보았다. 추출한 결과를 살펴보니 29번에 걸쳐서 76건의 결제를 통해 구매가 일어났음을 확인할 수 있었다.

 

 

* 쿼리 정리 

SELECT DISTINCT Country AS 국가,

   SUM(quantity) AS 총주문량,

   SUM(OrderDetails.Quantity*Products.Price) AS 총매출액,

   COUNT(DISTINCT orders.orderID) AS 결제회수,
   COUNT(*) AS 결제건수
FROM Customers, Orders, OrderDetails, Products
WHERE Customers.CustomerID=Orders.CustomerID 

   and Orders.OrderID=OrderDetails.OrderID 

   and products.productID=OrderDetails.productID
GROUP BY 국가
ORDER BY 총매출액 DESC; 

 

 

 

 

* 데이터 분석

 

미국을 디테일 하게 살펴보도록 하자.

 

1회당 주문한 제품(가짓)수 : 2.6개 = 결제건수(76건) / 결제회수(29회)

1건당 주문한 제품 양 : 28.1개 = 총주문량(2,139개) / 결제건수(76건)

1회당 주문한 제품 양 : 73.7개 = 총주문량(2,139개) / 결제회수(29회)

1건당 지출한 비용 : $915 = 총 매출액($69,611) / 결제건수(76건)

1회당 지출한 비용 : $2,400 = 총 매출액($69,611) / 결제회수(29회)

 

제품 1개당 지출한 비용 : $32.5 = 회당 지출비용(2,400) / 회당 주문양(73.7)

 

미국에 거주하는 고객이 자사의 제품을 주문할때는 평균적으로

한번에 2.6가지의 제품을 
한 제품당 약 28.1개씩 

개당 $32.5를 지불한다는 것을 확인할 수 있었다. 

 

리텐션을 고려하지 않고, 단발성 구매로 가정하였을 때 미국 고객의 LTV는 $2,400라고 볼 수 있겠다. 

(공격적으로 고객을 모집하고 싶어진다)

 

 

 

데이터를 살펴보면서 또 재밌는 점은 총 매출액 2위를 달성한 오스트리아다. 결제 회수나 결제 건수는 미국의 반정도밖에 되지 않음에도, 총 매출액에 있어서는 큰 차이가 나지 않는다는 점이었다. 미국을 분석한 것과 마찬가지로 오스트리아도 디테일하게 살펴보도록 하자

 

1회당 주문한 제품(가짓)수 : 3개 = 결제건수(39건) / 결제회수(13회)

1건당 주문한 제품 양 : 40.1개 = 총주문량(1,565개) / 결제건수(39건)

1회당 주문한 제품 양 : 120.3개 = 총주문량(1,565개) / 결제회수(13회)

1건당 지출한 비용 : $1,324 = 총 매출액($51,671) / 결제건수(39건)

1회당 지출한 비용 : $3,974 = 총 매출액($51,671) / 결제회수(13회)

 

제품 1개당 지출한 비용 : $33.11 = 회당 지출비용(3,974) / 회당 주문양(120.3)

 

오스트리아에 거주하는 고객이 자사의 제품을 주문할때는 평균적으로

한번에 3가지의 제품을 
한 제품당 약 40.1개씩 

개당 $33.11를 지불한다는 것을 확인할 수 있었다. 

 

 

 

미국과 오스트리아를 비교해 볼때 구매하는 제품의 가짓수도 2~3개로 유사했고,

지불하는 개당가격도 $32~$33로 큰 차이는 없었다. 

가장 큰 차이를 보였던 것은 회당 주문하는 양이 73.7개(미국)와 120.3개(오스트리아)로 두드러지게 나타났다. 

 

이는 오스트리아에 거주하는 고객들의 구매력이 미국의 고객보다 비교적 크다고 볼 수 있으며, 

시장의 총 크기를 고려하지 않았을때(or 동일하게 가정했을 때) 우선적으로 회사가 비즈니스를 확장해야 하는 곳은 오스트리아라고 볼 수 있다. 

 

 

 

마무리 

데이터를 살펴보는 것이 생각보다 재밌다고 느껴지는 시간이었다. SQL을 좀 더 깊이있게 공부해보고 싶고, 데이터 분석에 대한 인사이트를 많이 키우고 싶다는 생각을 하게 되었다. PM으로써 나아가고 싶은 포지션은 Business Planning을 하고 Development하는 비즈니스 기반 PM이다. 데이터분석을 배워갈수록 데이터를 읽어낼 수 있는 능력의 중요성을 더 느끼게 되는 것 같다. 

 

 

 

 

 

---

참고자료

 

[ ERD관련 ]

[개발 팁] ERD에 대하여

ERD (ER 다이어그램) 작성 방법

ERD란 무엇인가? - Youn's Journal

 

[ Database ] ER 다이어그램 / ERD 기호 및 표기법

 

 

[ERD를 제작할 수 있는 웹 서비스 소개]

Top 5 Free Database Diagram Design Tools