6  정렬과 필터

남극 데이터(survey.db) 검토를 시작하며, 알고 싶은 것은 다음과 같다.

각 사이트에서 수행된 측정을 결정하기 위해 Survey 테이블을 살펴볼 수 있다.

데이터에 종종 중복된 잉여정보가 포함되 있어 쿼리도 종종 과잉 정보를 반환한다. 예를 들어, Survey 테이블에서 측정된 수량 정보를 선택하면 다음을 얻게 된다.

SELECT quant FROM Survey;
quant
rad
sal
rad
sal
rad
sal
temp
rad
sal
temp
rad
temp
sal
rad
sal
temp
sal
rad
sal
sal
rad

결과를 좀더 읽을 수 있게 만들기 위해서 쿼리에 distinct 키워드를 추가해서 중복된 출력을 제거한다.

반환된 결과는 Survey 테이블의 다양한 quant 유형을 직관적으로 확인하기 어렵게 만든다. DISTINCT 키워드를 쿼리에 추가함으로써 중복된 출력을 제거하여 결과의 가독성을 높인다. 

SELECT DISTINCT quant FROM Survey;
quant
rad
sal
temp

taken 칼럼에 저장된 방문(visit)에서 quant 측정을 확인하려면, 여러 칼럼에 대해 DISTINCT 키워드를 사용한다. 둘 이상의 칼럼을 선택하면, 고유한 값의 집합이 반환된다(이 경우에는 두 칼럼을 선택하기 때문에 별개로 구별된 값의 쌍이 반환된다.)

SELECT DISTINCT taken, quant FROM Survey;
taken quant
619 rad
619 sal
622 rad
622 sal
734 rad
734 sal
734 temp
735 rad
735 sal
735 temp
751 rad
751 temp
751 sal
752 rad
752 sal
752 temp
837 rad
837 sal
844 rad

양쪽 경우에 설사 데이터베이스 내에서 서로 인접하지 않더라도 모두 중복이 제거된 것을 주목하세요. 다시 한번, 행은 실제로 정렬되지는 않았다는 것을 기억하세요. 단지 정렬된 것으로 화면에 출력된다.

6.1 정렬

두 경우 모두에서, 중복된 값이 제거되었음을 알 수 있다. 데이터베이스 테이블에서 해당 행들이 인접해 있지 않아도 마찬가지다.

다음 과제로 Person 테이블에서 탐사에 참여한 과학자들을 식별하는 것이다. 앞서 언급했듯이, 데이터베이스 레코드는 일반적으로 특정한 순서로 저장되지 않는다. 쿼리 결과가 반드시 정렬되어 있지 않으며, 설사 정렬되어 있다 해도, 원하는 다른 방식(예를 들어, 개인 이름 대신 식별자 등)으로 정렬 결과를 보고 싶을 때가 많다는 의미기도 하다. SQL에서는 쿼리에 ORDER BY 절을 추가함으로써 간단하게 구현할 수 있다. 

SELECT * FROM Person ORDER BY id;
id personal family
danfort Frank Danforth
dyer William Dyer
lake Anderson Lake
pb Frank Pabodie
roe Valentina Roerich

기본설정으로 ORDER BY를 사용할 때, 결과는 지정한 칼럼 오름차순으로 정렬된다 (즉, 가장 작은 값에서 가장 큰 값으로).

DESC(내림차순을 의미하는 “descending”의 약자)를 사용하여 반대 순서로 정렬할 수 있다.

정렬 참고 사항

데이터베이스에 쿼리문을 전송할 때마다 레코드가 일관되게 보이는 이유는 지금까지 아무도 데이터를 변경하거나 수정하지 않았기 때문이다. 행이 일관성을 갖고 예측 가능한 순서로 반환되기를 원한다면 ORDER BY를 사용하는 것을 기억하라.

(오름차순 정렬을 명확히 하고 싶다면, DESC 대신 ASC를 사용한다.)

