9  조인(Join)

과거 기상 데이터를 집계하는 웹사이트에 데이터를 제출하기 위해, 위도, 경도, 날짜, 측정량, 측정값 형식으로 데이터를 체계적으로 만들 필요가 있을 수 있다. 그러나, 위도와 경도는 Site 테이블에 있고, 측정 날짜는 Visited 테이블에, 측정값 자체는 Survey 테이블에 있다. 어떤 방식이든지 상기 테이블을 조합할 필요가 있다.

테이블들 간 관계는 다음 그림을 통해 확인할 수 있다.

조사 데이터베이스 구조

조사 데이터베이스 구조

9.1 데이터 모델

survey.db 데이터베이스의 정보를 이용해 데이터프레임을 추출하고, 이를 datamodelr에 넣어 데이터베이스 모델을 시각화한다. datamodelr 패키지 nycflights13 사례를 참조하여 유사한 방식으로 제작한다. datamodelr는 R에서 데이터 모델 다이어그램을 그리는 방법을 제공한다. 먼저 데이터프레임만 시각화하는 작업을 진행한다. 

# 0. 환경설정 -----------------------

library(dbplyr)
library(tidyverse)
library(datamodelr)
library(DBI)

# 1. 데이터 연결 -----------------------
survey_con <- DBI::dbConnect(RSQLite::SQLite(), "data/survey.db")

survey <- tbl(survey_con, "Survey") %>% collect()
person <- tbl(survey_con, "Person") %>% collect()
site <- tbl(survey_con, "Site") %>% collect()
visited <- tbl(survey_con, "Visited") %>% collect()

survey_model <- dm_from_data_frames(survey, person, site, visited)
survey_graph <- dm_create_graph(survey_model, rankdir = "BT", col_attr = c("column", "type"))

dm_render_graph(survey_graph)

dm_export_graph(survey_graph, file_name = "images/survey_dm_tables.png")

조사(survey) 데이터베이스 테이블

조사(survey) 데이터베이스 테이블

다음으로 테이블의 키를 찾아 이를 연결시켜서 관계도 함께 표현되도록 작업한다. 

survey_model <- dm_add_references(
  survey_model,
  
  person$id == survey$person,
  survey$person == visited$id,
  visited$site == site$name,
  survey$taken == visited$id
)

survey_rel_graph <- dm_create_graph(survey_model, rankdir = "LR", col_attr = c("column", "type"))

dm_render_graph(survey_rel_graph)

dbDisconnect(survey_con)

dm_export_graph(survey_rel_graph, file_name = "images/survey_dm_relation.png")

조사(survey) 데이터베이스 연결 관계

조사(survey) 데이터베이스 연결 관계

이러한 작업을 하는 SQL 명령어가 JOIN이다. 어떻게 동작하는지 확인하기 위해서, Site와 Visited 테이블을 조인하면서 살펴보자.

SELECT * FROM Site JOIN Visited;
name lat long id site dated
DR-1 -49.85 -128.57 619 DR-1 1927-02-08
DR-1 -49.85 -128.57 622 DR-1 1927-02-10
DR-1 -49.85 -128.57 734 DR-3 1930-01-07
DR-1 -49.85 -128.57 735 DR-3 1930-01-12
DR-1 -49.85 -128.57 751 DR-3 1930-02-26
DR-1 -49.85 -128.57 752 DR-3 -null-
DR-1 -49.85 -128.57 837 MSK-4 1932-01-14
DR-1 -49.85 -128.57 844 DR-1 1932-03-22
DR-3 -47.15 -126.72 619 DR-1 1927-02-08
DR-3 -47.15 -126.72 622 DR-1 1927-02-10
DR-3 -47.15 -126.72 734 DR-3 1930-01-07
DR-3 -47.15 -126.72 735 DR-3 1930-01-12
DR-3 -47.15 -126.72 751 DR-3 1930-02-26
DR-3 -47.15 -126.72 752 DR-3 -null-
DR-3 -47.15 -126.72 837 MSK-4 1932-01-14
DR-3 -47.15 -126.72 844 DR-1 1932-03-22
MSK-4 -48.87 -123.4 619 DR-1 1927-02-08
MSK-4 -48.87 -123.4 622 DR-1 1927-02-10
MSK-4 -48.87 -123.4 734 DR-3 1930-01-07
MSK-4 -48.87 -123.4 735 DR-3 1930-01-12
MSK-4 -48.87 -123.4 751 DR-3 1930-02-26
MSK-4 -48.87 -123.4 752 DR-3 -null-
MSK-4 -48.87 -123.4 837 MSK-4 1932-01-14
MSK-4 -48.87 -123.4 844 DR-1 1932-03-22

