DAY6

Markยท2022๋…„ 5์›” 11์ผ
0
post-thumbnail

๐Ÿ‘ฉ๐Ÿปโ€๐Ÿ’ป ํŒจ์ŠคํŠธ์บ ํผ์Šค ๊ตญ๋น„์ง€์›๊ณผ์ • 'SQL๋กœ ์‹œ์ž‘ํ•˜๋Š”
๋ฐ์ดํ„ฐ ๋ถ„์„ ์ฒซ๊ฑธ์Œ' ๊ฐ•์˜ ๋‚ด์šฉ ์ •๋ฆฌ๋ฅผ ๋ชฉ์ ์œผ๋กœ ๊ฐœ์ธ ๊ณต๋ถ€์ฐจ ์ž‘์„ฑํ•œ ๊ธ€์ž…๋‹ˆ๋‹ค.
โœ๏ธ ์ˆ˜๊ฐ• ์ค‘์ธ ๊ต์œก๊ณผ์ • : https://fastcampus.co.kr/b2g_kdc_sql

1. ๋ฐ์ดํ„ฐ ๊ทธ๋ฃนํ™”ํ•˜๊ธฐ

GROUP BY

  • ์ปฌ๋Ÿผ์—์„œ ๋™์ผํ•œ ๊ฐ’์„ ๊ฐ€์ง€๋Š” ๋กœ์šฐ๋ฅผ ๊ทธ๋ฃนํ™”
  • GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
  • ๊ทธ๋ฃน ๋ณ„ ๋ฐ์ดํ„ฐ ์ง‘๊ณ„ํ•  ๋•Œ ์‚ฌ์šฉ, ์—‘์…€์˜ ํ”ผ๋ฒ— ๊ธฐ๋Šฅ๊ณผ ์œ ์‚ฌํ•˜๋‹ค.
  • GROUP BY๊ฐ€ ์“ฐ์ธ SELECT ์ ˆ์—๋Š” GROUP BY ๋Œ€์ƒ ์ปฌ๋Ÿผ๊ณผ ๊ทธ๋ฃน ํ•จ์ˆ˜๋งŒ ์‚ฌ์šฉ ๊ฐ€๋Šฅ
  • ์—ฌ๋Ÿฌ ์ปฌ๋Ÿผ์œผ๋กœ ๊ทธ๋ฃนํ™” ๊ฐ€๋Šฅ, ํ‚ค์›Œ๋“œ ๋’ค์— [์ปฌ๋Ÿผ ์ด๋ฆ„] ๋ณต์ˆ˜ ๊ฐœ ์ž…๋ ฅํ•˜๋ฉด ๋จ
  • ์ปฌ๋Ÿผ ๋ฒˆํ˜ธ๋กœ๋„ ๊ทธ๋ฃนํ™” ๊ฐ€๋Šฅ (๋‹จ, ์ปฌ๋Ÿผ ๋ฒˆํ˜ธ๋Š” SELECT์ ˆ์˜ ์ปฌ๋Ÿผ ์ด๋ฆ„์˜ ์ˆœ์„œ๋ฅผ ์˜๋ฏธ)
SELECT [GROUP BY ๋Œ€์ƒ ์ปฌ๋Ÿผ ์ด๋ฆ„], ..., [๊ทธ๋ฃน ํ•จ์ˆ˜]
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„];

ย 

2. ๊ทธ๋ฃน์— ์กฐ๊ฑด ์ฃผ๊ธฐ

HAVING

  • ๊ฐ€์ ธ์˜ฌ ๋ฐ์ดํ„ฐ ๊ทธ๋ฃน์— ์กฐ๊ฑด์„ ์ง€์ •ํ•ด์ฃผ๋Š” ํ‚ค์›Œ๋“œ
  • HAVIG ์กฐ๊ฑด์‹
  • ์กฐ๊ฑด์‹์ด ์ฐธ์ด ๋˜๋Š” ๊ทธ๋ฃน๋งŒ ์„ ํƒ
  • HAVIG ์ ˆ์˜ ์กฐ๊ฑด์‹์—์„œ๋Š” ๊ทธ๋ฃน ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•œ๋‹ค.
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., [๊ทธ๋ฃน ํ•จ์ˆ˜]
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG ์กฐ๊ฑด์‹;

ย 

3. ๋‹ค์–‘ํ•œ ๊ทธ๋ฃน ํ•จ์ˆ˜

