const min = function(배열){ let output = 배열[0] for (let value of 배열){ if (output > value){ output = value } } return output } console.log(min([52,273,32,103,275,24,57]))
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
문자열 쪼개기
[이메일에서 아이디만 가져와보기]
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
[이메일에서 도메인만 가져와보기]
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
[포인트 보유액에 따라 다르게 표시해주기]
select pu.point_user_id, pu.point, (casewhen 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( 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 a.lv
또는
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 a.lv, count(*) as cnt from table1 a group by a.lv
select * from point_users left join users on point_users.user_id = users.user_id
[유저데이터로 Inner Join 이해해보기]
select * from users u inner join point_users p on u.user_id = p.user_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.title
[많은 포인트를 얻은 순서대로 유저 데이터 정렬해서 보기]
select * from point_users p inner join users u on p.user_id = u.user_id order by p.point desc
[네이버 이메일 사용하는 유저의 성씨벌 주문건수 세어보기]
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
3-3강 퀴즈
1) 결제 수단 별 유저 포인트의 평균값 구해보기
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
2) 결제하고 시작하지 않은 유저들을 성씨별로 세어보기
select name, count(*) as cnt_name from enrolleds e inner join users u on e.user_id = u.user_id where is_registered = 0 group by name order by cnt_name desc
3) 과목 별로 시작하지 않은 유저들을 세어보기
select c.course_id, c.title, count(*) as cnt_notstart from courses c inner join enrolleds e on c.course_id = e.course_id where is_registered = 0 group by c.course_id
4) 웹개발, 앱개발 종합반의 week 별 체크인 수를 세어보기
select c1.title, c2.week, count(*) as cnt from checkins c2 inner join courses c1 on c2.course_id = c1.course_id group by c1.title c2.week order by c1.title, c2.week
5) 연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내기
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
3-5강
1) Left join에서 Null값을 제외하고 뽑기
select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL group by name
2) 7월10일 ~ 7월19일에 가입한 고객 중, 포인트를 가진 고객의 숫자, 전체 숫자, 그리고 비율보기
select count(point_user_id) as pnt_user_cnt, count(*) as tot_user_cnt, round(count(point_user_id)/count(*),2) as ratio from users u left join point_users pu on u.user_id = pu.user_id where u.created_at between '2020-07-10' and '2020-07-20'
3-6강
union all 문법
( select '7월' as month, c.title, c2.week, count(*) as cnt from checkins c2 inner join courses c on c2.course_id = c.course_id inner join orders o on o.user_id = c2.user_id where o.created_at < '2020-08-01' group by c2.course_id, c2.week order by c2.course_id, c2.week ) union all ( select '8월' as month, c.title, c2.week, count(*) as cnt from checkins c2 inner join courses c on c2.course_id = c.course_id inner join orders o on o.user_id = c2.user_id where o.created_at > '2020-08-01' group by c2.course_id, c2.week order by c2.course_id, c2.week )
