데이터는 지저분한 상태로 도착합니다.

모두 대문자인 이름들. 행마다 다르게 철자가 적힌 도시 이름들. 아무도 요청하지 않은 소수점 다섯 자리의 시험 점수들. 보고서에는 "Prof. Njoroge"가 필요한데 교사가 "Mr. Njoroge"로 기재된 경우. 출력에는 단일 전체 이름이 필요한데 성과 이름이 별도의 열에 나뉘어 있는 경우 등 말이죠.

모든 분석가는 이런 종류의 데이터로 가득 찬 스프레드시트를 앞에 두고 수동으로 수정하느라 몇 시간을 보낸 경험이 있을 것입니다. 오늘 우리는 수업을 통해 SQL에서 이를 어떻게 해결하는지 가르쳤습니다. 쿼리 한 번으로, 테이블의 모든 행에 대해 자동으로 말이죠.

두 가지 카테兄弟. 텍스트를 위한 문자열 함수 (String functions). 숫자 값을 위한 숫자 함수 (Number functions). 하나의 데이터셋: Greenwood Academy - 학생, 과목, 교사, 시험 결과.

파트 1: 문자열 함수 (String Functions)

UPPER() 및 LOWER() - 대소문자 강제 지정

가장 기본적인 텍스트 변환입니다. UPPER()는 모든 것을 대문자로 변환합니다. LOWER()는 모든 것을 소문자로 변환합니다.

-- 모든 이름(first name)을 대문자로
SELECT
    first_name,
...

first_name  | upper_first_name
------------|------------------
Amina       | AMINA
...

-- 모든 성(last name)을 소문자로
SELECT
    last_name,
...

제가 수업에서 제시한 실질적인 사용 사례는 다음과 같습니다: "두 개의 데이터셋을 비교한다고 가정해 보세요. 하나는 이름이 모두 대문자로 입력되었고, 다른 하나는 Title Case(단어의 첫 글자만 대문자)로 입력되었습니다. 두 데이터를 JOIN(조인)하기 전에, 양쪽 모두에 LOWER()를 실행하여 비교 대상이 동일하도록 만듭니다. 함수 호출 한 번으로 수동 편집 없이 해결할 수 있습니다."

INITCAP() - 자동으로 Title Case 적용

INITCAP()은 각 단어의 첫 글자를 대문자로 만들고 나머지는 소문자로 변환합니다. 이는 모두 대문자이거나 모두 소문자로 들어온 데이터를 정제하기 위한 함수입니다.

저는 테이블을 건드리기 전에 제 이름을 사용하여 이를 라이브로 실행해 보았습니다:

SELECT 'NAVAS HERBERT';
SELECT INITCAP('NAVAS HERBERT');

NAVAS HERBERT
Navas Herbert

함수 하나로 모든 단어의 첫 글자가 적절하게 대문자로 변환되었습니다. 그다음 학생 테이블에 동일하게 적용하여, 잘못된 대소문자로 들어온 이름들을 단일 열 변환으로 정제했습니다.

세 가지 함수의 차이점은 다음과 같습니다:

대부분의 이름 정제 작업에는 INITCAP()이 적절한 선택입니다. 대소문자를 구분하지 않는 비교(case-insensitive comparisons)를 위해서는 양쪽 모두에 LOWER()를 사용하세요. UPPER()는 모든 글자가 강조되어야 하는 코드, 약어, 레이블 등에 유용합니다.

LENGTH() - 이 텍스트의 길이는 얼마인가요?

LENGTH()는 공백을 포함하여 문자열 내의 모든 문자를 계산합니다.

SELECT
    subject_name,
    LENGTH(subject_name) AS name_length
...

subject_name      | name_length
------------------|------------
Mathematics       | 11
...

가장 화려한 함수는 아니지만, 데이터 검증(data validation)에 있어 가장 유용한 함수 중 하나입니다. _"만약 전화번호 열의 LENGTH()가 10이 아니라면 무언가 잘못 입력된 것입니다. 국가 식별 번호(national ID)의 LENGTH()가 8이 아니라면 플래그를 표시하세요. LENGTH()는 모든 행을 일일이 읽지 않고도 형식 오류를 잡아내는 방법입니다."

SUBSTRING() - 텍스트의 일부 잘라내기

