흩어져있는 데이터 연결하기
엑셀로 데이터를 손쉽게 연결하는 방법 - 파워쿼리와 모델링
# 1. 데이터의 키
사과 - 과수원 - 원□
여러분, 끝말 잇기 좋아하세요?
사과 → 과수원 → 원리 → 리튬과 같이 단어들끼리 끝말 잇기를 하는 것처럼, 데이터도 서로 이어 붙일 수가 있다는 사실, 알고계신가요? 데이터를 연결하는 원리도 서로 다른 낱말을 잇는 것과 같아요.
끝말잇기는 어떻게 하나요? 앞 단어의 끝 글자가 뒷 단어의 첫 글자가 됩니다. 즉, 두 단어가 동일한 글자 하나를 공통으로 가지고 있어요. 두 개 이상의 단어를 공통된 글자로 연결하는 것처럼, 두 개 이상의 데이터에 공통으로 들어있는 값을 기준으로 합치는 것을 "데이터 병합" 이라고 하고, 이때 공통적으로 들어가는 기준이 되는 열을 "키(key)" 라고 부릅니다.
아래 그림에서 보시면 빨강 테이블과 노랑 테이블, 녹색 테이블, 파랑 테이블이 있습니다. 이들을 서로 이어 붙이고 싶다면 무엇이 필요할까요? 앞서 설명한 키(Key)값이 공통으로 들어가 있으면 하나로 연결이 가능하겠죠? 꼭 맨 마지막 글자나 첫 글자가 아니어도 같은 글자로 시작하는 낱말을 말하는 게임처럼, 여러 데이터 테이블에 공통된 키 값이 포함되어 있기만 하면 됩니다.
예를 들어, 데이터에서 ①제품정보, ②판매정보, ③고객정보, ④공급정보, 각 테이블에 제품ID 라는 변수가 공통적으로 포함되어 있다면, 4개 테이블 모두 하나의 데이터 테이블로 병합할 수도 있습니다.
데이터 연결과 키 값 <저자 정경문>위 그림에서 처럼, 제품 테이블에 제품을 식별하기 위한 제품의 고유번호 제품ID 라는 키값이 존재하고, 판매 테이블에는 제품ID와 그 제품을 구매한 고객ID 번호가 존재합니다. 그리고, 별도 고객 테이블에는 고객의 고유번호인 고객 ID와 고객명이 존재합니다. 그러면 끝말잇기와 같은 원리로, 제품 테이블의 제품ID를 키로 판매 테이블의 제품ID와 연결할 수가 있겠죠? 그리고 판매 테이블에 또 있는 고객ID를 키로 고객 테이블의 고객ID와 연결하면 오른쪽 그림과 같이 빨강, 노랑, 초록 테이블이 하나로 연결될 수가 있습니다.
또 다른 두번째 예로 그 아래를 보시겠습니다.
제품 테이블, 판매 테이블, 공급 테이블 3개의 테이블에 제품 ID라는 키 값이 모두 들어가 있다면 이들 세 개의 별도 데이터 테이블을 제품 ID 라는 공통 키값으로 묶어서 하나의 테이블로 연결할 수가 있습니다. 그렇게 되면 오른쪽 그림과 같이 빨강, 노랑, 파랑의 테이블을 하나로 묶을 수가 있습니다.
# 2. 데이터 간의 관계
앞서 설명드린 데이터 테이블 처럼 데이터 간에 서로 관계를 가지고 있어서 (키를 통해) 데이터를 잇거나 병합하고 묶고, 연결할 수 있는 데이터들의 집합을 관계형 데이터 베이스라고 합니다.
1) 관계형 데이터 베이스
그럼 관계형 데이터 베이스에 대해 알아볼까요?
관계형 데이터베이스는 영어로 RDBMS 라고 불러요. " Relational" DataBase Management System 인데요. 현재 가장 많이 사용되고 있는 데이터베이스의 한 종류입니다. 행과 열로 이루어진 데이터를 우리는 “테이블” 이라고 불렀잖아요. 그런데 각각의 테이블에는 서로 연결이 가능한 키(Key)를 가지고 있었습니다.
이 키들을 통해서 데이터들의 관계를 서로 연결하는 구조를 "관계형(R)" 데이터 베이스라고 하는 것이죠. 우리는 데이터베이스인 데이터 수집과 저장을 위한 저장소를 설계하는 것이 아니라, 활용이 가능한 데이터 셋을 다루고 있지만 개념과 원리는 동일합니다.
그럼 앞서 데이터들을 연결하는 핵심은 무엇이었나요? 바로 키(Key) 입니다.
우리는 키에 대해서 좀 더 알아보겠습니다.
2) 데이터를 연결하는 열쇠, 키(KEY)
앞서 언급했던 키는 데이터에서 각 행을 식별하는 데 중요한 역할을 합니다. 많은 양의 데이터에서는 관측된 정보가 행(가로 줄)의 형태로 저장이 되는 데, 이 때 각각의 행들을 유일하게 식별할 수 있는 값이 바로 이 키(key) 입니다. 키 값은 데이터와 데이터베이스의 구조를 유지하고, 데이터를 정확하고 중복없이 관리하는 매우 중요한 값입니다.
키 값은 데이터베이스나 테이블에서 특정 행을 구분하는 데 사용되는 값으로 이 값은 해당 행을 고유하게 식별하며, 중복되지 않아야 하죠. 예를 들어, 고객 정보 테이블에서의 고객번호 또는 주민등록번호가 해당됩니다. 판매정보 테이블에서의 판매번호가 키 값이 될 수 있어요.
고객은 한명한명은 고유하고, 판매에서 주문판매한 건건이 고유한 건이잖아요. 그 건을 대상으로 반품이나 교환도 해야하구요 이런 특징을 가졌기 때문에 키 값은 각 행을 식별하는 데 사용되며, 데이터에서 특정 행에 접근하거나 검색할 때 사용돼요.
# 3. 키의 종류
이러한 키의 종류는 기본키와 외래키 크게 2가지가 있습니다.
기본키(Primary Key)와 외래키(Foreign Key) by 저자① 기본키(Primary Key)
기본키는 각 행을 고유하게 만드는 값이고요 고유성과 무결성 두가지 성격을 가집니다.
고유성(Unique)이란, 기본키 값이 테이블 안에서 중복되지 않아야 함을 의미해요. 예를 들어, 고객의 이름은 동명이인과 같이 중복될 수 있어서 키값이 될 수 없어요. 대신 주민등록번호나 고객번호를 키 값으로 설정하죠. 이러한 고유성 때문에 기본키를 고유값, 고유키라고 부른답니다.
두번째 성격은 무결성(Not a Null)이예요. 기본키 값은 비워둘 수 없어요.
간단한 예시로, 고객 정보가 담긴 테이블을 생각해볼게요. 여기서 고객번호가 기본키가 될 수 있어요. 왜냐하면 모든 고객의 번호는 달라야 하고, 고객번호나 주민등록번호가 없는 고객은 없기 때문이죠. 고객번호를 비워두고서는 고객정보를 만들 수 없죠.
② 외래키(Foreign Key)
외래키는 한 테이블의 열이 다른 테이블의 기본키를 참조할 때 사용돼요. 예를 들어, 고객 정보 테이블과 판매 테이블이 있을 때, 고객 정보 테이블의 기본키인 id를 판매 테이블의 외래키로 지정해서 두 테이블을 연결할 수 있어요. 앞서 설명했던 끝말잇기와 키의 개념이 여기에 해당합니다.
키값을 데이터에 구성하게 되면 관계가 있는 데이터들끼리 연결할 수가 있습니다. 그러면 데이터 수집의 중복을 줄일 수 있고, 데이터를 더 효율적으로 관리할 수 있습니다. 또한, 다양한 분석과 연산이 가능해집니다.
# 4. 데이터 간의 관계를 연결
각 테이블에 키 값이 있다면 이제 관계를 맺어줄 차례입니다. 이러한 관계는 크게 다음의 3가지로 구분됩니다.
첫째, 일대일 관계(1:1) - 주민등록번호, 학번, 군번
국가에서 국민, 학교에서 학생, 그리고 군대에서 군인은 모두 각각의 고유한 식별자를 가집니다. 이를 통해 각각의 사람은 자신만의 식별번호를 가지며, 다른 사람과 중복되는 일이 없습니다.
둘째, 일대다 관계(1:*) -작가와 책, 제품번호과 판매번호
"다"는 많다는 뜻으로 기호로 *별 "스타" 를 쓰기로 합니다.
예를 들어 한 작가는 여러 권의 책을 쓸 수 있습니다. 하지만 그 작가는 하나의 주민등록번호를 가진 한 명 뿐이죠. 여러 권의 책은 각각 ISBN이라는 출판번호를 가진 여러 책이 됩니다. 그 작가가 쓴 여러권의 책들은 작가에게 속합니다. 이것이 바로 작가와 책 간의 일대다 관계입니다. 한 작가가 여러 책을 쓸 수 있지만, 각 책은 하나의 작가에 의해 쓰여진다는 것을 의미합니다.
셋째, 다대다 관계(N:M) - 학생과 강의, 제품목록과 고객목록
여러분이 학교에서 수업을 듣는 학생이라고 상상해보세요. 각 학생은 여러 개의 수업을 수강할 수 있고, 또한 하나의 수업에는 여러 학생이 참여할 수 있습니다. 이것이 바로 학생과 강의 간의 다대다 관계입니다.
예를 들어, 학생 A는 수학, 영어, 과학 등 여러 과목을 수강할 수 있으며, 학생 B, C도 마찬가지로 다양한 수업을 듣게 됩니다. 반면에, 수학 수업에는 학생 A, B, C가 함께 수강할 수 있고, 영어 수업에도 같은 학생들이 함께 수업을 듣게 될 수 있습니다.
# 5. 엑셀로 데이터 연결하기
이제 엑셀에서 실습을 할 차례입니다. 우리가 사용할 데이터는 카페에서 판매되는 홀케익 제품 정보를 기준으로 공급과 고객, 판매, 그리고 날짜를 연결해볼 계획입니다.
사용될 데이터와 완성된 샘플 데이터는 다음에 링크를 걸어두겠습니다. 빵집 데이터.xlsx
작업은 ① Power Pivot 추가기능 설치. ② 데이터 모델 추가 ③ 다이어그램보기 ④ 관계 모델링
순서로 진행합니다. 글의 주제에 맞게 ④ 관계 모델링 만 소개하도록 하겠습니다.
A. 가장 먼저 날짜를 정리해 줄건데요.
날짜가 있는 테이블은 날짜, 공급, 판매 였습니다. 그러면 날짜는 모든 날짜가 다 들어있는 날짜 테이블에서 공급된 날짜만 있는 공급 테이블의 날짜 칼럼 위로 드래그해서 놓아주겠습니다. 그러면 뭔가 선이 하나 만들어졌죠? 이것이 앞서 공부한 관계 선입니다. 그리고 자세히 보시면, 날짜 테이블에는 1, 공급 테이블에는 * 이 표시되었습니다.
그리고 가운데에 화살표 방향이 날짜 → 공급으로 데이터가 흐르는 방향이 설정되었습니다. 마찬가지로, 날짜 테이블의 날짜를 드래그 해서 판매된 날짜만 있는 판매 테이블의 날짜 칼럼 위에 놓아주겠습니다. 역시 마찬가지로 일대다의 관계가 형성되었네요.
B. 이제 제품 ID 키를 연결해보겠습니다.
제품 테이블의 제품ID가 유일한 고유값이 잖아요. 그래서 제품 테이블의 제품 ID 값을 드래그 해서 공급 테이블의 제품 ID 위로 올려놓습니다. 그리고 판매 테이블의 판매 ID 위로도 마찬가지입니다. 날짜 때와 마찬가지로 일대다의 관계선이 생성되었네요.
이렇게 관계를 형성할 때 반드시 칼럼명이 같아야 하는 것은 아닙니다. 고유한 키 값을 인식한다면 관계를 자동으로 형성할 수 있습니다.
C. 고객 ID
이제 마지막으로 고객 테이블에서 고객 ID를 판매 테이블의 고객ID 칼럼명 위로 드래그해 줍니다.
지금까지 목적에 맞는 데이터를 직접 만드는 과정에서 필요한 사고 방식 중 하나인 데이터의 연결에 대해 배워보았습니다. 데이터 분석 도구나 프로그램마다 명령어가 다르지만, 데이터를 활용하는 입장에서 보면 프로그래밍 명령어의 중요성은 크지 않습니다. 왜냐하면 SQL, R, 파이썬에서 명령어는 다르더라도 데이터를 다루는 방식, 즉 사고 방식은 공통적이기 때문입니다.
따라서 저는 코딩보다는 '사고력'이 중요하다고 강조합니다. 오늘 우리가 배운 데이터 활용 사고, 즉 데이터의 연결 핵심 원리를 잘 기억하시고, 다양한 데이터에 확장하여 적용한다면, 새로운 가치를 창출하고 실제 여러분의 문제를 해결하는 데 도움이 될 것입니다.
