4-1. 배울 것
- subquery
- with
- 문자열 가공
- case when
4-2. Subquery
select u.user_id, u.name, u.email from orders o
inner join users u on o.user_id = u.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'
)
4-3.Subquery 연습
- where
select * from point_users pu
where point > (
select round(avg(point),0) as avg_point 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 pu.user_id = u.user_id
where u.name = '이**'
)
select * from point_users pu
where pu.point > (
select avg(point) from point_users pu
where user_id in (select user_id from users where name = '이**')
)
- select
select c.checkin_id, c.course_id, c.user_id, c.likes,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
select c.checkin_id,
c.course_id,
c.user_id,
c.likes,
(
select title from courses
where course_id = c.course_id
) as title,
(
select round(avg(likes),1) from checkins
where course_id = c.course_id
) as course_avg
from checkins c
- inner join된 필드 모두 보고싶을 때 select에 alias.* 추가
select c.checkin_id,
c1.title,
c.user_id,
c.likes,
(
select round(avg(likes),1) 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 / inner join (가장 많이 사용)
- A inner join B inner join C 가능
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
4-4. 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
4-5. 문자열
- 문자열 쪼개기 : substring_index(필드, 기준, 에서 몇번째 보여줘)
** string : 문자열
select user_id, email, substring_index(email, '@', 1) as domain_id from users
select user_id, email, substring_index(email, '@', -1) as domain from users
select user_id, email, substring_index(email, '@', 2) from users
select user_id, email, substring_index(email, '@', 3) from users
- 문자열 일부만 출력 : substring(필드, 시작포인트, 그다음부터 몇자)
select order_no, created_at, SUBSTRING(created_at,1,10) as date from orders
- 조건을 걸면서 group by 할 수 있다.
select substring(created_at, 1, 10) as date, count(*) from orders
group by date
4-6. case
select user_id, point,
(case when point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
from point_users pu
with table1 as (
select user_id, point,
(case when point > 10000 then '1만 이상'
when 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
4-7.퀴즈
- case when은 select 문에 쓴다.
select pu.user_id,
pu.point,
(case when point >= (select avg(point) from point_users) then '잘 하고 있어요'
else '열심히 합시다!' end) as msg
from point_users pu
select substring_index(email,'@',-1) as domain, count(*) as cnt from users
group by domain
select * from checkins c
where comment like '%화이팅%'
- with절을 포함할 때는 전체 선택해서 실행해야 한다.
with table1 as (
select enrolled_id, count(*) as done_cnt from enrolleds_detail
where done = 1
group by enrolled_id
), table2 as (
select enrolled_id, count(*) as total_cnt from enrolleds_detail
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
select enrolled_id,
sum(done) as done_cnt,
count(*) as total_cnt,
round((sum(done)/count(*)),2) as ratio
from enrolleds_detail
group by enrolled_id
- 여기서 select 절에 done_cnt/total_cnt라고 하면 안된다.(왜지??)