때로는 문자열의 일부분만 필요할 때가 있습니다. SUBSTRING(text, start, length)는 지정된 시작 위치부터 지정된 문자 수만큼 잘라냅니다.

SELECT
    first_name,
    SUBSTRING(first_name, 1, 3) AS name_prefix
...

first_name  | name_prefix
------------|------------
Amina       | Ami
...

위치 1은 첫 번째 문자를 의미합니다. SUBSTRING(first_name, 1, 3)은 위치 1에서 시작하여 3글자를 가져오라는 의미입니다.

실제 활용 사례: 날짜 문자열에서 연도 추출하기, 긴 직원 ID에서 부서 코드 뽑아내기, 제품 SKU를 카테고리 접두사로 다듬기 등. 문자열의 일부를 일관되게 가져와야 할 때마다 SUBSTRING은 유용한 도구가 됩니다.

CONCAT() - 텍스트 결합하기

CONCAT()은 여러 개의 텍스트 조각을 받아 하나로 결합합니다. 열(column), 문자열 리터럴(string literals), 공백 등 무엇이든 전달할 수 있습니다.

-- 이름(first name)과 성(last name)을 결합하여 전체 이름(full name) 만들기
SELECT
    first_name,
...

first_name | last_name  | full_name
-----------|------------|------------------
Amina      | Hassan     | Amina Hassan
...

중간에 있는 공백 - ' ' - 은 인자 (argument)로 전달되는 문자열 리터럴 (string literal)입니다. 이것이 없다면, CONCAT(first_name, last_name)은 AminaHassan을 반환합니다. 항상 실제로 원하는 구분자 (separator)를 포함하세요.

그다음은 더 표현력이 풍부한 버전입니다 - 컬럼 (column)들을 사용하여 읽기 쉬운 문장을 만드는 방법입니다:

-- 과목명과 교사명을 읽기 쉬운 레이블로 결합
SELECT
    CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
...

subject_teacher
-----------------------------
Mathematics - Mr. Njoroge
...

한 걸음 더 나아가면:

SELECT
    CONCAT(teacher_name, ' is teaching ', subject_name) AS description
FROM greenwood_academy.subjects;

description
-----------------------------------------
Mr. Njoroge is teaching Mathematics
...

한 학생인 Otieno은 이렇게 말했습니다: "보고서 생성기 (report generators)가 이런 식으로 작동하는 거죠, 그렇죠? 그냥 컬럼 값들을 문장으로 CONCAT하는 거잖아요."
정확합니다. 모든 자동 이메일, 모든 생성된 PDF 헤더, "Welcome, Amina"라고 표시되는 모든 대시보드 레이블은 - 상류 (upstream) 어딘가에서 CONCAT이 실행되고 있는 것입니다.

TRIM() - 불필요한 공백 제거

데이터 입력은 일관적이지 않습니다. 사람들은 값의 앞이나 뒤에 스페이스바를 누르곤 하며, 'English'가 ' English '와 일치하지 않아 비교 (comparison)가 실패하거나 JOIN이 0개의 행을 반환할 때까지 아무도 이를 알아차리지 못합니다.

TRIM()은 앞뒤의 공백을 제거합니다.

SELECT
    '  English   ',
    LENGTH('  English   '),
...

?column?      | length | btrim   | length
--------------|--------|---------|-------
  English     |     12 | English |     7

공백을 포함하면 12자입니다. 공백이 없으면 7자입니다. 같은 단어이지만, TRIM 없이 비교나 JOIN을 수행하면 완전히 달라집니다.

저는 학생들에게 이 함수가 초보자 SQL에서 가장 혼란스러운 버그를 해결해 주는 함수라고 말했습니다. "분명히 일치해야 하는 WHERE 절을 작성했습니다. 그런데 아무것도 반환되지 않습니다. 10분 동안 뚫어지게 쳐다봅니다. 그러다 데이터베이스의 값에 뒤따르는 공백(trailing space)이 있다는 것을 깨닫게 됩니다. TRIM()이 해결책입니다. 사용자 입력에서 온 값들이 포함된 모든 비교 연산의 양쪽 모두에 이 함수를 적용하세요."

REPLACE() - 한 값을 다른 값으로 교체하기

REPLACE(string, find, replacement)는 문자열을 스캔하여 특정 텍스트 조각의 모든 발생 사례를 다른 값으로 교체합니다.

