오늘의 다짐 이벤트 : 오늘의 다짐을 남겨준 10명 추첨해서 기프티콘 지급
문제 발생 !
'오늘의 다짐' 이벤트 당첨자를 추첨하기 위해서는, 이름과 연락처 등의 정보를 알아야 하는데 여기에는 user_id라는 정보만 있다.
(0) 실무에서 가장 많이 쓰인다.
(1) '기준' 설정
(2) Key 이해
한 테이블에 모든 정보를 담을 수도 있겠지만, 불필요하게 테이블의 크기가 커져 불편해진다. 그래서, 데이터를 종류별로 쪼개 다른 테이블에 담아놓고 연결이 필요한 경우 연결할 수 있도록 만들어놓는다.
예를 들면, users와 checkins 테이블에 동시에 존재하는 user_id,
이런 필드를 두 테이블을 연결시켜주는 열쇠라는 의미로 'key'라고 부른다.
먼저,
SELECT * FROM users ;
SELECT * FROM point_users;
위 쿼리문을 통해 user_id가 공통 컬럼임을 확인할 수 있다.
SELECT * FROM users u
# 교집합
Inner join point_users p
on u.user_id = p.user_id;
Inner Join 이 더 쉽고 많이 쓰일 가능성이 높다.
SELECT * FROM orders o
Inner Join users u
on o.user_id = u.user_id
SELECT * FROM checkins c
Inner Join users u
on c.user_id = u.user_id ;
SELECT * FROM enrolleds e
Inner Join courses c
on e.course_id = c.course_id ;
위 쿼리가 실행되는 순서: from → join → select
SELECT c1.course_id,c2.title, COUNT(*) as CNT
FROM checkins c1
inner join courses c2
on c1.course_id = c2.course_id
group by c1.course_id ;
SELECT p.user_id, u.name, u.email,p.point
FROM point_users p
inner join users u
on p.user_id = u.user_id
order by p.point DESC ;
SELECT u.name, COUNT(*) as CNT from orders o
inner join users u
on o.user_id = u.user_id
WHERE o.email like '%naver.com'
group by u.name
- 위 쿼리가 실행되는 순서: from → join → where → group by → select
- Join의 실행 순서는 항상 from 과 붙어다닌다고 생각
join 할 테이블: point_users 에, orders 를 붙이기
SELECT o.payment_method as 결제수단,
ROUND(AVG(pu.point)) as 평균 FROM orders o
inner join point_users pu
on o.user_id = pu.user_id
group by o.payment_method
SELECT * FROM point_users pu ;
join 할 테이블: enrolleds 에, users 를 붙이기
SELECT u.name, COUNT(*) as CNT FROM enrolleds e
inner join users u
on e.user_id = u.user_id
WHERE is_registered = 0
group by u.name
order by CNT DESC ;
join 할 테이블: courses에, enrolleds 를 붙이기
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 is_registered = 0
group by c.title
join 할 테이블: courses에, checkins 를 붙이기
SELECT c.title, c2.week, COUNT(*) as CNT
FROM courses c
inner join checkins c2
on c.course_id = c2.course_id
group by c.title, c2.week
order by c.title, c2.week
join 할 테이블: courses에, checkins 를 붙이고 +checkins 에, orders 를 한번 더 붙이기!
SELECT c.title, c2.week, COUNT(*) as CNT
FROM courses c
inner join checkins c2
on c.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 c.title, c2.week
order by c.title, c2.week
# 별칭 제공
SELECT * FROM users u
left join point_users p
on u.user_id = p.user_id;
비어있는 데이터의 경우, 회원이지만 수강을 등록/시작하지 않아 포인트를 획득하지 않은 회원인 경우이다.
# is NULL , is not NULL 처리
SELECT name, COUNT(*) as CNT
FROM users u
left join point_users pu
on u.user_id = pu.user_id
WHERE pu.point_user_id is not NULL
7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율
SELECT COUNT(pu.point_user_id) as pnt_user_cnt,
COUNT(u.user_id) as tot_user_cnt,
round(COUNT(pu.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'
Select를 두 번 할 게 아니라, 한번에 모아서 보고싶은 경우
아래와 같은 모양으로 어떻게 만들까 ?
(
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' # 8월 미만
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
)
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
order by CNT DESC