day4

์ƒ์€๐Ÿ‘ธยท2023๋…„ 11์›” 30์ผ
0

๋šœ๋ฒ…๋šœ๋ฒ… ์„ธ๋ฒˆ์งธ

๋ชฉ๋ก ๋ณด๊ธฐ
4/11
post-thumbnail

๐Ÿ“– CASE ๋ฌธ

CASE ์ปฌ๋Ÿผ
WHEN ๊ฐ’ THEN ๊ฒฐ๊ณผ(์ถœ๋ ฅ)
WHEN ๊ฐ’ THEN ๊ฒฐ๊ณผ(์ถœ๋ ฅ)
WHEN ๊ฐ’ THEN ๊ฒฐ๊ณผ(์ถœ๋ ฅ)
ELSE ๊ฒฐ๊ณผ(์ถœ๋ ฅ)
END

CASE
WHEN ์ปฌ๋Ÿผ >= ๊ฐ’ THEN ์ถœ๋ ฅ
WHEN ์ปฌ๋Ÿผ >= ๊ฐ’ THEN ์ถœ๋ ฅ
WHEN ์ปฌ๋Ÿผ >= ๊ฐ’ THEN ์ถœ๋ ฅ
ELSE ์ถœ๋ ฅ
END

๐ŸŸ

SELECT 
    name,
    jumin,
    birthday,
    SUBSTR(jumin, 3, 2) ์ฃผ๋ฏผ์—์„œ์›”,
    TO_CHAR(birthday, 'MM') ๋‚ ์งœ์—์„œ์›”,
    CASE 
        WHEN SUBSTR(jumin, 3, 2) IN('01', '02', '03') THEN '1๋ถ„๊ธฐ'
        WHEN SUBSTR(jumin, 3, 2) IN('04', '05', '06') THEN '2๋ถ„๊ธฐ'
        WHEN SUBSTR(jumin, 3, 2) IN('07', '08', '09') THEN '3๋ถ„๊ธฐ'
        WHEN SUBSTR(jumin, 3, 2) IN('10', '11', '12') THEN '4๋ถ„๊ธฐ'
    END ๋ถ„๊ธฐ,
    
    CASE 
        WHEN TO_CHAR(birthday, 'MM') BETWEEN '01' AND '03' THEN '1๋ถ„๊ธฐ'
        WHEN TO_CHAR(birthday, 'MM') IN('04', '05', '06') THEN '2๋ถ„๊ธฐ'
        WHEN TO_CHAR(birthday, 'MM') BETWEEN '07' AND '09' THEN '3๋ถ„๊ธฐ'
        ELSE '4๋ถ„๊ธฐ'
    END ๋ถ„๊ธฐ2
    
--  DECODE(SUBSTR(jumin, 3, 2), '01', '1๋ถ„๊ธฐ', '02', '1๋ถ„๊ธฐ', '03', '1๋ถ„๊ธฐ', '04', '2๋ถ„๊ธฐ', ....)
FROM student;

๐Ÿ’ป

๐Ÿ“– ๋‹ค์ค‘(๋ณต์ˆ˜)ํ–‰ ํ•จ์ˆ˜

(1) ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜๋Š” ๊ฒฐ๊ณผ ํ–‰ ์ˆ˜๊ฐ€ ์›๋ž˜ ๋ฐ์ดํ„ฐ ํ–‰ ๊ฐฏ์ˆ˜์™€ ๋‹ค๋ฅด๋‹ค. -> 1๊ฐœ๋‹ค

(2) ๋‹จ์ผํ–‰ ํ•จ์ˆ˜๋Š” ํ–‰ ๊ฐฏ์ˆ˜๊ฐ€ ๋™์ผํ•˜๋‹ค -> ์—ฌ๋Ÿฌ๊ฐœ๋‹ค

(3) ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜์™€ ๋‹จ์ผํ–‰ ํ•จ์ˆ˜๋ฅผ ๋™์‹œ์— ์‚ฌ์šฉํ•˜๋ฉด์„œ ์กฐํšŒํ•˜์ง€ ๋ชปํ•œ๋‹ค.

(4) ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜ ๊ฒฐ๊ณผ์™€ ์ผ๋ฐ˜ ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ๋ฅผ ํ•จ๊ป˜ ํ‘œ์‹œํ•˜์ง€ ๋ชปํ•œ๋‹ค. (+ํŠน์ • ์ถ”๊ฐ€ ์ฟผ๋ฆฌ๋ฅผ ํ†ตํ•ด์„œ ๊ฐ€๋Šฅ)

๐Ÿ’Ž ๊ทธ๋ฃน ์ฒ˜๋ฆฌ

COUNT() : ๊ฐฏ์ˆ˜ ์„ธ๊ธฐ
SUM() : ์ดํ•ฉ
AVG() : ํ‰๊ท ๊ฐ’
MAX() : ์ตœ๋Œ€๊ฐ’
MIN() : ์ตœ์†Œ๊ฐ’
STDDEV() : ํ‘œ์ค€ํŽธ์ฐจ
VARIANCE() : ๋ถ„์‚ฐ

SELECT COUNT(*) --์ „์ฒด ํ•™์ƒ ์ˆ˜ (์ „์ฒด ํ–‰ ๊ฐฏ์ˆ˜)
FROM student;

๐Ÿ’ป

SELECT name, tel, SUBSTR(tel, 1, 3), COUNT(tel) --๋‹ค์ค‘ํ–‰ํ•จ์ˆ˜๋ž‘ ๋‹จ์ผํ–‰ํ•จ์ˆ˜๋Š” ๋™์‹œ์— ์„ž์—ฌ์„œ ์‚ฌ์šฉX 
FROM student;  
SELECT MAX(sal) ์ตœ๋Œ€๊ธ‰์—ฌ, MIN(sal) ์ตœ์†Œ๊ธ‰์—ฌ, AVG(sal) ํ‰๊ท ๊ธ‰์—ฌ
FROM emp;