우리는 두 가지 방식으로 이를 사용했습니다.

도시 이름 약어 만들기 - 지난주에 배운 CASE WHEN과 결합하여 사용:

SELECT DISTINCT city,
CASE
    WHEN city = 'Eldoret'  THEN 'ELD'
...

city     | city_short
---------|----------
Nairobi  | NRB
...

컬럼 전체의 직함 업데이트하기:

-- 모든 교사 이름에서 'Mr'를 'Prof'로 교체
SELECT
    teacher_name,
...

teacher_name   | teacher_renamed
---------------|----------------
Mr. Njoroge    | Prof. Njoroge
...

제가 즉시 주의를 준 한 가지 사항은 다음과 같습니다: REPLACE()는 리터럴(literal)이며 전역적(global)입니다. 즉, 컬럼 내에서 검색 문자열의 첫 번째 사례만 바꾸는 것이 아니라 모든 발생 사례를 교체합니다. 만약 teacher_name에 "Mr. Mwangi Mrima"가 포함되어 있다면, 두 번의 Mr가 모두 교체될 것입니다. 실제 데이터에는 때때로 이러한 예외 사례(edge cases)가 존재합니다. UPDATE를 실행하기 전에 항상 SELECT 문으로 먼저 테스트하십시오.

파트 2: 숫자 함수

세 가지 함수, 하나의 개념: 숫자가 반올림되는 방식을 제어하는 것입니다.

ROUND(), CEIL(), FLOOR() - 소수점을 처리하는 세 가지 방법

ROUND(value, decimal_places) - 지정된 소수점 자리에서 가장 가까운 값으로 반올림합니다. 표준 반올림 방식: 0.5 이상은 올림, 0.5 미만은 내림합니다.

CEIL(value) - 소수점이 아무리 작더라도 항상 다음 정수로 올림합니다.

FLOOR(value) - 소수점이 아무리 크더라도 항상 이전 정수로 내림합니다.

우리는 동일한 데이터에 대해 세 함수를 모두 실행했습니다. 10점 만점의 점수를 만들기 위해 시험 점수를 10으로 나눈 결과입니다:

SELECT
    result_id,
    marks,
...

result_id | marks | round_out_of_10 | ceil_out_of_10 | floor_out_of_10
----------|-------|-----------------|----------------|----------------
1         | 73    | 7.3             | 8              | 7
...

단일 행을 읽어보면: marks = 73입니다. 이를 10으로 나누면 7.3이 됩니다. ROUND() 함수는 소수점 첫째 자리까지 유지하여 7.3으로 만듭니다. CEIL() 함수는 8로 올립니다 - 항상 올림합니다. FLOOR() 함수는 7로 내립니다 - 항상 내림합니다.

저는 칠판에 다음과 같은 비유를 들었습니다: "ROUND는 공정한 선생님이 채점하는 방식입니다. CEIL은 낙관적인 선생님입니다 - 의심스러울 때는 학생에게 유리하게 점수를 줍니다. FLOOR는 엄격한 선생님입니다 - 다음 점수를 완전히 획득했을 때만 부분 점수를 인정합니다.""

/ 10 대신 / 10.0을 사용하는 점은 짚고 넘어갈 가치가 있습니다. PostgreSQL에서 정수를 정수로 나누면 정수가 반환됩니다 - 즉, 73 / 10은 7.3이 아니라 7을 반환합니다. 10.0이라고 작성하면 강제로 소수점 나눗셈(decimal division)을 수행하게 됩니다. 이는 놓칠 경우 완전히 잘못된 결과를 초래할 수 있는 작은 디테일입니다.

실제 사용 사례:

• ROUND() - 재무 합계, 평균, 보고서의 백분율
• CEIL() - 부분 단위를 기준으로 요금을 부과하는 과금 시스템 (예: 2.1시간을 사용했다면 3시간으로 청구)
• FLOOR() - 할인 등급 (예: KES 2,950을 지출했다면 3단계가 아닌 2단계 적용)

전체 함수 치트 시트 (Cheat Sheet)

문자열 함수 (String Functions):

숫자 함수 (Number Functions):

연습 문제

쉬움 (Easy):

