스파르타 SQL 2주차 내용 정리
> 통계 : 최대, 최소, 평균, 개수
* 데이터 분석의 목적 : 쌓여있는 날것의 데이터 => 의미를 갖는 정보로의 변환
* 데이터베이스 테이블에 저장된 데이터: 쌓여있는 날것의 데이터
==> 더 나아가다 보면 "범주(Category)" 각각의 정보가 궁금할 수 있지
ex. 과목별 신청자 평균 연령, 과목별 신청자수, 성씨별 회원 수 등.
-------------------
동일한 범주의 데이터를 묶어주는 Group by
- 동일한 범주를 갖는 데이터를 하나로 묶어서 범주별 통계를 내주는 것.
성씨별 회원수를 Group by로 쉽게 구해보기
select name, count(*) from users
group by name;-=> 우리가 이전 시간에 count를 했었을 때는 전체를 다 셌어요.
그런데 Group by 안에 있는 count를 쓰게 되면 name으로 묶이는 애들의 개수들을 써주는 것이다.
이거 작성 순서
1) select name from users;
2) group by name;
3) name, count(*) 삽입
교재에 나와있는 설명
1. from users: users 테이블에서 데이터를 불러옵니다.
2. group by name: name이라는 필드에서 동일한 값을 갖는 데이터를 하나로 합쳐줍니다.
3. select name, count(*)를 출력해 주는데,
여기서 count(*)는 group by로 합쳐진 데이터의 개수를 세어주는 것입니다.
------------------------
SQL 쿼리가 실행되는 순서
: Group by 제대로 알아보기
select name, count(*) from users
group by name;위 쿼리가 실행되는 순서: 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 = "신**";3) group by를 사용해서 '신'씨를 가진 데이터가 몇 개인지 살펴보기
select name, count(*) from users
group by name;
Group by로 통계를 내는 기능
공식
select 범주별로 세고 싶은 필드명, count(*) from 테이블명
groyp by 범주별로 세고 싶은 필드명;
Q. 주차별 오늘의 다짐 개수 구하기
select week, count(*) from checkins
group by week;작성 순서
1. select * from checkins
2. group by week
3. 1수정. select week, count(*) from checkins
공식
select 범주별로 세고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세고 싶은 필드명;
-- 먼저 주차로 묶고, count를 써준다.동일한 범주에서의 최솟값 구하기
공식
select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;
Q. 주차별 오늘의 다짐 좋아요 최솟값 구하기
예시코드
select week, min(likes) from checkins
group by week;- 여기서는 범주가 담긴 필드명이 week, 최솟값을 알고 싶은 필드명은 likes.
동일한 범주에서의 최댓값 구하기
공식
select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;
주차별 '오늘의 다짐'의 좋아요 최댓값 구하기
예시코드
select week, max(likes) from checkins
group by week;- 여기서는 범주가 담긴 필드명이 week, 최댓값을 알고 싶은 필드명은 likes.
동일한 범주에서의 평균 구하기
공식
select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;Q. 주차별 오늘의 다짐의 평균값 구하기
예시코드
select week, round(avg(likes), 2) from checkins
group by week;
-- round는 반올림. 여기서는 소숫점 둘째 자리까지 반올림하겠다는 뜻.- 여기서는 범주가 담긴 필드명이 week, 최댓값을 알고 싶은 필드명은 likes.
동일한 범주의 합계 구하기
공식
select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;Q. 주차별 '오늘의 다짐'의 좋아요 합계 구하기
예시코드
select week, sum(likes) from checkins
group by week;- 여기서는 범주가 담긴 필드명이 week, 최댓값을 알고 싶은 필드명은 likes.
---------------------
깔끔한 정렬이 필요할 땐? Order by
원본 쿼리 살펴보기
select name, count(*) from users
group by name;
결과의 개수 오름차순으로 정렬해보기
select name, count(*) from users
group by name
order by count(*);작성순서
1. select * from users
2. group by name
3. 1수정 select name, count(*) from users
4. order by count(*);
결과의 개수 내림차순으로 정렬해보기
select name, count(*) from users
group by name
order by count(*) desc;
작성순서
1) select * from users;
2) groyp by name;
3) 1) 변경 select name, count(*) from users
4) order by count(*);
=> 개수(count(*) 값) 을 기준으로 정렬해달라는 뜻.
기본적으로 뒤에 아무것도 안 붙으면 오름차순.
Order by 사용해보기
공식
select * from 테이블명
order by 정렬의 기준이 될 필드명;
[퀴즈] like를 많이 받은 순서대로 '오늘의 다짐'을 출력해보기
select * from checkins
order by likes desc;
적게 받은 순서대로 보기 위해서는 desc 제거해주면 된다.
[Order by SQL 쿼리가 실행되는 순서]
일단 예시코드
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(*): 합쳐진 데이터의 개수에 따라 오름차순으로 정렬해준다.
order by는 단독으로 사용 무조건 가능하다
(group by와 꼭 같이 쓸 필요가 없다는 뜻.)
------------------------
WHERE과 함께 사용해보기
[Where와 Group by, Order by 함께 사용해보기]
=> Where절로 조건이 하나 추가되고, 그 이후에 Group by, Order by가 실행되는 것!
[문제] 웹개발 종합반의 결제수단별 주문건수 세어보기
hint ) 원하는 테이블: orders / 범주로 사용할 필드: payment_method
일단 전체를 보기
select payment_method, count(*) from orders
group by payment_method;1) select * from orders
2) group by payment_method
3) select payment_method count(*) from orders
문제 요구사항 푸는 순
[순서]
1. orders 테이블에서 주문 데이터를 읽어오고
2. 웹개발 종합반 데이터만 남기고(이 테이블을 일단 웹개발 종합반으로 자른다.)
3. 결제수단(범주) 별로 그룹화하고
4. 결제수단별 주문건수를 세어준다
예시답안 코드
select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method;1) select * from orders
2) where course_title = "웹개발 종합반"
3) select payment_method, count(*) from orders
4) group by payment_method;
이 SQL 쿼리가 실행되는 순서 : 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개!
*Order by가 추가된다면? order by는 가장 나중에 실행된다.
(결과물을 정렬해주는 것이기 때문)
select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method
order by count(*);------------------------
Order by, Group by 같이 연습해보기
1. Order by 연습하기
Q. 문자열을 기준으로 정렬해보기
select * from users
order by email;Q.시간을 기준으로 정렬해보기
select * from users
order by created_at desc;
Group by 연습해보기
[퀴즈] 앱개발 종합반의 결제수단별 주문건수 세어보기
- 원하는 테이블 : orders / 범주로 사용할 필드 : payment_method
정답코드
select payment_method, count(*) from orders
where course_title = "앱개발 종합반"
group by payment_method;작성하자면
1) select * from users
2) wher course_title = "웹개발 종합반"
3) select payment_method, count(*) from orders
4) group by payment_method;
[퀴즈] Gmail 을 사용하는 성씨별 회원수 세어보기
[정답]
select name, count(*) from users
where email like '%gmail.com'
group by name;작성하자면
1) select * from users
2) where email like '%gmail.com'
3) select name, count(*) from users
4) group by name;
[퀴즈] course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기
정답 쿼리
select course_id, avg(likes) from checkins
group by course_id;작성 순서
1. select from checkins
2. group by course_id
3. select course_id, avg(likes) from checkins
작성
1) show tables로 어떤 테이블이 있는지 살펴보기
2) 제일 원하는 정보가 있을 것 같은 테이블에 select * from 테이블명 limit 10 쿼리 날려보기
3) 원하는 정보가 없으면 다른 테이블에도 2)를 해보기
4) 테이블을 찾았다! 범주를 나눠서 보고싶은 필드를 찾기
5) 범주별로 통계를 보고싶은 필드를 찾기
6) SQL 쿼리 작성하기!
----------------
별칭 기능 Alias
쿼리가 점점 길어지면서 종종 헷갈리는 일이 생길 수 있습니다. 그래서 SQL은 Alias라는 별칭 기능을 지원한다!
select * from orders o
where o.course_title = '앱개발 종합반'요게
select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_methodselect payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method1) select * from users
2) where email like '%naver.com' and course_title = '앱개발 종합반'
3) select payment_method, count(*) from orders
4) group by payment_method