[스파르타 코딩클럽] SQL 2주차 리뷰

엑셀보다 쉬운 SQL 2주차

이번 2주차에서는 통계에 대해서 배운다. 즉 최대/최소/평균/개수 등을 구하는 방법에 대해서 다룬다.

- 데이터 분석의 목적은 쌓여있는 날것의 데이터를 의미를 갖는 '정보'로의 변환을 말한다.
- 데이터베이스 테이블에 저장된 데이터는 쌓여있는 날것의 데이터를 의미하고
- 이 중 가장 많은 Like를 받은 사람의 이름, 전체 신청자 수, 평균 연령 등이 의미있는 '정보'이다.
- 더 나아가면 '범주(catagory)' 각각의 정보가 궁금할 수 있다.
예) 과목별 신청자 평균 연령, 과목별, 신청자수, 성씨별 회원수 등.

---

• 통계를 구해보자!

(예시) 과목별 신청자 수

select count(*) from orders
where course_title = "앱개발 종합반";
select count(*) from orders
where course_title = "웹개발 종합반";

각각 총 두 개의 과목이 있으니, 두 개의 쿼리를 작성해서 구할 수 있다.

---

(예시) 이번에는 성씨 별 회원수를 구해보자!
1주차에서 쿼리로 확인 했듯이, 스파르타 회원의 성씨는 총 54개이다.

select distinct(name) from users;

---

이때 54번의 쿼리를 작성하는 것이 아닌, 동일한 범주의 데이터를 묶어주는 Group by가 있다.

• Group by란?
  동일한 범주를 갖는 데이터를 하나로 묶어서, 범주별 통계를 내주는 것을 의미한다.
  Group by를 이용하면 1) 같은 성씨의 데이터를 하나로 묶고 2) 각 성씨의 회원수를 구할 수 있다.

(예시)

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

위와 같은 쿼리를 실행하면, 아래와 같은 결과물이 나온다.

성씨별로 회원이 몇 명인지 알 수 있다.

쿼리 뜯어보기!
1. from users: users 테이블에서 데이터를 불러온다.
2. group by name: name이라는 필드에서 동일한 값을 갖는 데이터를 하나로 합쳐준다.
3. select name, count(*): 이름과 count(*)를 출력해 주는데, 여기서 count(*)는 group by로 합쳐진 데이터의 개수를 세어주는 것이다!

쿼리가 실행되는 순서: from → group by → select
1. from users: users 테이블 데이터 전체를 가져온다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐준다.
3. select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어준다.

예) 이**, 이**, 김**, 김**, 박** 이렇게 데이터가 있었다면, 이**는 2개, 김**은 2개 , 박**은 1개다.

---

(예시) 1. users 테이블 전체 불러오기

select * from users;

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

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

1주차에서 배웠던 where를 사용해서 뽑는다. 총 14개가 나온다.

---

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

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

이렇게 하면 총 14개로 동일하게 나온다!

---

• Gruop by로 통계를 내는 기능을 알아보자!
  이번에는 수강생분들의 '오늘의 다짐'이 담겨있는 checkins 테이블을 사용한다.

select * from checkins limit 10

checkins 테이블이 나온다.

- week: 수강생이 '오늘의 다짐'을 남긴 시점의 강의 주차를 의미한다.
- likes: 남긴 '오늘의 다짐' 게시물에 달린 좋아요의 수를 의미한다.

---

• 동일한 범주의 개수 구하기 count(*)

(예시) 주차별 '오늘의 다짐' 개수 구하기

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

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

(여기선, week가 범주별로 세어주고 싶은 필드명)

---

• 동일한 범주에서의 최솟값 구하기 min(필드명)

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

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

(여기서는, 범주가 담긴 필드명은 week, 최솟값을 알고 싶은 필드명은 likes.)

---

• 동일한 범주에서의 최댓값 구하기 max(필드명)

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

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

(여기서는, 범주가 담긴 필드명은 week, 최대값을 알고 싶은 필드명은 likes 이다.)

---

• 동일한 범주의 평균 구하기 avg(필드명)

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

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

(여기서는, 범주가 담긴 필드명은 week, 평균값을 알고 싶은 필드명은 likes이다.)

---

• 동일한 범주의 합계 구하기 sum(필드명)

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

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

(여기서는, 범주가 담긴 필드명은 week, 합계를 알고 싶은 필드명은 likes이다.)

---

• order by로 앞의 결과를 정렬해보기
  성씨별 회원수 데이터를 정렬해보자!

<원본 쿼리>

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

---

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

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

아까 코드에 order by count(*) 만 추가한다.
갯수 (count(*) 값)을 기준으로 정렬해달라는 뜻이다.

---

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

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

여기서의 desc는 내림차순을 의미하는 영단어 descending의 약자이다.

---

• order by는 모든 SQL쿼리에 적용될 수 있는 기능이다.

<like를 많이 받은 순서대로 '오늘의 다짐' 출력해보기>

select * from checkins
order by likes desc;

• 규칙 살펴보기!

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

이 예시에서는, like의 갯수가 정렬의 기준이 되는 필드명이다.

---

• order by 제대로 알아보기

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

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

1. from users: users 테이블 데이터 전체를 가져온다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐준다.
3. select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어준다.
   예) 이**, 이**, 김**, 김**, 박** 이렇게 데이터가 있었다면, 이**는 2개, 김**은 2개, 박**은 1개이다.
4. order by count(*): 합쳐진 데이터의 개수에 따라 오름차순으로 정렬해준다.

---

• where와 Group by, Order by 같이 해보기

< 웹개발 종합반의 결제수단별 주문건수 세어보기 >

select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method;

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

1. from orders: users 테이블 데이터 전체를 가져온다.
2. where course_title = "웹개발 종합반": 웹개발 종합반 데이터만 남겨준다.
3. group by payment_method: 같은 payment_method을 갖는 데이터를 합쳐준다.
4. select payment_method, count(*): payment_method에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어준다.
   예) CARD, CARD, kakaopay 이렇게 데이터가 있었다면, CARD는 2개, kakaopay는 1개이다.

---

• Group by 연습하기

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

[퀴즈1] 앱개발 종합반의 결제수단별 주문건수 세어보기
힌트: (1)원하는 테이블: orders (2) 범주로 사용할 필드: payment_method

select payment_method, count(*) from orders
where course_title = "앱개발 종합반"
group by payment_method;

---

[퀴즈2] Gmail을 사용하는 성씨별 회원수 세어보기
힌트: where와 like를 함께 쓰기

select name, count(*) from users
where email like '%gmail.com'
group by name;

---

[퀴즈3] course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기

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

---

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

---

• 마지막으로 2주차 숙제 해보기!
  [숙제] 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기

select  payment_method, count(*) from orders 
where email like '%naver.com' and course_title= '앱개발 종합반'
group by payment_method