오늘은 3주자와 4주차 강의를 들었다
3주차 까지는 어찌어찌 따라갔는데
4주차 강의는 너무 어려웠다 SUBQUERY의 개념이 잘 이해가지 않았다
어디에 어떻게 기입해야 하는지가 복잡하고 어려웠다
나름대로 정리하여 계속 보는 방법 밖엔 없을 것 같다
JOIN 테이블과 테이블을 붙이는 것 기준이 가장 중요함
LEFT JOIN 어디에 무엇을 붙일 것인지 순서 중요
SELECT * FROM users u (테이블을 별칭으로 만듬)
left join point_users p (붙이는 테이블도 별칭으로 만듬)
on u.user_id = p.user_id (각 테이블에 있는 동일한 별칭을 찾아 연결)
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'
INNER JOIN 교집합 두 개다 포함 되는 것만 나옴
SELECT * FROM users u (테이블을 별칭으로 만듬)
inner join point_users p (붙이는 테이블도 별칭으로 만듬)
on u.user_id = p.user_id (각 테이블에 있는 동일한 별칭을 찾아 연결)
checkins 테이블에 courses 테이블 연결해서 통계치 내보기
select c.course_id, c1.title, count(*) as cnt from checkins c
inner join courses c1 on c.course_id = c1.course_id
group by c.course_id
point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
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(u.name) as count_name from orders o
inner join users u
on o.user_id = u.user_id
where u.email like '%naver.com'
group by u.name
결제 수단별 유저 포인트의 평균값 구해보기
SELECT o.payment_method, round(avg(p.point),0) from orders o
inner join point_users p on o.user_id = p.user_id
GROUP by o.payment_method
결제 시작하고 시작하지 않은 유저들을 성씨별로 세어보기
SELECT u.name, count(*) 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 count(*) desc
과목별로 시작하지 않은 유저들을 세어보기
SELECT c.course_id, c.title, count(*) 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
웹개발, 앱개발 종합반의 week 별 체크인 수 세기
SELECT c.title, c1.week, count(*) as cnt from courses c
inner join checkins c1 on c.course_id = c1.course_id
group by c.title, c1.WEEK
order BY c.title, c1.WEEK
웹개발, 앱개발 종합반의 week 별 체크인 수 세기 후 8월 1일 이후에 구매한 고객들만 발라내기
SELECT c.title, c1.week, count(*) as cnt from courses c
inner join checkins c1 on c.course_id = c1.course_id
inner join orders o on c1.user_id = o.user_id
WHERE o.created_at >='2020-08-01'
group by c.title, c1.WEEK
order BY c.title, c1.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, e.user_id
order by cnt desc
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
)
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
)
subquery 원하는 데이터를 더 쉽게 얻어 보기 쿼리 안의 쿼리라는 의미
SELECT u.user_id , u.name, u.email from users u
inner join orders o
on u.user_id = o.user_id
WHERE o.payment_method ='kakaopay'
위와 아래 동일한 결과 나옴 (①번 결과 먼저 나오고 밖에 있는 것을 실행함)
SELECT user_id, name, email from users
WHERE user_id in (
① select user_id from orders
WHERE payment_method ='kakaopay'
)
WHERE에 들어가는 SUBQUERY
SELECT user_id, name, email from users
WHERE user_id in (
select user_id from orders
WHERE payment_method ='kakaopay'
)
전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
SELECT * from point_users
where point > (
select avg(point) from point_users
)
이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기 2가지 방법
SELECT * from point_users
WHERE point > (
SELECT avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name='이**'
)
SELECT * from point_users
WHERE point >(
SELECT avg(point) from point_users pu
WHERE user_id in(
SELECT user_id from users WHERE name = '이**'
)
)
SELECT에 들어가는 SUBQUERY
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
checkins 테이블에 course_id 별 평균 likes 수 필드 우측에 붙여보기
SELECT c.checkin_id,
c.course_id,
c.user_id,
c.likes,
(
SELECT avg(likes) from checkins
WHERE course_id = c.course_id
) as course_avg
FROM checkins c
checkins 테이블에 과목별 평균 likes 수 필드 우측에 붙여보기
SELECT c.checkin_id,
c1.title,
c.user_id,
c.likes,
(
SELECT avg(likes) from checkins
WHERE course_id = c.course_id
) as course_avg
FROM checkins c
inner join courses c1 on c.course_id = c1.course_id
FROM에 들어가는 SUBQUERY
SELECT pu.user_id , pu.point, a.avg_LIKES FROM point_users pu
INNER JOIN(
SELECT user_id, ROUND(AVG(LIKES),1) AS avg_LIKES FROM checkins c
GROUP BY user_id
) a ON pu.user_id = a.USER_ID
연습
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
문자열 문법
문자열 쪼개보기 SELECT user_id , emaiL, SUBSTRING_INDEX(email,'@',1) FROM users (이메일을 @ 기준으로 첫 번째 것만 보여줘)
SELECT user_id , emaiL, SUBSTRING_INDEX(email,'@',-1) FROM users (이메일을 @ 기준으로 마지막 것만 보여줘)
문자열 일부만 출력하기 SELECT ORDER_NO, created_at, SUBSTRING(created_at,1,10) FROM orders (1~10번째 자리 까지 자르기)
케이스 문법
경우에 따라 원하는 값을 새필드에 출력
SELECT pu.user_id, pu.POINT,
(CASE WHEN pu.POINT>1000 THEN '잘 하고 있어요!'
ELSE ' 조금만 더 파이팅!' END) as msg
FROM point_users pu
통계 내기
with table1 as(
SELECT pu.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
)
SELECT a.lv, COUNT(*) as cnt from table1 a
group by a.lv
평균 이상의 포인트를 가지고 있으면 ‘잘하고 있어요’ 낮으면 ‘열심히 합시다’ 표시하기
SELECT pu.user_id,
pu.POINT,
(CASE WHEN pu.POINT > (select avg(point) from point_users) THEN '잘하고 있어요!'
else '조금만 더 화이팅' end) as msg
FROM point_users pu
이메일 도메인별 유저수 세어보기
SELECT domain, count(*) as cnt from(
SELECT SUBSTRING_INDEX(email,'@',-1) as domain from users
)a
group by domain
파이팅이 포함된 오늘의 다짐만 출력해보기 WHERE comment like '%화이팅%’
수강등록정보별 전체 강의 수와 들은 강의 수 출력해보기
SELECT a.enrolled_id, a.done_cnt, b.total_cnt from (
SELECT enrolled_id, count(*) as done_cnt from enrolleds_detail ed
WHERE done=1
group by enrolled_id
)a
inner join(
SELECT enrolled_id, count(*) as total_cnt from enrolleds_detail ed
group by enrolled_id
)b on a.enrolled_id = b.enrolled_id
위와 아래 동일
with table1 as (
SELECT enrolled_id, count(*) as done_cnt from enrolleds_detail ed
WHERE done=1
group by enrolled_id
),table2 as (
SELECT enrolled_id, count(*) as total_cnt from enrolleds_detail ed
group by enrolled_id
)
SELECT a.enrolled_id,
a.done_cnt,
b.total_cnt
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
수강등록정보별 전체 강의 수와 들은 강의 수 출력해보기+비율 넣기
with table1 as (
SELECT enrolled_id, count(*) as done_cnt from enrolleds_detail ed
WHERE done=1
group by enrolled_id
),table2 as (
SELECT enrolled_id, count(*) as total_cnt from enrolleds_detail ed
group by enrolled_id
)
SELECT a.enrolled_id,
a.done_cnt,
b.total_cnt,
round((a.done_cnt/b.total_cnt),2) as ratio
from table1 a
inner join table2 b on a.enrolled_id = b.enrolled_id
'항해99' 카테고리의 다른 글
| 23.08.09 항해 99 16기 개강준비과정 웹미니 프로젝트 1일차 (0) | 2023.08.10 |
|---|---|
| 23.08.08 항해 99 16기 개강준비과정 5일차 입학 시험 (0) | 2023.08.08 |
| 23.08.07 항해 99 16기 개강준비과정 4일차 배포 연습 (0) | 2023.08.07 |
| 23.08.05 항해 99 16기 개강준비과정 3일차 엑셀보다 쉬운 SQL 문제 풀기 (0) | 2023.08.05 |
| 23.08.03 항해 99 16기 개강준비과정 1일차 엑셀보다 쉬운 SQL 항해 1, 2주차 (0) | 2023.08.03 |