๐Ÿ’ป

๐Ÿ’Ž ๊ทธ๋ฃน ๊ธฐ์ค€ ์ง€์ •ํ•˜๊ธฐ

GROUP BY ์ ˆ : ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜ ์ฒ˜๋ฆฌ์‹œ์— ํŠน์ • ๊ทธ๋ฃน ๊ธฐ์ค€ ์ง€์ •
GROUP BY ์— ์ง€์ •๋˜๋ฉด ์ปฌ๋Ÿผ ์ถœ๋ ฅ์ด ๊ฐ€๋Šฅ

GROUP BY ์ฒ˜๋ฆฌ ์ดํ›„, ์กฐ๊ฑด ์ถ”๊ฐ€ํ•˜๊ธฐ
-> HAVING ์ ˆ ์‚ฌ์šฉ

ROLLUP() : ์ž๋™ ์†Œ๊ณ„, ํ•ฉ๊ณ„ ๊ณ„์‚ฐํ•ด์ฃผ๋Š” ํ•จ์ˆ˜ (๊ทธ๋ฃน์ฒ˜๋ฆฌํ•œ ํ•จ์ˆ˜๋“ค๋งˆ๋‹ค ์†Œ๊ณ„๋‚˜์˜ด!)
GROUP BY ํ•œ ์ปฌ๋Ÿผ์— ์จ์ฃผ๋Š”๊ฒƒ! ex. ROLLUP(p_date, p_code)

๐ŸŸ ๊ฐ ์ง์—…๋ณ„ ํ‰๊ท  ๊ธ‰์—ฌ ๊ตฌํ•ด๋ผ

SELECT job, AVG(sal)
FROM emp
GROUP BY job;

=> ๋จผ์ € ์ง์—…๋ณ„๋กœ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์™€์•ผ ํ•˜๋‹ˆ๊นŒ ์ง์—…์œผ๋กœ ๊ทธ๋ฃนํ•‘์‹œํ‚ค๊ณ ! AVG(๊ธ‰์—ฌ) : ํ‰๊ท  ๊ธ‰์—ฌ ์ถœ๋ ฅ๋˜๊ฒŒ๋”!

=> ์›๋ž˜๋Š” job, AVG(sal) ์€ ๋‹จ์ผํ–‰ ํ•จ์ˆ˜, ๋‹ค์ค‘ํ–‰ ํ•จ์ˆ˜๋ผ์„œ ๊ฐ™์ด ๋ชป์“ฐ์ด๋Š”๋ฐ GROUP BY ์—์„œ ์“ฐ์ธ job์€ ์“ฐ์ผ ์ˆ˜ ์žˆ๋‹ค!!

๐Ÿ’ป

๐ŸŒญ 1. ๊ธ‰์—ฌ๊ฐ€ 1500์ด์ƒ์ธ ๊ธˆ์•ก์— ๋Œ€ํ•ด์„œ ๊ฐ ์ง์—…๋ณ„ ํ‰๊ท  ๊ธ‰์—ฌ

SELECT job, AVG(sal)
FROM emp
WHERE sal >= 1500
GROUP BY job;

=> ๊ฐ ์ง์—…๋ณ„ ์ด๋ผ๊ณ  ํ–ˆ์œผ๋‹ˆ๊นŒ ์ง์—…์„ ๊ทธ๋ฃนํ•‘ํ•˜๊ณ 
=> ํ‰๊ท ๊ธ‰์—ฌ ๊ตฌํ•˜๋ผ๊ณ  ํ–ˆ์œผ๋‹ˆ๊นŒ AVG(sal)

๐Ÿ’ป

๐Ÿบ 2. ์ง์—…๋ณ„ ํ‰๊ท ๊ธ‰์—ฌ๊ฐ€ 1500 ์ด์ƒ์ธ ๊ฒฝ์šฐ์˜ ๊ธˆ์•ก๋งŒ

SELECT job, AVG(sal)
FROM emp
--WHERE AVG(sal) >= 1500 ๋ถˆ๊ฐ€๋Šฅ ---> ํ‰๊ท ๊ธ‰์—ฌ๊ฐ€ ๋‚˜์˜ค๊ธฐ๋„ ์ „์— ์กฐ๊ฑด์„ ๊ฑฐ๋Š”๊ฑด ๋ง์ด ์•ˆ๋จ!
GROUP BY job
HAVING AVG(sal) >= 1500;

=> ํ‰๊ท ๊ธ‰์—ฌ๊ฐ€ 1500์ด์ƒ์ธ ๊ฒฝ์šฐ
=> ํ‰๊ท ๊ธ‰์—ฌ๋ฅผ ๊ตฌํ•˜๊ธฐ๋„ ์ „์— ํ‰๊ท ๊ธ‰์—ฌ๋ฅผ ๊ฐ€์ง€๊ณ  ์กฐ๊ฑด์„ ๊ฑฐ๋Š”๊ฑด ๋ง์ด ์•ˆ๋จ! ๊ทธ๋ž˜์„œ GROUP BY ์ดํ›„์— HAVING ์กฐ๊ฑด์ ˆ์„ ๊ฑธ์–ด์คŒ

๐Ÿ’ป

profile
๋’ค์ฃฝ๋ฐ•์ฃฝ ๋ฒจ๋กœ๊ทธ

0๊ฐœ์˜ ๋Œ“๊ธ€