[코드스테이츠 PMB 7기] Database 스키마와 SQL을 다뤄보자...

728x90

생각을 시작하며...

PM 공부를 시작하기 전에도 '데이터가 중요하다, 데이터를 통한 사고를 해야 한다'라는 말을 귀에 박히도록 들어왔다. 물론 대학원 시절에 단련된 데이터 조사와 분석 능력을 통해 이러한 사고와 직무를 잘할 수 있지 않을까 하는 막연한 희망(?)을 가지고 있다. 하지만 실무에서 적용이 가능할지 정말 미지수이기 때문에 지금은 이 희망을 실현시키기 위해 부단한 노력을 하는 중이다. 이런 노력을 하는 도중 마주친 가장 큰 문제는 데이터를 추출하는 도구의 사용이었다.

모두가 중요하다고 말하는 '데이터 보고 측정하기'를 해내기 위해서 Front와 Back단의 데이터를 잘 다뤄야 할 것이다. Front단의 데이터(고객의 유입 후 행동을 보는 데이터)는 GA(Google Analytics) 혹은 Amplitude와 같은 도구로 측정할 수 있어서 비교적 쉬운 반면(적용이 어려울 뿐), 기본적인 고객의 정보 등 Back단에서 다루는 데이터는 SQL이라는 도구를 사용해 추출한 후 본격적인 분석을 위해 또 다른 도구를 써야 하는 이중고를 가지고 있다.

물론 GA는 이 블로그를 운영하면서 클릭률, 방문율, 유입 페이지 등을 분석하고 있기 때문에 그리 생소한 도구는 아니었다. 반면 SQL을 사용하는 방법은 매우 생소했다. 대학원 시절에는 SPSS라는 분석도구로 보다 쉽게 통계 분석을 진행하곤 했는데 SQL은 쉽게 분석을 하기에는 적합하지 않고, 데이터를 추출 후 분석을 위한 가공을 진행해야 했기에 개인적으로 SPSS보다 통계에 좀 더 쓰기 불편했던 것 같다.

또한 DB 스키마에 대한 이해도가 없으면 SQL 문장을 사용하기 참 어렵다는 생각이 들었다.
결국 Back단의 데이터를 조직화시켜 놓은 DB 스키마를 모르면 어떤 부분에서 어떻게 데이터를 추출하는지 모르기 때문이다. 하지만 기본적으로 새로운 서비스를 런칭하지 않는 이상 PM이 이 DB 스키마를 건드리는 일을 없을 것 같다. 또한 기존 서비스의 DB 스키마는 오늘 실습한 데이터처럼 그리 단순한 구조는 아닌 것으로 알고 있기에 그 끄트머리만 잡은 이해로는 실무에서 사용해 낼지 모르겠으나 이런 DB 스키마를 직접 그려봄으로 이해를 넓혀 보려고 한다.

DB 스키마 (ERD: Entity Relationship Dialog)

물론 실제 데이터가 있으면 정말 좋겠지만 일단은 w3school SQL에 예시를 이용해 역으로 DB 스키마를 만들어 보았다. 기본적으로 이 관계를 나타낼 때 여러 가지 선이 사용이 되는데 '일반적으로 공부를 해보면 이게 무슨 뜻이지?' 하는 생각이 들곤 한다. 그러나 이렇게 스키마를 정리하다 보니 이 관계가 어느 정도 눈에 익게 되었다.
(하지만 역시 완벽한 건 아니기에... 그저 Flow만 보도록 하자. - 특히 order과 order details의 관계를 정하기가 어려웠다.)

DB 스키마를 짜기 전에는 이 구조가 Customers에 맞춰져 있을 줄 알았는데 스키마를 짜다 보니 Orders로 귀결이 되는 것을 발견했다. 그래서 이 DB는 Orders에 더 초점이 맞춰진 DB 구조를 가지고 있다고 할 수 있을 것 같다. 확실히 이렇게 구조화를 시키고 나니 DB가 한눈에 보이고 개발자에게 어떤 자료를 요청하거나 혹은 SQL을 사용하여 불러올 때 어디에서 불러와야 하는지를 알 수 있게 되었다. 그렇담 이제 데이터를 불러와보자.

SQL Statement

