4 데이터 원본/의존성의 개념과 SQL 문법 익히기

4.1 사전 준비

4.1.1 mariaDB 설치

4.1.1.1 windows

4.1.1.2 mac

4.1.2 GUI 툴 설치

4.1.2.1 heidiSQL(for windows)

4.1.2.2 sequel pro (for mac)

4.2 데이터를 바라보는 시선

4.2.1 데이터 원본

데이터 원본이란 처음 생성된 데이터나 혹은 일정 처리에 대해서 사용자들이 합의하면 처리가 된 이후에 관리하는 데이터를 뜻합니다. 원칙적으로는 의존성이나 변형을 가하지 않은 데이터를 뜻하는데, 실무적으로는 순수한 원본을 관리하는게 쉬운 일은 아닌 것 같습니다.

데이터 원본은 데이터의 신뢰성 때문에 필요합니다. 데이터란 무언가를 측정한 결과물이기 때문에 측정하고자 하는 그 자체는 아닙니다. 그렇다고 측정하지 않으면 분석할 수 없고, 개선할 수 없기 때문에 최대한 그 자체를 측정하려고 노력합니다. 저 또한 사람의 노력으로 작성되는 데이터를 신뢰하지 않는데요. 센서가 기록하는 데이터의 오류가 사람에 비해 낮기 때문입니다. 이렇게 측정된 데이터를 이제 상황에 맞게 가공하게 되는데, 그 가공 과정에서 오류가 발생합니다. 그렇기 때문에 맨 처음 사용하기 시작하는 데이터가 무엇인지를 확정하고, 그 데이터가 원본임을 기록해둘 필요가 있습니다.

데이터 원본은 재현성에도 중요한 개념입니다. 원본이 무엇인지 알리고 그 가공 과정을 재현할 수 있는 방법으로 공개하는 것이 자료의 신뢰를 확보하는 방법입니다.

4.2.2 데이터 의존성

데이터 의존성은 지금 작성한 자료가 얼마나 가공된 데이터를 참고하는지를 뜻합니다. 가공과정이 많을 수록 관점의 차이에서 오는 데이터의 의미 변경과 계산 오류가 많습니다. 최대한 원본을 확보하고, 과정을 단순화하고 오류가 없음을 증명하는 과정이 끊임없이 필요하면서, 의존성을 낮춰야 합니다.

물론 원본을 확보할 수 없을 때는 가공된 데이터를 사용해야 합니다만, 가공된 데이터를 신뢰할 수 있을 때라야 그 데이터를 사용해서 만든 결과물의 신뢰성을 확보할 수 있습니다. 쓰레기를 넣으면 쓰레기가 나온다는 데이터 과학의 격언은 항상 염두해 두어야 합니다.

4.3 기본 SQL 문법

4.3.1 CRUD와 데이터베이스

SQL이란 Structured Query Language의 줄임말입니다. 그럼 query가 뭔지 알아야 하는데, query/질의는 데이터베이스에게 무언가 요청하는 행위를 뜻합니다. 그렇기 때문에 SQL이라고 하면 데이터베이스가 알아들을 수 있는 명령어셋을 의미합니다. 대표적으로 CRUD라고 하는데요. CRUD는 create, read, update, delete의 줄임말로 데이터베이스가 동작하는 형태를 뜻합니다. 특별하게 영어에 의미를 담아 사람들이 사용하는 방식으로 만들기 위해 고려가 많이 되어 있는 언어로 알려져 있습니다.

4.3.1.1 database

일반적으로 db라고 하면 디비 종류로써의 이름을 지칭하는 경우가 많습니다. mysql이나 oracle이 대표적이죠. 오픈소스로 postgresql, mongodb 같은 것이 있습니다. 지금 제목으로 넣은 database는 그 각각의 데이터베이스 안에, 관리하고자 하는 데이터베이스자체를 뜻합니다. database는 이름과 몇 가지 옵션(ex: 인코딩)을 가지고 있고 아래 table을 가지고 있습니다.

