์ด ๊ฒŒ์‹œ๋ฌผ์—์„œ๋Š” SQL์—์„œ ์ž์ฃผ ์‚ฌ์šฉ๋˜๋Š” ๊ฐ•๋ ฅํ•œ ๊ทธ๋ฃนํ™” ๋„๊ตฌ(Aggregate Functions)๋“ค์ธ ROLL UP, GROUPING, GROUPING SETS, CUBE์— ๋Œ€ํ•ด ๊ฐ„๋‹จํžˆ ์„ค๋ช…ํ•˜๊ณ , ๊ฐ ๊ธฐ๋Šฅ์— ๋Œ€ํ•œ ์˜ˆ์ œ ์ฝ”๋“œ๋ฅผ ์ œ๊ณตํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.๐Ÿ™‚

 

1๏ธโƒฃ ROLLUP : ๊ณ„์ธต์  ์†Œ๊ณ„ ์ƒ์„ฑ

ํ•จ์ˆ˜ ์ด๋ฆ„์˜ ์˜๋ฏธ

"Roll up"์€ '์ฐจ๊ณก์ฐจ๊ณก ์Œ“์•„์˜ฌ๋ฆฌ๋‹ค'๋ผ๋Š” ๋œป์ž…๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ์˜ ๊ณ„์ธต์  ์ง‘๊ณ„๋ฅผ ๋‚˜ํƒ€๋‚ด๋ฉฐ, ๊ฐ ๋ ˆ๋ฒจ์—์„œ ํ•ฉ๊ณ„๋ฅผ ๊ณ„์† ๋ˆ„์ ํ•˜์—ฌ ์Œ“์•„์˜ฌ๋ฆฌ๋Š” ๊ณผ์ •์„ ๋น„์œ ํ•ฉ๋‹ˆ๋‹ค.

์„ค๋ช…

์ฃผ์–ด์ง„ ์ปฌ๋Ÿผ๋“ค์— ๋Œ€ํ•ด ๊ณ„์ธต์ ์ธ ์†Œ๊ณ„๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. ์ฆ‰, ๊ฐ€์žฅ ์™ผ์ชฝ ์ปฌ๋Ÿผ๋ถ€ํ„ฐ ์‹œ์ž‘ํ•˜์—ฌ ๊ฐ ์ปฌ๋Ÿผ์— ๋Œ€ํ•œ ์†Œ๊ณ„๋ฅผ ๊ตฌํ•˜๊ณ , ์ตœ์ข…์ ์œผ๋กœ ๋ชจ๋“  ์ปฌ๋Ÿผ์— ๋Œ€ํ•œ ์ดํ•ฉ์„ ๊ตฌํ•ฉ๋‹ˆ๋‹ค.

์˜ˆ์ œ (ํ•จ์ˆ˜ ์ธ์ž 1๊ฐœ)

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT department_id, SUM(salary) AS total_salary -- ๋ถ€์„œ๋ณ„ ๊ธ‰์—ฌ ํ•ฉ๊ณ„ ๊ณ„์‚ฐ
FROM employees
GROUP BY ROLLUP(department_id); -- department_id์— ๋Œ€ํ•ด ROLLUP ์ ์šฉโœจ

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id total_salary ๋น„๊ณ 
10 15,000  
20 12,000  
NULL 27,000 ์ „์ฒด ์ดํ•ฉโœจ

์˜ˆ์ œ (ํ•จ์ˆ˜ ์ธ์ž 2๊ฐœ)

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT department_id, job_id, SUM(salary) AS total_salary -- ๋ถ€์„œ๋ณ„ ๋ฐ ์ง๋ฌด๋ณ„ ๊ธ‰์—ฌ ํ•ฉ๊ณ„ ๊ณ„์‚ฐ
FROM employees
GROUP BY ROLLUP(department_id, job_id); -- department_id์™€ job_id์— ๋Œ€ํ•ด ROLLUP ์ ์šฉโœจ

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id job_id total_salary ๋น„๊ณ 
10 IT 11,000  
10 HR 4,000  
10 NULL 15,000 department_id 10์˜ ์†Œ๊ณ„โœจ
20 IT 7,000  
20 HR 5,000  
20 NULL 12,000 department_id 20์˜ ์†Œ๊ณ„โœจ
NULL NULL 27,000 ์ „์ฒด ์ดํ•ฉ

 * ์œ„ ๊ฒฐ๊ณผ์—์„œ ROLLUP์„ ์‚ฌ์šฉํ•จ์œผ๋กœ์จ ๋ถ€์„œ๋ณ„, ์ง๋ฌด๋ณ„ ์ด๊ณ„๋ฅผ ํฌํ•จํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ํšจ์œจ์ ์œผ๋กœ ์ถ”์ถœํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

