5  계산과 집계 미스터리 해결

752번 방문에서 돌아온 Lake 고고학자와 Roerich 생물학자의 표정이 심상치 않았다. 베이스캠프의 텐트 안에서 Dyer 교수가 측정 기록지를 살펴보며 한숨을 내쉬었다.

“Roerich, 당신의 염도 측정값이… 41.6라고? 이건 불가능한 수치야. 바닷물도 이렇게 높지 않아.”

Lake가 고개를 저었다. “그것만이 아닙니다, 교수님. 제가 같은 지점에서 측정한 값은 0.09였는데… 뭔가 이상해요.”

박봉식(Pabodie) 엔지니어가 측정 장비를 점검해봤지만 문제를 찾을 수 없었다. Dyer 교수는 방사선 측정치도 5% 정도 보정이 필요하다고 덧붙였다.

그날 밤, 탐사대는 깨달았다. 이 이상치(outlier)들이 단순한 측정 오류가 아닐 수도 있다는 것을. 어쩌면 이것이 그들이 찾던 중대한 발견의 첫 번째 단서일지도 모른다.

90년이 지난 지금, 우리는 SQL의 집계 함수와 AI의 도움을 받아 그들이 놓친 패턴들을 발견할 수 있다. 이제 752번 방문의 진실에 한 걸음 더 가까이 다가가 보자.

5.1 SQL 계산과 집계

752번 방문의 미스터리를 해결하기 위해서는 정확한 측정값이 필요하다. Dyer 교수가 언급했듯이 방사선 측정치를 5% 상향 조정해야 한다면, 저장된 데이터를 변경하기보다는 쿼리에서 즉석으로 계산할 수 있다.

SELECT 1.05 * reading as 보정방사능 FROM Survey WHERE quant = 'rad';

보정방사능
10.311
8.19
8.8305
7.581
4.5675
2.2995
1.533
11.8125

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

SELECT taken, round(5 * (reading - 32) / 9, 2) as 섭씨온도 FROM Survey WHERE quant = 'temp';

taken	섭씨온도
734	-29.72
735	-32.22
751	-28.06
752	-26.67

예시에서 볼 수 있듯이, 방정식으로부터 생성된 새 필드를 설명하는 문자열은 매우 길고 복잡해질 수 있다. SQL에서 필드 이름을 바꾸는 것이 허용되는데, 계산된 필드든 데이터베이스 기존 필드든 상관없다. 간결함과 명확성을 위한 것이다. 예를 들어, 쿼리를 다음과 같이 작성할 수 있다:

SELECT taken, round(5 * (reading - 32) / 9, 2) as 섭씨온도 FROM Survey WHERE quant = 'temp';

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

SELECT personal || ' ' || family as 전체이름 FROM Person;

전체이름
William Dyer
Frank Pabodie
Anderson Lake
Valentina Roerich
Frank Danforth

---

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

표 5.1: 문화적 차이를 고려한 이름 정렬 방식

