소계와 합계를 붙이려고 같은 테이블을 세 번 읽고 UNION으로 쌓던 쿼리가, 키워드 하나로 한 번 읽는 쿼리가 됩니다.
풀고 시작
문제 1. 세 개의 컬럼을 지정한 ROLLUP과 CUBE가 각각 만들어 내는 소계 조합의 개수는?
ROLLUP은 지정한 순서를 따라 오른쪽부터 하나씩 지워 가므로 컬럼 수에 1을 더한 만큼, 즉 4개의 조합을 만듭니다. CUBE는 가능한 모든 조합을 만들므로 2의 세제곱인 8개입니다.
소계를 만드는 문제
부서별 급여 합계를 뽑고 그 아래에 전체 합계를 붙이는 보고서를 생각해 보죠. 4강의 GROUP BY만으로는 부서별 합계까지만 나옵니다. 전체 합계를 붙이려면 GROUP BY 없는 질의를 한 번 더 실행해 9강의 UNION ALL로 쌓아야 했습니다.
문제는 이 방식이 같은 테이블을 여러 번 읽는다는 것입니다. 부서별, 부서와 직급별, 전체 합계를 모두 붙이려면 세 번 읽어야 하죠. 그룹 함수는 이 일을 한 번 읽고 처리합니다. ROLLUP, CUBE, GROUPING SETS 세 가지가 있습니다.
ROLLUP, 오른쪽부터 지워 가기
ROLLUP은 지정한 컬럼을 오른쪽부터 하나씩 지워 가며 소계를 만듭니다. 부서와 직급을 지정하면 세 단계가 나옵니다. 부서와 직급별 합계, 부서별 합계, 전체 합계입니다. 즉 컬럼 수에 1을 더한 만큼의 조합이 생깁니다.
여기서 핵심은 순서가 결과를 바꾼다는 점입니다. 부서와 직급 순으로 적으면 부서별 소계가 나오지만, 직급과 부서 순으로 적으면 직급별 소계가 나옵니다. 계층 구조를 가진 항목을 위에서 아래로 적어 주는 것이 자연스럽습니다. 지역, 지점, 담당자 같은 순서죠.
부분적으로만 소계를 원한다면 일부 컬럼을 ROLLUP 밖에 둘 수도 있습니다. GROUP BY 절에 컬럼 하나를 그냥 적고 나머지를 ROLLUP으로 감싸면, 밖에 있는 컬럼은 항상 그룹 기준으로 유지되고 안쪽만 단계적으로 접힙니다.
CUBE와 GROUPING SETS
CUBE는 지정한 컬럼의 가능한 모든 조합으로 소계를 만듭니다. 두 컬럼이면 네 가지, 세 컬럼이면 여덟 가지입니다. 컬럼 수를 n이라 하면 2의 n제곱이죠. 다차원 분석표를 만들 때 쓰이지만, 컬럼이 늘면 조합이 폭발적으로 늘어 비용이 급격히 커집니다. 필요한 조합만 알고 있다면 CUBE보다 나은 선택이 있습니다.
GROUPING SETS가 그것입니다. 원하는 조합을 직접 나열합니다. 부서별과 직급별만 필요하고 둘의 교차는 필요하지 않다면 그 둘만 적으면 됩니다. 그리고 GROUPING SETS는 나열 순서가 결과 순서를 보장하지 않습니다. 순서가 필요하면 ORDER BY를 따로 써야 합니다.
함수
만드는 조합
언제 쓰나
ROLLUP
오른쪽부터 접어 가는 계층 소계
계층이 있는 항목의 소계와 총계
CUBE
모든 조합
다차원 교차 분석
GROUPING SETS
나열한 조합만
필요한 소계만 골라 뽑을 때
세 함수의 관계를 정리하면 이렇습니다. ROLLUP과 CUBE는 모두 GROUPING SETS로 표현할 수 있습니다. 반대는 안 됩니다. GROUPING SETS가 가장 일반적인 형태이고 나머지 둘은 자주 쓰는 조합에 붙인 이름인 셈이죠.
소계 행을 알아보는 법
소계 행에서는 접힌 컬럼의 값이 NULL로 나옵니다. 그런데 여기서 문제가 생깁니다. 원래 데이터에도 값이 없는 행이 있으면, 그 NULL과 소계의 NULL을 구별할 수 없습니다.
이 문제를 풀기 위해 GROUPING 함수가 있습니다. 인자로 컬럼을 주면, 그 컬럼이 소계 때문에 접힌 자리에서는 1을, 아니면 0을 돌려줍니다. 그래서 3강의 CASE 식과 함께 쓰면 소계 행에 "부서 합계"나 "전체 합계" 같은 이름을 붙일 수 있습니다. 화면에 그대로 내보낼 보고서를 SQL 한 문장으로 만들 수 있게 되죠.
GROUPING_ID 함수는 여러 컬럼의 접힘 여부를 한 번에 이진수로 묶어 하나의 숫자로 돌려줍니다. 소계의 단계별로 다른 처리를 하고 싶을 때, 조건을 여러 개 늘어놓지 않고 이 값 하나로 분기할 수 있습니다.
정렬할 때도 소계 행 때문에 신경 쓸 것이 생깁니다. 접힌 컬럼이 NULL이라 5강에서 본 NULL 정렬 규칙이 그대로 적용되어, 소계 행이 엉뚱한 자리에 끼어들 수 있습니다. GROUPING 함수 결과를 정렬 기준으로 앞세우면 소계를 원하는 위치에 고정할 수 있습니다.
다음 강은 윈도우 함수입니다. 그룹 함수가 행을 줄여 요약하는 도구라면, 윈도우 함수는 행을 줄이지 않고 요약값을 옆에 붙여 주는 도구입니다. 이 대비를 기억해 두면 두 영역이 섞이지 않습니다.
인출 문제
문제 1. ROLLUP에서 컬럼을 나열하는 순서를 바꾸면 어떻게 되나?
ROLLUP은 오른쪽부터 지워 가므로 순서가 곧 계층입니다. 부서와 직급 순이면 부서별 소계가, 직급과 부서 순이면 직급별 소계가 나옵니다. 조합의 개수는 컬럼 수에 1을 더한 값으로 순서와 무관하게 같습니다.
문제 2. GROUPING 함수가 필요한 이유로 가장 적절한 것은?
접힌 컬럼은 NULL로 표시되므로 원본에 있던 NULL과 겉모습이 같습니다. GROUPING 함수는 접힘 여부를 1과 0으로 알려 주어 두 경우를 가릴 수 있게 합니다.
문제 3. 필요한 소계 조합이 부서별과 직급별 두 가지뿐일 때 가장 적절한 선택은?
필요한 조합만 나열하는 것이 GROUPING SETS입니다. CUBE는 필요 없는 교차와 전체 합계까지 만들고, ROLLUP은 계층 방향의 조합만 만들며, 질의를 두 번 실행하면 테이블을 두 번 읽습니다.
문제 4. 그룹 함수를 사용하는 가장 큰 이점은?
소계 수준마다 질의를 따로 실행하고 합치는 대신 한 번 읽어 여러 수준을 만들어 냅니다. NULL 처리나 정렬은 여전히 별도로 다뤄야 하고, 여러 테이블을 다루려면 조인이 필요합니다.
더 풀기
출제기준의 세세항목을 따라 이 강의 범위에서 새로 낸 문제입니다. 모의고사도 여기에서 뽑습니다.
묶음 1
문제 1. 그룹 함수가 필요한 상황으로 가장 알맞은 것은?
여러 단계의 소계를 한 결과에 담는 것이 그룹 함수의 역할입니다. 그러지 않으면 여러 질의를 쌓아 붙여야 합니다.
문제 2. ROLLUP의 동작으로 옳은 것은?
ROLLUP은 계층적 소계를 만듭니다. 그래서 컬럼 순서가 결과의 의미를 정합니다.
문제 3. 부서와 직급으로 ROLLUP을 적용하면 만들어지는 집계 단위는?
오른쪽인 직급부터 지워지므로 조합과 부서별 소계와 전체가 나옵니다. 직급별 소계는 나오지 않습니다.
문제 4. 직급별 소계도 함께 보고 싶다면 어떤 함수를 써야 하나요?
CUBE는 모든 조합의 소계를 만듭니다. 부서별과 직급별과 조합과 전체가 모두 나옵니다.
문제 5. 부서 3개와 직급 4개가 모두 존재할 때 두 컬럼으로 ROLLUP을 적용하면 결과 행 수는?
조합 열두 행에 부서별 소계 세 행과 전체 합계 한 행을 더해 열여섯 행입니다.
문제 6. 같은 데이터에 CUBE를 적용하면 결과 행 수는?
조합 열둘에 부서별 셋과 직급별 넷과 전체 하나를 더해 스무 행입니다.
문제 7. N개의 컬럼에 ROLLUP을 적용하면 몇 단계의 집계 수준이 만들어지나요?
컬럼을 하나씩 지워 가므로 원래 조합부터 전체 합계까지 N 더하기 1 단계가 됩니다.
문제 8. N개의 컬럼에 CUBE를 적용하면 몇 개의 집계 수준이 만들어지나요?
각 컬럼을 포함하거나 빼는 모든 조합이므로 이의 N 제곱입니다. 컬럼이 늘면 결과가 급격히 커집니다.
문제 9. GROUPING SETS의 특징으로 가장 알맞은 것은?
필요한 단위만 나열하면 그것만 계산합니다. ROLLUP과 CUBE로 표현되지 않는 조합도 만들 수 있습니다.
문제 10. ROLLUP에서 컬럼 순서를 바꾸면 결과가 달라지나요?
부서와 직급 순서면 부서별 소계가, 직급과 부서 순서면 직급별 소계가 나옵니다.
묶음 2
문제 1. CUBE에서 컬럼 순서를 바꾸면 결과가 달라지나요?
모든 조합을 만들므로 순서와 무관하게 같은 집계 단위가 나옵니다. 표시 순서만 달라질 수 있습니다.
문제 2. 소계 행에서 집계 기준 컬럼의 값은 어떻게 표시되나요?
그 컬럼으로 묶지 않은 행이므로 값이 없다는 뜻의 NULL이 들어갑니다.
문제 3. 소계 행의 NULL과 원래 데이터에 있던 NULL을 구분하려면?
GROUPING 함수는 소계 행이면 일을 돌려줍니다. 원래 데이터의 NULL은 영을 돌려주므로 구분됩니다.
문제 4. GROUPING 함수가 일을 돌려준다는 것은 무엇을 뜻하나요?
집계에서 빠진 컬럼이면 일입니다. 이 값을 CASE와 함께 쓰면 소계 행에 합계나 총계 같은 이름을 붙일 수 있습니다.
문제 5. 소계 행에 부서 소계라는 문구를 표시하려면 어떻게 하나요?
소계 여부를 알 수 있으니 그에 따라 표시할 문자열을 바꾸면 됩니다.
문제 6. GROUPING_ID 함수를 가장 잘 설명한 것은?
컬럼마다 일과 영을 매겨 자릿수로 합칩니다. 어느 수준의 소계인지 하나의 값으로 판단할 수 있어 정렬이나 분기에 편합니다.
문제 7. ROLLUP 결과를 원하는 순서로 보여 주려면?
소계 행의 기준 컬럼이 NULL이라 정렬에서 위치가 달라집니다. GROUPING 함수를 정렬 기준에 함께 쓰면 원하는 자리에 놓을 수 있습니다.
문제 8. ROLLUP으로 얻은 결과를 여러 질의를 UNION ALL로 쌓아 얻는 것과 비교했을 때의 장점은?
쌓는 방식은 집계 단위마다 테이블을 다시 읽습니다. 그룹 함수는 한 번 읽고 여러 수준을 함께 만듭니다.
문제 9. 부서, 직급, 지역 세 컬럼에 CUBE를 적용하면 집계 수준은 몇 개인가요?
이의 세제곱인 여덟입니다. 컬럼이 하나 더 늘면 열여섯이 되므로 남용하면 결과가 감당하기 어려워집니다.
문제 10. 같은 세 컬럼에 ROLLUP을 적용하면 집계 수준은 몇 개인가요?
셋에 하나를 더한 넷입니다. 세 컬럼 조합과 두 컬럼 소계와 한 컬럼 소계와 전체입니다.
묶음 3
문제 1. 부분적으로만 소계를 만들고 싶을 때 ROLLUP 안에 괄호를 써서 컬럼을 묶으면 어떤 효과가 있나요?
묶음 단위로 계층이 만들어집니다. 연과 월을 함께 묶으면 연월 단위와 전체만 나오고 연 단위 소계는 생기지 않습니다.
문제 2. 소계가 포함된 결과를 HAVING으로 거를 때 주의할 점은?
소계도 하나의 그룹 결과입니다. 조건에 걸리면 함께 사라지므로 GROUPING 함수로 예외를 두어야 할 때가 있습니다.
문제 3. 그룹 함수로 만든 소계 행에서 개수를 세면 무엇을 세는 것인가요?
소계는 그 범위의 데이터를 다시 집계한 것입니다. 그래서 개수도 그 범위 전체의 개수가 됩니다.
문제 4. 전체 합계 행 하나만 추가로 얻고 싶다면 가장 간단한 방법은?
필요한 수준만 지정하면 불필요한 소계가 생기지 않습니다. GROUPING SETS가 가장 명확합니다.
문제 5. 그룹 함수를 쓴 결과를 보고서로 그대로 내보낼 때 흔히 필요한 후처리는?
그대로 두면 빈칸이 무엇을 뜻하는지 읽는 사람이 알 수 없습니다. 문구와 순서를 정해 주어야 보고서가 됩니다.
문제 6. 다음 중 그룹 함수에 대한 설명으로 옳지 않은 것은?
GROUPING SETS는 지정한 단위만 만듭니다. 전체 합계가 필요하면 명시적으로 넣어야 합니다.
문제 7. 매출 데이터를 연과 분기와 월로 ROLLUP할 때 컬럼 순서를 연, 분기, 월로 두는 이유는?
시간 계층은 큰 단위가 왼쪽에 와야 소계가 뜻대로 만들어집니다. 순서를 뒤집으면 의미 없는 소계가 나옵니다.
문제 8. CUBE를 남용하면 생기는 문제로 가장 알맞은 것은?
필요한 조합만 GROUPING SETS로 지정하면 같은 결과를 훨씬 싸게 얻을 수 있습니다.
문제 9. 소계 행을 항상 그룹의 맨 아래에 놓으려면 정렬을 어떻게 해야 하나요?
소계 행은 기준 컬럼이 NULL이라 위치가 제품 설정에 따라 달라집니다. 소계 여부를 명시적으로 정렬 기준에 넣어야 안정적입니다.
문제 10. 그룹 함수와 윈도우 함수의 차이로 가장 알맞은 것은?
결과의 모양이 다릅니다. 원래 행을 그대로 두고 옆에 합계를 붙이고 싶다면 윈도우 함수를 씁니다.