2๏ธโƒฃ CUBE : ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ ๊ทธ๋ฃนํ™” ์กฐํ•ฉ์˜ ์ง‘๊ณ„ ๊ณ„์‚ฐ

ํ•จ์ˆ˜ ์ด๋ฆ„์˜ ์˜๋ฏธ

"Cube"๋Š” '์ž…์ฒด์ ์ธ ๊ตฌ์กฐ'๋ฅผ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค. ์—ฌ๋Ÿฌ ์ฐจ์›์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ง‘๊ณ„ํ•˜๋Š” ๊ฒƒ์„ ๋น„์œ ์ ์œผ๋กœ ํ‘œํ˜„ํ•ฉ๋‹ˆ๋‹ค. CUBE๋Š” ๋‘ ๊ฐœ ์ด์ƒ์˜ ์—ด์— ๋Œ€ํ•ด ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ ์กฐํ•ฉ์˜ ์ง‘๊ณ„๋ฅผ ์ƒ์„ฑํ•˜๋ฏ€๋กœ, ๋ฐ์ดํ„ฐ๊ฐ€ ๋‹ค์ฐจ์›์ ์œผ๋กœ ๋ณด๊ด€๋˜๋Š” ์ž…์ฒด์ ์ธ ํ˜•์ƒ(ํ๋ธŒ)์œผ๋กœ ์ƒ์ƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ฆ‰, ์—ฌ๋Ÿฌ ์ถ•์„ ๋”ฐ๋ผ ๋ฐ์ดํ„ฐ๋ฅผ ์ง‘๊ณ„ํ•˜๊ณ , ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ ์กฐํ•ฉ์˜ ๊ฒฐ๊ณผ๋ฅผ ๋ณด์—ฌ์ฃผ๋Š” ๊ฒƒ์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

์„ค๋ช…

CUBE๋Š” ROLLUP์˜ ํ™•์žฅ๋œ ํ˜•ํƒœ๋กœ, ์ฃผ์–ด์ง„ ์ปฌ๋Ÿผ๋“ค์— ๋Œ€ํ•œ ๋ชจ๋“  ๊ฐ€๋Šฅํ•œ ์กฐํ•ฉ์˜ ์†Œ๊ณ„๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. ์ฆ‰, ๊ฐ ์ปฌ๋Ÿผ์— ๋Œ€ํ•œ ์†Œ๊ณ„, ๋‘ ๊ฐœ์˜ ์ปฌ๋Ÿผ ์กฐํ•ฉ์— ๋Œ€ํ•œ ์†Œ๊ณ„, ์„ธ ๊ฐœ์˜ ์ปฌ๋Ÿผ ์กฐํ•ฉ์— ๋Œ€ํ•œ ์†Œ๊ณ„ ๋“ฑ ๋ชจ๋“  ๊ฒฝ์šฐ์˜ ์ˆ˜๋ฅผ ๊ณ„์‚ฐํ•ฉ๋‹ˆ๋‹ค.

* ํ•จ์ˆ˜ ์ด๋ฆ„์˜ ์˜๋ฏธ: 

์˜ˆ์ œ (ํ•จ์ˆ˜ ์ธ์ž 1๊ฐœ)

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id); -- department_id์— ๋Œ€ํ•ด CUBE ์ ์šฉโœจ

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id total_salary ๋น„๊ณ 
10 15,000  
20 12,000  
NULL 27,000 ์ „์ฒด ์ดํ•ฉโœจ