각 사이트 방문 때 어떤 과학자가 양을 측정했는지 살펴보려면, 다시 Survey 테이블을 봐야 한다. 여러 칼럼을 한 번에 정렬할 수도 있다. 예를 들어, 다음 쿼리는 결과를 먼저 taken에 따라 오름차순으로 정렬한 다음, 각각의 동일한 taken 값 그룹 내에서 person에 따라 내림차순으로 정렬한다. 

SELECT taken, person, quant FROM Survey ORDER BY taken ASC, person DESC;
taken person quant
619 dyer rad
619 dyer sal
622 dyer rad
622 dyer sal
734 pb rad
734 pb temp
734 lake sal
735 pb rad
735 -null- sal
735 -null- temp
751 pb rad
751 pb temp
751 lake sal
752 roe sal
752 lake rad
752 lake sal
752 lake temp
837 roe sal
837 lake rad
837 lake sal
844 roe rad

6.2 중복제거

데이터베이스에서 중복 데이터의 존재는 정보의 해석을 복잡하게 만들 수 있다. 같은 데이터가 반복되면, 결과의 해석이 어려워지고, 필요한 정보를 찾는 데 시간이 더 걸릴 수 있다. DISTINCT 키워드는 이러한 중복을 제거하여 결과를 더 명확하고 간결하게 만드는 역할을 한다. 예를 들어, 여러 번의 측정에서 동일한 과학자가 나타날 수 있는데, DISTINCT를 사용하면 각 과학자가 수행한 고유한 측정 유형만을 표시하여 데이터의 중복을 최소화하고 결과를 더 쉽게 해석할 수 있다.

다음 쿼리를 통해 어떤 과학자가 방문에 관여했으며, 방문 동안 어떤 측정을 수행했는지를 잘 파악할 수 있다.

테이블을 살펴보면, 일부 과학자들이 특정 종류의 측정에 전문화되어 있는 것처럼 보인다. 적절한 칼럼을 선택하고 중복을 제거함으로써 어떤 과학자가 어떤 측정을 수행했는지 선명히 드러난다. 

SELECT DISTINCT quant, person FROM Survey ORDER BY quant ASC;
quant person
rad dyer
rad pb
rad lake
rad roe
sal dyer
sal lake
sal -null-
sal roe
temp pb
temp -null-
temp lake

데이터베이스 테이블의 레코드는 본질적으로 정렬되어 있지 않기 때문에, 특정 순서대로 표시하고 싶다면, ORDER BY를 명시적으로 사용하여 그 순서를 지정해야 한다. 데이터베이스 저장된 값은 고유함이 보장되지 않기 때문에, 중복을 제거하고 싶다면, DISTINCT를 사용하여 명시적으로 지정하여 처리해야만 된다.

6.3 필터

데이터베이스의 가장 강력한 기능 중 하나는 데이터를 필터(filter)하는 능력이다. 즉, 특정 기준에 맞는 레코드만 선택한다. 예를 들어, 특정 사이트를 언제 방문했는지 확인한다고 가정하자. 쿼리에 where 절을 사용해서 Visited 테이블로부터 조건에 맞는 레코드만 뽑아낼 수 있다. 

SELECT * FROM Visited WHERE site = 'DR-1';
id site dated
619 DR-1 1927-02-08
622 DR-1 1927-02-10
844 DR-1 1932-03-22

데이터베이스 관리자는 이 쿼리를 두 단계로 실행한다. 먼저, Visited 테이블 각 행을 확인하여 WHERE 조건을 만족하는 행을 찾는다. 그 다음, SELECT 키워드 뒤에 따라오는 칼럼 이름을 사용하여 표시할 칼럼을 결정한다.

이러한 처리 순서가 의미하는 바는 화면에 표시되지 않는 칼럼 값에 기반해서도 WHERE 절을 사용해서 레코드를 필터링할 수 있다는 것이다.

SELECT id FROM Visited WHERE site = 'DR-1';
id
619
622
844

SQL 필터링 동작방식

6.4 부울 연산자