4.3.1.2 table

table은 데이터베이스안에서 관리하는 데이터를 뜻합니다. 위키 백과를 보면 수직 열과 수평 행의 모델을 사용해 조직된 데이터 요소(값)들의 집합이다. 테이블의 열은 지정된 개수이지만, 행은 무한대로 확장될 수 있다. 각 행은 후보 키라고 식별되는 특정 열 서브셋에 나타나는 값으로 식별된다.라고 합니다. 여기서 열은 field라고도 하는데 attribute가 더 적절한 표현이라고 합니다. R에서의 data frame 입장에서는 column 입니다. 그리고 행은 recode라고도 하며 data frame 입장에서는 row입니다. 설문조사의 결과물을 정리했다고 하면에 각각 item, sample입니다.

4.3.1.3 index

데이터는 각각 의미를 지닐 때 저장하고 사용할 가치를 가집니다. 그렇기 때문에 중복된 데이터를 가지고 있을 필요는 없습니다. 문제는 위에 정의에서도 보듯 column부분은 고정되어 있고, row은 계속 확장할 수 있기 때문에 row방향의 데이터는 계속 쌓여나갑니다. column은 어떤 데이터를 저장할지를 결정하는 곳이라면, row은 그 결정된 방식대로 저장되어 있는 데이터를 뜻합니다. 그리고 그 row 중에 검색용, 혹은 데이터의 위치의 의미를 가지는 것을 index라고 합니다.

4.3.1.4 key

기본적으로 database의 table은 모두 key, 정확히는 primary key가 있습니다. key는 table에서 index의 역할을 하면서 데이터를 모두 유일한 값으로 만드는 역할을 합니다. R에서의 data frame 입장에서는 모든 column이 index역할을 하기도 하며 key는 상황에 따라 만들기도 하고, 신경쓰지 않기도 합니다. index와 primary key 역할은 row number가 기본적으로 수행하고 있다고 생각하시면 좋습니다.

4.3.1.5 create

create은 말 그대로 새롭게 만들 때 사용하는 명령입니다. 데이터를 만들거나(row를 추가) 테이블을 만들 때 사용합니다. 테이블을 만든다는 것은 빈 data frame을 만드는 것과 같습니다.

4.3.1.6 read

read는 있는 데이터를 읽어서 가져오는 것을 뜻합니다. 아래 자세히 다루겠지만 select, form, where, group by, order by의 형식을 띄구요. 각 어느 column을 가져올지, 어느 table에서 가져올지, 그 중에 어떤 조건을 만족하는 걸 가져올지, 어떻게 묶어서 가져올지, 어떻게 순서를 정할 건지를 명령합니다.

4.3.1.7 update

update는 있는 데이터를 고치는 것을 뜻합니다. 파일로 보자면 덮어쓰기 같은 개념이라고 보시면 됩니다.

4.3.1.8 delete

delete 또한 의미 그대로 지운다는 뜻입니다. 이 때도 데이터가 테이블을 지우는 것을 의미합니다. 이제 분석에서 많이 쓰는 read에 관련된 문법을 진행하겠습니다.

4.3.2 Read 문법

위에서 잠깐 언급 했듯 read는 데이터를 불러오는 행위를 뜻합니다. 우선 read의 기본 형식은 아래와 같습니다.

하나하나 살펴 보겠습니다.

