7  데이터 결측과 위생

데이터베이스가 어떻게 결측 정보를 표현하는지 살펴보고, 결측 정보를 다룰 때, 3개 값을 가진 로직(three-valued logic) 데이터베이스 사용하여 결측 정보를 올바르게 처리하는 쿼리를 작성한다.

현실 세계 데이터는 결코 완전하지 않고 구멍은 항상 있다. null로 불리는 특별한 값을 사용하여 데이터베이스는 구멍을 표현한다. null는 0, False, 혹은 빈 문자열도 아니다.”아무것도 없음(nothing here)“을 의미하는 특별한 값이다. null을 다루는 것은 약간 특별한 기교와 신중한 생각을 요구한다.

기본적으로 SQLite는 출력에서 NULL 값을 표시하지 않는다. .nullvalue 명령은 SQLite가 NULL에 대해 지정한 값을 표시하도록 한다. NULL을 더 쉽게 볼 수 있도록 -null- 값을 사용한다. 

.nullvalue -null-

시작으로 Visited 테이블을 살펴보자. 혹은 더 정확히 말하면 날짜가 null이다.

시작으로 Visited 테이블을 살펴보자. 레코드가 8개 있지만 #752은 날짜가 없다. 즉, 그 날짜는 null이다:

SELECT * FROM Visited;
id site dated
619 DR-1 1927-02-08
622 DR-1 1927-02-10
734 DR-3 1930-01-07
735 DR-3 1930-01-12
751 DR-3 1930-02-26
752 DR-3 -null-
837 MSK-4 1932-01-14
844 DR-1 1932-03-22

Null 다른 값과는 다르게 동작한다. 만약 1930년 이전 레코드를 선택한다면,

SELECT * FROM Visited WHERE dated < '1930-01-01';
id site dated
619 DR-1 1927-02-08
622 DR-1 1927-02-10

결과 2개를 얻게 되고, 만약 1930년 동안 혹은 이후 레코드를 선택한다면,

SELECT * FROM Visited WHERE dated >= '1930-01-01';
id site dated
734 DR-3 1930-01-07
735 DR-3 1930-01-12
751 DR-3 1930-02-26
837 MSK-4 1932-01-14
844 DR-1 1932-03-22

결과를 5개 얻게 되지만, 레코드 #752은 결과값 어디에도 존재하지 않는다. 이유는 null<'1930-00-00' 평가결과가 참도 거짓도 아니기 때문이다. null 이 의미하는 것은 “알수가 없다”는 것이다. 그리고 만약 비교 평가식의 왼쪽편 값을 알지 못한다면, 비교도 참인지 거짓인지 알수가 없다. 데이터베이스는 “알 수 없음”을 null로 표현하기 때문에, null<'1930-00-00'의 값도 사실 null이다. null>='1930-00-00'도 또한 null인데 왜냐하면 질문에 답을 할 수 없기 때문이다. 그리고, WHERE절에 레코드는 테스트가 참인 것만 있기 때문에 레코드 #752은 어느 결과값에도 포함되지 않게 된다.

비교 연산 뿐만 아니라 다른 연산들도 null과 함께 이러한 방식으로 동작한다. 1+null도 null이고, 5*null도 null이고, log(null)도 null이 된다. 특히, 무언가를 = 과 != 으로 null과 비교하는 것도 null이 된다.

SELECT * FROM Visited WHERE dated = NULL;

출력을 생성하지 않으며, 다음도 마찬가지로 출력을 생성하지 않는다.

SELECT * FROM Visited WHERE dated != NULL;

null 인지 아닌지를 점검하기 위해서, 특별한 테스트 IS NULL을 사용해야 한다.

id site dated
752 DR-3 -null-

혹은, 역으로는 IS NOT NULL을 사용한다. 

SELECT * FROM Visited WHERE dated IS NOT NULL;
id site dated
619 DR-1 1927-02-08
622 DR-1 1927-02-10
734 DR-3 1930-01-07
735 DR-3 1930-01-12
751 DR-3 1930-02-26
837 MSK-4 1932-01-14
844 DR-1 1932-03-22

null 값은 나타나는 곳마다 문제를 일으킬 수 있다. 예를 들어, Lake에 의해 측정되지 않은 모든 염도 측정을 찾고 싶다고 가정해보자. 쿼리를 다음과 같이 작성하는 것이 자연스럽다.

SELECT * FROM Survey WHERE quant = 'sal' AND person != 'lake';
taken person quant reading
619 dyer sal 0.13
622 dyer sal 0.09
752 roe sal 41.6
837 roe sal 22.5

하지만, 상기 쿼리 필터는 누가 측정을 했는지 모르는 레코드는 빠뜨린다. 다시 한번, 이유는 person이 null일 때, !=비교는 null값을 만들어서 레코드가 결과값에 있지 않게 된다. 만약 이런 레코드도 유지하려고 한다면, 명시적으로 검사를 추가할 필요가 있다.

