[데이터 분석 부트캠프] 5주차 진행 및 학습일지

이미지 출처 : 인프런

[패스트 캠퍼스] 데이터 분석 부트캠프 5주차!! 

5주차~6주차까지는 '데이터 분석을 위한 Tool, Python'을 통해서 배운 것들을 통해 팀 프로젝트를 진행했고,
금주부터는 'SQL'에 대해서 진행하고 있는데요~  
어떤 내용들을 배웠는지 저의 솔직한 리얼 후기&반성&회고를 통해서 만나보시죠!!

---

7/14(금)에 시작한 팀 프로젝트는 주말까지 겹쳐서 거의 2주정도 된 듯한 느낌으로 진행되었습니다. 정말 미치는줄 알았네요 ㅎㅎ 간단한 프로젝트라고 했지만, 전~혀 간단하지 않는 프로젝트들^^ 팀원들이 열정이 넘쳐서 그런 걸수도 ㅎㅎ 부트캠프 기수 수강생들이 역대급인거 같기두 하구요!! 다들 정말 잘하시는 분들 밖에 없어서 좌절감도 들었지만, 제가 더 열심히 해보는 수밖에 없겠죠!!! 🔥🔥

---

[6주차 : SQL 학습 및 정리 내용] 

Select

데이터를 선택해서 가져오기

Select 데이터 from 테이블

---

Where

가져올 데이터에 조건 걸기

Select * from point_users
where name = '이**'
and point > 20000

Where 절과 같이 쓰는 문법

같지 않음  ( != )

Select * from point_users
where name != "이**"

범위( between and )

Select * from point_users
where create_date between '2020-07-12' and '2020-07-13'
'7월 12일 ~ 7월 13일 사이의 생성 날짜이다.

포함 ( in () )

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

 

패턴(문자열 규칙) - like

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

쿼리 작성 Tip

1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select from 테이블명 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 조건을 걸 필드를 찾기
5) select from 테이블명 where 조건 이렇게 쿼리 완성!

---

Limit

일부 데이터만 가져오기

select * from orders 
where payment_method = "kakaopay"
limit 5

---

Distinct

중복 데이터 제외하고 가져오기

select distinct(payment_method) from orders

---

Count

숫자 세보기

select count(*) from orders

Group by

동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것

• 쿼리가 실행되는 순서: from → group by → select

select 컬럼 from 테이블
group by 컬럼

• 범주의 개수(count) 구하기

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;

• 범주의 최소값(min) 구하기

select 범주가 담긴 필드명, max(최소값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

• 범주의 최대값(max) 구하기

select 범주가 담긴 필드명, max(최대값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

• 범주의 평균(avg) 구하기

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

• 범주의 합계(sum) 구하기

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

Group by 작성 Tip

3가지만 기억하기
1) 원하는 테이블과
2) 범주로 사용할 필드
3) 범주에 따라 통계를 계산하고 싶은 필드 (개수의 경우 제외)

---

Order by

특정 기준을 순서대로 정렬하기

• 쿼리가 실행되는 순서: from → group by → select → order by

select * from 테이블명
order by 정렬의 기준이 될 필드명;

• 오름차순 정렬하기 (default)

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

• 내림차순 정렬하기 (desc = descending)

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

쿼리 작성 Tip

1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기
5) 범주별로 통계를 보고싶은 필드를 찾기
6) SQL 쿼리 작성하기!

---

Alias

별칭 붙이기

• 테이블에 별칭 붙이기

select * from orders o
where o.course_title = '앱개발 종합반'

• 필드에 별칭 붙이기 (as)

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method

Subquery 유형

Where 절

• Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용

where 필드명 in (subquery) 

• 예시
  카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력

select * from users u
where u.user_id in (select o.user_id from orders o 
					where o.payment_method = 'kakaopay');

• 쿼리가 실행되는 순서
  1) from 실행: users 데이터를 가져와줌
  2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
  3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
  4) 조건에 맞는 결과 출력

Select 절

• 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
• 예시
  '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 확인하기

(1) 좋아요 평균 구하기

select avg(likes) from checkins c2
where c2.user_id = '4b8a10e6'

(2) Subquery로 활용하기

select c.checkin_id, c.user_id, c.likes, 
	(select avg(likes) from checkins c2
	where c2.user_id = c.user_id) as avg_like_user
from checkins c;

• 쿼리가 실행되는 순서
  1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
  2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
  3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
  4) 함께 출력해준다!
  * 서브쿼리는 항상 별칭을 정해줘야함!! 

From 절

• 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하
• 예시
  (1) 유저 별 좋아요 평균

select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

(2) 해당 유저 별 포인트

select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_like from checkins
	group by user_id
) a on pu.user_id = a.user_id

쿼리가 실행되는 순서
1) 먼저 서브쿼리의 select가 실행되고,
2) 이것을 테이블처럼 여기고 밖의 select가 실행!

---

With 절

• with 절로 더 깔끔하게 쿼리문을 정리하기

With 테이블명 as (테이블 코드), 테이블명 as (테이블 코드)

• 예시

(1) Subquery 절

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

(2) 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

---

실전에서 유용한 SQL 문법

SUBSTRING_INDEX

• 특정 문자를 기준으로 문자열 가져오기
• ex 1) @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져와!

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

• ex 2) @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져와!

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

SUBSTRING

• 문자열 일부만 추출하기
• ex 1) SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

select order_no, created_at, substring(created_at,1,10) as date from orders

• 예시 2

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date

---

Case

• 조건문

case when 조건1 then
else 조건2
end

• ex 1) 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

select pu.point_user_id, pu.point,
case 
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;

• ex 2)  Subquery를 이용하여 통계내기

(1) Case 사용

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

(2) Subquery 사용

select level, 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 lv

(3) With 정리

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 level, count(*) as cnt from table1
group by lv

 

---

네 벌써 한달이 넘은 기간동안 부트캠프를 수강했네요~!! 벌써 한여름의 점정에 온 것 같습니다,, 날이 너무 더워요!!
지치지 않고 이 여름을 열정적이게 보낸다면 이번 연말에는 뿌린 씨를 잘 거둘 수 있겠죠~?? 오늘 하루도 열심히 해서 연말을 위해 노력해보겠습니다 :) 우리 모두 무더운 여름 지치지 말고, 힘내봅시다~~ 🔥🔥 🔥🔥 🔥🔥 🔥🔥