성명 전부(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’라는 두 부분으로 나누는 것만으로는 충분하지 않다.

5.1.1 합집합

UNION 연산자는 두 개의 쿼리 결과를 결합한다.

SELECT * FROM Person WHERE ident = 'dyer' 
UNION 
SELECT * FROM Person WHERE ident = 'roe';

ident	personal	family
dyer	William	Dyer
roe	Valentina	Roerich

UNION ALL 명령은 UNION 연산자와 동일하지만, UNION ALL은 모든 값을 선택한다는 점에서 차이가 있다. 차이점은 UNION ALL이 중복 행을 제거하지 않는다는 것이다. 대신, UNION ALL은 쿼리의 모든 행을 가져와서 하나의 테이블로 결합한다. UNION 명령은 결과 세트에 대해 SELECT DISTINCT를 수행한다. 만약 합병할 모든 레코드가 고유하다면, DISTINCT 단계를 건너뛰므로 더 빠른 결과를 얻기 위해 UNION ALL을 사용한다.

5.1.2 집계 함수

이제 데이터의 평균과 범위를 계산하고자 한다. Visited 테이블에서 모든 날짜 정보를 어떻게 선택하는지 알고 있다.

SELECT dated FROM Visited;

dated
1927-02-08
1927-02-10
1930-01-07
1930-01-12
1930-02-26
-null-
1932-01-14
1932-03-22

하지만 조합하기 위해서는 min 혹은 max 같은 집계 함수(aggregation function)를 사용해야만 한다. 각 함수는 입력으로 레코드 집합을 받고 출력으로 단일 레코드를 만든다.

SELECT min(dated) as 최이른날짜 FROM Visited;

최이른날짜
1927-02-08

SELECT max(dated) as 최근날짜 FROM Visited;

최근날짜
1932-03-22

min과 max는 SQL에 내장된 단지 두개의 집계 함수다. 다른 함수로 많이 사용되는 avg, count, sum을 들 수 있다.

SELECT round(avg(reading), 2) as 염도평균 FROM Survey WHERE quant = 'sal';

염도평균
7.20

SELECT count(reading) as 염도측정수 FROM Survey WHERE quant = 'sal';

염도측정수
9

SELECT sum(reading) as 염도합계 FROM Survey WHERE quant = 'sal';

염도합계
64.83

여기서 count(reading)을 사용했다. 테이블에서 quant나 다른 필드를 세는 것도 마찬가지로 쉽게 할 수 있다. 심지어 count(*)를 사용할 수도 있다. 이 함수는 값 자체에 대해서는 관심이 없고, 값이 몇 개 있는지만을 고려하기 때문이다.

SQL에서 한 번에 여러 집계작업을 수행할 수도 있다. 예를 들어, 적절한 염분측정치의 범위를 찾을 수도 있다.

SELECT min(reading) as 최소염도, max(reading) as 최대염도 
FROM Survey 
WHERE quant = 'sal' AND reading <= 1.0;

최소염도	최대염도
0.05	0.21

출력결과가 놀라움을 줄 수도 있지만, 원 결과값과 집계 결과를 조합할 수도 있다.

SELECT person, count(*) as 정상염도측정수 
FROM Survey 
WHERE quant = 'sal' AND reading <= 1.0;

person	정상염도측정수
lake	7

경고GROUP BY 없이 집계할 때 함정

Lake 이름이 Roerich나 Dyer 대신 나타나는 이유는 무엇일까? 이는 매우 중요한 경고 신호다!

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

이 쿼리에서 person 필드가 그룹화되지 않았는데도 집계 함수(count(*))와 함께 사용되었다. 데이터베이스는 여러 행에서 “임의로” 하나의 person 값을 선택해서 보여준다. ’lake’가 선택된 것은 우연이며, 다른 데이터베이스에서는 ’dyer’나 ’roe’가 나올 수도 있다.

실제로는 여러 사람이 염도를 측정했는데도 한 사람만 표시되는 것이다! 이런 실수를 피하려면 반드시 올바른 GROUP BY를 사용해야 한다:

-- 올바른 방법: 각 측정자별로 그룹화
SELECT person, count(*) as 측정횟수 
FROM Survey 
WHERE quant = 'sal' AND reading <= 1.0 
GROUP BY person;

person	측정횟수
dyer	2
lake	4
pb	0

이제 각 측정자별로 정확한 횟수가 나온다!

또 다른 중요한 사실은 집계할 값이 없을 때 — 예를 들어, WHERE 절을 만족하는 행이 없는 경우 — 집계의 결과는 “모른다”로 처리되며, 이는 0이나 다른 임의의 값이 아니라는 점이다.

예를 들어, 특정 조건을 만족하는 행이 하나도 없다면, count() 함수와 같은 집계 함수는 0을 반환하지만, sum()이나 avg()와 같은 다른 집계 함수들은 NULL을 반환할 수 있다. 이는 해당 집계 함수가 적용될 데이터가 전혀 없기 때문에, 그 결과가 “알 수 없는 값”이 됨을 의미한다. 이러한 방식은 데이터의 부재가 잘못된 결과로 이어지는 것을 방지하는 데 도움이 된다.

person	max(reading)	sum(reading)
-null-	-null-	-null-

집계 함수의 또 다른 중요한 특징은 SQL 나머지 부분과는 매우 유용한 방식으로 일관성이 없다는 것이다. 이러한 동작은 다음과 같은 쿼리를 작성할 수 있게 해준다:

집합 함수의 마지막 중요한 한가지 기능은 매우 유용한 방식으로 나머지 SQL과는 일관되지 않다는 점이다. 두 값을 더할 때 하나가 null이면 결과는 null이 된다. 이를 확장하면, sum을 사용하여 집합의 모든 값을 더하고 그 값들 중 하나라도 null이라면 결과도 null이 되어야 한다. 그러나 집계 함수가 null 값을 무시하고 null이 아닌 값들만 결합하는 것이 훨씬 더 유용하다. 명시적으로 항상 필터해야하는 대신에 이것의 결과 쿼리를 다음과 같이 작성할 수 있게 한다.

SELECT min(dated) FROM Visited;

min(dated)
1927-02-08

명시적으로 항상 다음과 같이 필터하는 쿼리를 작성할 필요가 없다.

SELECT min(dated) as 최이른날짜 FROM Visited WHERE dated IS NOT NULL;

최이른날짜
1927-02-08

모든 레코드를 한꺼번에 집계하는 것이 항상 의미 있는 것은 아니다. 예를 들어, 데이터에 체계적인 편향(bias)이 있을 것이라고 의심하고, 일부 과학자들의 방사능 측정값이 다른 사람들보다 높다고 생각한다고 가정해보자. 이런 경우에 다음과 같은 방법은 효과적이지 않다:

SELECT person, count(reading) as 측정수, round(avg(reading), 2) as 평균방사능
FROM Survey
WHERE quant = 'rad';

person	측정수	평균방사능
roe	8	6.56

데이터베이스 관리자가 각 과학자별로 별도로 집계하는 대신 임의로 한 과학자의 이름을 선택하기 때문에 이 방법은 효과적이지 않다. 과학자가 단지 다섯 명뿐이므로, 다음 형식의 다섯 개의 쿼리를 작성할 수 있다.

SELECT person, count(reading) as 측정수, round(avg(reading), 2) as 평균방사능
FROM Survey
WHERE quant = 'rad'
AND person = 'dyer';

person	측정수	평균방사능
dyer	2	8.81

하지만, 이러한 접근법은 성가시고, 만약 50명 혹은 500명의 과학자를 가진 데이터셋을 분석한다면, 모든 쿼리를 올바르게 작성할 가능성은 작다.

필요한 것은 데이터베이스 관리자에게 각 과학자별로 시간을 별도로 집계하도록 GROUP BY 절을 사용하여 지시하는 것이다.

SELECT person, count(reading) as 측정수, round(avg(reading), 2) as 평균방사능
FROM Survey
WHERE quant = 'rad'
GROUP BY person;

person	측정수	평균방사능
dyer	2	8.81
lake	2	1.82
pb	3	6.66
roe	1	11.25

GROUP BY는 그 이름이 암시하는 것처럼 정확히 다음과 같이 동작한다. 지정된 필드의 같은 값을 가진 모든 레코드를 그룹화하여 집계가 각 배치를 별도로 처리할 수 있도록 한다. 각 배치에 모든 레코드는 person에 대해 동일한 값을 가지기 때문에, 데이터베이스 관리자가 임의의 값을 잡아서 집합된 reading 값과 함께 표시하는 것은 더 이상 문제가 되지 않는다.

한 번에 여러 기준에 따라 정렬할 수 있는 것처럼, 다중 기준에 따라 그룹화할 수도 있다. 예를 들어, 과학자별 및 측정된 양별로 평균 측정값을 얻기 위해서 GROUP BY 절에 다른 필드를 추가하기만 하면 된다.

SELECT person, quant, count(reading) as 측정수, round(avg(reading), 2) as 평균값
FROM Survey
GROUP BY person, quant;

person	quant	측정수	평균값
-null-	sal	1	0.06
-null-	temp	1	-26.0
dyer	rad	2	8.81
dyer	sal	2	0.11
lake	rad	2	1.82
lake	sal	4	0.11
lake	temp	1	-16.0
pb	rad	3	6.66
pb	temp	2	-20.0
roe	rad	1	11.25
roe	sal	2	32.05

quant를 표시되는 필드 목록에 추가한 것에 주목하자. 그렇지 않으면 결과가 큰 의미를 가지지 않을 것이다.

한 단계 더 나아가 측정을 수행한 사람을 알 수 없는 모든 항목을 제거해보자.

SELECT person, quant, count(reading) as 측정수, round(avg(reading), 2) as 평균값
FROM Survey
WHERE person IS NOT NULL
GROUP BY person, quant
ORDER BY person, quant;

person	quant	측정수	평균값
dyer	rad	2	8.81
dyer	sal	2	0.11
lake	rad	2	1.82
lake	sal	4	0.11
lake	temp	1	-16.0
pb	rad	3	6.66
pb	temp	2	-20.0
roe	rad	1	11.25
roe	sal	2	32.05

좀더 면밀하게 살펴보면, 이 쿼리는,

1. Survey테이블에서 person 필드가 null이 아닌 레코드를 선택한다.

2. 상기 레코드를 부분집합으로 그룹지어서 각 부분집합의 person과 quant의 값은 같다.

3. 먼저 person으로 부분집합을 정렬하고나서 quant로 각 하위 그룹내에서도 정렬한다.

4. 각 부분집합의 레코드 숫자를 세고, 각각 reading 평균을 계산하고, 각각 person과 quant 값을 선택한다. (모두 동등하기 때문에 어느 것인지는 문제가 되지 않는다.)

---

5.2 AI와 SQL 협력 수사

1930년대 탐사대가 손으로 계산해야 했던 것들을 이제 순식간에 처리할 수 있다. 더 나아가 AI의 추론 능력과 SQL의 정확한 계산을 결합하면 당시 탐사대가 미처 발견하지 못한 패턴들을 찾아낼 수 있다.

752번 방문에서 발견된 Roerich의 이상한 염도값 41.6이 정말 무엇을 의미하는지, AI와 SQL을 동원해서 철저히 분석해보자.

5.2.1 752번 방문 이상치 원인 분석

이전 장에서 우리는 NULL의 미스터리를 통해 752번 방문의 숨겨진 진실을 엿보았다. 하지만 더욱 놀라운 발견은 바로 측정값 자체에 숨어있었다. Roerich가 기록한 41.6이라는 염도값이 과연 무엇을 의미하는지, 이제 AI의 추론 능력과 SQL의 집계 함수를 동원해 과학적으로 분석해보자. 90년 전 탐사대가 혼란에 빠졌던 이 수수께끼를 현대의 도구로 해결할 수 있을까?

ChatGPT 프롬프트: “1930년 2월 26일 752번 방문에서 Lake가 측정한 염도는 0.09인데, 같은 지점에서 Roerich가 측정한 값은 41.6이다. 이런 극단적인 차이의 원인을 추측해줘.”

ChatGPT 응답: “같은 지점에서 이런 극단적인 차이가 나는 원인은:

1. 측정 단위 착오 - ppm vs ppt 단위 혼동 가능성
2. 장비 오작동 - Roerich의 염도 측정기에 문제 발생
3. 특별한 지질 현상 - 지하 염수층이나 광물층 발견
4. 오염된 시료 - 바닷물이나 기타 오염물질 혼입

특히 41.6은 정상 남극 측정값(0.05-0.21)보다 약 200배 높은 수치입니다.”

SQL로 검증해보기:

-- 이상치 정도 분석
SELECT 
    person,
    quant,
    reading,
    ROUND(reading, 2) as 측정값,
    CASE 
        WHEN reading > 20 THEN '극단적 이상치'
        WHEN reading > 10 THEN '심각한 이상치'
        WHEN reading > 5 THEN '중간 이상치'
        ELSE '정상 범위'
    END as 이상치분류
FROM Survey 
WHERE quant = 'sal'
ORDER BY reading DESC;

person	quant	reading	측정값	이상치분류
roe	sal	41.6	41.6	극단적 이상치
roe	sal	22.5	22.5	극단적 이상치
lake	sal	0.21	0.21	정상 범위
dyer	sal	0.13	0.13	정상 범위
lake	sal	0.10	0.10	정상 범위
dyer	sal	0.09	0.09	정상 범위
lake	sal	0.09	0.09	정상 범위
-null-	sal	0.06	0.06	정상 범위
lake	sal	0.05	0.05	정상 범위

결과를 보면 ChatGPT의 분석이 얼마나 정확했는지 놀라게 된다. 전체 9건의 염도 측정 중에서 오직 Roerich의 2건만이 ’극단적 이상치’로 분류되었다. 나머지 7건은 모두 0.05~0.21 범위의 ’정상 범위’에 속한다. 더욱 흥미로운 것은 Lake가 4번, Dyer가 2번 측정했지만 모두 일관되게 정상 범위 내의 값들을 기록했다는 점이다. 심지어 측정자가 불분명한 1건(-null-)도 0.06으로 정상 범위였다. 이는 Roerich의 이상치가 단순한 우연이 아니라 체계적인 원인이 있음을 강력히 시사한다.

5.2.2 탐사대원별 측정 신뢰도 분석

Roerich의 극단적 이상치를 확인한 후, 더 근본적인 질문이 생겼다. 과연 이것이 Roerich 개인만의 문제일까, 아니면 탐사대 전체에 영향을 미친 체계적 문제일까? 1930년대 남극이라는 극한 환경에서 각 탐사대원들이 얼마나 일관성 있는 측정을 할 수 있었는지 분석해보자. 개인의 전문성, 장비 숙련도, 그리고 극한 상황에 대한 적응력이 데이터 품질에 어떤 영향을 미쳤는지 SQL의 집계 함수를 통해 객관적으로 평가할 수 있다.

ChatGPT 프롬프트: “752번 방문 이후 각 탐사대원의 측정 패턴을 보니 Roerich만 계속 이상한 값을 기록한다. 탐사대원별 측정 신뢰도를 분석해줘.”

ChatGPT 응답: “측정자별 신뢰도 차이는 다음과 같은 이유로 발생합니다:

1. 전문 분야 - Lake(고고학), Pabodie(공학)는 정밀 측정에 능숙
2. 장비 숙련도 - 일부 대원은 특정 장비에 더 익숙
3. 측정 환경 - 추위나 스트레스 상황에서 개인차 발생
4. 기록 방식 - 일부는 현장 기록, 일부는 나중에 정리

Roerich의 반복적인 이상치는 체계적인 문제를 시사합니다.”

SQL로 검증해보기:

-- 측정자별 신뢰도 분석
SELECT 
    person,
    COUNT(*) as 총측정수,
    COUNT(DISTINCT quant) as 측정종류,
    ROUND(AVG(reading), 2) as 평균값,
    ROUND(MIN(reading), 2) as 최솟값,
    ROUND(MAX(reading), 2) as 최댓값,
    ROUND(MAX(reading) - MIN(reading), 2) as 변동폭,
    CASE 
        WHEN MAX(reading) - MIN(reading) < 5 THEN '일관성 높음'
        WHEN MAX(reading) - MIN(reading) < 20 THEN '일관성 보통'
        ELSE '일관성 낮음'
    END as 신뢰도
FROM Survey 
WHERE person IS NOT NULL
GROUP BY person
ORDER BY 변동폭 ASC;

person	총측정수	측정종류	평균값	최솟값	최댓값	변동폭	신뢰도
dyer	4	2	4.46	0.09	9.82	9.73	일관성 보통
lake	7	3	-1.29	-16.0	2.19	18.19	일관성 보통
pb	5	2	-2.67	-21.5	8.41	29.91	일관성 낮음
roe	3	2	18.95	11.25	41.6	30.35	일관성 낮음

결과는 탐사대 내부의 흥미로운 역학관계를 보여준다. Dyer 교수가 9.73이라는 가장 낮은 변동폭으로 최고의 일관성을 보였다. 이는 그가 탐사대의 리더로서 측정 기준을 엄격히 관리했음을 시사한다. Lake는 7번이나 측정을 수행하며 가장 활발했지만, 변동폭 18.19로 ‘일관성 보통’ 수준이었다. 이는 그가 다양한 환경에서 측정하며 겪은 어려움을 반영하는 것 같다.

반면 Pabodie와 Roerich는 둘 다 30 이상의 높은 변동폭을 보여 ’일관성 낮음’으로 분류되었다. 하지만 그 원인은 다를 것이다. Pabodie의 경우 이전 장에서 본 것처럼 극한 상황(-26도)에서의 측정이 포함되어 있어 환경적 요인이 클 것이다. 반면 Roerich의 경우 41.6이라는 극단적 값이 평균을 18.95까지 끌어올렸는데, 이는 정말로 ’체계적인 문제’가 있었음을 보여준다. 흥미롭게도 측정 횟수가 적을수록(Roerich 3번) 변동성이 더 크게 나타나는 경향도 확인할 수 있다.

5.2.3 752번 방문 이후 시간적 패턴 분석

개별 측정자의 신뢰도 분석을 통해 Roerich의 문제가 개인적인 것임을 확인했다. 이제 마지막 퍼즐 조각을 맞춰보자. 752번 방문을 기점으로 시간이 흘러가면서 이상치 발생 패턴이 어떻게 변화했는지 살펴보는 것이다. 만약 이것이 단순한 측정 오류였다면 시간이 지나면서 해결되었을 것이다. 하지만 만약 정말로 무언가 중대한 발견이 있었다면, 그 영향은 이후 측정들에도 지속적으로 나타날 것이다. 시간의 흐름 속에서 드러나는 패턴을 통해 752번 미스터리의 진실에 한 걸음 더 다가가 보자.

ChatGPT 프롬프트: “752번 방문(날짜 미기록) 이후 1932년까지의 측정 패턴을 보니 Roerich의 이상치가 지속된다. 이런 시간적 패턴이 의미하는 바는?”

ChatGPT 응답: “752번 방문 이후의 패턴은 다음을 시사합니다:

1. 장비 문제 지속 - Roerich의 염도 측정기가 계속 고장 상태
2. 새로운 발견의 확인 - 특별한 지질 현상의 재검증 과정
3. 팀 내 갈등 - 측정 방법에 대한 의견 차이 발생
4. 기밀 유지 - 752번 방문 날짜 누락이 의도적일 가능성

특히 1932년 1월 14일 22.5라는 두 번째 이상치는 우연이 아닐 수 있습니다.”

SQL로 검증해보기:

-- 방문별 이상치 패턴 분석
SELECT 
    v.dated as 날짜,
    v.site as 사이트,
    COUNT(s.reading) as 측정수,
    ROUND(AVG(s.reading), 2) as 평균값,
    COUNT(CASE WHEN s.reading > 20 THEN 1 END) as 극단이상치,
    COUNT(CASE WHEN s.reading > 10 THEN 1 END) as 이상치,
    GROUP_CONCAT(DISTINCT s.person) as 측정자들
FROM Visited v
LEFT JOIN Survey s ON v.id = s.taken
WHERE s.reading IS NOT NULL
GROUP BY v.dated, v.site
HAVING 이상치 > 0
ORDER BY v.dated;

날짜	사이트	측정수	평균값	극단이상치	이상치	측정자들
NULL	DR-3	4	6.97	1	1	lake,roe
1932-01-14	MSK-4	3	8.06	1	1	lake,roe
1932-03-22	DR-1	1	11.25	0	1	roe

이 결과는 정말로 충격적이다. ChatGPT의 예측이 놀라울 정도로 정확했던 것이다! 이상치가 발생한 세 번의 방문을 시간 순으로 보면 명확한 패턴이 드러난다. 특히 첫 번째 행에서 날짜가 NULL로 표시된 것은 바로 752번 방문이다 - 의도적으로 날짜가 기록되지 않은 그 미스터리한 방문 말이다! 이 비밀스러운 DR-3 방문에서 시작된 이상 현상이 단발적 사건으로 끝나지 않았다는 점이 더욱 놀랍다. 무려 2년 후인 1932년에도 Roerich와 관련된 이상치가 두 번 더 발생했다. 더욱 흥미로운 것은 지역적 패턴이다. 처음 DR-3에서 시작된 현상이 MSK-4, DR-1로 확산되었는데, 이는 탐사대가 유사한 현상을 다른 지역에서도 찾고 있었음을 의미할 수 있다. 특히 세 번째 방문(1932-03-22)에서는 Roerich만 단독으로 측정했는데, 이는 그가 이 특별한 현상의 전담 연구자가 되었을 가능성을 시사한다.

5.2.4 AI와 SQL의 협업

탐사대의 미스터리를 추적하면서 우리는 흥미로운 발견을 했다. ChatGPT와 SQL이 각각 다른 방식으로 데이터를 해석하고 있었던 것이다. 인공지능은 마치 경험 많은 탐정처럼 직관과 추론으로 가능성을 제시했고, SQL은 냉철한 법의학자처럼 정확한 수치와 패턴을 추출했다. 이 두 접근법의 차이를 비교해보니, 각자의 강점이 명확히 드러났다.

표 5.2: AI와 SQL의 집계 분석 능력 비교

분석 영역	ChatGPT	SQL	승자
이상치 탐지	일반적 기준 제시	정확한 수치 분석	SQL ✅
원인 추론	다양한 가설 생성	패턴 기반 증거	ChatGPT ✅
신뢰도 평가	정성적 판단	정량적 지표	SQL ✅
시계열 분석	경험 기반 예측	실제 데이터 검증	SQL ✅
통계적 해석	직관적 설명	수학적 정확성	SQL ✅

표 5.2 이 보여주는 결과는 매우 시사적이다. SQL이 5대 1로 압도적인 우위를 보이는 것처럼 보이지만, 실제로는 ChatGPT의 한 가지 승리가 결정적인 의미를 갖는다. 바로 ‘원인 추론’ 영역이다. 데이터가 아무리 정확해도 그것이 무엇을 의미하는지 해석하지 못한다면 단순한 숫자 나열에 불과하기 때문이다.

752번 방문 미스터리를 해결하는 과정에서 이 점이 극명하게 드러났다. SQL은 Roerich의 41.6이라는 이상치를 정확히 찾아냈지만, 그것이 장비 오류인지, 실험 실수인지, 아니면 진짜 발견인지는 알려주지 못했다. 반면 ChatGPT는 날짜 누락, 반복 패턴, 측정자 행동 변화 등을 종합하여 “의도적 은폐”라는 가설을 제시했다. 이는 단순한 추측이 아니라 맥락을 읽는 지능의 힘이었다.

실무에서 진정한 힘은 이 둘을 결합할 때 나타난다. 마치 홈즈와 왓슨의 콤비처럼, ChatGPT의 창의적 가설과 SQL의 철저한 검증이 만날 때 진실이 드러난다. 우선 AI에게 “이상한 패턴이 보이는가?”라고 물어 잠재적 문제를 발견하고, SQL로 “정확히 얼마나 이상한가?”를 계산한다. 그리고 다시 AI에게 “왜 이런 일이 생겼을까?”를 묻고, SQL로 그 가설을 검증하는 순환 구조를 만드는 것이다.

이러한 협업 모델은 특히 복잡한 비즈니스 문제를 해결할 때 빛을 발한다. 매출 급감의 원인을 찾거나, 고객 이탈 패턴을 분석하거나, 품질 이상을 추적할 때, AI의 직관과 SQL의 정밀함이 함께 작동하면 단독으로는 불가능했던 통찰을 얻을 수 있다. 752번 방문의 비밀이 결국 이 두 도구의 협업으로 밝혀진 것처럼 말이다.

---

5.3 집계 함수로 밝혀낸 최종 증거

지금까지 우리는 단순한 GROUP BY부터 복잡한 CASE WHEN까지, 집계 함수의 기본기를 752번 미스터리를 통해 익혔다. 이제 마지막 단계로, 지금까지 배운 것들을 조합해서 더욱 강력한 분석 기법들을 살펴보자. 실무에서 진짜 위력을 발휘하는 고급 집계 패턴들이다.

5.3.1 다차원 분석 GROUP BY

지금까지는 한 번에 하나씩(person별, quant별, 날짜별) 그룹화했지만, 실제 비즈니스에서는 여러 차원을 동시에 분석해야 한다. 탐사대 데이터를 3차원으로 분석해보자 - 날짜, 사이트, 측정자를 모두 고려한 완전한 그림이다.

-- 3차원 복합 분석: 날짜 × 사이트 × 측정자
SELECT 
    COALESCE(v.dated, '날짜미기록') as 날짜,
    v.site as 사이트,
    COALESCE(s.person, '측정자미상') as 측정자,
    COUNT(s.reading) as 측정건수,
    ROUND(AVG(s.reading), 2) as 평균값,
    COUNT(DISTINCT s.quant) as 측정종류수,
    GROUP_CONCAT(DISTINCT s.quant) as 측정종류들
FROM Visited v
LEFT JOIN Survey s ON v.id = s.taken
WHERE s.reading IS NOT NULL
GROUP BY v.dated, v.site, s.person
ORDER BY v.dated IS NULL DESC, v.dated, v.site, s.person;

날짜	사이트	측정자	측정건수	평균값	측정종류수	측정종류들
날짜미기록	DR-3	lake	3	-4.57	3	rad,sal,temp
날짜미기록	DR-3	roe	1	41.6	1	sal
1927-02-08	DR-1	dyer	2	4.98	2	rad,sal
1927-02-10	DR-1	dyer	2	3.94	2	rad,sal
1930-01-07	DR-3	lake	1	0.05	1	sal
1930-01-07	DR-3	pb	2	-6.54	2	rad,temp
1930-01-12	DR-3	측정자미상	2	-12.97	2	sal,temp
1930-01-12	DR-3	pb	1	7.22	1	rad
1930-02-26	DR-3	lake	1	0.1	1	sal
1930-02-26	DR-3	pb	2	-7.08	2	rad,temp
1932-01-14	MSK-4	lake	2	0.83	2	rad,sal
1932-01-14	MSK-4	roe	1	22.5	1	sal
1932-03-22	DR-1	roe	1	11.25	1	rad

이런 복합 분석의 진가는 패턴의 교차점에서 드러난다. 결과를 보면 정말 놀라운 사실들이 드러난다! 먼저 “날짜미기록 × DR-3”라는 조합에서 Lake는 평균 -4.57로 정상 범위지만, Roerich는 41.6이라는 극단적 이상치를 기록했다. 같은 시간, 같은 장소에서 이런 극단적 차이가 발생한 것이다.

더욱 흥미로운 것은 시간적 패턴이다. Roerich의 이상치가 날짜미기록(752번), 1932-01-14(22.5), 1932-03-22(11.25)로 시간이 갈수록 점차 감소하고 있다. 마치 무언가의 효과가 시간이 지나면서 약해지는 것처럼 보인다. 반면 Lake는 일관되게 정상 범위의 측정값을 유지하고 있어, 이것이 개인적 문제임을 명확히 보여준다.

실무에서는 “지역별 × 시기별 × 담당자별” 매출 분석이나 “제품별 × 채널별 × 고객군별” 수익성 분석에 이런 3차원 패턴 분석을 활용한다. 특정 조합에서만 나타나는 이상 현상을 포착할 수 있기 때문이다.

5.3.2 GROUP_CONCAT 전체 한눈에 보기

752번 방문의 미스터리를 추적하면서 우리는 누가 어떤 측정을 했는지 각각 확인했다. 하지만 한 번에 모든 정보를 조합해서 볼 수 있다면 어떨까? GROUP_CONCAT 함수는 여러 행의 값들을 하나의 문자열로 연결해주는 마법 같은 도구다.

-- 752번 방문에 참여한 모든 측정자를 한 줄로
SELECT taken,
       GROUP_CONCAT(DISTINCT person) as 측정자목록,
       COUNT(DISTINCT person) as 측정자수,
       GROUP_CONCAT(person || ':' || quant || '=' || reading, ' | ') as 측정상세
FROM Survey 
WHERE taken = 752 AND person IS NOT NULL
GROUP BY taken;

taken	측정자목록	측정자수	측정상세
752	lake,roe	2	lake:rad=2.19, lake:sal=0.09, lake:temp=-16.0, roe:sal=41.6

놀랍지 않은가! 한 행에 752번 방문의 전체 스토리가 담겨있다. Lake는 3종류를 모두 측정했지만 Roerich는 염도만 측정했고, 그 값이 비정상적으로 높다는 사실이 한눈에 보인다.

이 기법을 전체 탐사 기간에 적용하면 더욱 흥미로운 패턴이 드러난다:

-- 모든 과학자들의 이름을 한 줄로 연결
SELECT '탐사대 전체 명단: ' || GROUP_CONCAT(personal || ' ' || family, ', ') as 탐사대원
FROM Person;

탐사대원
탐사대 전체 명단: William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

GROUP_CONCAT은 단순한 문자열 연결 이상의 의미를 갖는다. 실무에서는 “한 고객이 구매한 모든 상품 목록”, “한 프로젝트에 참여한 모든 팀원”, “한 환자가 복용하는 모든 약물”을 한 줄로 요약할 때 필수적이다. 특히 보고서 작성이나 데이터 요약 시 GROUP_CONCAT 없이는 불가능한 작업들이 많다.

5.3.3 숨은 패턴을 찾는 HAVING 절

GROUP BY 후에 조건을 걸 때는 WHERE가 아닌 HAVING을 써야 한다. 왜 새로운 키워드가 필요할까? WHERE는 개별 행을 대상으로 하지만, GROUP BY 후에는 여러 행이 하나의 그룹으로 합쳐진다. 이때 “이 그룹의 최댓값이 얼마인가?”, “이 그룹의 평균이 기준을 넘는가?” 같은 질문은 WHERE로 할 수 없다. 집계 함수 결과에 조건을 걸기 위해서는 HAVING이라는 전용 도구가 필요한 것이다.

-- 이상치를 포함한 그룹만 필터링 (조건 완화)
SELECT 
    s.person as 측정자,
    s.quant as 측정종류,
    COUNT(*) as 측정횟수,
    ROUND(AVG(s.reading), 2) as 평균값,
    ROUND(MAX(s.reading), 2) as 최댓값,
    ROUND(MAX(s.reading) - AVG(s.reading), 2) as 최대편차
FROM Survey s
WHERE s.person IS NOT NULL
GROUP BY s.person, s.quant
HAVING MAX(s.reading) > AVG(s.reading) + 5  -- 평균+5 이상인 이상치 보유 그룹
ORDER BY 최대편차 DESC;

측정자	측정종류	측정횟수	평균값	최댓값	최대편차
roe	sal	2	32.05	41.6	9.55

HAVING의 정확한 작동 방식: 처음에 평균+10 조건으로 했을 때는 결과가 나오지 않았지만, 평균+5로 완화하니 Roerich의 염도 측정 그룹만 정확히 추출되었다! 이것이 바로 HAVING의 특성이다. WHERE처럼 개별 행을 필터링하는 것이 아니라, GROUP BY로 만들어진 그룹 전체를 평가해서 조건에 맞지 않으면 그룹 자체를 제외한다.

Roerich의 염도 측정 그룹에서 최댓값 41.6이 평균 32.05보다 9.55나 큰 것을 발견했다. 만약 조건을 평균+10으로 했다면 9.55 < 10이므로 이 그룹도 제외되었을 것이다. 실무에서는 이런 특성을 이용해 “매출이 목표 대비 20% 이상 떨어진 지점”, “클레임이 평균의 3배 이상인 제품군” 등을 정확히 필터링할 수 있다.

5.3.4 더 깊은 분석 윈도우 함수

집계 함수의 한계는 “그룹 내에서만” 계산한다는 점이다. 하지만 때로는 “전체 대비 이 그룹의 위치”나 “이전 값과의 비교”가 필요하다. 다음 장에서 배울 JOIN과 함께, 윈도우 함수는 더욱 정교한 분석의 문을 연다.

-- 윈도우 함수 맛보기: 각 측정자의 순위와 비교
SELECT 
    person as 측정자,
    reading as 측정값,
    quant as 종류,
    RANK() OVER (ORDER BY reading DESC) as 전체순위,
    RANK() OVER (PARTITION BY quant ORDER BY reading DESC) as 종류별순위,
    LAG(reading) OVER (ORDER BY reading DESC) as 이전값,
    reading - LAG(reading) OVER (ORDER BY reading DESC) as 차이
FROM Survey 
WHERE person IS NOT NULL
ORDER BY reading DESC
LIMIT 5;

측정자	측정값	종류	전체순위	종류별순위	이전값	차이
roe	41.6	sal	1	1	NULL	NULL
roe	22.5	sal	2	2	41.6	-19.1
roe	11.25	rad	3	1	22.5	-11.25
dyer	9.82	rad	4	2	11.25	-1.43
pb	8.41	rad	5	3	9.82	-1.41

윈도우 함수의 결과를 보면 정말 마법 같은 일이 일어났다! 각 행이 그대로 유지되면서도 “전체에서 몇 등인지”, “같은 종류(sal/rad) 내에서 몇 등인지”, “바로 앞 행의 값은 무엇인지”를 모두 동시에 알 수 있게 되었다. 이것이 바로 GROUP BY와 윈도우 함수의 결정적 차이점이다. GROUP BY는 여러 행을 하나로 뭉개버려서 요약 정보만 남기지만, 윈도우 함수는 모든 행을 그대로 유지하면서 각 행에 추가 정보를 덧붙여준다. 마치 각 학생의 성적표에 개별 점수는 물론 “반 평균”, “전교 등수”, “이전 시험과의 차이”까지 모두 적어주는 것과 같다.

이런 강력한 분석 도구 덕분에 우리는 Roerich의 미스터리를 한층 더 정확하게 규명할 수 있었다. 그의 41.6이라는 수치가 단순히 “높다”는 수준이 아니라 “전체 1위이면서 2위와의 격차가 무려 19.1이나 되는 압도적 이상치”라는 것을 수치로 증명했다. 더욱 놀라운 것은 Roerich가 염도 측정에서는 1,2위를 독차지하고, 방사능 측정에서도 1위를 기록했다는 사실이다. 이는 우연이 아니라 체계적인 현상임을 강력히 시사한다.

5.3.5 서브쿼리 이상치 정량화

Roerich의 측정값이 얼마나 비정상적인지 정확히 수치화해보자. 각 개별 측정값이 전체 평균과 얼마나 차이나는지 계산하는 것이다. 이때 서브쿼리(subquery)라는 강력한 도구를 사용한다.

-- 각 염도 측정값과 전체 평균의 편차
SELECT person as 측정자, 
       reading as 측정값,
       ROUND((SELECT AVG(reading) FROM Survey WHERE quant='sal'), 2) as 전체평균,
       ROUND(reading - (SELECT AVG(reading) FROM Survey WHERE quant='sal'), 2) as 평균대비편차
FROM Survey 
WHERE quant='sal' AND person IS NOT NULL
ORDER BY 평균대비편차 DESC;

측정자	측정값	전체평균	평균대비편차
roe	41.6	7.20	34.40
roe	22.5	7.20	15.30
lake	0.21	7.20	-6.99
dyer	0.13	7.20	-7.07
lake	0.10	7.20	-7.10
dyer	0.09	7.20	-7.11
lake	0.09	7.20	-7.11
lake	0.05	7.20	-7.15

결과가 충격적이다! Roerich의 첫 번째 측정값은 평균보다 34.40이나 높고, 두 번째도 15.30이나 높다. 반면 다른 모든 측정자는 평균보다 6-7 정도 낮은 값들을 기록했다. 이는 Roerich만 완전히 다른 범주의 측정값을 갖는다는 것을 명확히 보여준다.

서브쿼리는 “쿼리 안의 쿼리”다. (SELECT AVG(reading) FROM Survey WHERE quant='sal')는 먼저 실행되어 평균값 7.20을 계산하고, 이 값이 외부 쿼리에서 각 행의 측정값과 비교된다. 이런 기법은 실무에서 “작년 동기 대비 성장률”, “업계 평균 대비 우리 회사 성과”, “개인별 KPI 달성률” 등을 계산할 때 필수적이다.

과학 연구에서도 윈도우 함수는 GROUP BY만으로는 불가능한 정교한 분석을 가능하게 한다. 기상 관측소에서 온도 변화 순위를 추적하면서 동시에 전일 대비 증감률을 계산하거나, 실험 데이터의 이동평균선을 생성하면서 개별 측정값도 유지하는 작업에서 윈도우 함수는 필수적이다. 특히 장기간 관측 데이터 분석에서는 GROUP BY의 집계만으로는 한계가 있어, 윈도우 함수의 도움 없이는 시계열 패턴 분석이 거의 불가능하다.

💭 생각해볼 점

COUNT, SUM, AVG 같은 기본 함수부터 복합 GROUP BY, HAVING 절, 윈도우 함수까지… 752번 방문의 미스터리를 추적하며 우리는 집계 함수의 진정한 힘을 경험했다. 단순한 계산 도구가 아니라 숨겨진 패턴을 찾고, 이상 징후를 탐지하고, 복잡한 과학적 수수께끼를 해결하는 탐정의 도구였다.

지금까지 배운 집계 함수들은 데이터 분석의 절반에 불과하다. 나머지 절반은 바로 테이블 간의 관계를 이해하는 것이다. 다음 장에서는 JOIN을 통해 여러 테이블을 연결하고, 752번 미스터리의 마지막 조각들을 맞춰보자. Person, Site, Visited, Survey 테이블들이 어떻게 유기적으로 연결되어 탐사대 전체의 완전한 그림을 그려내는지 확인할 시간이다…