JOIN은 두 테이블의 교차 곱(cross product)을 생성한다. 즉, 한 테이블의 각 레코드를 다른 테이블의 각 레코드와 결합하여 가능한 모든 조합을 만든다. Site에는 세 개의 레코드가 있고 Visited에는 여덟 개가 있으므로, 조인의 출력에는 24개의 레코드가 있다(3 * 8 = 24). 그리고 각 테이블에는 세 개의 필드가 있으므로, 출력에는 여섯 개의 필드가 있다(3 + 3 = 6).

조인이 수행하지 않은 것은 조인되는 레코드가 서로 관계가 있는지를 파악하는 것이다. 어떻게 조인할지 명시할 때까지 레코드가 서로 관계가 있는지 없는지 알 수 있는 방법은 없다. 이를 위해서 동일한 사이트 이름을 가진 조합에만 관심있다는 것을 명시하는 절(clause)을 추가한다.

SELECT
  Site.lat,
  Site.long,
  Visited.dated
FROM
  Site
  JOIN Visited ON Site.name = Visited.site;
name lat long id site dated
DR-1 -49.85 -128.57 619 DR-1 1927-02-08
DR-1 -49.85 -128.57 622 DR-1 1927-02-10
DR-1 -49.85 -128.57 844 DR-1 1932-03-22
DR-3 -47.15 -126.72 734 DR-3 1930-01-07
DR-3 -47.15 -126.72 735 DR-3 1930-01-12
DR-3 -47.15 -126.72 751 DR-3 1930-02-26
DR-3 -47.15 -126.72 752 DR-3 -null-
MSK-4 -48.87 -123.4 837 MSK-4 1932-01-14

ON은 WHERE와 매우 유사하며, SQL 기본과정에서 이 둘을 서로 바꿔가며 사용할 수 있다. 외부 조인(outer joins)이 미치는 영향에 대해 차이점이 있지만, SQL 기본과정 범위를 벗어난다. 쿼리에 ON을 추가하면, 데이터베이스 관리자는 두 다른 사이트에 대한 정보를 결합한 레코드를 제거하고 원하는 것만 남긴다.

조인의 출력에서 필드 이름을 지정하기 위해 Table.field를 사용했음을 주목하자. 테이블에 동일한 이름의 필드가 있을 수 있고, 어떤 것에 대해 이야기하고 있는지 구체적으로 명시할 필요가 있기 때문이다. 예를 들어, Person과 Visited 테이블을 조인하면, 결과는 각 원래 테이블에서 id라는 필드를 상속받는다.

이제 같은 점 표기법(dotted notation)을 사용하여 조인에서 실제로 원하는 칼럼 3개를 선택할 수 있다. 

SELECT
  Site.lat,
  Site.long,
  Visited.dated
FROM
  Site
  JOIN Visited ON Site.name = Visited.site;
lat long dated
-49.85 -128.57 1927-02-08
-49.85 -128.57 1927-02-10
-49.85 -128.57 1932-03-22
-47.15 -126.72 -null-
-47.15 -126.72 1930-01-12
-47.15 -126.72 1930-02-26
-47.15 -126.72 1930-01-07
-48.87 -123.4 1932-01-14

두 테이블을 조인하는 것이 좋다면, 여러 테이블을 조인하는 것이 더 좋을 것이다. 실제로, 쿼리에 더 많은 JOIN 절을 추가하고 논리적으로 맞지 않는 레코드 조합을 걸러내기 위해 더 많은 ON 테스트를 추가함으로써 어떤 수의 테이블이든 조인할 수 있다.

SELECT
  Site.lat,
  Site.long,
  Visited.dated,
  Survey.quant,
  Survey.reading
FROM 
  Site
  JOIN Visited
  JOIN Survey ON Site.name = Visited.site
  AND Visited.id = Survey.taken
  AND Visited.dated IS NOT NULL;