* ํ•จ์ˆ˜ ์ธ์ž๊ฐ€ 1๊ฐœ์ผ ๋•Œ CUBE์™€ ROLLUP์€ ๊ฒฐ๊ณผ์ ์œผ๋กœ ๋™์ผํ•œ ์ถœ๋ ฅ ๊ฒฐ๊ณผ๋ฅผ ์ œ๊ณตํ•ฉ๋‹ˆ๋‹ค. 

์˜ˆ์ œ (ํ•จ์ˆ˜ ์ธ์ž 2๊ฐœ)

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY CUBE(department_id, job_id); -- department_id์™€ job_id์— ๋Œ€ํ•ด CUBE ์ ์šฉโœจ

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id job_id total_salary ๋น„๊ณ 
10 IT 11,000  
10 HR 4,000  
10 NULL 15,000 department_id 10์˜ ์†Œ๊ณ„โœจ
20 IT 7,000  
20 HR 5,000  
20 NULL 12,000 department_id 20์˜ ์†Œ๊ณ„โœจ
NULL IT 18,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„โœจ
NULL HR 9,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„โœจ
NULL NULL 27,000 ์ „์ฒด ์ดํ•ฉ

 * CUBE๋Š” ROLLUP๋ณด๋‹ค ๋” ๋งŽ์€ ๊ทธ๋ฃนํ™” ์กฐํ•ฉ์˜ ์ง‘๊ณ„๋ฅผ ํฌํ•จํ•˜๋ฉฐ, ๊ฐ ์ง๋ฌด๋ณ„ ์ด๊ณ„๋„ ํฌํ•จ๋ฉ๋‹ˆ๋‹ค.

 

3๏ธโƒฃ GROUPING : ๊ทธ๋ฃนํ•‘๋œ ํ–‰์˜ ํŠน์„ฑ ํŒŒ์•…

์„ค๋ช…

GROUPING ํ•จ์ˆ˜๋Š” ๊ฒฐ๊ณผ์—์„œ NULL ๊ฐ’์ด ์‹ค์ œ ๋ฐ์ดํ„ฐ์ธ์ง€, ์•„๋‹ˆ๋ฉด ๊ทธ๋ฃนํ™”๋กœ ์ธํ•ด ์ƒ์„ฑ๋œ ๊ฒƒ์ธ์ง€๋ฅผ ๊ตฌ๋ณ„ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ง‘๊ณ„์— ์‚ฌ์šฉ๋œ ์ปฌ๋Ÿผ์ด๋ฉด 1, ์•„๋‹ˆ๋ฉด 0์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. ์ด ํ•จ์ˆ˜๋Š” ROLLUP์ด๋‚˜ CUBE์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ๋˜์–ด, ๋ณด๋‹ค ๋ช…ํ™•ํ•œ ๋ณด๊ณ ์„œ๋ฅผ ์ž‘์„ฑํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์˜ˆ์ œ

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT
DECODE(GROUPING(department_id), 1, '<<์ด๊ณ„>>', department_id) AS department_id, -- ๋ถ€์„œ ID๊ฐ€ NULL์ด๋ฉด '<<์ด๊ณ„>>'๋ฅผ ๋ฐ˜ํ™˜ํ•˜๊ณ , ๊ทธ๋ ‡์ง€ ์•Š์œผ๋ฉด ์›๋ž˜์˜ department_id ๋ฐ˜ํ™˜โœจ
DECODE(GROUPING(job_id), 1, '<์†Œ๊ณ„>', job_id) AS job_id, -- ์ง๋ฌด ID๊ฐ€ NULL์ด๋ฉด '<์†Œ๊ณ„>'๋ฅผ ๋ฐ˜ํ™˜ํ•˜๊ณ , ๊ทธ๋ ‡์ง€ ์•Š์œผ๋ฉด ์›๋ž˜์˜ job_id ๋ฐ˜ํ™˜โœจ
SUM(salary) AS total_salary
FROM employees
GROUP BY ROLLUP(department_id, job_id); -- department_id์™€ job_id์— ๋Œ€ํ•ด ROLLUP ์ ์šฉํ•˜์—ฌ ์†Œ๊ณ„ ๋ฐ ์ด๊ณ„๋ฅผ ์ƒ์„ฑ

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id job_id total_salary ๋น„๊ณ 
10 IT 11,000  
10 HR 4,000  
10 <์†Œ๊ณ„>โœจ 15,000 department_id 10์˜ ์†Œ๊ณ„
20 IT 7,000  
20 HR 5,000  
20 <์†Œ๊ณ„>โœจ 12,000 department_id 20์˜ ์†Œ๊ณ„
<<์ด๊ณ„>>โœจ IT 18,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„
<<์ด๊ณ„>>โœจ HR 9,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„
<<์ด๊ณ„>>โœจ <์†Œ๊ณ„>โœจ 27,000 ์ „์ฒด ์ดํ•ฉ

