-- 문자열 데이터 활용해보기
-- substring_index활용 하기
select user_id , email, SUBSTRING_INDEX(email, '@', 1) from users
-- email에서 '@'를 기준으로 나눠서 1번째 것을 보여줘
select user_id , email, SUBSTRING_INDEX(email, '@',-1) from users
-- email에서 '@'를 기준으로 나눠서 마지막 것을 보여줘
-- 문자열 일부만 출력하기
select order_no , created_at , SUBSTRING(created_at,1,10) as date from orders
-- created_at을 1번째부터 n글자까지 자를것인가, 시작위치 마지막위치를 숫자로 표현해줌~
select SUBSTRING(created_at,1,10) as date, count(*) from orders
group by date
-- SUBSTRING()활용해서 날짜별로 몇개 주문 했는지 확인하기
-- CASE문에서 활용해보기
-- 경우에 따라서 출력해줌
select pu.user_id , pu.point,
(case when pu.point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as 문구
-- case when ~~ then -> ~~가 ~~라면 을 표기
-- else ~~ end 아니라면 ~~을 표기하고 end
from point_users pu
-- 서브쿼리를 같이 활용해서 통계를 내보기
select a.lv, count(*) as cnt from (
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
) a
group by a.lv
-- from 절에다가 넣어서 활용 해봤다 이제 with절 까지 사용해보자!
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.point_user_id , pu.point ,
(case when pu.point > (select avg(point) from point_users) then '잘 하고 있어요!'
else '열심히 합시다!' end
) as msg
from point_users pu
-- 퀴즈2 이메인 도메인별 유저수 세어보기
select SUBSTRING_INDEX(email,'@',-1) as domain, count(*) from users
group by domain
-- 퀴즈3 코멘트에 '화이팅' 이 들어가있는 것만 출력하기
select user_id , comment from checkins
where comment like '%화이팅%'
-- 퀴즈4 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
with table1 as (
select ed.enrolled_id, COUNT() as done_cnt from enrolleds_detail ed
where done = 1
group by ed.enrolled_id
), table2 as(
select enrolled_id ,count() as total_cnt from enrolleds_detail
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
