๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
Back-end ๋ฐ๋ธŒ์ฝ”์Šค/week 01 - 02 TIL (java, DB)

[TIL] 221026 - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค SQL : SELECT, GROUP BY

by young-ji 2022. 10. 26.

์‹ค๋ฆฌ์ฝ˜๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค - 3. SQL ๊ธฐ๋ณธ๋‹ค์ง€๊ธฐ(SELECT์™€ GROUP BY)

 

1. ์‹ค์Šตํ™˜๊ฒฝ

SQL Workbench๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ SQL ์‹ค์Šต

  • SQL Workbench : MySQL ์‚ฌ์ดํŠธ์—์„œ ์ œ๊ณตํ•ด์ฃผ๋Š” ๋ฌด๋ฃŒ ํด๋ผ์ด์–ธํŠธ SQL ์—๋””ํ„ฐ
    1. ์„ค์น˜
    2. connection ๋งŒ๋“ค๊ธฐ (์ž‘์—…์ด ์ง„ํ–‰๋  ์„œ๋ฒ„ - rds)
  • ๋“ค์–ด๊ฐ€๊ธฐ์ „

SHOW DATABASES;

USE prod;

SHOW TABLES;

2. SELECT

ํ…Œ์ด๋ธ”์—์„œ ๋ ˆ์ฝ”๋“œ๋ฅผ ์ฝ์–ด์˜ค๋Š”๋ฐ ์‚ฌ์šฉ

  • CASE WHEN : ํ•„๋“œ ๊ฐ’์˜ ๋ณ€ํ™˜์„ ์œ„ํ•ด ์‚ฌ์šฉ ๊ฐ€๋Šฅ
    • CASE WHEN ์กฐ๊ฑด THEN ์ฐธ์ผ ๋•Œ ๊ฐ’ ELSE ๊ฑฐ์ง“์ผ๋•Œ ๊ฐ’ END ํ•„๋“œ์ด๋ฆ„
  • NULL : null์— ์‚ฌ์น™์—ฐ์‚ฐ์„ ํ•˜๋ฉด null
  • IN / LIKE : ๋ฌธ์ž์—ด ๋งค์นญ / BETWEEN
  • STRING functions
    • LEFT, REPLACE, UPPER, LOWER, SUBSTRING, CONCAT, RPAD ๋“ฑ
  • ORDER BY : null ๊ฐ’์˜ ์ˆœ์„œ๋Š” ์˜ค๋ฅธ์ฐจ์ˆœ ๊ธฐ์ค€ ๋งจ ์ฒ˜์Œ
  • ํƒ€์ž… ๋ณ€ํ™˜
    • STR_TO_DATE, DATE_FORMAT
    • cast ํ•จ์ˆ˜ : cast, convert

data ํ•จ์ˆ˜

์‹ค์Šต

-- ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์ˆ˜
select count(1)
from session;

-- channel_id๊ฐ€ 5์ธ ๋ ˆ์ฝ”๋“œ ์ˆ˜
select count(1)
from session
where channel_id = 5;

-- case when
select channel_id, case 
		when channel_id in (1,5,6) then 'Social-Media'
		when channel_id in (2,4) then 'Search-Engine'
		else 'Something-Else'
end channel_type
from session;

-- COUNT ํ•จ์ˆ˜
select count(1) from count_test; -- ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์ˆ˜ 
select count(0) from count_test; -- ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์ˆ˜
select count(NULL) from count_test; -- 0
select count(value) from count_test; -- value๊ฐ€ NULL์ด ์•„๋‹Œ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์ˆ˜
select count(DISTINCT value) from count_test; -- value๊ฐ€ NULL์ด ์•„๋‹Œ ๊ฐ’ ์ค‘ ์œ ๋‹ˆํฌ ๊ฐ’

-- G๊ฐ€ ๋“ค์–ด๊ฐ„ ์ฑ„๋„ ๋ ˆ์ฝ”๋“œ์˜ ์ˆ˜
select count(1)
from chennel
where chennel like '%G%';

-- casting ํ•จ์ˆ˜
select cast('100.0' as float), covert('100.0', float);

 

 

3. GROUP

SELECT์‹œ ์‹คํ–‰์‹œ ํ…Œ์ด๋ธ”์˜ ๋ ˆ์ฝ”๋“œ๋ฅผ ๊ตฌ๋ฃนํ•‘ํ•˜์—ฌ ๊ทธ๋ฃน๋ณ„๋กœ ๋‹ค์–‘ํ•œ ์ •๋ณด๋ฅผ ๊ณ„์‚ฐ

  1. ๋จผ์ € ๊ทธ๋ฃนํ•‘ ํ•  ํ•„๋“œ๋ฅผ ๊ฒฐ์ • (ํ•˜๋‚˜ ์ด์ƒ์˜ ํ•„๋“œ)
  2. GROUP BY ๋กœ ์ง€์ •
  3. ๋‹ค์Œ ๊ทธ๋ฃน๋ณ„๋กœ ๊ณ„์‚ฐํ•  ๋‚ด์šฉ์„ ๊ฒฐ์ • : Aggregate ํ•จ์ˆ˜
  4. COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT

์‹ค์Šต

-- ์›”๋ณ„ ์„ธ์…˜์ˆ˜ ๊ณ„์‚ฐ
select 
	left(created,7) as mon, -- date์—์„œ ์›”๋งŒ ์ถ”์ถœ
	count(1) as session_count
from session
group by 1 -- select์—์„œ ์ฒซ๋ฒˆ์งธ ํ•„๋“œ = left(created,7)
order by 1;

-- ๊ฐ€์žฅ ๋งŽ์ด ์‚ฌ์šฉ๋˜๋Š” ์ฑ„๋„
select channel_id,count(1) as session_count
from session
group by 1
limit 1;

-- ์›”๋ณ„ ์œ ๋‹ˆํฌํ•œ ์‚ฌ์šฉ์ž ์ˆ˜
select 
	left(created,7) as mon, -- date์—์„œ ์›”๋งŒ ์ถ”์ถœ
	count(distinct user_id) as user_count
from session
group by 1 -- select์—์„œ ์ฒซ๋ฒˆ์งธ ํ•„๋“œ = left(created,7)
order by 1;

-- ์›”๋ณ„ ์ฑ„๋„๋ณ„ ์œ ๋‹ˆํฌํ•œ ์‚ฌ์šฉ์ž ์ˆ˜
select 
	left(s.created,7) as mon,
	c.channel,
	count(distinct user_id) as mau
from session s
join chennel c on c.id = s.chennel_id
group by 1,2
order by 1 desc, 2;

 

 

์ถœ์ฒ˜ - ํ•œ๊ธฐ์šฉ ๊ฐ•์‚ฌ๋‹˜ : ์‹ค๋ฆฌ์ฝ˜๋ฐธ๋ฆฌ์—์„œ ๋‚ ์•„์˜จ ๋ฐ์ดํ„ฐ ๋ฒ ์ด์Šค

๋Œ“๊ธ€