SQL 기초 4일차

SQL 기초 4일차
#스파르타코딩클럽, #내일배움단
학습일자: 2022/03/10
재수강일: 2022/05/01
강의: 엑셀보다 쉬운 SQL
진도: 3-5 ~ 4-4

어제 쿼리가 조금 길어지면서 나도 조금 어질어질해졌지만,
아직 괜찮다. 앱개발보단 쉬우니까~~~ㅋㅋㅋㅋ

=====================

left join을 통해서 강의db를 가지고 알아낼 수 있는 것
point가 없는 사람 = 강의 시작을 안한 사람
point 값이 NULL인 사람은 강의 시작을 안한것(수강하면 포인트가 생기니까)

select * from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL

is NULL 대신 <= 'NULL'>을 써봤는데 안먹힌다.ㅎㅎ;;
NULL은 무조건 대문자로 넣어야한다. 사실 소문자로 써도 글자 보정되더라.

is NULL을 써서 나오는 값들은 수강시작을 안해서 포인트가 없는 사람들.

is not NULL을 써서 나오는 값들은 수강시작해서 포인트가 있는 사람들.

=====================

신나고 재밌는 퀴즈풀기~~~

7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요!

select count(u.user_id) as 전체유저, count(point_user_id) as 수강유저, round(count(point_user_id)/count(u.user_id), 2) 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'

잘...한건가? 맞지?

count는 null값을 자동으로 제외하고 센다고 한다.

그러므로, null이 없는 필드를 전체유저로, null이 있는 유저를 수강유저로 한 뒤에, 그 값을 나누면 비율이 뜰 것이라는 발칙한 상상

※ 여기서 배운 것!
select 뒤에 꼭 필드명이 들어갈 필요는 없고, 카운트(필드)를 사칙연산할 수도 있다! 새로운 지식이 늘었다!

복습!

=====================
3-6
결과물 합치기! union
select를 두번 할게 아니라, 한번에 모아서 보고 싶을 경우

(강의자료 그림)
무슨 뜻이지...?
join은 데이터가 옆으로 들어오지만,
union은 합쳐지지 않고 아래로 들어간다는 뜻인가?

union 설명 교재
(
select '7월' as month, c1.title, c2.week, count(*) as cnt 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
) union ALL 
(
select '8월' as month, c1.title, c2.week, count(*) as cnt 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
)

() union () 형식이고, 소괄호 안에 order by가 있지만 적용되지 않았다.
왜? union으로 묶고 난 뒤에 정렬시켜야함. 그 전에 정렬한건 결과값에 영향을 못줌.

=====================

3-7 끝, 숙제 설명

숙제: enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_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

어...? 마지막꺼 이해안된다...뭐지?..

10분 후...

enrolled가 뭔뜻인지를 몰라서 모르는거 같다. 문법을 이해하는덴 무리 없다. 넘어가자!

=====================

4-1. subquery(서브쿼리)
쿼리문을 복잡하게 쓸수있는 서브쿼리
서브쿼리의 가시성을 높여주는 with
지저분한 데이터를 정리하는 문법
조건문
을 배울 예정

=====================

4-2. 원하는 데이터를 더 쉽게: subquery

카카오페이로 결제한 유저들의 정보를 보기

sql을 어떻게 쓰는게 좋을까?

select * from users u
inner join orders o
on u.user_id = o.user_id
where o.payment_method = 'kakaopay';

요렇게 하면 될듯

딱 맞췄쥬?

이렇게 join을 해서 추출한 것을, 서브쿼리를 써서도 볼 수 있다.

1단계.
select * from users
where user_id in (....) << 여기에 들어가게?!!(*in (a, b) = a 또는 b인 데이터를 가져오는 문법)

select user_id from orders
where payment_method = 'kakaopay'

2단계. 1단계의 in (....) << 이 안에 아래 두줄을 넣어주면?!!!(이렇게도 되는구나!)
select * from users
where user_id in (select user_id from orders
where payment_method = 'kakaopay') << 이렇게!

여기서 깨달았다. 서브쿼리란 쿼리 안의 쿼리. 쪼꼬미쿼리라는 것을..

서브쿼리를 먼저 실행해서 결과를 만들고, 그 값을 메인쿼리(대인배쿼리)에 넣어서 실행시킨다.

서브쿼리는 where절, select절, from절 등등 다양한 곳에 위치시킬 수 있다.

쪼꼬미쿼리를 넣을수록 코드가 길어지고 복잡해지기때문에, 들여쓰기를 잘 해서 가시성이 좋게 하자!

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

참 쉽네요.

=====================

select절에 들어가는 서브쿼리

커여운 쪼꼬미쿼리를 select(쪼꼬미쿼리) from ~~~ 형식으로 써보자.

연습. checkins 안에 있는 user_id 별로 평균 likes를 알고 싶음

select user_id, avg(likes) from checkins
where user_id = '4b8a10e6'

user_id가 4b8a10e6인 사람의 평균 좋아요 수를 요러케 구했읍니다.

첫번째 쿼리에서, user_id의 평균 좋아요를 4번째 필드로 붙이고 싶어!

그렇다면?

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 

마치 반복문처럼 계속 실행된다! 무친!!! for 어디갔어 ㅋㅋㅋ

=====================

from절에 들어가는 서브쿼리 (가장 많이 사용됨)

연습. 유저별 좋아요 평균 구하기

연습2. point_users에서 포인트가 많다는건, 열심히 활동한다는 뜻임
그러면, 그 사람들도 좋아요도 많이 받았을까? 궁금쓰 ㅋㅋ 데이터를 뽑아보자.

그림1. point_users에서 뽑은 user_id와 point

그림2. checkins에서 뽑은 user_id와 평균좋아요수

이 두 테이블을 합치는 방법이 있다?! = 쪼꼬미쿼리를 from에 넣으면 된다.

select pu.user_id, pu.point, a.평균좋아요수
from point_users pu
inner join (
		select user_id, round(avg(likes),1) as 평균좋아요수 from checkins
		group by user_id) a on pu.user_id = a.user_id

아까 만든 그림2의 테이블을 a로 명명하고, inner join on으로 접합.

내가 만든 select문을 마치 원래 있었던 테이블처럼 사용하는 방법이다.

재밌다. 약간 어지럽다. 문제 빨리 풀고 싶다.

=====================

4-3. Subquery 연습해보기 (where, select, from, inner join)

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

select user_id, point from point_users pu
where point >
			 (select avg(point) from point_users pu
		     )
order by point asc

전체 유저의 포인트는 약 5380점.

오름차순으로 정렬해서 가장 먼저 나오는게 5380보다 높으니 성공이라고 하자

데이터가 변경되면서 평균포인트가 변동될수 있으므로 5380을 넣는게 아니라 서브쿼리를 쓰는게 옳다.

=====================

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

※ 다르게 푸는 방법

서브쿼리 안에 서브쿼리를 넣어서 만들었다.

=====================

select절에 들어가는 서브쿼리 연습

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

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

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

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

=====================

from 절에 쓰이는 서브쿼리

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

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

타이틀, 체크인한 수, 총 인원, 비율을 구해보기!

아 여기서 멘붕... 너 내가 해내고 만다...

=====================

4-4. with절 연습하기

쿼리문을 간단하고 보기좋게 쓸 수 있는! with!

with문 쓰기 전 쿼리
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

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

결과는 이렇게 똑같이 나온다.

=====================

아~~~ 어렵네... 주말에 약속도 없으니까 복습 오지게 달려야겠다~ㅠ