[SQL 데이터 분석, 활용!] 섹션 6. 그룹화의 집계

[개념은 호옹~, 실습 빡]

SQL 데이터 분석, 활용!

참고사이트:

SQL, 데이터 분석! | CloudStudying

그룹화와 집계(feat. 마켓컬리)

 

그룹화, 무엇?

그룹화:

데이터를 특징별로 분류한 다음에

이를 기준으로 분석하는 기법

예시) 학생 테이블 있다고 할 때,

성별을 기준으로 분류 가능

남성과 여성을 나눈 다음에

각각을 집계할 수 있다

쿼리로 작성하면  다음과 같다

(GROUP BY 절 사용)

GROUP BY와  COUNT(어느 카드가 가장 많이 결제됩니까?)

- DB관계 분석 -

상품과 주문이 다대다 연결

결제 방법으로 분류

(그룹화)

그룹화된 데이터를 갖고

각각을 집계한다 (결제횟수)

결제방법 데이터는

PAYMENTS 에 담겨있으니

FROM 을 사용해서

 PAYMENTS 테이블을 갖고온다

 

각 카드별로 그룹을 나눠준다

(GROUP BY절을 통해서 나눠줄 수 있음)

 

해당 테이블을 갖고 그룹화된

새롭게 결과를 만든다

GROUP BY에서 명시한 컬럼을 갖고

분류를 한다

 

삼성이면 삼성

롯데면 롯데

카카오면 카카오

그러면 분류 기준을 갖고

각 카드마다

각각의 데이터가 붙여진다

각 카드별로 데이터가 묶인 모습

 

각각의 그룹별로 집계 가능

SELECT 절에서 결제방법을 먼저 갖고온다

SELECT를 통해서 PTYPE을 명시함

 

그리고 추가로 결제횟수,

즉 각 그룹별 집계를 만들어준다\

 

  COUNT() 함수를 이용해서

결제횟수를 집계함

이렇게 각각 그룹별로 집계가 됨

 GROUP BY 절로 인해 그룹화되고

COUNT 함수로 집계

 GROUP BY와 AVG

상품 테이블을 보관 타입에 따라 분류

(그룹화의 기준)

그룹화된 기준을 갖고 집계한다

(상품개수, 평균가격)

FROM절로 PRODUCT  데이터를 갖고 옴

 

상품보관타입으로 분류한다

(냉장/상온)

(GROUP BY 절을 사용)

 

냉장식품은 냉장식품별로

상온식품은 상온식품별로 분류됨

 

집계:

상품개수 - COUNT() 함수사용

평균가격 - AVG() 함수 사용 

SELECT 절을 사용해서

GROUP BY 절에서 명시한

PTYPE을 적어준다

 

파란부분이 SELECT 되는 것

 

집계를 위해서 각각의 함수를 호출

테이블 조인과 그룹화(사용자별 배송 완료 주문건수, 얼마나 나와요?)

닉네임 별 - 그룹화

(별로 분류하니까 그룹화)

배송완료 주문수 - 집계

FROM 절을 사용해서 사용자 데이터를 갖고오고

 JOIN을 통해서 주문데이터도 갖고온다

PK 와 FK 가 같은지도 확인

배송된 조건인 것도 확인

JOIN 된 데이터를 갖고

사용자 닉네임 별 분류를 하려면

GROUP BY 절을 사용해서

 데이터를 분류한다

 

나눠진 데이터를 갖고

집계 가능

닉네임을 갖고와야하니까

SELECT 절을 사용해서

"사용자의 닉네임" 을 갖고와준다

 

GROUP BY 절에서 명시했기 때문에

사용자의 닉네임 갖고 올 수 있는 것

배송완료 주문 수 구하기:

각각의 그룹 별로 COUNT 해주면 된다

 

- 결과 -

닉네임별로 그룹화 되고,

그룹화된 데이터별로 집계를 구함

HAVAING, ORDER BY, LIMIT 절 개요

HAVING 절:

분류된 그룹들 중에서

특정 그룹만 갖고오는 것

이렇게 모양 별로 분류했다면