SELECT * FROM Survey WHERE quant = 'sal' AND (person != 'lake' OR person IS NULL);
taken person quant reading
619 dyer sal 0.13
622 dyer sal 0.09
735 -null- sal 0.06
752 roe sal 41.6
837 roe sal 22.5

여전히 이러한 접근법이 맞는 것인지 아닌 것인지 판단할 필요가 있다. 만약 절대적으로 결과에 Lake가 측정한 어떠한 값도 포함하지 않는다고 확신한다면, 누가 작업을 한 것인지 모르는 모든 레코드를 제외할 필요가 있다.

산술 또는 부울 연산자와 달리, min, max 또는 avg와 같은 여러 값을 결합하는 집계 함수는 null 값을 무시한다. 대부분의 경우, 이는 바람직한 결과이다. 예를 들어, 알려지지 않은 값들이 평균을 낼 때 null값은 데이터에 영향을 주지 않는다. 집계 함수는 다음 장에서 더 자세히 다룰 것이다.

7.1 데이터 위생

데이터 위생(data hygiene)은 특히 데이터 분석, 데이터베이스 관리 및 데이터 처리에서 중요한 개념으로, 오류가 적고 정확한 데이터를 보장하기 위한 일련의 방법론을 의미한다. 지금까지 조인이 어떻게 동작하는지 살펴봤으니, 왜 관계형 모델이 그렇게 유용한지 그리고 어떻게 가장 잘 사용할 수 있는지 살펴보자. 이를 위해, 데이터베이스 설계자들이 데이터를 어떻게 구조화하는지 살펴보자.

첫번째 규칙은 모든 값은 독립 요소로 분해될 수 없는 원자(atomic)적 속성을 지녀야 한다. 하나의 칼럼에 전체 이름을 넣는 대신에 별도로 구별되는 칼럼에 이름과 성을 저장해서 이름 컴포넌트를 뽑아내는 부분 문자열 연산(substring operation)을 사용할 필요가 없다. 좀더 중요하게는, 이름을 두 부분으로 저장한다. 왜냐하면, 공백으로 쪼개는 것은 신뢰성이 약하다. “Eloise St. Cyr” 혹은 “Jan Mikkel Steubart” 같은 이름을 생각하면 쉽게 알 수 있다.

두번째 규칙은 모든 레코드는 유일한 기본키를 가져야한다. 내재적인 의미가 전혀없는 일련번호가 될 수도 있고, 레코드 값중의 하나 (Person 테이블의 ident 필드), 혹은 Survey 테이블에서 심지어 모든 측정값을 유일하게 식별하는 (taken, person, quant) 삼중값의 조합도 될 수 있다.

세번째 규칙은 불필요한 정보가 없어야 한다. 예를 들어, Site테이블을 제거하고 다음과 같이 Visited 테이블을 다시 작성할 수 있다.

id lat long dated
619 -49.85 -128.57 1927-02-08
622 -49.85 -128.57 1927-02-10
734 -47.15 -126.72 1930-01-07
735 -47.15 -126.72 1930-01-12
751 -47.15 -126.72 1930-02-26
752 -47.15 -126.72 -null-
837 -48.87 -123.40 1932-01-14
844 -49.85 -128.57 1932-03-22

사실, 스프레드쉬트와 마찬가지로 각 행에 각 측정값에 관한 모든 정보를 기록하는 하나의 테이블을 사용할 수도 있다. 문제는 이와 같은 방식으로 조직된 데이터를 일관성있게 관리하는 것은 매우 어렵다. 만약 특정한 사이트의 특정한 방문 날짜가 잘못된다면, 데이터베이스에 다수의 레코드를 변경해야한다. 더 안좋은 것은 다른 사이트도 그 날짜에 방문되었기 때문에 어느 레코드를 변경할지 추정해야하는 것이다.

네번째 규칙은 모든 값의 단위는 명시적으로 저장되어야 한다. 예제 데이터베이스는 그렇지 못해서 문제다. 로에리히(Roerich)의 염도 측정치는 다른 사람들보다 몇 배나 더 높지만, 천분율(parts per thousand) 대신 백만분율(parts per million)을 사용했는지, 아니면 1932년 그 사이트에서 실제로 염분 이상 현상이 있었는지 알 수 없습니다.

한걸음 물러나서 생각하자, 데이터와 저장하는데 사용되는 도구는 공생관계다. 테이블과 조인은 데이터가 특정 방식으로 잘 조직되었다면 매우 효과적이다. 하지만, 만약 특정 형태로 되어 있다면 효과적으로 다룰 수 있는 도구가 있기 때문에 데이터를 그와 같은 방식으로 조직하기도 한다. 인류학자가 말했듯이, 도구는 도구를 만드는 손을 만든다. (the tool shapes the hand that shapes the tool) 즉, 도구(기술, 방법론 등)가 사용자(인간, 조직 등)에게 영향을 미치며, 동시에 사용자가 그 도구를 개선하거나 변형시키는 과정을 의미한다. 결과적으로, 도구와 사용자는 서로 영향을 주고받으며 발전해 나간다는 개념을 내포하고 있다.

