brunch

라이킷 8 댓글

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by 허윤희 Jul 02. 2021

아장아장 데이터 분석, SQL

[코드스테이츠 PMB 07] SQL을 사용해 데이터 추출하기

잠깐! 이 글은 프로그래밍 전문 개념을 설명하고 있지 않습니다. ERD 및 쿼리문 실습과 관계선 공부를 기록하고, 개인적인 감흥을 적어내린 '일지' 임을 유의해주세요!

아, 물론 코드 피드백은 환영입니다 :-)

요즘 자의반 타의반으로 SQL과 Python을 호기롭게 뚜닥거리고 있다.

데이터를 다루는/다룰 수 있는 언어지만, 그 둘은 그 접근 방식도 느낌도 미묘하게 아주 많이 다르다.

이번에는 PMB에서 과제도 받은 겸, 개발 공부도 되돌아볼겸 SQL 개발 일지를 적어본다.

SQL 사용해 데이터 추출하기

Mission

1. 데이터를 바탕으로 DB 스키마를 정리합니다. DB간 개체, 속성, 관계를 알 수 있도록 작성합니다.
2. 여러 DB에서 데이터를 추출해 원하는 내용만 확인할 수 있는 SQL 문장을 3개 이상 만들어 봅니다.

참고(w3school SQL) : https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

1. DB 스키마 정리하기 (ERD)

ERD은 Entity- Relation Diagram의 약자로, 말 그대로 개체 관계 다이어그램이다.

다시 말해, 데이터(속성별 집합)들의 관계를 직관적으로 보기 쉽게 도식화한 자료를 이야기한다.

조금 변태 같이 들릴지는 모르겠는데, 이렇게 도식화된 자료를 만들면 약간 희열을 느낀다.

줄 글로 설명해놓은 것 보다 이렇게 직접 그려보는게 좀 더 이해가 쏙쏙 되는 기분이랄까/

아무튼 이번 과제를 통해 관계선 그리는 것을 확실히 이해 할 수 있었다.

▾ w3school SQL 데이터베이스 ERD

diagrams.net (무료) 툴을 이용

(1) ID 식별관계

모든 개체가 각각 ID를 가지고 있으므로(비식별 관계), 점선으로 표현했다.

TMI. w3school 데이터는 PK가 아닌 모든 ID 속성을 FK(외래키)로 써서 그리기 편했다.

(2) 관계선

Orders - Customers (1:N)

1. 모든 주문서에는 반드시 고객이 각 한 명씩 있다. (필수)

2. 고객은 아예 주문을 안하거나, 여러 건(1+)의 주문을 할 수도 있다 (0 가능)

Orders - OrderDetails (1:N)

1. 모든 주문 상세는 반드시 하나의 주문서로 연결된다. (필수)

2. 모든 주문서에는 여러 개(1+)의 주문 상세를 가지고 있다. 한 번에 N개의 상품 주문 가능 (필수)

Orders - Shippers (1:N)

1. 모든 주문는 하나 택배사를 가지거나, 미정일 수 있다.(0 가능)

2. 모든 택배사는 주문를 아예 받지 않았거나, 여러 건(1+)의 주문을 가진다.(0 가능)

Orders - Employees (1:N)

1. 모든 사원은 맡은 주문요청이 없거나, 여러 건(1+)의 주문요청를 맡을 수 있다. (0 가능)

2. 모든 주문서는 반드시 한 명의 담당 사원을 배정받는다(필수)

OrderDetails - Products (1:N)

1. 모든 제품은 아예 주문이 안되거나, 여러 번(1+) 될 수 있다. (0 가능)

2. 모든 주문에는 제품이 반드시 있다. (필수)

Products - Categories (1:N)

1. 모든 제품은 반드시 하나의 카테고리에 속한다.(필수)

2. 모든 카테고리는 제품이 아예 없거나, 여러 개(1+) 의 제품을 포함할 수 있다. (0 가능)

Products - Suppliers (1:N)

1. 모든 공급원은 여러 개(1+)의 제품을 공급하거나, 하나도 안할 수 있다. (0 가능)

2. 모든 제품은 반드시 하나의 공급원을 가지고 있다.(필수)

2. SQL 쿼리문 만들기

김대리SQL의 김대리는 실습예제에 자꾸 변화구를 던진다.

그래서 나도 상황을 설정하고, 필요할 법한 가설과 필요한 데이터를 추측하여 쿼리문을 작성해보기로 했다.

일명, 찰떡같이 알아듣는 일잘러 프로젝트.

Q1. 고객 중 제품 수량에 상관없이 주문 횟수가 가장 많은 순으로 선발하여 VIP 등급으로 변경하고자 한다

기대효과

VIP 고객을 위한 마케팅을 진행할 수도 있고, 주문 횟수가 적은 고객을 타겟으로 마케팅하여 리텐션을 높일 수 있다.

과제 : 고객 ID별 주문 횟수를 뽑아서 내림차순으로 정렬하자

▾ 쿼리문

▾ 택배사 이용 현황

Q. 감사제(라고 부르는 재고정리)를 하려고 한다.

재고가 남는 품목을 가장 잘 팔리는 품목과 함께 묶어 세일을 진행하면 재고를 정리할 수 있을 것이다

필요한 데이터 : 카테고리별 주문 수량

▾ 쿼리문

▾ 각 품목별 상품과 주문 현황

Insight

잘 나가는 품목인 유제품(Diary Products)와 농산물(Produce)을 묶음 상품으로 판매하는 방법이나, 다른 제품들을 살 때 농산물(Produce)을 추가하면 무료배송이 되는 가격대로 책정하는 방법 등이 있을 것 같다.

Q. 매출이 가장 낮은 국가는 어디인가? (BEP를 넘지 못한 국가)

해당 쇼핑몰은 전 세계를 대상으로 하는 쇼핑몰인 만큼 국가별 지표도 관리해야한다. 다양한 국가들 중 다행히 매출이 제법 나오는 곳도 있지만, 생각보다 미미한 곳도 있다. 매출이 발생하지 않더라도 각 국가에서 발생하는 유지비와, 인건비 등은 비슷하게 지출하고 있다. 따라서, BEP : Break Even Point(Sales_sum: 1000 이상)을 달성하지 못한 국가에서 거래를 중단하고자 한다.