처음 어떻게 데이터를 불러와 분석하는 것이 좋을까 고민을 하면서 일반적으로 원하는 값들을 선택하여 불러오기보다는 새로운 테이블 구성해서 기존의 있는 데이터들을 불러와 배치하는 것이 좋을 것 같다는 생각을 했다. 각 테이블에서 데이터를 하나하나 SELECT로 불러와 합칠 수 있겠지만 새로운 표를 만들어 필드를 추가, 제거, 생략해가며 원하는 형태로 만들어 간다면 최종적으로 다른 수치와 비교가 더 수월할 것 같았기 때문이다.

하지만 분석을 용도로 테이블을 생성 구문을 사용한다면 DB에 새로운 테이블이 생겨 실제로 데이터가 저장될 것이다. 이 의미는 관계형 데이터베이스(RDB)에서 각각의 스키마를 이루는 Attribute, Entity들이 연계를 이루게 되는데 이 새로운 표는 연계가 되지 않는 동떨어진 DB가 될 가능성이 크다. 그렇다면 분석에만 사용되는 테이블이 늘어나 DB 유지비용 측면, 그리고 DB 접근성이 저하되는 문제 등이 발생할 위험이 있다.

그럼에도 새로운 테이블을 만들어야 하는 경우가 있을 수 있다. 이 작업은 아마도 PM이 아닌 백엔드 개발자와의 협업을 통해 이루어지겠지만 만일 회사의 사업 방향성, 혹은 새로운 서비스 런칭 등 확장이나 피벗이 필요한 상황에서 새로운 DB 스키마 요소를 만드는 경우일 것이다.

▶ Customer 중심의 표 만들기

이런 가정을 해보자.
기존에 Order 중심으로 구성되었던 정보(Customer, Product, Order 테이블)를 변형하여 Customer를 중심으로 만들고 이를 바탕으로 서비스를 런칭해야 한다. 하지만 지금 DB에는 Orders를 중심으로 거의 모든 스키마들이 귀결이 되기 때문에 어렵더라도 Customer_order라는 표를 새로이 만들어 데이터를 보다 쉽게 저장, 추출할 수 있게 만들어야 한다.

그렇다면 가장 먼저 할 일은 DB에 새로운 Table을 형성하는 것이다. 이를 실행하는 방법은 크게 2가지 방법이 있을 것이다. DB 내 다중 테이블 데이터를 뽑는 방법이 있고, 또 다른 하나는 새로운 테이블을 먼저 생성하고 각 필드에 해당하는 값들을 하나씩 배치하는 방법이 있을 것이다. 예시로 사용했던 w3school는 다중 테이블을 데이터로 불러오는 것이 불가능한 한계가 있어 조금 번거롭더라도 Customer_order라는 테이블을 새로 생성해보도록 하겠다.

CREATE TABLE Customer_order (
CustomerID INT NOT NULL,
CustomerName VARCHAR(30),
ProductID INT,
OrderID INT,
Quantity INT,
Price DECIMAL(3, 2),
PRIMARY KEY(CustomerID)
FOREIGN KEY(ProductID) REFERENCES Products(ProductID)
);

이제 CustomerID라는 Primary Key를 가진 테이블이 하나 만들어졌다. 이 테이블의 구성 요소로는 CustomerName, ProductID, OrderID, Quantity가 있다. 그럼 이제 기존 DB에 저장되어 있는 Customers 테이블의 CustomerID, CustomerName와 OrderDetails의 ProductID, OrderID, Quantity를 가져와서 새로운 테이블에 덧붙여 보도록 하자.

INSERT INTO Customer_order (CustomerID, CustomerName)
SELECT CustomerID, CustomerName
FROM Customers;

INSERT INTO Customer_order (ProductID, OrderID, Quantity)
SELECT ProductID, OrderID, Quantity
FROM Orderdetails;

문제는 이렇게 진행했을 때 2개의 테이블에서 얻어온 정보가 각자 필드에 잘 배치되는 것이 아니라 각각 새로운 열에 부분적인 정보가 기입되는 것이다. 하나의 Primary KEY를 가진 데이터들로 묶이는 것이 바라는 바이겠지만 Primary Key로 각 테이블을 연결할 수 없는 구조 때문에 아래와 같은 상황이 발생하게 된 것이다. (도저히 내 머리로는 이해가 되지 않아 개발자에게 물어봄... ㅋㅋ)

