[내일배움단] 엑셀보다 쉬운 SQL - 4주차 완강! / 22년 1월 27일

[수업목표]

1. Subquery(서브쿼리)의 사용 방법을 배워본다
2. 실전에서 유용한 SQL 문법을 더 배워본다
3. SQL을 사용하여 실전과 같은 데이터분석을 진행해본다

 

[배운 것]

• where, select, from절을 적절하게 활용할 것

일단 쿼리 서브쿼리 이거 개념부터 잡아야 될 거 같은데..

그니까 일단 로우데이터가 있고 그 중에서 특정 데이터만 뽑아서 보고 싶다 이거 아님.. 이 로우 데이터 필터링하는 걸 쿼리라고 하는데 얘를 좀 더 세밀하게 필터링 할 수 있게 쿼리 안에 숨어있는 애들이 서브쿼리..

 

select * from users u

where u.user_id in (select o.user_id from orders o

where o.payment_method = 'kakaopay');

 

그니까 쿼리 = 카카오페이로 결제한 유저를 출력해줘

유저 명단을 가져와야 되는데 결제수단이 오더에만 있자네...

어차피 유저나 오더나 회원명단 둘 다 있는데 오더에서 가져와 = 이게 서브쿼리

실행 순서는 서브쿼리먼저, 그 다음이 select...

 

평균 구하는 쿼리 구한 후에 그 후에 평균보다 높은 사람들 셀렉하는 쿼리 짜보랬는데

