스파르타 코딩 클럽 SQL 개발일지 4주차

 02.원하는 데이터를 더 쉽게: Subquery

 

쿼리안에 또 다른 쿼리를 넣는 것

select u.user_id, u.name, u.email from users u
inner join orders o on u.user_id = o.user_id
where o.payment_method = 'kakaopay'

원래는 위와 같이 쓰는 것을 

아래와 같이 서브쿼리를 이용해서 쓸 수 있다. 

select user_id, name, email from users
where user_id in (
	select user_id from orders
	where payment_method = 'kakaopay'
)

서브쿼리에서는 줄을 잘 세워줘서 그냥 쿼리와 구별해줘야한다. 

 

순서: 서브쿼리 먼저 하고 점점 바깥에 있는 쿼리를 써간다.

 

03. Subquery 유형

 

where 에 들어간 것

위에 있는 처음 예시가 where  절 안에 들어간 subquery

 

Select 에 들어간 것

select c.checkin_id, 
	   c.user_id, 
	   c.likes,
	   (
	     select avg(likes) from checkins
	     where user_id = c.user_id
	   ) as avg_likes_user
from checkins c

select가 될 때 마다 subquery가 하나 하나 실행되게 된다. 

 

From 에 들어간 것 (제일 많이 사용되는 유형)

SELECT pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_likes from checkins c
	group by c.user_id
) a on a.user_id = pu.user_id

내가 새로 만든 select query가 마치 원래 있었던 테이블인 것 처럼 join 시켜주었다.

 

원하는 테이블이 있을 때 할 줄 아는 것 따로 따로 만들어놓고 연결하는 것하면 만들 수 있다.

 

04. Subquery 연습

where 절에 들어가는 Subquery

