본격 쿼리 직접 작성해보기 (feat. inner join)

오늘도 예제를 가지고 쿼리를 직접 작성하고 그 과정을 적어내려 갈 것이다.

 

---

결제 수단 별 유저 포인트의 평균값 구해보기

결제 수단과 포인트관련 테이블을 먼저 살펴봐야겠다.

select *
from orders o

 

select *
from point_users pu

 

 

pint_users 테이블을 주체 테이블로 사용할 것 이다. 연결하기 위한 key필드는 user_id가 되겠다.

select *
from point_users pu
inner join orders o
on pu.user_id = o.user_id

 

이제 결제 수단별로 이제 묶어주자.

select *
from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

 

 

평균값을 구해야 하니 avg를 활용해주고

select avg(pu.point)
from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

 

 

어떤 게 어떤 건지 모르겠으니 select을 조금 수정해주자.

select o.payment_method avg(pu.point)
from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

어?.. 오류? avg근처에서 올바르게 구문이 사용되지 않았다고? 음.. 어째서? why?!

 

어 음 아? ㅠㅠ select을 여러 개 할 때 쉼표를 잊어버렸다.

이런 기초적인 실수를.. 흐어

select o.payment_method, avg(pu.point)
from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

하하.. 민망하게 성공

 

 

소수점 자리가 보기 불편하니 round를 활용해서 나타내자.

select o.payment_method, round(avg(pu.point), 0)
from point_users pu
inner join orders o
on pu.user_id = o.user_id
group by o.payment_method

 

 

 

---

결제하고 시작하지 않은 유저들을 성씨별로 세어보기

일단 성씨별 이니까 해당 데이터가 있는 users 테이블이 필요할꺼고

select *
from users u

 

시작했는지 안했는지는 어떻게 알 수 있을까? 하나씩 테이블을 살펴봐서 어떤 데이터들이 있는지 확인해봐야 한다.

찾았다!

select *
from enrolleds e

is_registered라는 필드로 0과 1로 표시되어 있는 것을 찾았다. 1이 수강을 시작한거다.

 

여기선 음 enrolleds테이블에 users를 붙이는게 좋아 보인다. key필드는 user_id

select *
from enrolleds e
inner join users u 
on e.user_id = u.user_id

 

 

이제 성씨별로 묶어주자.

select *
from enrolleds e
inner join users u 
on e.user_id = u.user_id
group by u.name

 

 

시작하지 않은 유저는 is_registered가 0으로 표시되니까, 이 필드가 0인 사람들로만 추려보자.

select *
from enrolleds e
inner join users u 
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name

 

 

 

성씨별로 몇 명인지 세어 봐야하니 count를 활용해주면 되겠다.

select u.name, count(u.name)
from enrolleds e
inner join users u 
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name

 

 

보기 편하게 오름차순으로 정렬도 하고.

select u.name, count(u.name)
from enrolleds e
inner join users u 
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by count(u.name)

 

 

더 보기 편하게 별칭도 주고

select u.name, count(u.name) as cnt
from enrolleds e
inner join users u 
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by cnt

 

 

select을 수정해서 어떤 상태인 사람들인지 표시도 하고

select e.is_registered, u.name, count(u.name) as cnt
from enrolleds e
inner join users u 
on e.user_id = u.user_id
where e.is_registered = 0
group by u.name
order by cnt

 

 

---

과목 별로 시작하지 않은 유저들을 세어보기

여기도 시작하지 않은 유저들이니까 방금 했던 테이블을 활용하자.

select *
from enrolleds e

 

 

추가로 과목이 필요하니 courses테이블도!

select *
from courses c

 

 

course_id를 key로 활용하면 되겠다.

select *
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id

 

 

시작하지 않은 사람은 is_registered가 0인 사람들이니까, where절로 추가하자

select *
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0

 

 

 

과목별로 묶어 준 다음

select *
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id

 

 

 

세어봐야 하니 count를 활용

select count(e.is_registered) as cnt
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id

 

 

 

어떤 게 몇 명인지 알 수 없으니 보기 좋게 수정!

select c.course_id, count(e.is_registered) as cnt
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id

 

 

이렇게 해도 여전히 저 course_id가 뭔지 알아보기 힘드니까 select문구를 추가!

select c.course_id, c.title, count(e.is_registered) as cnt
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id

짜잔~ 성공! 음 그런데 저 cnt가 어떤 걸 카운터 했는지 구분이 힘들 수 있으니 별칭을 cnt_nostart로 시작하지 않은 사람들이라고 알아 볼 수 있게 수정하자

 

 

select c.course_id, c.title, count(e.is_registered) as cnt_notstart
from courses c 
inner join enrolleds e 
on c.course_id = e.course_id 
where e.is_registered = 0
group by c.course_id

 

깔끔!!