데이터를 필터링할 때 부울 연산자(Boolean Operators)를 사용한다. 이는 특정 조건을 만족하는 데이터를 선택하거나 제외하는 데 유용하다. 예를 들어, DR-1 사이트에서 1930년 이후로 수집된 모든 정보를 요청하는 경우, 부울 연산자를 사용하여 ‘사이트 이름이 DR-1이고, 수집 연도가 1930년 이후인’ 데이터를 필터링할 수 있다. 이렇게 SQL 쿼리에서 부울 연산자를 사용하면, 복잡한 데이터 집합에서 필요한 정보를 정확하고 효율적으로 추출할 수 있다. 

SELECT * FROM Visited WHERE site = 'DR-1' AND dated < '1930-01-01';
id site dated
619 DR-1 1927-02-08
622 DR-1 1927-02-10
날짜 자료형

거의 모든 데이터베이스 관리자는 날짜 자료 처리를 위한 특별한 자료형(Data Type)을 가지고 있다. 실제로 많은 데이터베이스는 크게 두개로 갈린다. 하나는 “1971년 5월 31일”과 같은 날짜용이고, 다른 하나는 “31일”과 같은 기간용(duration)이다. 하지만, SQLite는 그렇지 않다. 대신, SQLite는 날짜를 텍스트(ISO-8601 표준 형식 “YYYY-MM-DD HH:MM:SS.SSSS”), 실수(율리우스 일자, 기원전 4714년 11월 24일부터 일수), 또는 정수(유닉스 시간, 1970년 1월 1일 자정 이후의 초 수)로 저장한다. 만약 복잡하게 들린다면, 그럴수도 있다 하지만 옛날 스웨덴 날짜를 파악하는 것만큼 복잡하지는 않다.

Lake나 Roerich에 의해 수행된 측정이 무엇인지 알아보려면, 그들의 이름에 대한 검사를 OR를 사용하여 결합한다.

SELECT * FROM Survey WHERE person = 'lake' OR person = 'roe';
taken person quant reading
734 lake sal 0.05
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

다른 방식으로, IN을 사용하여 특정 집합에 값이 있는지 확인할 수 있다. 

SELECT * FROM Survey WHERE person IN ('lake', 'roe');
taken person quant reading
734 lake sal 0.05
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

AND와 OR을 결합할 수 있지만, 어떤 연산자가 먼저 실행되는지 주의해야 한다. 괄호를 사용하지 않으면, 다음과 같은 결과를 얻게 된다: 

SELECT * FROM Survey WHERE quant = 'sal' AND person = 'lake' OR person = 'roe';
taken person quant reading
734 lake sal 0.05
751 lake sal 0.1
752 lake sal 0.09
752 roe sal 41.6
837 lake sal 0.21
837 roe sal 22.5
844 roe rad 11.25

이 쿼리는 Lake에 의한 염도(salinity) 측정과 Roerich에 의한 모든 측정을 포함한다. 대신에 아마도 다음과 같은 결과를 얻고자 했을 것이다.

SELECT * FROM Survey WHERE quant = 'sal' AND (person = 'lake' OR person = 'roe');
taken person quant reading
734 lake sal 0.05
751 lake sal 0.1
752 lake sal 0.09
752 roe sal 41.6
837 lake sal 0.21
837 roe sal 22.5

6.5 LIKE 키워드

SQL 필터링에서 LIKE 키워드의 중요성은 부분 일치를 통해 데이터를 필터링할 수 있게 해준다는 데에 있다. 예를 들어, ’DR’로 시작하는 사이트 이름과 같이 특정 패턴이나 문자열을 포함하는 레코드를 찾고 싶을 때 LIKE 키워드를 사용한다. 퍼센트 기호(%)는 와일드카드로서, 그 위치에 어떤 문자열이든 일치할 수 있게 한다. 이를 통해 문자열의 시작, 중간, 끝 부분에서 특정 패턴을 검색할 수 있다. LIKE와 와일드카드의 조합은 SQL 쿼리에서 매우 유연한 문자열 검색을 가능하게 하며, 복잡하거나 정확하지 않은 데이터에서 원하는 정보를 효과적으로 추출하는 데 중요한 역할을 한다. 

