스파르타코딩클럽 SQL 4주차로
드디어 완강을 했습니다.
4주간의 기간이지만, 3주정도만에
다 강의를 들을 수 있었는데요.
이마저도 좀더 부지런히 들었다면
2주만에도 다들을수있는 그런 난이도였습니다.
스파르타코딩클럽 SQL 4주차에서는
기존에 배웠던 쿼리문들을
활용해서 계속 연결해서
새로운 데이터들을 뽑아내는 문제풀이를
하면서 학습을 진행했습니다.
두개까지합치는건 괜찮았는데,
3개이상이라던가 새로운 테이블을
만들어서, 데이터를 추출한다던가
이런부분은 쿼리가 길어지다보니 약간의
이해할만한 시간이 필요하더라구요.
그래도 파이썬에 비하면 크게 어렵지않게
수강을 마칠수 있었습니다.
언제든지 다시볼수있는, SQL문법 총정리
포스팅도 다시한번 해봐야겠습니다.
SQL 반복 복습도하고, 이제는 파이썬을
수강할 차례네요. 다시한번 엄청난 오류의
난관으로 들어갈 준비... 해야겠죠..
[새로운 테이블을 만들고 서브쿼리로 넣어서 inner join하기]
SELECT pu.user_id ,pu.point, a.avg_likes FROM point_users pu
inner join (
SELECT c.user_id, round(avg(c.likes),1) as avg_likes FROM checkins c
group by c.user_id
) a on pu.user_id =a.user_id
[포인트 평균이상인 유저정보추출하기]
SELECT * from point_users pu
WHERE point > (
SELECT round(avg(pu.point),1) from point_users pu
)
[이씨성을 가진 유저의 평균보다 큰 유저 데이터추출]
SELECT * FROM point_users pu
where pu.`point` > (
SELECT avg(pu.`point`) FROM point_users pu
inner join users u on u.user_id =pu.user_id
where u.name ='이**'
)
SELECT c1.checkin_id ,c1.course_id ,c1.user_id ,c1.likes ,a.avg_likes from checkins c1
inner join (SELECT c1.course_id,avg(c1.likes) as avg_likes FROM checkins c1
inner join courses c2 on c2.course_id = c1.course_id
group by c1.course_id
) a on a.course_id = c1.course_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
SELECT * from courses c
[with로 as처럼 만들기]
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
[SUBSTRING_INDEX ; 뭔가를 기준으로 쪼갤때]
SELECT user_id , email, SUBSTRING_INDEX(email,'@',1),SUBSTRING_INDEX(email,'@',-1) from users u
[SUBSTRING ; 글자 자르기]
SELECT created_at,SUBSTRING(created_at,1,10) as date, count(*) from orders o
group by date
[Case 문법 ; 포인트가 1만넘으면 good, 아니면 bad ]
SELECT pu.user_id ,pu.point,
( case when point >10000 then 'Good'
else 'bad' end ) as msg
FROM point_users pu
[평균이상 ; 서브쿼리]
select pu.user_id, pu.point,
(case when pu.point > (select avg(pu.point) from point_users pu ) then 'GOOD'
else 'BAD' end) as msg
from point_users pu
[도메인별 갯수]
SELECT SUBSTRING_INDEX(email,'@',-1) as user_domain,count(*) from users u
group by user_domain
[화이팅이 포함된..]
SELECT * from checkins c
where comment like '%화이팅%'
[done한 강의수랑 전체강의수, 그리고 진도율]
SELECT a.enrolled_id, done_cnt, total_cnt from (SELECT ENROLLED_ID, count(*) as total_cnt from enrolleds_detail ed
group by enrolled_id
) a
inner join (SELECT enrolled_id, count(*) as done_cnt FROM enrolleds_detail ed
where done = '1'
group by enrolled_id
) b on a.enrolled_id =b.enrolled_id
[위에 거에, 강의 진도율까지 추가하기 +with사용]
with table1 as (SELECT ENROLLED_ID, count(*) as total_cnt from enrolleds_detail
group by enrolled_id ),
table2 as (SELECT enrolled_id, count(*) as done_cnt FROM enrolleds_detail
where done = '1'
group by enrolled_id )
SELECT a.enrolled_id, done_cnt, total_cnt, round(done_cnt/total_cnt,2) as ratio from table1 a
inner join table2 b on a.enrolled_id =b.enrolled_id
