subquery (from절, with, when)
2023-02-22 (수) 15:16
서브쿼리 from절부터 이어서 시작해봅시다
from절에 사용되는 서브쿼리는 이미 select되어 출력된 결과에 기존의 테이블을 join시켜 결과물에 필요한 것을 추가할 때 주로 사용됩니다
마찬가지로 예시와 함께 보겠습니다
chechins 테이블을 먼저 살펴보겠습니다
먼저 checkins 테이블에서 user_id와 likes의 평균을 구하여 출력해보겠습니다
likes의 평균값은 소수점 첫째자리까지 표현하고 avg_likes라고 별칭을 부여합니다
select c.user_id, round(avg(c.likses),1)as avg_likes from checkins c
group by c.user_id
잘 출력되었습니다
스파르타에서는 강의를 듣고 숙제나 계발일지를 작성하면 point를 줍니다 즉 활동이 많을수록 point를 많이 보유하고 있다 볼 수 있으니 높은 likes를 기록한 유저는 많은 활동을 한 유저인지 한 번 확인해봅시다
유저가 받은 likes와 유저가 보유한 point의 관계를 알아보기 위해 point_users 테이블과 join하여 avg_likes필드 옆에 point를 출력시켜보겠습니다
먼저 point_users 테이블을 확인합니다
user_id를 이용하여 join시키면 될 것 같습니다
from절 서브쿼리를 사용해 봅시다
select (필드명),(필드명),(필드명) from (point_users pu)
inner join (
select c.user_id, round(avg(c.likses),1)as avg_likes from checkins c
group by c.user_id <-위에서 뽑은 결과물입니다!
) a <- a라고 별칭을 부여해주었습니다
on pu.user_id = a.user_id
이런형태로 join시켜줍니다
자 완성시켜보겠습니다
select a.user_id, avg_likes, pu.point from (point_users pu)
inner join(
select c.user_id, round(avg(c.likses),1)as avg_likes from checkins c
group by c.user_id
) a
on pu.user_id = a.user_id
잘 출력됩니다
문제 하나 풀어보겠습니다
checkins 테이블은 강의를 듣는 회원이 comment를 달거나 좋아요를 눌러주는 등 활동한 것을 기록한 테이블입니다
이 테이블을 이용하여 강의별(강의는 2가지입니다) 유저의 활동력을 알아보고(1)
실질적으로 강의를 신청한 것을 기록하는 orders 테이블에서 강의별 전체 수강인원을 뽑아봅시다(2)
먼저 (1)번 부터 해보겠습니다
select * from checkins 를 사용하여 테이블을 먼저 살펴봅시다
출력할 필드는 checkin_id와 checkin을 한 유저의 수 입니다
select checkin_id, count(user_id) from checkins c
group by c.cours_id
이렇게 하면 될까요?
하나 더 추가해야 합니다. 테이블을 보면 동일한 아이디가 주차별로 comment를 달아 checkin_id가 추가되어 있습니다
그러니 distinct를 이용하여 중복을 제거하고 as 를 사용하여 별칭을 부여해 줍시다
select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id
출력해보겠습니다
잘 나왔습니다
(2)번을 해보겠습니다
select * from orders o
를 이용하여 orders 테이블을 먼저보겠습니다
필요한 것은 course_id와 강좌별 수강인원입니다
select course_id, count(*) as total from orders o
group by course_id
course_id로 그룹화 하고 구분된 두 강의의 신청자 수를 count 하게 했고 별칭도 부여했습니다
출력해보겠습니다
잘 나왔습니다
이제 두 출력된 결과물을 서브쿼리를 이용하여 join시켜보겠습니다
마찬가지로 형태는
select (필드명), (필드명),(필드명) from
(1번 결과물) a ->a는 별칭입니다
inner join (2번 결과물) b ->b도 별칭입니다
on a.course_id = b.course_id
입니다
완성시켜 보겠습니다
select a.course_id, cnt_checkins, cnt_total from
(select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id ) a
inner join
(select course_id, count(*) as total from orders o
group by course_id) b
on a.course_id = b.course_id
출력해보겠습니다
잘 출력됩니다!
여기서 체크인을 한 수강생의 비율은 어떻게 구할까요?
(cnt_checkins/cnt_total)을 통해 구할 수 있습니다
이것을 select 절에 넣어주고 별칭을 붙여준다면
select a.course_id, cnt_checkins, cnt_total,
(cnt_checkins/cnt_total) as ratio <-(ratio는 비율입니다)
from
(select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id ) a
inner join
(select course_id, count(*) as total from orders o
group by course_id) b
on a.course_id = b.course_id
진짜 마지막으로 course_id를 강의명으로 표시해봅시다
orders 테이블에 title을 가져오면 될까요?
가능합니다
물론 select 절에 b.course_title을 사용하면 오류가 발생합니다
이렇게 해봅시다
orders테이블을 사용한 서브쿼리를 봅시다(b입니다)
select course_id, count(*) as total from orders o
group by course_id
이것이 하나의 테이블이라 생각하시면 됩니다 위의 오류가 왜 발생했을까요?
테이블에 course_title 필드가 없기 때문입니다
b 테이블에 course_title을 추가시킵니다
select course_id, course_title,count(*) as total from orders o
group by course_id
이것을 b의 자리에 넣어주고
select에 b.course_title를 넣어줍니다 a.course_id는 지우겠습니다
select b.course_title, cnt_checkins, cnt_total,
(cnt_checkins/cnt_total) as ratio
from
(select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id ) a
inner join
(select course_id,course_title ,count(*) as total from orders o
group by course_id) b
on a.course_id = b.course_id
잘 출력됩니다
다른 방법도 있습니다
새로운 테이블을 join시켜서 그 table에 있는 title을출력하는 방법입니다
join시킬 course테이블입니다
select * from course c
title를 가져오고 course_id를 통해 join 시킵시다
select a.course_id, cnt_checkins, cnt_total,
(cnt_checkins/cnt_total) as ratio
from
(select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id ) a
inner join
(select course_id, count(*) as total from orders o
group by course_id) b
on a.course_id = b.course_id
inner join courses c
on a.course_id = c.course_id
실행시키면 변화는 없을 것입니다 이제 c에 있는 title을 출력해 봅시다
select c.title, cnt_checkins, cnt_total,
(cnt_checkins/cnt_total) as ratio
from
(select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id ) a
inner join
(select course_id, count(*) as total from orders o
group by course_id) b
on a.course_id = b.course_id
inner join courses c
on a.course_id = c.course_id
출력이 잘 됩니다!
이제 with를 알아봅시다
with는 쿼리를 더 쉽게 파악하기 위해 사용한다고 하네요
바로 위의 것으로 어떻게 사용하는지 알아봅시다!
with table1 as(
select checkin_id, count(DISTINCT(user_id)) as cnt_checkins from checkins c
group by c.cours_id
), table2 as(
select course_id, count(*) as total from orders o
group by course_id)
)
select c.title, cnt_checkins, cnt_total,
(cnt_checkins/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 (테이블 명1) as
(a의 서브쿼리),(테이블 명2) as
(b의 서브쿼리)
select (필드명),(필드명),(필드명)
from 테이블 명1 a <- a는 alias를 이용한 별칭입니다
inner join 테이블 명2 b <-b는 alias를 이용한 별칭입니다
on a.course_id = b.course_id ->join이랑 똑같습니다
inner join courses c
on a.course_id = c.course_id ->title을 가져오기 위해서 했던 join이죠!
마지막까지 다 왔습니다
문법 몇가지 보고 가겠습니다
-SUBSTRING_INDEX
이것은 문자열을 쪼개고 선택하여 추출하는 것입니다
users 테이블을 이용하여 예시를 봅시다
users 테이블입니다 유저의 이름과 substring_index를 이용하여 이메일의 @ 앞 부분만을 출력해보겠습니다
select name, substring_index(email,'@',1)
실행시켜보겠습니다
아이디 부분만 출력되었습니다
substring_index는 (적용할 필드명,'기준',1 or -1)을 통해 사용합니다
1은 기준의 앞을 -1은 기준의 뒤를 선택합니다
다음은 substring을 알아봅시다
마찬가지로 users 테이블을 사용해봅시다
created_at은 생성날짜입니다, name과 created_at을 출력하는데 created_at은 substring을 사용하여 xxxx-xx-xx 형태로 출력해 봅시다
select name, string(created_at,1,10)
잘 출력되었습니다
그럼 날짜부분을 날리고 시간부분만 출력할 수 있을까요?
select name, string(created_at,12,8)
잘 출력되었습니다
string(적용할 필드,시작지점,시작지점부터 ~까지)를 의미합니다
숫자는 공백이나 기호 -,:도 카운트합니다
마지막으로 case(when)을 알아보고 마치겠습니다
case는 기준을 주고 그 기준에 따라 새로운 필드를 통해 정해진 값을 출력합니다
point_users 테이블을 통해 자세히 알아봅시다
user_id 별 point를 볼 수 있습니다
테이블에서 출력할 것은 user_id, point 그리고 case를 사용하여 수준을 표시해줄 것 입니다
select user_id, point from point_users pu
case를 사용해봅시다
select user_id, point,
case
when pu.point > 10000 then '잘 하고있어요!'
else '노력이 필요해요!'
end as LV
from point_users pu
출력결과를 보겠습니다
잘 출력됩니다
case는 select절에 들어갑니다 그러니 꼭 ,를 사용해주어야 합니다
문제 하나 풀어봅시다
마찬가지로 point_users 테이블을 이용해봅시다
가져올 정보는 point_user_id와 point 그리고
case를 사용하여 point가 10000 이상이면 1만 이상, 5000 이상이면 5천 이상 그 외에는 5천 미만으로 표시하며 필드 명은 lv로 별칭을 달아줍니다
select pu.point_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
잘 출력됩니다!
비교연산자를 사용할 때에는 알맞은 비교연산자를 사용해야 합니다 에러 발생의 원인이 될 수 있습니다
>,<,>=,<= 등등
이제 통계를 내봅시다
lv를 그룹으로 묶은 후 각 항목에 해당하는 유저의 수를 확인해봅시다 count는 cnt로 별칭을 사용해봅시다
select count(*)as cnt,
case
when pu.point >= 10000 then '1만 이상'
when pu,point>= 5000 then '5천 이상'
else '5천 미만'
end as lv
from point_users pu
group by lv
출력이 됩니다
다른 방법은 없을까요?
from절에 서브쿼리를 사용하는 방법이 있습니다
select * from(
select pu.point_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
서브쿼리가 보이시나요?
select * from을 위 쪽에 하나 만들고 from절에 서브쿼리로 위에서 만들었던 쿼리를 넣고 a라는 별칭을 붙여주었습니다
실행시켜보겠습니다
달라진 것이 보이시나요?
보이시면 이상한 것입니다 달라진게 없어요!
우리가 만든 별칭 a를 하나의 테이블로 사용하여 출력한 것이기 때문입니다
이제 a테이블에서 lv를 그룹화시키고 위와같이 해당하는 유저의 수를 구해보겠습니다
select lv, count(*) from(
select pu.point_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 lv
(서브쿼리 때문에 복잡해 보이지만 서브쿼리를 하나의 테이블로 생각하시면 좀 더 이해하기 편할 것 입니다, 내용물이 보이는 테이블 느낌이죠!)
출력해보겠습니다
잘 출력됩니다!
with도 사용해볼까요?
with table1 as(
select pu.point_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
) ->서브쿼리를 table1에 통째로 넣어주고!
select lv, count(*) from table1 ->select 뒤에는 출력할 것을 정해줍니다!
group by lv -table1에 들어간 서브쿼리에서 가져오니 from에는 table가 들어가야겠죠!
자! 이것으로 SQL 강의는 끝났습니다 문제 몇가지 남은 건 지금 배운 것과 크게 차이가 없으니 넘어가도록 하겠습니다
내일은 사정 때문에 계속 미뤄두었던 숙제(select 문 문법 총정리하기!)를 하는 시간을 가지고 마무리하는 시간을 가지면 되겠네요!