본 포스팅은 스파르타 코딩클럽 SQL강의를 참고하였습니다.
2주차 내용은 MySQL에서 통계를 위한 최대, 최소, 평균, 개수 등을 조작하는 방법을 배우고,
나아가 위의 통계를 범위를 설정하기 위한 방법, 정렬하는 법 등을 배우게 되었다.
범주의 통계를 내주는 Group by
성씨별로 몇 명이 회원이 있는지 구하려고 기존의 배운 SQL내에서 동작시키게 된다면,
select count(*) from users where name = '이**'
select count(*) from users where name = '신**'
select count(*) from users where name = '황**'
select count(*) from users where name = '남**'
select count(*) from users where name = '고**'...
위와같이 하나씩 성의 개수만큼 쿼리를 작성해서 결과를 얻어야할 것이다.
이러한 비효율적인 문제를 Group by를 이용해 이름에 대해서 범주를 묶게되면 간단하게 해결할 수 있다.
SELECT name, COUNT(*) FROM users GROUP BY name;
SELECT (범주별로 세어주고 싶은 필드명), COUNT(*) FROM (테이블명)
GROUP By (범주별로 세어주고 싶은 필드명)
위 쿼리가 실행되는 순서: from → group by → select
GROUP BY를 이용한 통계
개수를 세는 것 뿐만 아니라 평균값 최대값 최솟값 합계 등도 동일한 방법으로 실행한다. 다만 COUNT()가 개수를 보여주듯이 각 통계값을 위한 함수가 따로 존재한다.
최대값 MAX
SELECT (범주가 담긴 싶은 필드명), MAX(최댓값을 알고 싶은 필드명) FROM (테이블명)
GROUP BY (범주가 담긴 싶은 필드명)
최소값 MIN,
SELECT (범주가 담긴 싶은 필드명), MIN(최솟값을 알고 싶은 필드명) FROM (테이블명)
GROUP BY (범주가 담긴 싶은 필드명)
평균AVG,
SELECT (범주가 담긴 싶은 필드명), AVG(평균값을 알고 싶은 필드명) FROM (테이블명)
GROUP BY (범주가 담긴 싶은 필드명)
평균값의 경우 상황에 따라서 소숫점이 길게 나올 수 있는데 이때는 round()를 사용해서 소숫점을 컨트롤할 수 있다.
SELECT (범주가 담긴 싶은 필드명), ROUND (AVG(평균값을 알고 싶은 필드명), 2(원하는 소숫점 아래 자리)) FROM (테이블명)
GROUP BY (범주가 담긴 싶은 필드명)
합계SUM
SELECT (범주가 담긴 싶은 필드명), SUM(합계를 알고 싶은 필드명) FROM (테이블명)
GROUP BY (범주가 담긴 싶은 필드명)
6월 30일 추가
정렬을 위한 ORDER BY
이전에 GROUP BY를 이용해 조회한 데이터의 결과가 아래와 같이 존재한다. 하지만 COUNT의 수가 작아졌다 커졌다 왔다갔다 불규칙 적인것을 확인할 수 있다.
이럴떄 ORDER BY를 이용해서 데이터를 정렬 해 줄 수 있다.
정렬은 언제 할까? 이미 모든 조회가 끝나고 마지막에 정리를 한다는 생각으로 마지막에 작성한다.
SELECT (범주별로 세어주고 싶은 필드명), COUNT(*) FROM (테이블명)
GROUP BY (범주별로 세어주고 싶은 필드명)
ORDER BY ( 정렬 기준이 될 필드명 )
기본적으로 ORDER BY ( 정렬 기준이 될 필드명 ) 까지만 적어주면 정렬 기준이 될 필드명에 대해서 오름차순(ASC)로 정렬된다.
내림차순(최신순)의 정렬을 원한다면 ORDER BY ( 정렬 기준이 될 필드명 )뒤에 내림차순을 위한 'DESC'를 작성해 줘야한다.
SELECT (범주별로 세어주고 싶은 필드명), COUNT(*) FROM (테이블명)
GROUP BY (범주별로 세어주고 싶은 필드명)
ORDER BY ( 정렬 기준이 될 필드명 ) DESC
위 쿼리가 실행되는 순서:from → group by → select → order by
ORDER BY의 같은 경우 정렬이 숫자라면 당연히 내림차순, 오름차순이 높은수, 낮은수 부터 차례로 정렬된다.
문자열의 경우에는 영어는 알파벳 순서, 한글은 국문순서에 따라서 차례로 정렬된다.
연습문제
1. 웹개발 종합반의 결제수단별 주문건수 세어보기
- 결제수단별 주문건수를 가지고 있는 테이블을 확인하기
show tables => select * from orders - 웹개발 종합반의 데이터 이기 때문에 조건을 웹종반에 맞춰주기
select * from orders where course_title = '웹개발 종합반' - 결제수단의 범주를 가지고 있기 때문에 Group by를 결제수단으로 맞춰주고 개수 새기
select payment_method, count(*) from orders where course_title = '웹개발 종합반' group by payment_method
위 쿼리가 실행되는 순서: from → where → group by → select
2. Gmail 을 사용하는 성씨별 회원수 세어보기
- 회원수이기 때문에 테이블을 확인하고 유저에 대한 데이터가 어디 있는 정보인지 확인한다.
show tables => select * from users
- Gmail이 조건이 되기때문에 where절 조건으로 email이 Gmail이 되는 유저를 간추려낸다.
- 성씨별의 범주를 가지고 회원수를 세기때문에 name으로 묶고 개수를 세어준다.
3. course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기
- 오늘의 다짐에 달린 like의 수 이기 때문에 course_id, 오늘의 다짐, like가 모두 있는 테이블을 찾자
show tables => select from courses => select from checkins
- 따로 조건을 걸지 않았기 때문에 course_id의 범주로 묶고 like의 평균을 구해보자
select course_id, avg(likes) from checkins group by course_id
4. 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기
- 필요한 필드명이 포함된 테이블 찾기
네이버 이메일을 사용 => email , 앱개발 종합반, 결제수단 세가지 필드가 필요한 테이블을 찾기
show tables => select * from orders
- 조건 만족시키기 where절 naver이메일을 사용하며, 앱개발 종합반을 신청한 주문데이터 조회
select * from orders
where email like '%@naver.com' and course_title = '앱개발 종합반'
- 주문 결제수단의 범주로 건수의 개수를 세기
SELECT payment_method, count(*) FROM orders
where email like '%@naver.com' and course_title = '앱개발 종합반'
group by payment_method