select는 column을 선택하는 구문입니다. 기존에 있는 데이터를 기준으로 어떤 column을 선택할꺼냐를 묻는 곳으로 전체가 필요하면 *를 사용합니다.
from은 table을 선택하는 구문입니다. 생각 같아서는 어디 테이블에 어디 컬럼이 좋을 것 같은데, 영문권에서는 이 순서가 더 자연스러운 모양입니다.
where는 조건문입니다. 위에 두 경우(table, column)에 해당하는 데이터 전체를 가져오고 싶으면 select * from bank 같이 bank테이블 전체를 가져오는 query를 작성하시면 됩니다. 하지만 그 와중에 데이터들이 조건에 해당하는 일정 부분만 필요하면 그 부분을 where 뒤에 작성하시면 됩니다.
group by는 선택한 column으로 묶어서 처리하라는 뜻입니다. 예를 들어 A반의 학생들 평균을 알고 싶으면 A반 학생이라는 조건인 성적만 가져와서 평균을 낼 수도 있지만, 반끼리 데이터를 사용하라고 알려주고 평균을 구할 수도 있습니다.
order by는 정렬을 위한 column을 정할 때 사용합니다. db는 table의 데이터를 저장할 때 순서를 고려해서 저장하지 않습니다. 그렇기 때문에 사람의 편의를 위해 순서를 강제하는 방법을 알려주는 것입니다.
하나 더 as는 길게 만들어진 무엇을 줄여서 쉽게 작성하기 위해서 사용합니다. column명이나 table명에서 많이 사용합니다.

위에 있는 내용 중에 중요한 개념이 하나 더 있습니다. 그것은 join인데요. table끼리 합칠 때 사용합니다.

그럼 data frame이랑 기능을 맞춰 보겠습니다. R에서 변수 train에 데이터가 있다고 할때,

# for R
train

# for db
select * from train

그럼 column을 선택하는 것은 어떻게 하나요?

# for R
train[,c("fecha_dato","age")]

# for db
select fecha_dato, age from train

이제 row에 조건을 줄 수도 있겠네요?

# for R
train[train$fecha_dato > 2016-06-28 ,c("fecha_dato","age")]

# for db
select fecha_dato, age from train where train.fecha_dato > 2016-07-28

각 성별에 나이 평균도 구할 수 있습니다.

# for R
data<-train[,c("sexo","age")]
mean(data[data$sexo=="F","age"])
mean(data[data$sexo=="M","age"])

# for db
select sexo, avg(age) from train group by sexo

4.3.3 Join

데이터를 사용하기 편하게 만들기 위해서는 join이 꼭 필요합니다. 여러가지 사정으로 인해서 사용하고자 하는 사람에게 꼭 맞는 형태의 table이 모두 준비되어 있을 수 없기 때문입니다. 그래서 여러 table을 합쳐서 원하는 하나의 table로 만들어야 하는데요. 그렇게 만들어 주는 구문이 바로 join입니다. 우선 join의 종류를 보여드리겠습니다.

많이 헷갈리시는 부분이 있는데 위의 포함관계는 row값, 즉 데이터의 관점에서를 뜻합니다. 두 개의 테이블을 각 column을 합쳐서 데이터를 만드는데, key를 기준으로 column을 합치는 것입니다. 그래서 각 포함관계에 있는 row 방향 데이터를 사용할꺼냐 아니냐를 기준으로 보시면 됩니다.

모두 left, right, inner, outer의 4가지가 있으며 inner을 제외한 3가지는 겹치는 부분을 포함하냐, 제외하냐의 경우까지 따져서 총 7가지 방법으로 join할 수 있습니다.

이 중에 left와 방향만 다른 right을 제외하고 outer에서 inner를 빼는 경우를 제외한 총 4가지를 보도록 하겠습니다.

4.3.3.1 inner

inner는 가장 단순하고 이해하기 쉬운 join의 종류입니다. 합치고자 하는 테이블 A, B가 있다고 했을 때, A가 가지고 있는 column과 B가 가지고 있는 column을 key로 지정한 column을 기준으로 컬럼 방향을 합칩니다. 그런데 양 데이터가 전혀 연관이 없다면 합치는게 의미가 없겠죠. 각 테이블의 key의 데이터가 같은 데이터끼리 합치는 과정이 join입니다.

