유용한 SQL 문법 : 스파르타 코딩클럽 엑셀보다 쉬운 SQL 4주차

1.  substring index

문자열 쪼개기

SELECT 
user_id, 
email, 
SUBSTRING_INDEX(email, '@',1) 
from users u

SELECT 
user_id, 
email, 
SUBSTRING_INDEX(email, '@',-1) #-는 뒤에서부터, @를 기준으로 나눔   
from users u

 

2. substring

문자열 일부만 출력하기

 

created at 의 1번째 부터 10번째 문자까지 뽑기

SELECT order_no , created_at , substring(created_at, 1, 10) from orders

날짜별 주문 수

SELECT  substring(created_at, 1, 10)as date, count(*) from orders 
group by date

 

3. Case

조건에 따라 데이터를 구분해서 정리

 

SELECT user_id , point, 
		(case when pu.point > 10000 then '잘 하고 있어요!'
		else '조금만 더 파이팅!' end) as message
	from point_users pu ;

SELECT a.lv, count(*) as cnt from(
	select pu.user_id, pu.point,
		(case when pu.point > 10000 then '1만 이상'  
				when pu.point > 5000 then '5 이상'  
				else '5천 미만 ' end) as lv
	from point_users pu 
)a
group by a.lv

with table1 as (
select pu.user_id, pu.point,
		(case when pu.point > 10000 then '1만 이상'  
				when pu.point > 5000 then '5 이상'  
				else '5천 미만 ' end) as lv
	from point_users pu 
) 
SELECT lv, count(*) as cnt from table1 	
group by lv

 

4.  복습 퀴즈

 

[퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다' 표시하기!

SELECT pu.user_id, pu.point, 
		(case when pu.point > (select avg(point) from point_users pu) then '잘 하고 있어요!'
		else '열심히 합시다 !' end) as message
	from point_users pu

[퀴즈] 이메일 도메인별 유저의 수 세어 보기

select SUBSTRING_INDEX(email,'@',-1)as domain , count(*) as cnt_domain  from users u
	group by SUBSTRING_INDEX(email,'@',-1)

[퀴즈] '화이팅'이 포함된 오늘의 다짐만 출력해 보기

주의!! : in을 쓸 경우 정확하게 화이팅이라고 적은 것만 출력된다.

like 와 %를 이용해 포함하는 모든 정보를 출력하자.

SELECT * from checkins c 
where comment like '%화이팅%'

[퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

with table1 as(
select ed.enrolled_id, count(*) as done_cnt from enrolleds_detail ed 
WHERE done = 1
GROUP by ed.enrolled_id
),
table2 as (SELECT ed.enrolled_id, COUNT(*) as total_cnt from enrolleds_detail ed 
group by ed.enrolled_id
)
SELECT a.enrolled_id, a.done_cnt, b.total_cnt 
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

[퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해 보기

with table1 as(
select ed.enrolled_id, count(*) as done_cnt from enrolleds_detail ed 
WHERE done = 1
GROUP by ed.enrolled_id
),
table2 as (SELECT ed.enrolled_id, COUNT(*) as total_cnt from enrolleds_detail ed 
group by ed.enrolled_id
)
SELECT a.enrolled_id, 
		a.done_cnt, 
		b.total_cnt,
		round(a.done_cnt/b.total_cnt,2)*100 as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id

 

5. 소감

 

마지막 복습 퀴즈는 어려워서 혼자 힘을 풀지 못했다. 그래도 조금 더 열심히 하면 혼자 힘으로도 할 수 있으리라 생각한다.

SQL 과정을 마쳤다. 스스로가 신기하다.