클라이밍하는 개발자

https://teamsparta.notion.site/SQL-60c2a83eecdb473887ba73a75bd1c053

SQL 전체 강의자료

[수업 목표]

1. Subquery(서브쿼리)의 사용 방법을 배워본다
2. 실전에서 유용한 SQL 문법을 더 배워본다
3. SQL을 사용하여 실전과 같은 데이터분석을 진행해본다

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

문자열 쪼개기

[이메일에서 아이디만 가져와보기]

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,
(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(
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

3-2강

[Joint을 사용해서 Key값으로 두테이블 연결해보기]

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
)

2-2강

[성씨별 회원수를 Group by로 쉽게 구해보기]

select name, count(*) from users
group by name;

[users 테이블 전체 불러오기]

select * from users;

[users 테이블에서 '신'씨를 가진 데이터만 불러와서 개수 살펴보기]

select * from users 
where name = "신**";

[group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지 살펴보기]

select name, count(*) fro[m users
group by name;

2-3강

[주차별 '오늘의 다짐' 개수 구하기]

select week, count(*) from checkins
group by week;

[주차별 '오늘의 다짐'의 좋아요 최소,최대,평균,합계값 구하기]

select week, min(likes) from checkins
group by week;

select week, max(likes) from checkins
group by week;

select week, avg(likes) from checkins
group by week;

select week, sum(likes) from checkins
group by week;

[원본 쿼리 살펴보기]

select name, count(*) from users
group by name;

[결과의 개수 오름차순으로 정렬해보기]

select name, count(*) from users
group by name
order by count(*);

[결과의 개수 내림차순으로 정렬해보기]

select name, count(*) from users
group by name
order by count(*) desc;

2-4강

없음

2-5강

쿼리 작성하는 꿀팁!

1) show tables로 어떤 테이블이 있는지 살펴보기

2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기

3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기

4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기

5) 범주별로 통계를 보고싶은 필드를 찾기

6) SQL 쿼리 작성하기!

3강

[스파르타 데이터베이스의 테이블 보기]

show tables;

[orders 테이블의 특정 필드만 가져와보기]

select created_at, course_title, payment_method, email from orders;

[orders 테이블의 데이터가져와 보기]

select * from orders;

4강

[같지 않음 조건 걸어보기]

select * from orders
where course_title != "웹개발 종합반";

[범위 조건 걸어보기]

select * from orders
where created_at between "2020-07-13" and "2020-07-15";

[포함조건 걸어보기]

select * from checkins 
where week in (1, 3);

[패턴(문자열규칙) 조건 걸어보기]

select * from users 
where email like '%daum.net';

나오는 수량 제한하기 [방대한 데이터 양으로 인한 렉방지]

limit 5

중복제거하기 [종류찾기]

select distinct(payment_method) from orders

수량세기

select count(*) from orders

중복제거하고 수량세기

select count(distinct(name)) from orders