[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu
where point > (select avg(point) from point_users pu)

[연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu
where point > (
	select avg(point) from point_users pu
    where user_id in ( select user_id from users where name = '이**')
                 )

 

select * from point_users pu
where point > (
	select avg(point) from users u
	inner join point_users pu on pu.user_id = u.user_id
	where name like '이%'
	group by name
				)

group by를 넣을 필요없이 avg만 넣으면 된다.

 

Select 절에 들어가는 Subquery 연습해보기

[연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select c1.checkin_id, 
	   c1.course_id, 
	   c1.user_id , 
	   c1.likes, 
	   a.avg_likes  
from checkins c1
inner join (
	select c.course_id, 
		   round(avg(likes),1) as avg_likes 
	from checkins c
	group by c.course_id
) a on a.course_id= c1.course_id

 

[연습] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

 

select c1.checkin_id, 
	   a.title, 
       c1.user_id, 
       c1.likes, 
       a.avg_likes 
from checkins c1
inner join (
	select c.course_id ,
    	   c2.title, 
           round(avg(likes),2) as avg_likes 
    from checkins c
	inner join courses c2 on c.course_id = c2.course_id
	group by c.course_id
    		) a on c1.course_id = a.course_id

완전 다 틀림, selcect 절을 이용해야하는데 from에다 subquery 넣음.

 

정답은

select c1.checkin_id,
	   c1.course_id,
	   c1.user_id, 
	   c1.likes,
	  (select round(avg(likes),1) as course_avg from checkins c
		where c.course_id = c1.course_id)
from checkins c1

서브쿼리에서 코스아이디가 더 큰 테이블의 각각의 코스 아이디에 해당한다고 넣어줬다 .여기에서 group by를 해주지 않아도 그것에 해당하는 것들 끼리 모여서 평균을 내준다.where이 들어갔기 때문이다.

select c1.checkin_id,
	   c2.title,
	   c1.user_id , 
	   c1.likes,
	  (select round(avg(likes),1) from checkins c
where c.course_id = c1.course_id) as course_avg
from checkins c1
inner join courses c2 on c2.course_id= c1.course_id

c2를 확인하려면  select 뒤에 c2.*라고 하나 붙여서 확인해볼 수 있다. 

 

05. subquery 연습해보기 (from, inner join)

From 절에 들어가는 Subquery 연습해보기

[준비1] course_id별 유저의 체크인 개수를 구해보기! 

select c.course_id, 
	   count(DISTINCT(c.user_id)) as cnt_checkins
from checkins c
group by c.course_id

 

distinct 쓰는 법 까먹어서  1강 자료보고옴

course_id로 group by 하고

얼마나 유저들이 체크인 했는지를 보려면 그냥 user_id를 count 해주면 안되나??

 

아마도 질문의 의도는 유저들이 여러개 남긴 체크인 개수를 다 세주라는게 아니라, 유저를 세주라는 건가보다.

질문을 그냥 course_id별 유저 수를 구해보기라고 하면 안되나? 나만 그렇게 생각하남?

 

[준비2] course_id별 인원을 구해보기!

select o.course_id,
	   count(o.user_id) as cnt_total 
from orders o
group by o.course_id

[진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기

select a.course_id, a.cnt_checkins, b.cnt_total from (
				select c.course_id, 
	   				   count(DISTINCT(c.user_id)) as cnt_checkins
				from checkins c
				group by c.course_id
			   ) as a 
inner join (
				select o.course_id,
	   				   count(*) as cnt_total 
				from orders o
				group by o.course_id
			) as b on a.course_id = b.course_id

내가 처음 했던 방식이 아니지만 두번째 하면서 강사님 방식으로 써보았다.

 

[한 걸음 더] 퍼센트를 나타내기

select a.course_id, 
	   a.cnt_checkins, 
	   b.cnt_total,
	  a.cnt_checkins/b.cnt_total as ratio
from (
				select c.course_id, 
	   				   count(DISTINCT(c.user_id)) as cnt_checkins
				from checkins c
				group by c.course_id
			   ) as a 
inner join (
				select o.course_id,
	   				   count(*) as cnt_total 
				from orders o
				group by o.course_id
			) as b on a.course_id = b.course_id

 

[반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

내가 쓴 코드

select c3.title,
	   count(DISTINCT(c.user_id)) as cnt_checkins,
	   a.cnt_total,
	   count(DISTINCT(c.user_id))/a.cnt_total as ratio
from checkins c
inner join (select o.course_id,
			 count(o.user_id) as cnt_total 
	   		 from orders o
			 group by o.course_id) a on a.course_id = c.course_id
inner join courses c3 on c.course_id =c3.course_id
group by c.course_id

정답코드

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

나는 처음 체크인 한 유저수를 세었던 테이블에 inner join 해주었다. 

그러나 정답코드는 체크인 한 유저수 테이블을  from 뒤에 서브쿼리로 넣어주어 a라고 칭해주었다. 

from subquery 배울 때 처음 테이블 부터  서브쿼리를 넣어준 적이 없어서 몰랐는데, 이렇게도 되는건가보다 

 

06. with절 연습하기

with table1 as(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
)


select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a
inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

with는 항상 맨 위에 넣어준다. 

와! with 기능 짱짱맨 

subquery 넣고나서부터 너무 복잡해져서 눈알이 핑핑돌고 한눈에 안 들어왔었는데, with로 다른 이름으로 대체에서 그 자리에 넣어주니까 한눈에 들어와서 좋다 

 

근데 그냥 실행시켜주면 안 되고, 전체 선택해서 실행해야 오류없이 같은 결과가 나온다.

 

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

 

문자열 쪼개보기

 SUBSTRING_INDEX를 사용하면 된다.

이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

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

 앞의 아이디만 나옴 (숫자 1을 넣음)

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

뒤의 이메일 도메인만 나옴(숫자 -1을 넣음)

 

문자열 일부만 출력하기

 

2020-07-13 22:30:18 이렇게 생긴  created_at을 잘라줘야함

SUBSTRING(필드명, 시작 문자 자리, 끝날 문자 자리)

날짜만 출력

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

시간만 출력

select SUBSTRING(created_at,11,9)  from orders o​

 

08. 실전에서 유용한 SQL 문법 (Case)

case when ~ then

else ~ end

 

10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!',  낮으면 '조금 더 파이팅!' 라고 표시

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

case 문으로 통계내는 법

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 a.lv,count(*) as cnt from table1 a
group by a.lv

 1.   select 안에 case when 을 여러개 만들어서 숫자별로 다른 결과가 뜨도록 하고,  lv이라고 이름 지어준다

2.  위의 테이블을 subquery로 하고 from 안에 넣어준다. 그러면 간단하게 새 테이블을 이용한 select 쿼리가 만들어진다.

3. group by lv 하고 select에 lv과 그 수를 센것을 넣는다.

4. with으로 subquery를 table1으로 만들어주고 보기 원래 자리에 넣어주어 보기 간단하게 만든다.

 

09. SQL 문법 복습. 또 복습! (초급)

 

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

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

정답을 보니 서브쿼리의 poin_users의 대표 이름은 다른 것으로 지어 원래 쿼리와 구별해주었다. 

 

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

 

내 쿼리

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

정답

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

정답에서는  domain을  찾은것을  subquery로 하고 그걸 from에 넣고나서  domain을  group by하고

domain 과 그 카운트를 필드로 넣어주었다.  

 

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

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

 

 

10. SQL 문법 복습. 또 복습! (중급)

 

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

select a.enrolled_id, b.done_cnt, a.total_cnt 
from 
(
	select ed.enrolled_id, count(ed.current_order) as total_cnt from enrolleds_detail ed
	group by ed.enrolled_id 
) a 
inner join 
(
	select ed1.enrolled_id, count(*) as done_cnt from enrolleds_detail ed1 
	where done=1
	group by ed1.enrolled_id
) b 
on a.enrolled_id=b.enrolled_id

정답과 비교해보니  전체 강의 수를 current_order을 세야한다고 생각해서

나는 그걸 넣었는데, current_order은 몇번째 주문인지를 보여주는 것 같고,

실상 아이디 당 데이터 갯수를 세주면 되는거라  *이 들어가면 된다.

근데 어쨌거나  current_order의 데이터 갯수를 넣어도 결과는 같다.

 

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

 

select a.enrolled_id, b.done_cnt, a.total_cnt , round(b.done_cnt/a.total_cnt,2) as ratio
from 
(
	select ed.enrolled_id, count(ed.current_order) as total_cnt from enrolleds_detail ed
	group by ed.enrolled_id 
) a 
inner join 
(
	select ed1.enrolled_id, count(*) as done_cnt from enrolleds_detail ed1 
	where done=1
	group by ed1.enrolled_id
) b 
on a.enrolled_id=b.enrolled_id

with 를 쓰면 

with table1 as 
(
	select ed.enrolled_id, count(ed.current_order) as total_cnt from enrolleds_detail ed
	group by ed.enrolled_id 
), table2 as 
(
	select ed1.enrolled_id, count(*) as done_cnt from enrolleds_detail ed1 
	where done=1
	group by ed1.enrolled_id
)

select a.enrolled_id, b.done_cnt, a.total_cnt , round(b.done_cnt/a.total_cnt,2) as ratio
from table1 a 
inner join table2 b 
on a.enrolled_id=b.enrolled_id

21) [함께] 그러나, 더 간단하게 만들 수 있지 않을까!

select ed.enrolled_id, 
	   sum(ed.done) as done_cnt, 
	   count(*) as total_cnt, 
	   round(sum(ed.done)/count(*),2) as ratio  
from enrolleds_detail ed
group by ed.enrolled_id

sum은 해당 필드의 숫자 데이터들을 다 더해주는 query인데 done 필드는 모두 1이기 때문에  sum을 통해 더해준 숫자가 

done=1인 데이터를 count 해주는 것과 같다. 

 

이렇게도 간단하게 할 수 있었던 거였다니, 퀴즈가 힌트주는대로 해서 다른 방법은 생각도 안 해봤다. 충격 

 

11. 마지막 숙제는, 문제를 푸는 것이 아니라, 문법을 정리해보는 것이에요!