이 부분을 해결하기 위해서 또 다른 방법으로 FULL JOIN이란 기능을 써보려 했지만 이것 역시 기준이 되는 Primpary Key의 부재로 인해 불가능할 뿐만 아니라 w3school에서는 FULL JOIN 기능 지원을 하지 않는다고 한다.

INSERT INTO Customer_order (CustomerID, OrderID)
SELECT CustomerID, OrderID
FROM Orders
FULL OUTER JOIN Orders ON Customer_order.CoumsterID=Orders.CustomerID;

이쯤 되면... 나가린데...라는 생각이 머릿속을 스치고.... ㅜㅜ

결국은 DB에 내가 원했던 테이블을 만들 수 없었으나 만들고 싶었던 테이블은 "어떤 Customer가 어떤 물건을 얼마나 샀는지를 한눈에 볼 수 있는 테이블"이었다. 이렇게 여러 테이블의 필드를 활용해 새로운 테이블을 만드는 시도를 한다면 데이터를 한 눈에 볼 수 있고, 이 데이터를 추가 가공해 나가면서 인사이트를 뽑아낼 수 있을 것이다.

▶ 데이터 추출을 합시다.

그렇다면 이제는 일반적으로 DB에서 데이터를 추출하는 방식으로 원하는 데이터를 추출해보도록 하자. 위 가정처럼 Customer를 중심으로 Product와 Order정보를 묶어보려면 INNER JOIN으로 기능으로 데이터를 묶고, ORDER BY로 원하는 분류대로 나열을 하면 될 것 같다.

SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
P.ProductID,
P.Price,
OD.ProductID,
OD.Quantity
FROM Customers AS C
INNER JOIN Orders AS O ON C.CustomerID=O.CustomerID
INNER JOIN OrderDetails AS OD ON O.OrderID=OD.OrderID
INNER JOIN Products AS P on OD.ProductID=P.ProductID
ORDER BY OD.ProductID DESC; --- 원하는 Sorting을 여기다 집어넣으면 될 듯하다 ---
(혹은 Customer ID로 GROUP BY를 묶어보는 것도 도움이 될지도?)

▶ 문제점 발견해내기

이런 식으로 위에서 Customer_order라는 테이블을 만들었다는 가정하에 분석을 할 수 있는 SQL 구문을 만들어보자. 어떤 고객이 어떤 물건을 가장 많이 구매를 했는지 보려면...

SELECT * FROM Customer_order
ORDER BY Quantity DESC;

이렇게 구분해 볼 수 있을 것이다. 가장 많이 구매한 개수는 120개이고 Save-a-lot Markets의 Jose Pavarotti가 구매를 가장 많이 한 사람으로 나오고 있다. 또한 이 상품은 Pate chinois라고 나오고 있다.
반대로 가장 팔리지 않은 상품을 보면 3개의 상품 1개씩만 판매가 된 것을 볼 수 있다. (Teatime Chocolate Biscuits, Gravad lax, Gudbrandsdalsost)

만일 새로운 테이블을 만들지 않고 INNER JOIN 방식을 택했다면 ORDER BY를 통해 원하는 기준대로 분류할 수 있겠지만 그 자체로 합산이나 혹은 평균을 내기가 조금 어려울 수 있다.
그렇기에 좀 더 용이하게 합산/평균을 낼 수 있도록 테이블을 새로이 만드는 것도 방법이겠다.
(물론 상황이 허락하는 한에서... ㅋㅋ)

생각을 마무리하며...

SQL을 이용해 데이터를 가공하는 것에 대해 알아보았지만 수박 겉핥기 수준인지라 'SQL만으로 분석을 하는 것은 불편하다'라는 생각이 들었다. 물론 SQL은 데이터를 분석하는 툴이 아닌 추출을 목적으로 하는 툴이라서 처음에 생각했었던 R, SPSS 같은 분석용 툴과는 결이 다르다는 것을 확실히 느꼈다.
앞으로 Google Analytics와 Google Big Query에 대하여 좀 더 공부를 할 예정이지만 이러한 분석, 데이터 툴을 효과적으로 다룰 수 있는 능력을 키워야 내가 생각하는 단계의 인사이트를 뒷받침할 수 있다고 생각하게 되는 기회였다.

제가 공부하고, 이해해본 PM에 관련된 내용을 포스팅으로 남깁니다.
잘못된 생각이나 혹은, 이견, 참고자료 등은 댓글로 남겨주세요.
감사합니다