두 번째 강의 내용 - Group by, Order by

​
우리가 데이터에서 궁금한 것.
통계 : 최대, 최소, 평균, 갯수

데이터 분석의 목적
쌓아있는 날 것의 데이터를 의미를 갖는 '정보'로 변환

where 절을 사용해서 수십개의 쿼리를 작성하는 것은 너무 비효율적
Group by를 이용하여 범주의 통계 가능

• ; 코드가 끝났다는 표시

1. 범주의 통계를 내주는 Group by

Group by란?

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


1. 동일한 범주의 개수 구하기

• count(*)

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

• 예시1) 주차별 '오늘의 다짐' 좋아요 개수 구하기
  예시에 사용될 테이블명 checkins
  week: 수강생이 '오늘의 다짐'을 남긴 시점의 강의 주차를 의미
  likes: 남긴 '오늘의 다짐' 게시물에 달린 좋아요의 수를 의미

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

2. 동일한 범주에서의 최소값 구하기

• min(필드명)

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

• 예시2) 주차별 '오늘의 다짐'의 좋아요 최소값 구하기

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

3. 동일한 범주에서의 최대값 구하기

• max(필드명)

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

• 예시3) 주차별 '오늘의 다짐'의 좋아요 최대값 구하기

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

4. 동일한 범주의 평균 구하기

• avg(필드명)

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

• 예시4) 주차별 '오늘의 다짐'의 좋아요 평균값 구하기

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

*평균의 소수점을 표시하지 않거나 지정해 줄 때
round(avg(필드명),표시하고 싶은 자리수)로 한번 더 묶어주기

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

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

5. 동일한 범주의 합계 구하기

• sum(필드명)

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

• 예시5) 주차별 '오늘의 다짐'의 좋아요 합계 구하기

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

- Group by, SQL 쿼리가 실행되는 순서가 중요

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

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

1. from users: users 테이블 데이터 전체를 가져옵니다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐줍니다.
3. select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다.

• 예) 이XX, 이XX, 김XX, 김, 박** 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박XX은 1개겠죠!
  ​

2. 데이터 정렬, Order by

Order by란?

데이터를 정렬해주는 것을 의미
두 가지로 사용 가능

1) group by를 사용하여 통계된 데이터를 수치에 맞게 정렬 해주거나

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

• 예시6) 결과의 개수 내림차순으로 정렬해보기

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

2) order by 단독으로 사용 가능

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

• 예시7) like를 많이 받은 순서대로 '오늘의 다짐'을 출력해 볼까요?

select * from checkins
order by likes desc;

• 기본 - 오름차순 - asc
  내림차순 - desc

- order by, SQL 쿼리가 실행되는 순서가 중요

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

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

1. from users: users 테이블 데이터 전체를 가져옵니다.
2. group by name: users 테이블 데이터에서 같은 name을 갖는 데이터를 합쳐줍니다.
3. select name, count(*): name에 따라 합쳐진 데이터가 각각 몇 개가 합쳐진 것인지 세어줍니다.

• 예) 이XX, 이XX, 김XX, 김XX, 박XXX 이렇게 데이터가 있었다면, 이는 2개, 김은 2개, 박은 1개!
  

4. order by count(*): 합쳐진 데이터의 개수에 따라 오름차순으로 정렬해줍니다.

*꿀팁* 이렇게 쿼리를 작성하면 편해요!

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

​

3. 이외 유용한 문법 배워보기 - 별칭 기능 Alias

Alias란?

쿼리가 점점 길어지면서 생기는 혼동을 최소화하고 원하는 이름으로 결과를 출력하기 위해 사용

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

위의 쿼리에서 select * from orders o
orders에는 별칭 ' o ' 가 생김 -> order.course_title 이렇게 하지 않아도 o.course_title로 간편하게 지칭할 수 있음

• 테이블명 뒤에 as를 붙여서 별칭을 추가하는 것도 가능

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

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

• count(*)가 아니라 cnt로 출력
• 보통 1,2자리로 짧게 지정
  ​

*퀴즈 및 숙제

[퀴즈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 course_id, avg(likes) from checkins
group by course_id 

[숙제] 네이버 이메일을 사용하여 앱개발 종합반을 신청한 주문의 결제수단별 주문건수 세어보기

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