기획자의 SQL 맛보기(4) GROUP BY

안녕하세요, 오늘도 기획자 팥씨입니다. 🥮
SQL 맛보기 4탄은 GROUP BY 입니다.

 

 

 

목차

 

 

1. GROUP BY | ~에 의해서 그룹을 나눈

• 데이터를 '특정 컬럼 기준'으로 그룹화시키는 구문
• 그룹을 나누고 다시 그 그룹 안에서 세부 그룹으로 나눌 수 있음
• GROUP BY 컬럼 1, 컬럼 2 = 컬럼 1로 나눈 그룹1에서 다시 컬럼2로 그룹을 나눔
• 각 그룹에 대한 연산결과 (합, 평균, 카운트 등)를 산출하기 위해서는 '집계함수'를 사용함. 

 

1-1.  데이터 조회 순서

SELECT - FROM - WHERE - GROUP BY  일 때,

SELECT
	category,
    sum(order_price) as ord_price
FROM
	orders
WHERE
Rgn1_nm = '서울특별시'

GROUP BY
category

*GROUP BY  다음에 나온 그룹의 위치와 개수는 SELECT 다음에도 같은 그룹의 위치와 개수로 나와야 함. (category)

 

(1) FROM '어느 테이블로부터 가지고 와?'

(2) WHERE '테이블에서 어떤 기준으로 필터를 걸지?'

(3) GROUP BY '어떤 컬럼을 기준으로 그룹화를 만들지?'

(4) SELECT '어떤 컬럼을 검색하지?'

 

1-2. 그룹함수의 종류

1. 하나 이사의 행을 그룹으로 묶어 연산하기 위함
2. COUNT 함수를 제외한 나머지 그룹함수 = NULL 값을 제외하고 계산
   즉, COUNT는 NULL 포함하여 계산
3. GROUP BY 기준으로 나누어진 각 그룹의 그룹함수의 결과를 검색 (*그룹 특징을 확인할 수 있는 방법)

함수종류	정의	참고
COUNT	'행'의 개수를 셈	데이터 NULL인 경우도 COUNT
SUM	합계	NULL 값 제외하고 연산 --> 단, NULL 값 포함하여 AVG(평균) 계산하려면 NULL 값을 0으로 변경해야 함.
AVG	평균
MAX	최댓값
MIN	최솟값
STDDEV	표준편차
VARIANCE	분산

 

 

1-3. 예제로 SELECT FROM WHERE GROUP BY 연습하기

 

"OE_ORDERS 테이블을 사용하여 2007년의 ORDER_MODE 별 주문지표를 비교하세요.

(주문 수, 고객 수, 고객당 주문수, 건당거래액, 총거래액)"

 

1. 전체 컬럼 조회로 테이블 둘러보기

select * from

 

(2). 'ORDER_MODE 별' 어떤 요소 들어있는지 알기 위해 distinct 사용하여 고유값 추출

direct, online 확인

 

3. 필터조건은 2007년, 그룹은 ORDER_MODE 설정 --> 2007년 먼저 가져오기

order_date 날짜 타입으로 자동으로 스키마 저장되어있음!! 날짜 데이터 비교할 때 작은 따옴표 써줘야 함.

order_date 날짜 타입으로 자동으로 스키마 저장되어있음!!

 

 

4. ORDER_MODE별 주문지표 --> direct / online 별 주문수, 고객수, 고객당주문수, 건당거래액, 총거래액 조회

• ORDER_MODE는 GROUP BY 뒤에 나와야 하고, SELECT 뒤에도 나와야 함. 
• 지표설정
  • 주문수 = count(order_id)
  • 고객수 = count(customer_id)
  • 고객당주문수 = count(order_id) / count(customer_id)
  • 총거래액 = sum(order_total)
  • 건당거래액 = sum(order_total) / count(order_id)
•  BUT !!!!!!!!! 중복제거해야 함. 
  • 중복되는 경우 (고객 한명이 두개의 주문을 했을 때 - 고객 id 중복 카운트 되는 것) 제거 고려!!
  • 중복제거한 지표설정 아래와 같음

SELECT 
order_mode,

--주문 수
count(order_id) as ord_cnts,
count(distinct order_id) as ord_cnt, --테이블에 따라 주문번호도 중복될 수 있으므로

--고객 수
count(customer_id) as cus_cnts,
count(distinct customer_id) as cus_cnt,

--고객당 주문 수
count(order_id) / count(CUSTOMER_ID) as ord_cnt_by_cuss,
count(order_id) / count(distinct customer_id) as ord_cnt_by_cus,

--총 거래액
sum(order_total) as price,

-- 건당 거래액
sum(order_total) / count(order_id) as avg_price

 FROM `ls-data-literacy-410915.database.oe_orders`
 WHERE ORDER_DATE >= '2007-01-01' AND ORDER_DATE <= '2007-12-31'
group by order_mode

 

중복제거 is 핵심 !!!!!!!!!!

 

2. 오늘의 팥앙금🥮

• 필터는 WHERE, 그룹(~별)은 GROUP BY
• GROUP BY 뒤 카테고리는 SELECT 뒤에도 써줘야 한다. 
• 문제를 마주했을 때 차례차례 풀어가자. 구조를 먼저 생각하자. (WHERE은? GROUP은?)
• 지표 설정 시 '중복제거' 유의하자.
  • 중복제거는 distinct를 컬럼명 앞에써서 괄호 안에서 사용 !
  • 중복제거 하지 않은 것은 이름에 s 붙여서 비교 조회해보자!