* ์ด ๊ฒฐ๊ณผ๋Š” GROUPING ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด NULL ๊ฐ’์„ ๋ช…ํ™•ํ•˜๊ฒŒ ํ‘œ์‹œํ•จ์œผ๋กœ์จ, ์–ด๋А ๋ฐ์ดํ„ฐ๊ฐ€ ์ด๊ณ„์ธ์ง€ ์‰ฝ๊ฒŒ ๊ตฌ๋ถ„ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

4๏ธโƒฃ GROUPING SETS : ํŠน์ • ๊ทธ๋ฃนํ™” ์กฐํ•ฉ ์ •์˜

์„ค๋ช…

GROUPING SETS๋Š” ์‚ฌ์šฉ์ž๊ฐ€ ์›ํ•˜๋Š” ์กฐํ•ฉ์˜ ์†Œ๊ณ„๋งŒ์„ ์„ ํƒ์ ์œผ๋กœ ์ƒ์„ฑํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ROLLUP์ด๋‚˜ CUBE๊ฐ€ ์ž๋™์œผ๋กœ ์ƒ์„ฑํ•˜๋Š” ์ง‘๊ณ„ ์กฐํ•ฉ๊ณผ ๋‹ฌ๋ฆฌ, GROUPING SETS๋Š” ํŠน์ •ํ•œ ์ง‘๊ณ„ ์กฐํ•ฉ๋งŒ ์„ ํƒ์ ์œผ๋กœ ๊ตฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์˜ˆ์ œ

โœ”๏ธ ์ฟผ๋ฆฌ๋ฌธ

SELECT department_id, job_id, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
(department_id, job_id), -- ๋ถ€์„œ๋ณ„ ๋ฐ ์ง๋ฌด๋ณ„ ์ง‘๊ณ„(๊ฒฐ๊ณผ 4ํ–‰)โœจ
(department_id), -- ๋ถ€์„œ๋ณ„ ์ง‘๊ณ„(๊ฒฐ๊ณผ 2ํ–‰)โœจ
(job_id), -- ์ง๋ฌด๋ณ„ ์ง‘๊ณ„(๊ฒฐ๊ณผ 2ํ–‰)โœจ
() -- ์ „์ฒด ์ง‘๊ณ„(๊ฒฐ๊ณผ 1ํ–‰)โœจ
);

โœ”๏ธ ์‹คํ–‰๊ฒฐ๊ณผ

department_id job_id total_salary ๋น„๊ณ 
10 IT 11,000  
10 HR 4,000  
10 NULL 15,000 department_id 10์˜ ์†Œ๊ณ„
20 IT 7,000  
20 HR 5,000  
20 NULL 12,000 department_id 20์˜ ์†Œ๊ณ„
NULL IT 18,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„
NULL HR 9,000 ์ง๋ฌด๋ณ„ ์ด๊ณ„
NULL NULL 27,000 ์ „์ฒด ์ดํ•ฉ

* GROUPING SETS๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๋ถ€์„œ๋ณ„ ์ง‘๊ณ„, ์ง๋ฌด๋ณ„ ์ง‘๊ณ„, ๊ทธ๋ฆฌ๊ณ  ์ „์ฒด ์ดํ•ฉ์„ ๊ฐ๊ฐ ๋ณ„๋„๋กœ ๊ตฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด์ฒ˜๋Ÿผ GROUPING SETS๋Š” ๋ฐ์ดํ„ฐ ์š”์•ฝ์„ ๋ณด๋‹ค ์œ ์—ฐํ•˜๊ฒŒ ์ œ์–ดํ•  ์ˆ˜ ์žˆ๊ฒŒ ํ•ด์ค๋‹ˆ๋‹ค.

 

yewon31