SQL 기초 5일차

전윤환·2022년 3월 11일
0

SQL왕초보

목록 보기
6/6

SQL 기초 5일차
#스파르타코딩클럽, #내일배움단
학습일자: 2022/03/11
재수강일: 2022/05/01
강의: 엑셀보다 쉬운 SQL
진도: 4-5 ~ 4-7

=====================

실전에서 유용한 SQL 문법 (문자열)

select user_id, email from users

email에서 메일주소를 뺀 아이디만 보기

select user_id, email, substring_index(email, '@', 1) from users

메일주소만 보고싶으면 1 대신 -1

파이썬이나 자바스크립트의 인덱싱이랑은 조금 다르네용, 굳이 따지자면 엑셀의 left 함수 같은 느낌?

왜냐면 1 대신 2를 넣는다고 해서 2번째 위치의 문자열만 불러오는게 아님

=====================

문자열 일부만 출력하기

select order_no, created_at, substring(created_at, 1, 10) as date from orders

일별로 몇개씩 주문했는지 알려면?

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

엑셀의 mid 함수와 같은 조건값

=====================

case문 = 경우에 따라 원하는 값을 출력해주는 문법

select user_id, point,
	   (case when point > 10000 then '잘하고 있어요!'
	   else '좀만 화이팅!' end) as msg
from point_users pu 

배운걸 토대로 0~5천, 5천~1만, 1만 이상 통계 내기

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

with로 깔끔하게 정리

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

=====================

4-6 퀴즈풀기

점점 어려워지는 SQL... 철저한 복습이 필요할 때! 오늘 나 잠 안자!

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

select avg(point) as avg_point from point_users pu2

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

case when A then a else b end, 그리고 서브쿼리를 활용해서 해결

평균포인트 값 = 5380.8339

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

select substring_index(email, "@", -1)as use_mail, count(*) from users u
group by use_mail

아까 배운 substring_index를 활용해서 처리했다.

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

select comment from checkins c 
where comment like '%화이팅%'

서브스트링이니 뭐니보다 like가 문제를 해결해줄 때가 많다

 

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

퀴즈4의 1단계

테이블을 두개 만든다. 전체강의와 수강한강의 각각 하나씩
1) 전체강의수 파악
select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
group by enrolled_id

2) 수강강의수 파악
select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
where done = 1
group by enrolled_id 

퀴즈4의 2단계

inner join할 틀을 만든다

select * from (

)
inner join (

)
on 

1과 2에 아까 만든 테이블 하나씩 넣고 3에 연결키값 넣어줌

select * from (
				select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
				group by enrolled_id
				) a
inner join (
				select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
				where done = 1
				group by enrolled_id 
				) b
on a.enrolled_id = b.enrolled_id

퀴즈4의 3단계

select * -> 원하는 추출값으로 변경

select a.enrolled_id, 전체강의수, 수강한강의수, round(수강한강의수/전체강의수, 2) as ratio
	from (
		select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
		group by enrolled_id
		) a
inner join (
			select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
			where done = 1
			group by enrolled_id 
			) b
	on a.enrolled_id = b.enrolled_id

퀴즈4의 4단계

with문으로 쿼리를 깔끔하게 정리하기
with table1 as(
		select enrolled_id, count(*)as 전체강의수 from enrolleds_detail
		group by enrolled_id
), table2 as (
			select enrolled_id, count(*) as 수강한강의수 from enrolleds_detail
			where done = 1
			group by enrolled_id 
)
select a.enrolled_id, 전체강의수, 수강한강의수, round(수강한강의수/전체강의수, 2) as ratio
	from table1 a
 inner join table2 b
	on a.enrolled_id = b.enrolled_id

퀴즈4의 다른 풀이

select enrolled_id,
	   count(enrolled_id) as 총강의,
	   sum(done) as 수강한강의,
	   round(sum(done)/count(*),2) as 진도율
  from enrolleds_detail
group by enrolled_id

=====================

4-7 끝, 숙제 설명

드디어 마지막 강!

개발일지에 쿼리문법을 정리해서 올리기

끝!

profile
코딩 연습장. 발전하고 싶습니다. 모든 방향에서의 비판 부탁드립니다.

0개의 댓글