오늘은 3주차 및 4주차 내용을 공부를 했었는데 서브 쿼리 및 활용을 하는 방법 inner join문 등을 중점적으로 하였다.
역시 DML 위주로 데이터를 뽑아서 원하는 데이터를 가져오는 연습을 했지만 4주차 부터는 조금씩 집중력이 풀려서 인지 아니면 SQL 위주로 공부를 많이 안해서 안익숙해서? 인지는 모르겠지만 조금 어려웠다. 이해하는 것은 그렇게 어렵지 않지만 안보고 문제를 풀라하면 완전히 YES라고 말하기는 어렵다. 그래서 전체적인 구문을 정리를 할려고 한다.
Left Join
A데이터와 B데이터의 이름이 같은 컬럼을 가져와서 붙힌다고 생각을 해보자.....
select * from users u
left join point_users p
on u.user_id = p.user_id;
Inner Join
select * from users u
inner join point_users p
on u.user_id = p.user_id;또다른 join의 예를 보자 조인은 하나의 집합이라고 생각해야한다.
inner join은 예시를 들자면
enrolleds 라는 테이블에서 courses 테이블에 존재하는 코스_id를 서로 공통된 데이터를 들고 와서 특정 데이터의 갯수 등을 가져 올 때 쓸 수 있다.
select * from enrolleds e
inner join courses c
on e.course_id = c.course_id이런 식으로 서로 테이블의 교집합으로 연결을 시켜준다고 생각하면 편하다.
응용 예제
select co.title, count(co.title) as checkin_count from checkins ci
inner join courses co
on ci.course_id = co.course_id
group by co.titlecourses 테이블과 checkins 테이블의 코스_id으로 연결을 시켜주고 co 테이블 안의 title 즉 공통된 연결한 테이블의 코스_id와 같은 title의 갯수를 카운트 해주고 as으로 checkin_count 라는 이름으로 컬럼을 붙혀서 출력을 해주는 것이다.
이번에는 특정 컬럼의 이름이 끝나는 녀석을 출력하는 예제를 보자
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
우선 orders라는 테이블을 users테이블로 이너 조인을 하고 그에 대한 조건은 유저_id으로 조인을 시켜준다.
where은 이전에 조건문이라고 했었다. naver.com으로 끝나는 lke로 또다른 조건을 줬으니
users 테이블 안에서 name이라는 컬럼을 가진 녀석이 이메일이 naver.com인 유저를 찾는 구문이다.
조금 심화 예제
select o.payment_method, round(AVG(p.point)) from point_users p
inner join orders o
on p.user_id = o.user_id
group by o.payment_method
point_users라는 테이블을 orders으로 조인을 시키고 서로 같은 유저_id에서 본다.
payment_method를 출력하고 point_users테이블의 포인트의 평균을 결제별 방법으로
구분을 해서 출력을 해준다.
심화 예제
select 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
courses 테이블에서 checkins 테이블과 조인을 시키고 서로 코스_id가 같은 녀석과
오더스 테이블에서 checkins 테이블의 유저_id와 오더스 테이블의 유저_id 가 같은 녀석과
where은 조건 : 오더스 테이블에서 날짜가 2020-08-01 이상은 녀석을 카운트해서 보여준다.
Union을 정리 해볼까...???................
유니언은 그냥 합집합이다. 그냥 서로의 테이블을 싸그리 합해준다. 그게 전부이다. 이렇게 생각하는게 속 편하더라
(첫 번째 테이블의 SELECT문) UNION (두 번째 테이블의 SELECT문)
자 이제 오늘의 메인이다. 서브쿼리.....
서브쿼리는 중간에 내가 특정한 조건을 주는 구문을 넣거나 뽑아주고 싶고 중간에 속속 넣을 수 있다?
이새끼 뭔 개소리를 갑자기 하지?? 이럴 수 있다. 백문의 불어 머시기?? 뭐였지?? 암튼 예제를 보자
집어치우고
요런 방법으로 where 조건을 주고 다음 in 을 준다음 다시 쿼리문을 짜서
오더스 테이블의 payment_method가 카카오페이이면 그에 해당하는 user_id를 불러오고 users 테이블의
user_id의 값과 같다면???
느낌이 안오는가 ?? 그렇다면
그렇다면 요런 느낌으로 다가 쓸 수 있다. 그러니까.. 흠
checkins 테이블에서 불러는 올건데 그 와중에 평균 좋아요를 알고 싶다.
as 으로 avg_like_user라고 컬럼 이름을 지어주고 checkins 테이블에서 c2와 c1의 유저_id가 같은 놈으로 한정을 해서
좋아요 평균을 내는 놈을 select 문과 from문 사이에 찍어 줄 수 있는 것이다. 굉장하지 않은가??(억지억지)
그렇다면 다른 서브 쿼리
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);point_users 테이블에서 pu라는 이름으로 포인트를 비교를 하는데
그 다음 서브 쿼리로 point_users테이블에서 pu2라는 이름으로 평균을 가저와서 비교를 하는데
점수 자체가 서브 쿼리에서 뽑은 평균 값보다 큰 녀석 데이터만 골라서 전부 가져오는 예제 이다.
이제는 감이 슬슬 오는가???
자 그러면 다음은 이해하기 편할걸??
select * from point_users pu
where pu.point >
(select avg(pu2.point) from point_users pu2
inner join users u
on pu2.user_id = u.user_id
where u.name = "이**");
select checkin_id, course_id, user_id, likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c;위의 결과 처럼 찍어내는 녀석의 이름은 checkin_id, course_id, likes 이고 course_avg를 뽑아내는 것이다
checkins의 c2으로 좋아요의 평균을 다음으로 서브 쿼리로 뽑아낸다는 뜻이고
코스 아이디가 같은 녀석으로 한정으로해서 checkins 테이블에서 가져오는 것이다.
평균을 구해주는 코드
select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
(
select course_id, count(*) as cnt_total from orders
group by course_id
) a
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
) b
on a.course_id = b.course_id결과
코스_id에 cnt_total이라는 이름으로 갯수를 새주고
아래 조인문에서 서브 쿼리 안에서 유저_id 를 중복 없이 cnt_checkins라는 이름으로 상위 select문에서 토탈에서 체크인을 나눈 비율로 ratio라는 이름으로 출력하는 예제이다.
흐............................이제 다왔다. with은 생략 너무 힘드네
일단 더 중요한 놈 부터
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users요 자식은 substring_index라는 것을 이용해서 유저 테이블에 존재하는 email에 앞부분을 가져옴
반대
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users뒷 부분 도매인 naver.com gmail.com 등등 짤라서 가져온다 생각하면 된다.
또 다른 중요한 요 녀석
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
point_users라는 테이블에서 1만 이상이면 그것에 level이라는 컬럼 이름 밑에 그 해당 범위에 맞게
적어준다.
select level, count(*) as cnt from (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
) a
group by level
with table1 as (
select pu.point_user_id, pu.point,
case
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu
)
select level, count(*) as cnt from table1
group by levelwith 절로 간결화 화면 이렇게도 나타 낼 수 있다.
도메인별 유저의 수를 세어보자
select SUBSTRING_INDEX(u.email,"@", -1 ) as domain,
COUNT(*) as cnt_domain
from users u
group by domain내가 적은 구문이다
스파르타에서 준 구문은??
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain요렇게 해준단다.
마지막 복습 차원에서 with 문을 정리하기 좋은 예제는 이것 인것 같다.
with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed
group by enrolled_id
)
select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id
3주차 까지는 강의가 쉬웠다 4주차 부터는 쿼리를 많이 안짜서?? 익숙하지 않아서 바로바로 생각은 나지 않았다.
하지만 강의를 본 것으로 끝내는 것이 아니라 강의 자료안 예제 문제를 한번 다시 풀어보고 다르게 접근 하는 방법도
시간이 나중에 난다면 짬짬히 하는 것도 굉장히 좋을 것으로 생각이 된다.