COUNT

  • ๊ทธ๋ฃน์˜ ๊ฐ’ ์ˆ˜๋ฅผ ์„ธ๋Š” ํ•จ์ˆ˜
  • COUNT([์ปฌ๋Ÿผ ์ด๋ฆ„])
  • COUNT(1) : ํ•˜๋‚˜์˜ ๊ฐ’์„ 1๋กœ ์น˜ํ™˜
  • SELECT, HAVING ์ ˆ์—์„œ ์‚ฌ์šฉ
  • ์ง‘๊ณ„ํ•  ์ปฌ๋Ÿผ ์ด๋ฆ„์€ ๊ธฐ์ค€์ด ๋˜๋Š” ์ปฌ๋Ÿผ ์ด๋ฆ„๊ณผ ๊ฐ™์•„๋„ ๋˜๊ณ , ๋‹ฌ๋ผ๋„ ๋จ
    • ๊ธฐ์ค€์ด ๋˜๋Š” ์ปฌ๋Ÿผ ์ด๋ฆ„ : GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
  • GROUP BY๊ฐ€ ์—†๋Š” ์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉ ๊ฐ€๋Šฅ, ์ „์ฒด ๋กœ์šฐ์— ํ•จ์ˆ˜๊ฐ€ ์ ์šฉ๋จ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., COUNT([์ปฌ๋Ÿผ ์ด๋ฆ„])
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG ์กฐ๊ฑด์‹;

SUM

  • ๊ทธ๋ฃน์˜ ํ•ฉ์„ ๊ณ„์‚ฐํ•˜๋Š” ํ•จ์ˆ˜
  • SUM([์ปฌ๋Ÿผ ์ด๋ฆ„])
  • SELECT, HAVING ์ ˆ์—์„œ ์‚ฌ์šฉ
  • ์ง‘๊ณ„ํ•  ์ปฌ๋Ÿผ ์ด๋ฆ„์€ ๊ธฐ์ค€์ด ๋˜๋Š” ์ปฌ๋Ÿผ ์ด๋ฆ„๊ณผ ๊ฐ™์•„๋„ ๋˜๊ณ , ๋‹ฌ๋ผ๋„ ๋จ
  • GROUP BY๊ฐ€ ์—†๋Š” ์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉ ๊ฐ€๋Šฅ, ์ „์ฒด ๋กœ์šฐ์— ํ•จ์ˆ˜๊ฐ€ ์ ์šฉ๋จ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., SUM([์ปฌ๋Ÿผ ์ด๋ฆ„]) -- ๊ทธ๋ฃน์˜ ๊ธฐ์ค€์ด ๋˜๋Š” ์ปฌ๋Ÿผ์ด๋ฆ„์„ SELECTํ•ด์•ผ ํ•จ 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG ์กฐ๊ฑด์‹;

AVG

  • ๊ทธ๋ฃน์˜ ํ‰๊ท ์„ ๊ณ„์‚ฐํ•˜๋Š” ํ•จ์ˆ˜
  • AVG([์ปฌ๋Ÿผ ์ด๋ฆ„])
  • SELECT, HAVING ์ ˆ์—์„œ ์‚ฌ์šฉ
  • ์ง‘๊ณ„ํ•  ์ปฌ๋Ÿผ ์ด๋ฆ„์€ ๊ธฐ์ค€์ด ๋˜๋Š” ์ปฌ๋Ÿผ ์ด๋ฆ„๊ณผ ๊ฐ™์•„๋„ ๋˜๊ณ , ๋‹ฌ๋ผ๋„ ๋จ
  • GROUP BY๊ฐ€ ์—†๋Š” ์ฟผ๋ฆฌ์—์„œ ์‚ฌ์šฉ ๊ฐ€๋Šฅ, ์ „์ฒด ๋กœ์šฐ์— ํ•จ์ˆ˜๊ฐ€ ์ ์šฉ๋จ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., AVG([์ปฌ๋Ÿผ ์ด๋ฆ„]) 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG ์กฐ๊ฑด์‹;

MIN

  • ๊ทธ๋ฃน์˜ ์ตœ์†Ÿ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜
  • MIN([์ปฌ๋Ÿผ ์ด๋ฆ„])
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., MIN([์ปฌ๋Ÿผ ์ด๋ฆ„]) 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG ์กฐ๊ฑด์‹;

MAX

  • ๊ทธ๋ฃน์˜ ์ตœ๋Œ“๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜
  • MAX([์ปฌ๋Ÿผ ์ด๋ฆ„])
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ..., MAX([์ปฌ๋Ÿผ ์ด๋ฆ„]) 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
GROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
HAVIG MAX(์ปฌ๋Ÿผ ์ด๋ฆ„);

ย 

4. ์ฟผ๋ฆฌ ์‹คํ–‰ ์ˆœ์„œ

ํ‚ค์›Œ๋“œ๋ฌธ๋ฒ•์ž‘์„ฑ ์ˆœ์„œ์‹คํ–‰ ์ˆœ์„œ
SELECTSELECT [์ปฌ๋Ÿผ ์ด๋ฆ„]15
FROMFROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]21
WHEREWHERE ์กฐ๊ฑด์‹32
GROUP BYGROUP BY [์ปฌ๋Ÿผ ์ด๋ฆ„]43
HAVINGHAVING ์กฐ๊ฑด์‹54
ORDER BYORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„]66
profile
๊ฐœ์ธ ๊ณต๋ถ€ ์ •๋ฆฌ

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

comment-user-thumbnail
2022๋…„ 5์›” 17์ผ

ํ•ญ์ƒ ๋ฐœ์ „ํ•˜๋Š”๋ชจ์Šต ๋ฉ‹์ง€๋„ค์š”~

๋‹ต๊ธ€ ๋‹ฌ๊ธฐ