7.2 세 종류 키

지금까지 데이터를 다중 연결된 테이블에 넣고 키(keys)를 사용하여 행을 연결하는 방식으로 데이터 모델을 생성했는데, 키와 관련된 몇몇 용어를 살펴볼 필요가 있다. 일반적으로 데이터베이스 모델에서 세가지 종류의 키가 사용된다.

  • 논리 키(logical key)는 “실제 세상”이 행을 찾기 위해서 사용하는 키다. 데이터 모델 예제에서, name 필드는 논리키다. 사용자에 대해서 screen_name이고, name 필드를 사용하여 프로그램에서 여러번 사용자 행을 찾을 수 있다. 논리 키에 UNIQUE 제약 사항을 추가하는 것이 의미있다는 것을 종종 이해하게 된다. 논리 키는 어떻게 바깥 세상에서 행을 찾는지 다루기 때문에, 테이블에 동일한 값을 가진 다중 행이 존재한다는 것은 의미가 없다.

  • 기본 키(primary key)는 통상적으로 데이터베이스에서 자동 대입되는 숫자다. 프로그램 밖에서는 일반적으로 의미가 없고, 단지 서로 다른 테이블에서 행을 열결할 때만 사용된다. 테이블에 행을 찾을 때, 통상적으로 주키를 사용해서 행을 찾는 것이 가장 빠르게 행을 찾는 방법이다. 주키는 정수형이어서, 매우 적은 저장공간을 차지하고 매우 빨리 비교 혹은 정렬할 수 있다. 이번에 사용된 데이터 모델에서 id 필드가 주키의 한 예가 된다.

  • 외래 키(foreign key)는 일반적으로 다른 테이블에 연관된 행의 주키를 가리키는 숫자다. 이번에 사용된 데이터 모델의 외부 키의 사례는 from_id다.

주키 id필드명을 호출하고, 항상 외부키에 임의 필드명에 접미사로 _id 붙이는 명명규칙을 사용한다.

연습문제

객관식

  1. 문제: SQL에서 NULL 값을 처리할 때 사용되는 특별한 키워드는 무엇입니까?
    • A. EMPTY
    • B. BLANK
    • C. IS NULL
    • D. NO VALUE
    • 정답: C. IS NULL
  2. 문제: SQL에서 NULL과 다른 값의 비교 결과는 무엇입니까?
    • A. 참(True)
    • B. 거짓(False)
    • C. NULL
    • D. 오류(Error)
    • 정답: C. NULL
  3. 문제: NULL 값을 포함하는 열에 대해 평균을 계산할 때, SQL 함수는 NULL 값을 어떻게 처리합니까?
    • A. NULL을 0으로 간주
    • B. NULL 값을 무시
    • C. 계산을 중지
    • D. 오류를 반환
    • 정답: B. NULL 값을 무시
  4. [원자 값 식별] 다음 중 어떤 것이 원자 값인가? 어떤 것이 아닌가? 그 이유는 무엇인가?
    1. 뉴질랜드 (New Zealand)
    2. 87 튜링 애비뉴 (87 Turing Avenue)
    3. 1971년 1월 25일 (January 25, 1971)
    4. XY 좌표 (0.5, 3.3)

알려진 날짜별로 정렬

날짜가 알려지지 않은 (즉 null) 항목은 빼고, 날짜 순으로 Visited 테이블에 있는 레코드를 정렬한 쿼리를 작성하시오.

집합에서 NULL

다음 쿼리가 어떤 결과를 생성할 것으로 예상하는가?

SELECT * FROM Visited WHERE dated IN ('1927-02-08', NULL);

실제로 어떤 결과를 생성하는가?

표식값 장단점

일부 데이터베이스 설계자들은 null 대신 표식값(sentienl value)을 사용하여 누락된 데이터를 표시하는 것을 선호한다. 예를 들어, 누락된 날짜에 “0000-00-00”을 사용하거나, 염도나 방사능 측정치가 누락된 경우 -1.0을 사용한다(실제 측정치는 음수가 될 수 없으므로). 이러한 접근방법이 단순화시킨 것은 무엇인가? 어떤 부담이나 위험을 도입하는가?

기본 키 식별하기

다음 테이블에 기본 키는 무엇인가? 즉, 어떤 값 혹은 값들을 조합해야 레코드를 유일무이하게 식별해낼 수 있을까?

latitude longitude date temperature
57.3 -22.5 2015-01-09 -14.2