데이터 분석 기초 : SQL 테이블 검색(1~3주차)

1주 차 강의

select DISTINCT(payment_method) from orders : 테이블에서 특정 칼럼의 내용을 중복제거하고 보여주기

select * from orders

where payment_method!= 'kakaopay' : 주문테이블에서 특정 결제 수단과 상이한 내역들만 보여주기

select * from users

limit 2 : table에서 리스트 2개만 보여주기

. 1주 차 숙제 : naver 이메일을 사용하면서, 웹개발 종합반을 신청했고 결제는 kakaopay로 이뤄진 주문데이터 추출하기

(3가지 조건 검색해서 보여주기)

select * from orders

where email like '%naver.com'

and course_title = '앱개발 종합반'

and payment_method = 'kakaopay'

주문테이블 3가지 조건 검색 결과

2주 차 강의

select week, round(avg(likes),1), max(likes), min(likes) from checkins

group by week : 특정 item group 별로 통계 보여주기

select name, count(*) from users : group item과 통계를 보여주기 item으로 설정해야 무슨 내용인지 확인이 가능합니다.

group by name

select payment_method, COUNT(*) from orders

where course_title = '웹개발 종합반'

group by payment_method

order by count(*) ASC #Desc : group item으로 정렬해서 보여주기

. 2주 차 숙제 : 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기

select payment_method, count(*) as cnt from orders o

where o.email like '% naver.com' and course_title = '앱개발 종합반'

group by payment_method

결재수단 별 주문 건수 검색 결과

3주 차 강의

# 두 테이블의 공통된 정보 (key값)를 기준으로 테이블을 연결해서 한 테이블처럼 보는 것을 의미합니다.

select * from users u

#left join point_users p on u.user_id = p.user_id #null value도 포함 왼쪽으로 붙임

inner join point_users p on u.user_id = p.user_id #교집합

select * from orders o

inner join users u on o.user_id = u.user_id #inner join으로 시작

select * from checkins c

inner join users u on c.user_id = u.user_id

select * from enrolleds e

inner join courses c on e.course_id = c.course_id

select c1.course_id, c2.title, count(*) as cnt from checkins c1

inner join courses c2 on c1.course_id = c2.course_id

group by c1.course_id #Group by 사용 시 select statement에 칼럼정보를 추가해야 해석이 가능합니다.

select pu.user_id, u.name, u.email, pu.point from point_users pu

inner join users u on pu.user_id = u.user_id

order by pu.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 o.email like '% naver.com'

group by u.name

select o.payment_method, round(avg(pu.point),0) from point_users pu

inner join orders o on pu.user_id = o.user_id

group by o.payment_method

select u.name, count(*) as cnt from enrolleds e

inner join users u on e.user_id = u.user_id

where e.is_registered = 0

group by u.name

order by cnt desc

select c.course_id, c.title, count(*) as cnt_not_start from courses c

inner join enrolleds e on c.course_id = e.course_id

where e.is_registered = 0

group by c.course_id

order by cnt_not_start desc

select c1.title, c2.week, count(*) as cnt from courses c1

inner join checkins c2 on c1.course_id = c2.course_id

group by c1.title , c2.week

order by c1.title, c2.week

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

select u.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 u.name

select count(pu.point_user_id) as pnt_user_cnt,

count(u.user_id) as tot_user_cnt,

round(count(pu.point_user_id) / count(u.user_id),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'

. 2개의 검색 결과를 합치는 기능

select count(pu.point_user_id) as pnt_user_cnt,

count(u.user_id ) as tot_user_cnt,

round(count(pu.point_user_id) / count(u.user_id ),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'

(

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 e.enrolled_id,

e.user_id,

count(*) as max_cnt

from enrolleds e

inner join enrolleds_detail ed on ed.enrolled_id = e.enrolled_id

where ed.done = 1

GROUP by e.enrolled_id, e.user_id

order by max_cnt DESC