select point from point_users pu
where > (select round(avg(pu.point),2) from point_users

 

계속 이거만 쓰다가 졸려서 잠와가지고 그냥 해설 영상 바로 봄...

 

select * from point_users pu
where point > (
select round(avg(point),2) from point_users
)

 

쉽군요... 내가 너무 머저리 같애영......

이따 자고 일어나서 해야겠어 집중이 안도ㅑ

 

~ 그리고 자고 일어나서 한다는 것이 5일이 지났다 ~

 

• 깰끔한 정리는 with절과 상의하세용

with절로 깔끔하게.. with절은 제일 위에. 정리할 때 유용하다...

 

이하 삽질의 흔적

 

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

이메일을 @로 쪼개는데 @를 기준으로 그 앞에 있는 거 보여줘
그러니까 이메일 아이디만 쪼개지는 거임


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

이건 도메인이 나오게 됨



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

created_at은 심지어 생략해도 됨. 서브스트링 옆에 1,10은 시작포인트, 그 후부터 몇자인지~ 뜻임





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





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 * 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       

서브쿼리화



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


with 절 이용해서 서브쿼리화한거 쟤를 위에 table1로 정의하고 앞으로 쟤를 테이블1로 갖다 쓰겠다고 해서 깔끔하게 정리 가능함.





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



select pu.point_user_id, round(avg(pu.point),2), - 이렇게 해서 평균 구함
   case when pu.point > 10000 then '잘 하고 있어요!'
   else '열심히 합시다!' end as msg
   from point_users pu




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




select SUBSTRING_INDEX(email,'@',-1) as domain from users

이렇게 짠 다음에 얘를 서브쿼리화

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


이게 최종 코드



강사님꺼

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




select count(*) as cnt from checkins c
where comment like '화이팅';


select count(*) as cnt from checkins c
where comment like '%파이팅';


너무 적게 나와서 이상하다고 생각하고 있음

정답은

select count(*) as cnt from checkins c
where comment like '%화이팅%';

끝에 % 이걸 붙여줘야 했음




select count(ed.enrolled_id) as total_cnt from enrolleds_detail ed
group by enrolled_id

전체 강의 수, 얘를 서브쿼리화




select done_cnt, count(ed.enrolled_id) as total_cnt (
select count(ed.done) as done_cnt
from enrolleds_detail ed
)

자꾸 오류 뜨는 중



select ed.enrolled_id, count(ed.done) as done_cnt from enrolleds_detail ed
where ed.done = '1'
group by ed.enrolled_id

일단 이렇게 해서 수강완료 갯수 세기 완성





select ed.enrolled_id, count(ed.done) as total_cnt from (
select count(ed.done) as done_cnt
from enrolleds_detail ed
where ed.done = '1'
)
group by ed.enrolled_id

틀리고 있어



with table1 as (
select ed.enrolled_id, count(ed.done) as done_cnt from enrolleds_detail ed
where ed.done = '1'
group by ed.enrolled_id)
select ed.enrolled_id, a.done_cnt, count(ed.done) as total_cnt from table1 a
group by a.enrolled_id


또 틀림


with table1 as (
select ed.enrolled_id, count(ed.done) as done_cnt, from enrolleds_detail ed
where ed.done = '1'
group by ed.enrolled_id)
select enrolled_id, count(done_cnt), count(ed.done) from table1 a

또 틀리다가 case when을 써야 될 것 같다는 생각이 듦



select ed.enrolled_id, count(ed.done) as done_cnt, from enrolleds_detail ed
case when where ed.done = '1' then as done_cnt
else as total_cnt
group by ed.enrolled_id

또 틀렸음


select ed.enrolled_id, count(ed.done) as done_cnt, count(ed.done) as total_cnt from enrolleds_detail ed
case when where ed.done = '1' then as 'done_cnt'
else as 'total_cnt'

이것도 아님



with table1 as (
select ed.enrolled_id, count(ed.done)
(case when ed.done = '1' then 'done_cnt'
  when ed.done >= '0' then 'total_cnt'
) as cnt
from enrolleds_detail ed
)
select a. count(a.cnt) from table1 a

이것도 실패




select ed.enrolled_id, count(ed.done), count(ed.enrolled_id) as total_cnt from enrolleds_detail ed
where ed.done = '1'

이렇게 했다가 서브쿼리 2개 만들고 이너조인이라는 걸 뒤늦게 봄




select e.enrolled_id, count(dc), count(ed.done) from (
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = '1'
from enrolleds e
) as dc
group by e.enrolled_id



또 실패



select e.enrolled_id,
   count(ed.done) as done_cnt
  from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = '1'
group by e.enrolled_id, ed.done

아무튼 여기까진 하겠는데 토탈 카운트 붙이는 걸 못하겠당...



with table as (
select e.enrolled_id,
   ed.done as done_cnt,
from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = '1'
) dc
group by dc

select a.dc, count(ed.done) as total_cnt from table a


여기까지 하다가 힌트 듣고 다시 시도...



select ed.enrolled_id, count(ed.done) as done_cnt from enrolleds_detail ed
where ed.done = '1'
group by ed.enrolled_id

select ed.enrolled_id, count(ed.done) as total_cnt from enrolleds_detail ed
group by ed.enrolled_id

일단 2개 만들고 얘네 둘을 조인하면 되는데.



with table1 as (
select ed.enrolled_id, count(ed.done) as done_cnt from enrolleds_detail ed
where ed.done = '1'
group by ed.enrolled_id
) 
select a.done_cnt, count(*) from table1 a


with table2 as (
select ed.enrolled_id, count(ed.done) as total_cnt from enrolleds_detail ed
group by ed.enrolled_id
)
select b.total_cnt, count(*) from table2 b

inner join enrolled_id ed on table1.enrolled_id = table2.enrolled_id


이너조인을 못하겠어서 포기....




튜터님 정답

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


위드절 사용

with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
group by 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



이렇게...



with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
group by enrolled_id
)
select a.enrolled_id,
   a.done_cnt,
   b.total_cnt,
   (a.done_cnt/b.total_cnt) as ratio
  from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id

ratio는 아래 추가만 하면 됨

소수점은 round(a.done_cnt/b.total_cnt,2) as ratio

 

 

[수강소감]

쉽다고 생각했는데 뒤로 갈수록 논리적인 사고가 필요한 거 같음 하지만 나는 무논리적인 사람이지

어떻게 해야 되는지 대충 개념은 알겠는데 방법을 모르겠는 그런..

계속 하면 익숙해 질 것 같긴 한데 업무 상 쓸 일이 없어서 까먹을 거 같달까 ㅋㅋㅋ

그래도 가끔 생각날 때마다 한 번 씩 써먹어야겠다.