다시 조건을 줘서

그룹을 선별하는 것

(도형개수 3개)

WHERE 절과 비슷하지만

WHERE 절은 일반레코드를

필터링 하는 반면에,

 

HAVING 절은 그룹화된 데이터만을

필터링 한다

ORDER BY 절:

결과를 정렬하는 문법이다

 

오름차 순은 ASC

내림차 순은 DESC

 

 

LIMINT 절:

다중 레코드의 개수를 제한하는 구문

일부 몇 개 데이터만 갖고오고 싶은 경우에,

사용하는 구문

그룹 필터링 #1

36,000원보다 작은

롯데 카드는 사라짐

    (삼성카드와 카카오페이만 조회돼야 함)

결제 수단 별:

수단별은 ptype에 저장돼있으니까

  GROUP 화를 PTYPE으로 해준다

GROUP  BY 할 때 주의할 점:

GROUP BY 절에 명시된 컬럼만

SELECT 가능!

"평균" 결제 금액 구하기:

 AVG 사용한다

ROUND 를 통해서

소수점 2째 자리까지만 구한다

 

평균 36,000원 이상인 것만 조회 하면 되니까

HAVING 절을 넣어준다

(그룹필터링: 그룹화된 것들을 조건으로 선별)

     그룹 필터링 #2

그룹화 된 것을 갖고

집계를 추출하고 있다

 

코드 실행하면

 NULL 값이 나오기도 함

 LEFT JOIN 을 이용했기 때문 

1) 누적 매출이 35,000원 이상인 상품을 조회하기

그룹의 집계를 갖고 필터링 하는 거니까

HAVING 을 사용한다

그룹 필터링 #3

- 누적 매출이 2만원 이상이면서,

누적 판매량도 10개 이상인 상품을 조회하기 -

HAVING 절을 사용해서

누적 매출을 35,000원 -> 20,000 원으로 바꿔준다

AND 를 사용해서

누적 판매량 10개 이상인 상품을 지정해준다

 정렬과 NULL 값 비교 (하나도 안 팔린 상품 중, 싼 것부터 차례로 알 수 있나요?)

- 누적 매출이 없는 제품,

가격을 기준으로

오름차순 정렬 조회하기 - 

 

누적 매출이 없다 => NULL

하단과 같이 = NULL로 설정하면

데이터가 아무것도 나오지 않는다

 

NULL 값을 비교할 땐,

 '=' 수식으로 비교하면 안된다

NULL  값은 IS 로 비교가능

NULL 인것만 가져와라 라는 뜻

-결과-

- 가격을 기준으로 정렬하기 -

ORDER BY 사용

 

가격이니까

 PRODUCT 의 PRICE 기준으로 정렬

 

오름차순은 ASC 써야하는게 아닌가?

=  ASC 는 가장 기본 정렬이라

생략도 가능

정렬과 개수 제한 ( 매출  TOP 5  상품이 어떻게 됩니까?)

- 누적 매출 상위 5개 상품을 조회하기 - 

ORDER BY 절을 누적매출 기준으로 정렬함

 

ORDER BY 절은

SELECT 절에서 준 

별칭을 사용할 수 도 있다

NULL 값들이 나오는데

 HAVING 을 줘서

누적 매출이  NULL 값이 나오지 않도록

지정해준다

IS NOT NULL 써준다

상위 5개 상품이니까

위에서 5개만 뽑으면 된다

=  LIMIT 절을 사용

 

-  요약 -

HAVING 절:

GROUP 필터링

 

ORDER BY:

정렬

(ASC / DESC)

 

LIMIT:

조회 개수 지정

 

IS NULL:

NULL값인 걸 찾을 때 사용

 

IS NOT NULL:

NULL 값이 아닌 걸 찾을 때 사용

- 실행 순서 -

FROM 절 먼저 시행

HAVING 절이 그룹화 다음으로 시행

그리고 실행되는 건 ORDER BY 가 아니라

SELECT 절이다

그리고 나서 ORDER BY 가 6번째로 시행된다

마지막으로 LIMIT