lat long dated quant reading
-49.85 -128.57 1927-02-08 rad 9.82
-49.85 -128.57 1927-02-08 sal 0.13
-49.85 -128.57 1927-02-10 rad 7.8
-49.85 -128.57 1927-02-10 sal 0.09
-47.15 -126.72 1930-01-07 rad 8.41
-47.15 -126.72 1930-01-07 sal 0.05
-47.15 -126.72 1930-01-07 temp -21.5
-47.15 -126.72 1930-01-12 rad 7.22
-47.15 -126.72 1930-01-12 sal 0.06
-47.15 -126.72 1930-01-12 temp -26.0
-47.15 -126.72 1930-02-26 rad 4.35
-47.15 -126.72 1930-02-26 sal 0.1
-47.15 -126.72 1930-02-26 temp -18.5
-48.87 -123.4 1932-01-14 rad 1.46
-48.87 -123.4 1932-01-14 sal 0.21
-48.87 -123.4 1932-01-14 sal 22.5
-49.85 -128.57 1932-03-22 rad 11.25

Site, Visited, Survey 테이블 레코드들이 서로 어떻게 대응하는지 알 수 있는 이유는 테이블들이 기본 키(primary key)와 외래 키(foreign key)를 포함하기 때문이다. 기본 키는 테이블 각 레코드를 고유하게 식별하는 값 또는 값의 조합이다. 외래키는 또 다른 테이블에 있는 유일하게 레코드를 식별하는 하나의 값(혹은 여러 값의 조합)이다. 다른 방식으로 말하면, 외래 키는 한 테이블의 기본 키가 다른 테이블에 존재하는 것이다. 예제 데이터베이스에서 Person.ident는 Person 테이블의 기본키인 반면에, Survey.person은 외래키로 Survey 테이블의 항목과 Person 테이블의 항목을 연결하는 외래 키다.

대부분의 데이터베이스 설계자들은 모든 테이블이 잘 정의된 기본 키를 가져야 한다고 믿는다. 또한, 이 키는 데이터 자체와 별개여야 하므로, 데이터를 변경할 필요가 있을 때 한 곳에서만 변경을 하면 된다. 이를 위한 쉬운 방법 중 하나는 데이터베이스에 레코드를 추가할 때마다 임의 고유 ID를 생성하는 것이다. 실제로 매우 흔한 방법이며, “학생 번호”나 “환자 번호”와 같은 이름을 가진 이러한 ID는 거의 항상 데이터베이스 시스템에서 고유한 레코드 식별자로 사용된다. 아래 쿼리에서 보여주는 것처럼, SQLite는 테이블에 레코드가 추가될 때 [자동으로 레코드 번호를 부여][rowid]하고, 쿼리에서 레코드 번호를 사용한다.

SELECT rowid, * FROM Person;
rowid id personal family
1 dyer William Dyer
2 pb Frank Pabodie
3 lake Anderson Lake
4 roe Valentina Roerich
5 danforth Frank Danforth

연습문제

객관식

  1. 문제: SQL에서 INNER JOIN은 어떤 조건에서 레코드를 반환합니까?
    1. 모든 레코드
    2. 첫 번째 테이블의 레코드만
    3. 두 테이블 간 일치하는 레코드만
    4. 일치하지 않는 레코드만
  1. 문제: LEFT JOIN을 사용할 때 결과 집합에 포함되는 것은 무엇입니까?
    1. 오른쪽 테이블의 모든 레코드
    2. 일치하는 레코드가 없는 왼쪽 테이블의 레코드
    3. 왼쪽 테이블의 모든 레코드와 일치하는 오른쪽 테이블의 레코드
    4. 두 테이블 간 일치하지 않는 모든 레코드
  1. 문제: FULL OUTER JOIN의 기능은 무엇입니까?
    1. 두 테이블의 교집합만 반환
    2. 두 테이블의 합집합을 반환
    3. 오직 한 테이블의 레코드만 반환
    4. 일치하는 레코드가 없는 경우에만 반환

방사능 측정값 목록

DR-1 사이트의 모든 방사선 측정치를 출력하는 쿼리를 작성하세요.

프랭크 위치

“Frank” 가 방문한 모든 사이트를 출력하는 쿼리를 작성하세요.

쿼리 독해

다음 쿼리가 무슨 결과를 산출하는지 말로 기술하세요.

SELECT Site.name FROM Site JOIN Visited
ON Site.lat < -49.0 AND Site.name = Visited.site AND Visited.dated >= '1932-01-01';

누가 어디에 방문했는가?

각 사이트의 정확한 위치(위도, 경도)와 방문 날짜별로 정렬된 목록을 작성하고, 사이트를 방문한 사람의 개인 이름과 성, 그리고 측정 유형 및 측정값을 나타내는 쿼리를 작성한다. null 값은 모두 피한다. 힌트: 15개의 레코드와 8개의 필드를 얻어야 한다.