[개발일지-3] SQL 3주차

2주차 SQL 강의를 수강하고 개발일지 쓴지 얼마 안된 것 같은데 SQL 3주차 강의까지 다 수강했다. 사실 웹개발 보다는 좀 쉬워서 그런지 진도가 쭉쭉 잘 나가는 것 같다. 웹개발은 다시 차근차근 해봐야 할 것 같다. 파이썬에 대해서도 조금씩 공부하면서 해야될 것 같아서 SQL부터 끝내고 웹개발&파이썬만 집중적으로 해야할 것 같기도 하다.

JOIN : 테이블과 테이블을 묶어주자

select * from point_users
left join users
on point_users.user_id = users.user_id

이런식으로 point_users 테이블과 users 테이블을 user_id라는 키값(기준이 되는 값)을 기준으로 묶는 것을 말한다.

left join과 inner join

• left join은 A와 B라는 테이블을 합칠때 왼쪽에 있는 A를 위주로 묶는다고 생각하면 된다. 그래서 Join결과 중에 비어있는 데이터가 있을 수도 있는데 이 경우에는 B테이블에 값이 없는 경우이다.
• Inner join은 A와 B테이블의 교집합이라고 생각하면 된다. 비어있는 데이터 없이 서로 중복되는, 겹치는 값을 보여준다.

지금까지 배웠던 문법과 함께 쿼리 작성해보기 (quiz)

1. 결제수단 별 유저 포인트의 평균값 구해보기
   join할 테이블 : point_users에 orders 테이블 붙이기
   

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

2. 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
   join할 테이블 : enrolleds에 users 테이블 붙이기
   is_registered = 0 인 사람들을 세어보자!
   

select u.name, count(*) as cnt_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 cnt_name desc

3. 과목별로 시작하지 않은 유저들을 세어보기
   join할 테이블 : courses에, enrolleds 테이블 붙이기
   is_registered = 0 인 사람들을 세어보자!
   

select c.course_id,c.title, count(*) 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.title

4. 웹개발, 앱개발 종합반의 week별 체크인 수를 세어보자...
   join할 테이블 : courses에 checkins를 붙인다
   group by, order by에 콤마로 이어서 두 개 필드를 건다
   

select c1.title,c2.week, count(*) from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
group by c1.title, c2.week
order by c1. title, c2.week

5. 4번에서 8월 1일 이후에 구매한 고객들만 발라내어보자
   join할 테이블 : courses에 checkins를 붙이고 checkins에 orders를 한번 더 붙인다.
   orders 테이블에 inner join을 한번더 걸고 where절로 마무리하자
   

select c1.title,c2.week,count(*) from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id 
where o.created_at >='2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week

(나 생각보다 잘하는듯..ㅎㅎㅋㅎㅋ)

left join은 언제 사용하는거?

유저들 모두가 포인트를 갖고 있지 않을 수도 있는데, 포인트를 갖고 있지 않은 사람들은 어떻게 통계를 내지? > 이럴때 씀

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name

select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by name

is NULL과 is not NULL을 사용하여 갖고 있지 않은 사람, 갖고 있는 사람의 통계를 내볼 수 있다.

Q. 7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요! (꼭 복습하기!!!!!)

select count(point_user_id) as pnt_user-cnt
		count(*) as tot_user_cnt
		round(count(point_user_id)/count(*)) as ratio
 from users u 
left join point_users pu on u.user_id = pu.user_id
where u.created_at BETWEEN '2020-07-10' and '2020-07-20'

(아까 나 좀 잘한다는 거 취소...) 나중에 꼭 복습해봐야할 퀴즈임

union 결과물 합치기

select 두번 할게 아니라, 한번에 모아서 보고싶은 경우!

select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
inner join courses c on c2.course_id = c.course_id
inner join orders o on o.user_id = c2.user_id
where o.created_at < '2020-08-01'
group by c2.course_id, c2.week
order by c2.course_id, c2.week

이거랑, 8월 결과값이랑 합쳐서 보고싶으면?

(
	select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at < '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)
union all
(
	select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2
	inner join courses c on c2.course_id = c.course_id
	inner join orders o on o.user_id = c2.user_id
	where o.created_at > '2020-08-01'
	group by c2.course_id, c2.week
  order by c2.course_id, c2.week
)

이런식으로 해주면 됨

단, union에서는 내부 정렬이 먹지 않는다... 이럴땐 subquery 사용하면 됨!

3주차 숙제

숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력되어야 한다.
조인해야 하는 테이블: enrolleds, enrolleds_detail
조인하는 필드: enrolled_id

select e.enrolled_id, e.user_id, count(*) as 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, e.user_id
order by cnt desc

이렇게 3주차 강의도 마무리했다.
아무래도 웹개발하는 코딩보단 쉬워서 그런지 재미있다. 웹개발 강의는 1주차부터 다시 차근차근.. 해봐야지!! 점프투 파이썬 강의도 같이 들어야겠다. 기초부터 탄탄히 잡아나가면서 강의를 들어야 좀 덜 불안할 것 같은 느낌도 든다.