새로운 값 계산하기

주의깊이 탐험 기록을 다시 정독한 뒤에, 탐험대가 보고한 방사선 측정치가 5%만큼 상향되어 수정될 필요가 있다는 것을 깨달았다. 저장된 데이터를 변형하기 보다는 쿼리의 일부분으로서 즉석에서 계산을 수행할 수 있다.

%load_ext sqlitemagic

%%sqlite survey.db
select 1.05 * reading from Survey where quant='rad';

쿼리를 실행하면, 표현식 1.05 * reading이 각 행마다 평가된다. 표현식에는 임의의 필드, 통상 많이 사용되는 연산자, 그리고 다양한 함수를 사용한다. (정확하게는 어느 데이터베이스 관리자를 사용되느냐에 따라 의존성을 띄게된다.) 예를 들어, 온도 측정치를 화씨에서 섭씨로 소수점 아래 두자리에서 반올림하여 변환할 수 있다.

%%sqlite survey.db
select taken, round(5*(reading-32)/9, 2) from Survey where quant='temp';

다른 필드의 값을 조합할 수도 있다. 예를 들어, 문자열 접합 연산자 (string concatenation operator, ||)를 사용한다.

%%sqlite survey.db
select personal || ' ' || family from Person;

first와 last 대신에 필드 이름으로 personal과 family를 사용하는 것이 이상해 보일지 모른다. 하지만, 문화적 차이를 다루기 위한 필요한 첫번째 단계다. 예를 들어, 다음 규칙을 고려해보자.

성명 전부(Full Name)	알파벳 순서	이유
Liu Xiaobo	Liu	중국 성이 이름보다 먼저 온다.
Leonardo da Vinci	Leonardo	"da Vinci" 는 "from Vinci"를 뜻한다.
Catherine de Medici	Medici	성(family name)
Jean de La Fontaine	La Fontaine	성(family name)이 "La Fontaine"이다.
Juan Ponce de Leon	Ponce de Leon	전체 성(full family name)이 "Ponce de Leon"이다.
Gabriel Garcia Marquez	Garcia Marquez	이중으로 된 스페인 성(surnames)
Wernher von Braun	von or Braun	독일 혹은 미국에 있는냐에 따라 달라짐
Elizabeth Alexandra May Windsor	Elizabeth	군주가 통치하는 이름에 따라 알파벳순으로 정렬
Thomas a Beckett	Thomas	시성된(canonized) 이름에 따라 성인이름 사용

분명하게, 심지어 두부분 "personal"과 "family"으로 나누는 것도 충분하지 않다.

도전 과제

1. 좀더 조사한 뒤에, Valentina Roerich는 염도를 퍼센티지(%)로 작성한 것을 알게되었다. Survey 테이블에서 값을 100으로 나누어서 모든 염도 측정치를 반환하는 쿼리를 작성하세요.

2. union 연산자는 두 쿼리의 결과를 조합한다.

%%sqlite survey.db
select * from Person where ident='dyer' union select * from Person where ident='roe';

union을 사용하여 앞선 도전과제에서 기술되어 수정된 Roerich가 측정한, Roerich만 측정한 염도 측정치의 통합 리스트를 생성하세요. 출력결과는 다음과 같아야 한다.

619	0.13
622	0.09
734	0.05
751	0.1
752	0.09
752	0.416
837	0.21
837	0.225

1. Visited 테이블에 사이트 식별자는 '-'으로 구분되는 두 부분으로 구성되어 있다.

%%sqlite survey.db
select distinct site from Visited;

몇몇 주요 사이트 식별자는 두 문자길이를 가지고 몇몇은 3문자길이를 가진다. "in string" 함수 instr(X, Y)은 X 문자열에 문자열 Y가 첫번째 출현의 1-기반 인덱스를 반환하거나 Y가 X에 존재하지 않으면 0 을 반환한다. 부분 문자열 함수 substr(X, I)은 인덱스 I에서 시작하는 문자열 X의 부분문자열을 반환한다. 상기 두 함수를 사용해서 유일한 주요 사이트 식별자를 생성하세요. (이 데이터에 대해서 작업된 리스트는 "DR"과 "MSK"만 포함해야 한다.)