inner join은 이렇게 합칠 때 양쪽에 데이터가 모두 있는 경우만을 사용한다고 선언하는 것입니다. 포함관계에서 양쪽에만 있는 데이터가 안쪽에 표시되므로, 안쪽의 데이터만 사용하겠다고 해서 inner 라고 보면 좋을 것 같습니다.

select *
from A inner join B on A.key = b.key;

위에 잘 보시면 select와 from까지만 사용한 것입니다. from안에 테이블을 지정할 때, 두개의 테이블을 합치는 방법에 대해 작성해서 테이블이 들어갈 위치에 넣었습니다. 여기서 join 구문은 key를 작성하기 위해 on이라는 구문을 사용했습니다. 참, sql은 마지막에 꼭 ;표시를 해줘야 합니다. query 구문이 끝났다는 뜻입니다.

그럼 from에 속한 부분만 다시 보겠습니다.

A inner join B on A.key = B.key는 테이블 A와 테이블 B를 inner join 할 껀데 A에서는 key 라는 column을, B에서는 key라는 column을 key로 정해서 같은 데이터끼리 합쳐라는 뜻입니다. 이렇게 합치면 특징이 원래 테이블에 데이터가 NULL이 없다면 NULL인 데이터가 만들어지거나 하지 않습니다.

4.3.3.2 outer

outer join은 모든 데이터를 사용한다는 뜻입니다. 그러다 보니 양쪽 테이블에 서로에게 없는 column 데이터는 없으므로 없는 데이터를 모두 null로 표시해주고 데이터를 줍니다. 위에서 inner로 정의한 안 쪽을 제외하면 두 곳의 바깥쪽이 남습니다. 양쪽 모두를 부를 때는 full, 나머지는 아시다시피 left, right입니다. 그런데 mysql이 근간인 mariaDB는 full outer join을 사용할 수 없습니다. 이때는 union으로 우회하는데 여기서는 다루지 않겠습니다.

4.3.3.3 left

이제 outer조인 중 왼쪽을 지칭해서 사용하는 left를 보겠습니다.

우선 왼쪽 테이블을 기준으로 삼습니다. 그리고 양쪽에 같은 데이터를 가지고 있는 column을 key로 선정합니다. 이 key는 테이블 두 개를 합쳤을 때 양쪽 다 있는 데이터이므로 한번만 나오면 되는 데이터 입니다. 그리고 양쪽 테이블에 모두 있는 데이터를 쓸꺼냐 말꺼냐를 결정합니다.

왼쪽의 테이블을 A, 오른쪽의 테이블을 B라고 하고 join을 진행해 보겠습니다.

select *
from A left join B on A.key = b.key;

위에 처럼 했을때 무슨 의미 인지 같이 짚어 보겠습니다.

A 테이블을 왼쪽에 두고 A 테이블과 B 테이블에 있는 key를 기준으로 합치는데 B 테이블에 만약 데이터가 없으면 null로 처리줘

그리고 양쪽 테이블에 있는 데이터를 쓴다 안쓴다 언급이 없었으므로 쓰는 형태로 진행이 되었습니다. 사용하는 것이 기본값이라는 거죠. 이번엔 데이터를 안쓴다고 해볼까요.

select *
from A left join B on A.key = b.key
where B.key is NULL;

where에는 데이터에 대한 조건이 들어간다고 설명드렸죠. 이건 B.key가 NULL 인 조건만 데이터를 사용하겠다는 뜻입니다. 그러니까 양쪽 테이블에 모두 있는 데이터는 사용하지 않겠다는 뜻입니다. 반대로 말하면 B 테이블에도 있는 데이터를 빼고 순수하게 A 테이블에만 있는 데이터를 사용하겠다는 뜻입니다.

4.3.4 실습

실습은 github을 클론하셔서 codeForclass4.R 파일을 확인하시면 됩니다.
작은 파일은 저장소에 함께 있고, 큰 파일은 코드내에서 다운로드 받도록 작성했습니다.