SQL 개발일지 (2) - 데이터를 활용하여 통계 구하기

데이터 분석의 목적: 쌓여있는 날것의 데이터를 <의미를 갖는 '정보'>로 변환시키는 것

- 데이터베이스 테이블에 저장된 데이터: 쌓여있는 날것의 데이터
- 가장 많은 like를 받은 사람의 이름, 전체 신청자 수, 평균 연령: 의미있는 '정보'

여기서 더 나아가면 '범주(category)' 각각의 정보가 필요한 상황이 있을 수 있는데,
(예를 들어 과목별 신청자 평균 연령, 과목별 신청자 수, 성씨별 회원 수 등)
2주차 강의에서는 이 데이터들을 묶는 방법에 대해 배워볼 것!

지금까지는 select count를 두 번 써서 따로따로 데이터를 추출했었다면, 한 번에 묶어서 볼 수 있는 문법을 배울 차례.

group by: 동일한 범주의 데이터를 묶어줌
order by: 깔끔하게 데이터를 정렬

 

 

[Group by]
성씨별로 몇 명의 회원이 있는지 
select name, count(*) from users
group by name

 

select * from users 인데,
이걸 group by name으로 묶겠다.
-> name과 count(*)를 보고싶은 것.

 

그래서 select 뒤엔 name, count(*)가 들어가고, 그걸 name으로 묶은 결과 데이터를 볼 수 있게 추출!

(실행되는 순서에 대해 이해하는 게 중요)

 

avg(평균) 구할 시 소수점으로 나오게 되는데, round 함수를 활용하여 소수점 몇 번째 자리까지 나오게 할건지를 정할 수 있다.

Ex. avg(likes)의 결과값이 2.2242라면, round(avg(likes),2)를 입력하여 2.22까지만 나오게 만들 수 있다.

 

* 평균 avg 합계 sum 최소값 min 최대값 max 

 

 

[Order by]

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

이 밑에
order by count(*)
를 입력하면

오름차순(기본, asc)으로 정렬이 된다.
내림차순으로 하고 싶으면 뒤에 desc를 붙여주면 된다.

 

Ex. like(좋아요)가 가장 많은 순으로 정렬해서 보여줘!

select * from checkins
order by likes desc

 

실행순서:
1. users에서 name으로 묶었다.
2. name, count(*)를 출력했다.
3. 정렬은 다 뽑고 나서 맨 마지막에 order by로 한다.

참고로 세미콜론(;)은 안 붙여도 상관 없지만 우리가 여러 가지 쿼리들을 썼을 때 '이 쿼리가 여기서 여기까지가 끝이다'를 알려주는 표식 정도로 이해하면 됨.

 

---

where와 group by를 어떻게 같이 쓰고, order by는 어떻게 붙이면 되는지!?

 

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

* select 뒤에 payment_method가 없어도 상관은 없지만, 그럼 어떤 결제수단이 몇 개인지 확인할 수 없기 때문에 붙여주는 것.

 

먼저 <결제수단별 주문건수 세어보기> 부터 해보면,

select * from orders 테이블을 먼저 불러오고, 
group by payment_method 로 묶어줌.
-

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

 

실행순서:

1. orders 테이블을 course_title 웹개발 종합반으로 잘라서

2. 그걸 가지고 payment_method로 묶어서

3. 묶은 것을 통계내고(맨윗줄)

4. 맨 마지막에 order by를 사용하여 정렬한다.

---

자주 나는 에러!

select * from orders //Ctrl + Enter
group by payment_method

이렇게 명령하면 내가 원했던 결과가 아니라 한 줄씩만 나옴. 묶긴 묶었는데 뭘 통계내라고 하는지 써주지 않았기 때문.

select count(*) from orders

이렇게 고친다고 하면 payment_method라고 하는 범주값이 출력되지 않아서 어떤 데이터인지 분석이 안 됨.


1. 에러 메시지가 나도 당황하지 않고 그 메시지를 읽어볼 것
2. 원하는 결과가 아니더라도 그 이유를 생각해보고, 수정해보기!


order by & group by 연습해보기 :)

[order by]

문자열을 기준으로 정렬해보기
select * from users
order by email

이름 기준으로 내림차순 정렬해보기
-> order by name desc

시간으로 정렬하려면? (처음->최근)
-> order by created_at

Quiz:
1. 앱개발 종합반의 결제수단별 주문건수 세어보기
select payment_method, count(*) from orders
where course_title = '앱개발 종합반'
group by payment_method 

2. gmail을 사용하는 성씨별 회원수 세어보기

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

3. course_id별 '오늘의 다짐'에 달린 평균 like 갯수 구해보기

SELECT avg(likes), course_id, count(*) from checkins
group by course_id 

SELECT course_id, round(avg(likes)) from checkins
group by course_id 

 

 

 

 

★ 별칭 기능: Alias
- 쿼리가 점점 길어지면서 종종 헷갈리는 일이 생김. 그래서 SQL은 Alias(알리아스)라는 별칭 기능을 지원한다.

 

1. orders 뒤에 o를 붙여서 o.course_title, 즉 어떤 테이블인지 지정해서 호출할 수 있도록 하는 알리아스

 

2. count(*) 뒤에 as cnt라고 붙여주면 출력될 때도 count(*) 대신 cnt라고 보여짐!

 

스파르타코딩클럽 SQL 강의 2주차 숙제!
네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기 
- 힌트: 네이버 이메일 -> like 사용

조건1. 네이버 이메일 사용
조건2. 앱개발 종합반 신청

 

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