select count(pu.user_id)as pnt_user_cnt,
count(u.user_id)as tot_user_cnt,
round(count(pu.user_id)/count(*),2) as ratio
from users u
left join point_users pu on pu.user_id = u.user_id
where u.created_at BETWEEN "2020-07-10" and "2020-07-20"
힌트1 → count 은 NULL을 세지 않는답니다!
힌트2 → Alias(별칭)도 잘 붙여주세요!
힌트3 → 비율은 소수점 둘째자리에서 반올림!
6. 결과물 합치기 Union
Select를 두번 해야할 상황이 있어 결과를 한번에 보고 싶을 때 사용합니다.
(
select '7월' as month, 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
)
union all
(
select '8월' as month, 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
)
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 like '이%')
SELECT checkin_id,
course_id,
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
checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
SELECT c.checkin_id,
cs.title,
c.user_id,
c.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 cs
on cs.course_id = c.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
5. With 절
서브쿼리를 사용하다 보면 헷갈리고 보기 힘들때 사용합니다. (보기 좋게)
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 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
사용법 : with 별칭 as (서브쿼리)
6. 문자열
문자열 쪼개기 ( SUBSTRING_INDEX )
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
SUBSTRING_INDEX(필드명, '@'를 기준으로, 첫번째(앞) 조각을 표시한다.
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
SUBSTRING_INDEX(필드명, '@'를 기준으로, 마지막(뒤) 조각을 표시한다.
문자열 일부만 출력하기 ( SUBSTRING )
SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
select order_no, created_at, substring(created_at,1,10) as date from orders
SUBSTRING(필드명(문자열),첫 글짜는 첫 번째 글자, 첫번째 글자부터 10개의 글자 출력)
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
7. 조건문 Case
조건에 따라 원하는 값을 출력하고 싶다면 Case
pu.point 가 10000보다 클 때 '잘 하고 있어요!' 출력, else(아니라면) '조금 더 달려주세요!' 출력, 마침(End) as '구분'
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;
