행을 열로 눕히는 일은 보고서의 요구이고, 열을 행으로 세우는 일은 데이터의 요구입니다. 방향이 다른 두 작업이 같은 이름으로 묶여 있습니다.
풀고 시작
문제 1. PIVOT 절을 쓰려면 반드시 함께 지정해야 하는 것은?
여러 행이 한 칸으로 접히므로 그 값을 어떻게 요약할지 정하는 집계 함수가 필요하고, 어떤 값들이 열이 될지도 미리 적어 주어야 합니다. 열 이름은 실행 전에 정해져야 하기 때문입니다.
행을 열로, 열을 행으로
같은 데이터를 어떤 모양으로 보여 줄지는 목적에 따라 다릅니다. 저장에는 세로로 긴 형태가 유리합니다. 항목이 늘어도 구조를 고칠 필요가 없으니까요. 반면 사람이 읽는 보고서에는 가로로 넓은 형태가 편합니다. 월별 매출을 열로 늘어놓은 표가 그렇죠.
PIVOT은 세로를 가로로 눕히고, UNPIVOT은 가로를 세로로 세웁니다. 방향이 반대인 두 작업입니다.
PIVOT을 쓸 때 지정해야 하는 것이 셋입니다. 무엇을 열로 만들지(기준 컬럼과 그 값의 목록), 그 칸에 어떤 값을 넣을지(집계 함수), 그리고 나머지 무엇을 행으로 남길지입니다. 마지막 항목이 헷갈리기 쉬운데, PIVOT은 명시하지 않은 나머지 컬럼 전체를 그룹 기준으로 삼습니다. 그래서 쓸데없는 컬럼이 섞여 있으면 행이 예상보다 잘게 쪼개집니다. 인라인 뷰로 필요한 컬럼만 남긴 뒤 PIVOT을 걸는 것이 정석입니다.
집계 함수가 필수인 이유도 분명합니다. 열로 접히는 자리에 여러 행이 몰릴 수 있으니 무엇을 대표값으로 삼을지 정해 줘야 합니다.
열 이름이 실행 전에 정해져야 한다는 제약도 있습니다. 어떤 값들이 열이 될지 목록으로 적어야 하죠. 그래서 월이 늘어나면 쿼리를 고쳐야 합니다. 목록이 동적으로 바뀌는 화면이라면 SQL 안에서 해결하기보다 응용 프로그램에서 처리하는 편이 낫습니다.
CASE로 만드는 PIVOT
PIVOT 구문을 지원하지 않는 환경에서도 같은 결과를 만들 수 있습니다. 3강의 CASE 식과 4강의 집계 함수를 조합하면 됩니다.
원리는 이렇습니다. 열이 될 값마다 CASE 식을 하나씩 만들어, 그 값일 때만 대상 금액을 남기고 아니면 NULL로 둡니다. 그 결과에 SUM을 씌우면 4강에서 배운 대로 NULL이 계산에서 제외되므로 해당 값의 합계만 남습니다. 이 패턴을 열 개수만큼 나열하면 PIVOT과 같은 표가 나옵니다.
문법이 길어져 보이지만 장점이 있습니다. 어떤 제품에서도 동작하고, 열마다 다른 조건과 다른 집계 함수를 섞어 쓸 수 있습니다. PIVOT 구문은 정해진 틀 안에서만 움직이지만 CASE 조합은 자유롭죠. 시험에서는 두 방식이 같은 결과를 낸다는 점과 CASE 방식이 왜 동작하는지를 묻습니다.
UNPIVOT은 반대로 열 여럿을 행으로 세웁니다. 열 이름이 값이 되고, 각 칸의 값이 또 다른 컬럼의 값이 됩니다. 잘못 설계된 테이블을 정규화된 형태로 옮길 때, 또는 열마다 흩어진 값을 한 컬럼에서 한꺼번에 집계할 때 씁니다. 한 가지 성질을 알아 두세요. UNPIVOT은 값이 없는 칸을 기본적으로 결과에서 제외합니다. 빈 칸까지 행으로 남겨야 한다면 옵션을 지정해야 합니다.
정규 표현식, 패턴으로 문자열을 다루기
2강에서 본 LIKE는 와일드카드 두 개로 패턴을 표현합니다. 간단한 검색에는 충분한데, "숫자로만 이뤄졌는지", "특정 형식의 전화번호인지"처럼 조건이 복잡해지면 손을 들게 됩니다. 이때 쓰는 것이 정규 표현식입니다.
주요 함수는 네 갈래입니다. 패턴이 맞는지 판정하는 LIKE 계열, 패턴에 맞는 부분을 뽑아내는 SUBSTR 계열, 바꿔치는 REPLACE 계열, 위치나 횟수를 세는 INSTR과 COUNT 계열입니다. 함수 이름 앞에 정규 표현식임을 알리는 접두어가 붙는 형태로, 제품마다 이름은 조금씩 다릅니다.
패턴 문법에서 시험에 자주 나오는 것들만 정리해 두죠.
표기
뜻
마침표
임의의 한 문자
별표
앞의 것이 0번 이상 반복
더하기
앞의 것이 1번 이상 반복
물음표
앞의 것이 0번 또는 1번
꺾쇠 시작 표시
문자열의 시작
달러 기호
문자열의 끝
대괄호
그 안의 문자 중 하나
중괄호 안의 숫자
정확한 반복 횟수
주의할 점이 하나 있습니다. 정규 표현식은 강력하지만 컬럼을 가공하는 함수입니다. 2강에서 말한 원칙이 그대로 적용되어 그 컬럼의 인덱스를 쓸 수 없습니다. 그래서 대량 데이터에 정규 표현식 조건을 걸면 전체를 훑게 됩니다. 범위를 좁히는 조건을 함께 걸어 대상을 줄인 뒤 정규 표현식으로 마지막 걸러 내기를 하는 것이 실전 요령입니다.
여기까지가 SQL 활용입니다. 다음 두 강은 관리 구문입니다. 조회만 하던 데이터를 실제로 만들고 바꾸고 지우는 명령들이고, 되돌릴 수 있는 것과 없는 것의 구분이 핵심입니다.
인출 문제
문제 1. PIVOT 절에 집계 함수가 반드시 필요한 이유는?
행을 열로 접으면 같은 자리에 여러 행이 들어올 수 있어 무엇을 표시할지 결정해야 합니다. 그 결정이 집계 함수이며, 그래서 PIVOT은 집계 없이 쓸 수 없습니다.
문제 2. CASE 식과 SUM을 조합해 PIVOT과 같은 결과를 만들 수 있는 이유는?
집계 함수는 NULL을 제외하고 계산하므로, 원하는 값일 때만 값을 남기고 나머지를 NULL로 두면 그 값만의 합계가 나옵니다. SUM이 0으로 치환하는 것이 아니라 아예 빼는 것이 핵심입니다.
문제 3. UNPIVOT의 기본 동작에 대한 설명으로 옳은 것은?
UNPIVOT은 값이 없는 칸을 기본적으로 버립니다. 빈 칸까지 남겨야 한다면 옵션을 지정해야 하고, 열 이름은 새 컬럼의 값으로 남으며 집계 함수는 필요하지 않습니다.
문제 4. 대량 데이터에 정규 표현식 조건을 걸 때 유의할 점은?
함수로 컬럼을 가공하면 저장된 값이 아니라 계산 결과를 비교해야 하므로 인덱스가 무력해집니다. 범위 조건으로 대상을 줄인 뒤 마지막 걸러 내기에 쓰는 편이 현실적입니다.
더 풀기
출제기준의 세세항목을 따라 이 강의 범위에서 새로 낸 문제입니다. 모의고사도 여기에서 뽑습니다.
묶음 1
문제 1. PIVOT 연산을 가장 잘 설명한 것은?
월별 매출이 행으로 쌓여 있을 때 월을 컬럼으로 펼치는 것이 PIVOT입니다. 반대 방향이 UNPIVOT입니다.
문제 2. UNPIVOT 연산이 하는 일은?
일월부터 십이월까지 컬럼이 있는 표를 월과 매출 두 컬럼의 열두 행으로 바꾸는 것이 UNPIVOT입니다.
문제 3. PIVOT을 쓸 때 반드시 함께 지정해야 하는 것은?
한 칸에 여러 행이 모일 수 있으므로 집계 방법이 필요하고, 컬럼으로 만들 값의 목록도 미리 정해야 합니다.
문제 4. PIVOT에서 펼칠 값의 목록을 미리 적어야 하는 이유로 가장 알맞은 것은?
컬럼 구조는 데이터가 아니라 문장이 정합니다. 그래서 값이 늘어나도 컬럼이 자동으로 생기지 않습니다.
문제 5. 데이터에 새로운 분류가 추가되었는데 PIVOT 결과에 나타나지 않는다면 이유는?
목록에 없는 값은 결과 컬럼이 되지 않습니다. 분류가 자주 늘어나는 데이터라면 문장을 동적으로 만들어야 합니다.
문제 6. CASE 표현식으로 PIVOT과 같은 결과를 만드는 방법으로 가장 알맞은 것은?
월별 컬럼마다 그 월일 때만 값을 남기고 나머지는 NULL로 만든 뒤 합계를 구하면 됩니다. 집계 함수가 NULL을 무시하는 성질을 이용한 것입니다.
문제 7. CASE 방식에서 집계 함수로 감싸지 않으면 어떤 문제가 생기나요?
집계와 그룹화가 있어야 여러 행이 한 행으로 접힙니다. 그러지 않으면 대각선 모양의 결과가 나옵니다.
문제 8. PIVOT 결과에서 해당하는 데이터가 없는 칸은 어떻게 표시되나요?
집계할 값이 없으면 NULL입니다. 영으로 보이고 싶다면 NULL 대체 함수를 감싸야 합니다.
문제 9. PIVOT과 그룹 함수의 차이로 가장 알맞은 것은?
결과가 늘어나는 방향이 다릅니다. 세로로 늘리는 것과 가로로 펼치는 것의 차이입니다.
문제 10. UNPIVOT을 적용할 때 원래 컬럼이 NULL인 값은 기본적으로 어떻게 처리되나요?
값이 없는 자리를 행으로 만들 이유가 대개 없어 기본은 제외입니다. 필요하면 포함하도록 지정할 수 있습니다.
묶음 2
문제 1. 정규 표현식이 LIKE보다 유리한 상황으로 가장 알맞은 것은?
LIKE는 자리와 길이 정도만 표현합니다. 형태를 규칙으로 기술해야 하면 정규 표현식이 필요합니다.
문제 2. 정규 표현식에서 문자 하나를 가리키는 기호는?
마침표는 임의의 한 글자입니다. 별표와 더하기와 물음표는 반복 횟수를 나타냅니다.
문제 3. 정규 표현식에서 앞의 요소가 영 번 이상 반복됨을 뜻하는 기호는?
별표는 없어도 되고 여러 번 있어도 됩니다. 더하기는 최소 한 번, 물음표는 영 번이나 한 번입니다.
문제 4. 정규 표현식에서 문자열의 시작을 나타내는 기호는?
캐럿은 시작을, 달러는 끝을 나타냅니다. 다만 대괄호 안의 캐럿은 부정을 뜻해 뜻이 달라집니다.
문제 5. 대괄호 안에 캐럿을 맨 앞에 두면 무엇을 뜻하나요?
위치를 나타내는 캐럿과 부정을 나타내는 캐럿은 놓인 자리로 구분됩니다.
문제 6. 정규 표현식에서 여러 후보 중 하나를 뜻하는 기호는?
세로 막대는 선택을 뜻해 왼쪽이나 오른쪽 중 하나가 맞으면 됩니다.
문제 7. 정규 표현식에서 반복 횟수를 정확히 지정하려면 어떤 기호를 쓰나요?
중괄호에 숫자를 넣어 정확히 몇 번, 또는 몇 번에서 몇 번까지를 지정합니다.
문제 8. 정규 표현식으로 문자열이 조건에 맞는지 참과 거짓으로 판단할 때 쓰는 기능은?
조건절에서 형식 검증을 할 때 씁니다. 위치를 얻거나 일부를 뽑아내는 기능은 별도의 함수가 담당합니다.
문제 9. 정규 표현식으로 패턴에 맞는 부분을 다른 문자열로 바꾸는 기능의 쓰임으로 가장 알맞은 것은?
형태가 제각각인 값을 하나의 형식으로 정리할 때 유용합니다.
문제 10. 정규 표현식으로 패턴에 맞는 부분만 뽑아내는 기능의 쓰임으로 가장 알맞은 것은?
고정된 위치가 아니라 형태로 찾아야 할 때 씁니다. 자를 위치를 미리 알 수 없기 때문입니다.
묶음 3
문제 1. 조건절에서 정규 표현식을 쓰면 성능에 미치는 영향으로 가장 알맞은 것은?
값을 하나씩 검사하는 방식이라 정렬된 구조를 활용할 수 없습니다. 범위를 먼저 좁힌 뒤 적용하는 편이 좋습니다.
문제 2. 정규 표현식을 데이터 정제에 쓸 때 권장되는 방식으로 가장 알맞은 것은?
매번 검사하는 것은 비용이 큽니다. 들어올 때 정리해 두면 조회는 단순해집니다.
문제 3. 형식이 제각각인 전화번호를 하나의 형식으로 맞추는 작업에 가장 알맞은 조합은?
먼저 불필요한 문자를 걷어 내 숫자만 남기고 그다음 원하는 형식으로 만드는 순서가 안전합니다.
문제 4. PIVOT 결과를 다시 세로로 되돌리고 싶다면?
두 연산은 서로 반대 방향입니다. 다만 집계로 합쳐진 값은 원래대로 복원되지 않습니다.
문제 5. PIVOT을 거친 뒤 원래 데이터로 완전히 되돌아가지 못하는 이유는?
합계로 접힌 값은 어떤 행들이 모여 그 값이 되었는지 알 수 없습니다. 정보가 줄어드는 방향의 연산입니다.
문제 6. 여러 컬럼으로 나뉜 분기별 매출을 한 컬럼으로 접어 분석하려는 이유로 가장 알맞은 것은?
항목이 컬럼으로 있으면 항목마다 문장을 따로 써야 합니다. 값으로 접으면 조건과 집계로 한 번에 다룰 수 있습니다.
문제 7. 보고서용으로는 가로로 펼친 형태가, 분석용으로는 세로로 접힌 형태가 선호되는 이유로 가장 알맞은 것은?
읽는 주체가 다릅니다. 그래서 저장은 세로로 하고 보여 줄 때 가로로 펼치는 구성이 흔합니다.
문제 8. 컬럼 이름에 값이 들어 있는 설계, 예를 들어 일월매출과 이월매출 같은 컬럼이 늘어서 있는 구조의 문제로 가장 알맞은 것은?
데이터여야 할 것이 구조에 들어간 형태입니다. 모델링 관점에서도 유연성을 해칩니다.
문제 9. 정규 표현식과 일반 문자열 함수 중 무엇을 쓸지 판단하는 기준으로 가장 알맞은 것은?
고정 위치는 문자열 함수가 훨씬 싸고 명확합니다. 정규 표현식은 형태가 가변일 때 값을 합니다.
문제 10. 다음 중 옳지 않은 설명은?
결과의 컬럼 구조는 실행 전에 정해져야 합니다. 자동으로 늘어나게 하려면 문장을 동적으로 만들어야 합니다.