SELECT * FROM Visited WHERE site LIKE 'DR%';
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
844 DR-1 1932-03-22

마지막으로, DISTINCT를 WHERE와 함께 사용하여, 두 번째 수준 필터링 작업을 수행한다.

SELECT DISTINCT person, quant FROM Survey WHERE person = 'lake' OR person = 'roe';
person quant
lake sal
lake rad
lake temp
roe sal
roe rad

하지만, 기억하라. DISTINCT는 처리될 때 선택된 칼럼에 표시되는 값에만 적용되고 전체 행에는 적용되지 않는다.

쿼리작성 방법

방금 수행한 방식이 대부분의 사람들이 SQL 쿼리를 “발전시키는” 방식이기도 하다. 의도한 것의 일부를 수행하는 단순한 것에서부터 시작했다. 그리고 절을 하나씩 하나씩 추가하면서 효과를 테스트했다. 좋은 전략이다. 사실 복잡한 쿼리를 작성할 때, 거의 유일한 전략이다. 하지만 이런 전략은 빠른 결과 확인과 더불어, 올바른 결과를 얻었을 때 빠른 인식에도 상당히 의존한다.

빠른 결과 확인을 이루는 가장 좋은 방법은 데이터의 일부를 임시 데이터베이스에 저장하고 그 위에서 쿼리를 실행하는 것이거나, 혹은 합리적으로 구성된 레코드로 소규모 데이터베이스를 채워두고 실험하는 것이다. 예를 들어, 실제 2000만 호주 인구의 데이터베이스에서 쿼리를 실행하기보다는 1만 명의 샘플을 추출하여 실험을 하거나, 무작위 또는 그럴듯한 1만 명의 레코드를 생성할 수 있는 작은 프로그램을 작성해 사용하는 것이다.

연습문제

객관식

  1. 문제: SQL에서 DISTINCT 키워드의 주요 용도는 무엇입니까?
    1. 정렬 순서 변경
    2. 중복된 레코드 제거
    3. 데이터베이스 백업 생성
    4. 쿼리 실행 속도 향상
  1. 문제: ORDER BY 절의 기본 정렬 순서는 무엇입니까?
    1. 내림차순
    2. 오름차순
    3. 무작위 순서
    4. 알파벳 순서
  1. 문제: LIKE 키워드와 % 와일드카드의 조합은 무엇을 위해 사용됩니까?
    1. 정확한 문자열 매칭
    2. 패턴에 기반한 부분 일치 검색
    3. 숫자 범위 내 검색
    4. 데이터 타입 변환
  1. 다음 표현식 중 참은 무엇인가?
    1. 'a' LIKE 'a'
    2. 'a' LIKE '%a'
    3. 'beta' LIKE '%a'
    4. 'alpha' LIKE 'a%%'
    5. 'alpha' LIKE 'a%p%'

중복 날짜

Visited 테이블에서 별개로 구별되는 고유한(distinct) 날짜들을 선택하는 쿼리를 작성하시오.

조사자명

Person 테이블에 있는 과학자들 전체 이름을 표시하고, 가족 이름(family name)으로 정렬하는 쿼리문을 작성하시오.

쿼리 디버깅

극에서 48&deg보다 고위도에 위치한 모든 사이트를 선택하고자 한다고 가정하자. 작성한 첫번째 쿼리는 다음과 같다.

SELECT * FROM Site WHERE (lat > -48) OR (lat < 48);

왜 이 쿼리가 잘못된 것인지 설명하고, 쿼리를 다시 작성해서 올바르게 동작하게 만드세요.

이상치 탐지

정규화된 염분 수치는 0.0에서 1.0 사이에 있어야 한다. 상기 범위 밖에 있는 염분수치를 가진 모든 레코드를 Survey 테이블에서 선택하는 쿼리를 작성하세요.