5 데이터 선택하기
1920년 후반, 1930년 초반 William Dyer, Frank Pabodie,Valentina Roerich는 남태평양 도달불가능한 극(Pole of Inaccessibility)과 이어서 남극 대륙을 탐험했다. 2년 전에 이들의 탐험 기록이 Miskatonic 대학 창고 사물함에서 발견됐다. 기록을 스캔해서 OCR로 저장했고, 이제는 검색가능하고 분석이 용히한 방식으로 정보를 저장하고자 한다.
기본적으로 3가지 선택 옵션(텍스트 파일, 스프레드쉬트, 데이터베이스)이 있다. 텍스트 파일은 생성하기 가장 쉽고 버젼 제어와 궁합이 맞지만, 검색과 분석 도구를 별도로 구축해야한다. 스프레드쉬트는 단순한 분석에는 적합하지만, 크고 복잡한 데이터셋을 매우 잘 다루지는 못한다. 그래서 데이터를 데이터베이스에 넣어서 어떻게 검색과 분석을 하는지 이번 학습에서 배울 것이다.
5.1 기본 지식
관계형 데이터베이스(relational database)는 테이블(tables)로 정렬된 정보를 저장하고 다루는 방식이다. 각 테이블은 데이터를 기술하는 필드(fields)로도 알려진 열(column)과 데이터를 담고 있는 레코드(records)로 알려진 행(row)으로 구성된다.
스프레드쉬트를 사용할 때, 이전 값에 기초하여 새로운 값을 계산할 때 공식을 셀(cell)에 넣어서 구한다. 데이터베이스를 사용할 때는 쿼리(queries, 질의)로 불리는 명령문을 데이터베이스 관리자(database manager)에게 보낸다. 데이터베이스 관리자는 사용자를 대신해서 데이터베이스를 조작하는 프로그램이다. 데이터베이스 관리자는 쿼리가 명기하는 임의의 조회작업과 계산작업을 수행하고 다음 쿼리작업 시작점으로 사용될 수 있는 테이블 형식으로 결과값을 반환한다.
모든 데이터베이스 관리자(IBM DB2, PostgreSQL, MySQL, Microsoft Access, SQLite)는 서로 다른 고유한 방식으로 데이터를 저장하기 때문에, 특정 데이터베이스로 저장된 데이터를 다른 곳의 데이터베이스에서 직접적으로 사용할 수는 없다. 하지만, 모든 데이터베이스 관리자에는 데이터를 다양한 형식으로 가져오기(import)와 내보내기(export) 기능을 제공한다. 그래서 한 곳에서 다른 곳으로 정보를 이동하는 것이 가능하다.
쿼리는 SQL로 불리는 언어로 작성된다. SQL 은 “Structured Query Language”(구조적 질의 언어)의 약자다. SQL은 데이터를 분석하고 다시 조합할 수 있는 수백개의 다른 방식을 제공한다. 본서에서 일부를 살펴볼 것이지만, 일부작업이 데이터 과학자가 수행하는 일의 대부분을 처리할 것이다.
5.2 조사 데이터베이스
SQLite를 사용하여 본격적인 쿼리문 작성에 앞서 실습에 사용될 데이터베이스 구조를 살펴보자.
Person: 측정값을 기록한 사람, id는 해당 인물을 위한 고유 식별자를 나타낸다.
| id | personal | family |
|---|---|---|
| dyer | William | Dyer |
| pb | Frank | Pabodie |
| lake | Anderson | Lake |
| roe | Valentina | Roerich |
| danforth | Frank | Danforth |
Site: 측정값이 기록된 sites의 위치를 나타낸다.
| name | lat | long |
|---|---|---|
| DR-1 | -49.85 | -128.57 |
| DR-3 | -47.15 | -126.72 |
| MSK-4 | -48.87 | -123.4 |
Visited: 측정 위지에서 측정값이 기록된 구체적인 위치와 날짜에 대한 특정 식별 id를 나타낸다.
| 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 |
Survey: 각 측정지의 정확한 위치에서 취한 측정값들로, taken으로 식별된다. quant 필드는 ‘양’을 의미하는 줄임말로 측정 대상을 나타낸다. 값은 각각 ’방사능’, ‘염도’, ’온도’를 의미하는 rad, sal, temp로 표시된다.
| taken | person | quant | reading |
|---|---|---|---|
| 619 | dyer | rad | 9.82 |
| 619 | dyer | sal | 0.13 |
| 622 | dyer | rad | 7.8 |
| 622 | dyer | sal | 0.09 |
| 734 | pb | rad | 8.41 |
| 734 | lake | sal | 0.05 |
| 734 | pb | temp | -21.5 |
| 735 | pb | rad | 7.22 |
| 735 | -null- | sal | 0.06 |
| 735 | -null- | temp | -26.0 |
| 751 | pb | rad | 4.35 |
| 751 | pb | temp | -18.5 |
| 751 | lake | sal | 0.1 |
| 752 | lake | rad | 2.19 |
| 752 | lake | sal | 0.09 |
| 752 | lake | temp | -16.0 |
| 752 | roe | sal | 41.6 |
| 837 | lake | rad | 1.46 |
| 837 | lake | sal | 0.21 |
| 837 | roe | sal | 22.5 |
| 844 | roe | rad | 11.25 |
3개 항목 (Visited 테이블에서 1개, Survey 테이블에서 2개)은 실제 데이터가 아닌 특별한 -null- 항목을 가지고 있다. 왜냐하면 어떠한 값도 담고 있지 않아서 그렇다. 뒤에서 결측값(missing)을 다시 다룰 것이다.
5.3 데이터 유무 확인하기
쉘 명령으로, survey.db를 저장한 디렉토리로 작업 디렉토리를 변경한다. 만약 바탕화면에 저장했다면 다음 명령어를 사용해야 한다.
$ cd Desktop
$ ls | grep survey.dbsurvey.db동일한 출력이 나오면 다음 명령어를 실행할 수 있다.
$ sqlite3 survey.dbSQLite version 3.8.8 2015-01-16 12:08:06
Enter ".help" for usage hints.
sqlite>SQLite에 survey.db 파일에 있는 데이터베이스를 로드하라는 명령어다.
유용한 시스템 명령어 목록을 보려면 .help를 입력한다.
SQLite 시스템 명령어들은 SQL 명령어와 구별하기 위해 .로 시작한다.
데이터베이스의 테이블 목록을 보려면 .tables를 입력한다.
.tablesPerson Site Survey Visited위와 같은 테이블이 있다면 각 테이블에 어떤 정보가 저장되어 있는지 궁금할 수 있다. 테이블에 대한 자세한 정보를 얻으려면 .schema를 입력해 데이터베이스의 테이블을 생성하는 데 사용된 SQL 문을 확인한다. 테이블명과 함께 테이블을 구성하는 칼럼과 칼럼 자료형이 목록으로 나열된다.
.schemaCREATE TABLE Person(
ident text,
personal text,
family text
);
CREATE TABLE Site(
name text,
lat real,
long real
);
CREATE TABLE Visited(
ident integer,
site text,
dated text
);
CREATE TABLE Survey(
taken integer,
person text,
quant text,
reading real
);출력 형식은 <columnName dataType>로 되어 있다. 따라서 첫 번째 줄에서 Person 테이블에 세 개의 열이 있음을 알 수 있다:
- id는 text 자료형
- personal는 text 자료형
- family는 text 자료형
참고: 사용 가능한 자료형은 데이터베이스 관리자에 따라 다르며, 지원되는 자료형은 온라인에서 검색할 수 있다.
SQLite 설정을 변경하여 출력 가독성을 높일 수 있다. 먼저, 왼쪽 정렬 출력 모드를 설정한다. 그런 다음 칼럼명 헤더 표시를 켠다.
.mode column
.header on또 다른 방법으로, .sqliterc 파일을 생성하여 설정사항을 자동으로 가져올 수 있다. 위의 명령어를 추가하고 SQLite를 다시 연다. 윈도우에서는 C:\Users\<yourusername>.sqliterc를 사용한다. 리눅스와 맥(Linux/MacOS)에서는 /Users/<yourusername>/.sqliterc를 사용한다.
SQLite를 종료하고 쉘 명령줄로 돌아가려면, .quit 또는 .exit를 사용하여 종료한다.
5.4 SQL 헬로월드
과학자의 이름을 화면에 표시하는 SQL 쿼리문을 작성해 보자. SQL SELECT 문을 사용해서 원하는 칼럼의 이름과 해당 열이 있는 테이블을 지정한다. 쿼리와 결과는 다음과 같다.
SELECT family, personal FROM Person;| family | personal |
|---|---|
| Dyer | William |
| Pabodie | Frank |
| Lake | Anderson |
| Roerich | Valentina |
| Danforth | Frank |
쿼리 끝에 세미콜론(;)은 쿼리가 완료되어 실행준비 되었다고 데이터베이스 관리자에게 알린다. 명령문과 칼럼 이름을 모두 소문자로 작성했고, 테이블 이름은 타이틀 케이스(Title Case, 단어의 첫 문자를 대문자로 표기)로 작성했다. 하지만 그렇게 반듯이 할 필요는 없다. 아래 예제가 보여주듯이, SQL은 대소문자 구분하지 않는다(case insensitive).
SeLeCt FaMiLy, PeRsOnAl FrOm PeRsOn;| family | personal |
|---|---|
| Dyer | William |
| Pabodie | Frank |
| Lake | Anderson |
| Roerich | Valentina |
| Danforth | Frank |
모두 소문자, 타이틀 케이스, 소문자 낙타 대문자(Lower Camel Case)를 선택하든지 관계없이 일관성을 가져라. 무작위 대문자를 추가적으로 인지하지 않더라고 복잡한 쿼리는 충분히 그 자체로 이해하기 어렵다.
SQL의 대소문자 구분이 없는 특성을 이용해 SQL 문의 다른 부분을 구분할 수 있다. 일반적인 SQL 문 작성사례로, SQL 키워드(예: SELECT, FROM)는 대문자로, 테이블 이름은 타이틀 케이스로, 필드 이름은 소문자로 사용하는 관례를 따른다. 어떤 대소문자 사용 관례를 선택하든 일관성을 유지하는 것이 중요하다.
SQL 구문의 한 측면인 ; (세미콜론)으로 명령을 마치지 않는 것은 초보자와 전문가 모두에게 혼란을 주고 있다. ;를 추가하지 않고 명령어를 입력한 후 엔터를 누르면 다음과 같은 상황이 발생한다.
SELECT id FROM Person
...>
...>이것은 SQL이 추가 명령을 기다리거나 ;를 통해 SQL에게 명령을 마칠 준비가 되었음을 알리는 프롬프트다. 이 문제는 쉽게 해결할 수 있다! 단순히 ;를 입력하고 엔터를 누르면 된다!
쿼리로 돌아가서, 데이터베이스 테이블의 행과 칼럼이 특정한 순서로 저장되어 있지 않는다는 것을 이해하는 것이 중요하다. 어떤 순서로 항상 표시되지만, 다양한 방식으로 출력을 제어할 수 있다. 예를 들어, 쿼리를 다음과 같이 작성해서 칼럼을 교환할 수 있다.
SELECT personal, family FROM Person;| personal | family |
|---|---|
| William | Dyer |
| Frank | Pabodie |
| Anderson | Lake |
| Valentina | Roerich |
| Frank | Danforth |
혹은 심지어 칼럼을 반복할 수도 있다.
SELECT id, id, id FROM Person;| id | id | id |
|---|---|---|
| dyer | dyer | dyer |
| pb | pb | pb |
| lake | lake | lake |
| roe | roe | roe |
| danforth | danforth | danforth |
손쉬운 방법으로, *을 사용해서 테이블의 모든 칼럼을 선택할 수도 있다.
SELECT * FROM Person;| id | personal | family |
|---|---|---|
| dyer | William | Dyer |
| pb | Frank | Pabodie |
| lake | Anderson | Lake |
| roe | Valentina | Roerich |
| danforth | Frank | Danforth |
연습문제
객관식
- 문제: SQL에서
SELECT문은 무엇에 사용됩니까?- 데이터베이스에 새로운 테이블을 생성
- 테이블에서 특정 칼럼을 선택
- 데이터베이스의 보안 설정을 변경
- 데이터베이스의 저장된 프로시저를 실행
- 문제: 다음 중 SQL 쿼리 작성 시 고려해야 할 사항은 무엇입니까?
- 항상 모든 쿼리를 대문자로 작성
- 쿼리의 마지막에는 반드시 세미콜론을 사용
- 모든 SQL 명령어는 고유한 테이블에 연결
- 테이블 이름은 항상 소문자로 작성
- 문제:
SELECT문을 사용하여 여러 열을 선택할 때, 열 이름 사이에 무엇을 넣어야 합니까?- 쉼표
- 세미콜론
- 콜론
- 슬래시
CREATE 쿼리문
데이터베이스 SQL 문을 작성할 때 테이블 칼럼 자료형(문자, 숫자, 날짜 등)을 파악하고 있어야 버그없는 깔끔한 쿼리를 작성할 수 있다.
.schema 명령어를 사용하여 정수를 포함하는 칼럼을 확인하시오.
.schemaCREATE TABLE Person (id text, personal text, family text);
CREATE TABLE Site (name text, lat real, long real);
CREATE TABLE Survey (taken integer, person text, quant text, reading real);
CREATE TABLE Visited (id integer, site text, dated text);사이트 이름 선택하기
Site 테이블에서 name 칼럼만 선택하는 쿼리를 작성하시오.
쿼리 스타일
많은 사람들은 쿼리를 다음과 같이 작성한다.
SELECT personal, family FROM person;또는,
select Personal, Family from PERSON;어떤 스타일이 가장 읽기 쉽다고 생각하며, 그 이유는 무엇인가?