-- 1. 모든 학생의 이름을 소문자로 표시하세요
-- 2. 각 과목 이름의 글자 수는 몇 개인가요?
-- 3. SUBSTRING을 사용하여 모든 교사 이름의 첫 글자를 추출하세요

중간 (Medium):

-- 학생 명부 라벨을 만드세요:
-- 형식: "Hassan, Amina - Nairobi"
-- (성, 이름 - 도시)
...

도전 (Challenge):

-- Greenwood Academy는 깔끔한 결과 요약을 원합니다:
-- 표시 항목: 전체 이름 (CONCAT), 점수, 10점 만점 기준 점수 (소수점 1자리까지 ROUND),
-- 그리고 CASE WHEN을 사용한 'grade_label':
...

이번 세션을 가르치며 느낀 점

1. 개인 이름에 INITCAP을 적용하면 즉시 이해됩니다. 테이블을 건드리기 전에 실시간으로 INITCAP('NAVAS HERBERT') — 여러분 자신의 이름 — 을 실행해 보면, 이 함수가 추상적인 개념이 아니라 즉시 유용한 기능이라는 것을 느끼게 됩니다. 모든 학생은 이름의 대소문자가 잘못되어 있었던, 자신이 사용해 본 데이터셋을 떠올렸습니다.

2. TRIM은 누구나 겪어본 버그를 설명해 줍니다. WHERE 절이 분명히 일치해야 함에도 불구하고 아무것도 반환하지 않는 순간 — 한쪽에는 뒤따르는 공백(trailing space)이 있고, 다른 쪽에는 깔끔한 값이 있는 경우 말이죠. 학생들이 자신의 경험에서 그 버그를 인식할 때, TRIM은 잊을 수 없는 함수가 됩니다.

3. 문장 속의 CONCAT은 CONCAT을 이해하게 만듭니다. CONCAT(teacher_name, ' is teaching ', subject_name) — 출력 결과가 실제 문장처럼 읽히며, 학생들은 이를 즉시 자동화된 보고서, 이메일 템플릿, 그리고 그들이 보았던 대시보드 레이블과 연결 지었습니다. 이 함수는 더 이상 SQL 기술(trick)처럼 느껴지지 않고, 실제로 사용할 수 있는 도구로 느껴지기 시작했습니다.

4. 동일한 쿼리 내의 동일한 데이터에 대해 ROUND, CEIL, FLOOR를 함께 보여주는 것이 이들을 가르치는 유일한 방법입니다. 동일한 점수에 대해 나란히 배치하면 차이점이 즉각적이고 시각적으로 드러납니다. 별도의 예시로 순차적으로 설명하면, 각 함수를 기억하게 만드는 대조 효과를 놓치게 됩니다.

다음 단계: JOINs

오늘 배운 모든 함수는 단일 테이블에서 작동합니다. 다음 세션에서는 여러 테이블(학생, 과목, 결과)을 연결하고, 결합된 데이터 전체에 대해 이와 동일한 함수들을 실행해 볼 것입니다.

-- 미리보기: 과목과 반올림된 점수가 포함된 전체 이름
SELECT
    CONCAT(s.first_name, ' ', s.last_name) AS full_name,
...

동일한 함수, 두 개의 JOIN, 하나의 깔끔한 보고서. 다음 주에 진행됩니다.

직접 시도해 보세요

• 데이터셋: Greenwood Academy - 링크 곧 공개 예정
• 이번 주 SQL 스크립트 GitHub - 링크 곧 공개 예정

도전 과제를 시도해 보세요 - CONCAT, ROUND, 그리고 CASE WHEN을 결합한 전체 결과 요약입니다. 이 단일 쿼리는 세 번의 학습 세션을 사용합니다: 오늘의 함수 (functions), 지난주의 CASE WHEN, 그리고 2주 전의 GROUP BY입니다. 만약 이 쿼리가 문제없이 실행된다면, 여러분은 진정한 SQL 직관 (SQL intuition)을 쌓아가고 있는 것입니다.

저는 나이로비에서 Python 기초 → 데이터 과학 (Data Science) 또는 데이터 엔지니어링 (Data Engineering) 전문 과정으로 이어지는 전체 데이터 프로그램을 운영하는 데이터 트레이너입니다.
저는 매주 우리가 무엇을 다루었는지, 무엇이 효과적이었는지, 그리고 무엇이 저를 놀라게 했는지에 대해 글을 씁니다.