subquery
쿼리안의 쿼리
where 필드명 in (subquery) 이런 방식으로요!
실행순서
(1) from 실행: users 데이터를 가져와줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력
select 필드명, 필드명, (subquery) from ..
실행순서
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!
from 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때
실행순서
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!
각 항목을 어떻게 산출할수 있는가 --서브쿼리
얻어진 결과는 어떻게 연결할수 있나
Subquery 연습해보기 (where, select)
전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu
where pu.point > (select avg(pu2.point) from 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 = "이**");
checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
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;
checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select checkin_id, c3.title, user_id, likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3
on c.course_id = c3.course_id;
Subquery 연습해보기 (from, inner join)
[준비1] course_id별 유저의 체크인 개수를 구해보기!
[준비2] course_id별 인원을 구해보기!
[진짜 하고 싶은 것] course_id별 체크인 개수에 전체 인원을 붙이기
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
[한 걸음 더] 퍼센트를 나타내기
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
[반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!
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
실전에서 유용한 SQL 문법 (문자열)
substring_index
substring
이메일에서 아이디만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
orders 테이블에서 날짜까지 출력하게 해보기
select order_no, created_at, substring(created_at,1,10) as date from orders
일별로 몇 개씩 주문이 일어났는지 살펴보기
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
실전에서 유용한 SQL 문법 (Case)
포인트 보유액에 따라 다르게 표시해주기
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
우선 몇 가지로 구분을 나누고,
select pu.point_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
서브쿼리를 이용해서 group by로 통계를 낼 수 있습니다.
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 lv
from point_users pu
) a
group by lv
with 절과 함께하면 금상첨화죠!
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 lv
from point_users pu
)
select level, count(*) as cnt from table1
group by lv
