[SQL] 09. 값을 요약하는 Aggregation 알아보기

뉴욕에는 에이비앤비 숙소가 몇개 있는거지?

뉴욕 에어비앤비의 평균 숙소 가격은 얼마지?

가장 저렴하거나 가장 비싼 숙소는 얼마지?

...

맨해튼, 브루클린, 지역별로 숙소는 몇개 있지?

지역별로 평균 숙소 가격은 어떻게 다르지?

... 

 

데이터를 하나의 값으로 요약하기

SQL에서는 Aggregate 함수를 사용한다.

• sum
• count
• avg
• ...

 

1. Aggregate 함수를 이용해 데이터 집계하기: COUNT

• COUNT(컬럼명) - 해당 컬럼에 값이 있는 데이터가 몇개인지 (비어 있는 데이터는 카운트 X)

뉴욕에 있는 숙소 개수 구하기

SELECT COUNT(id) AS id_count
FROM data.airbnb_nyc

테이블의 로우 수 구하기

SELECT COUNT(*) AS row_count
FROM data.airbnb_nyc

COUNT에서 NULL 카운트 확인: last_review 컬럼 이용

• null값은 카운트하지 않는다.

SELECT COUNT(last_review) AS last_review_count
FROM data.airbnb_nyc

SELECT COUNT(*) AS row_count, COUNT(last_review) AS last_review_count
FROM data.airbnb_nyc

COUNT에서 컬럼의 중복 확인

SELECT COUNT(id) AS id_count, count(DISTINCT id) AS unique_id_count
FROM data.airbnb_nyc

 

2. Aggregate 함수를 이용해 데이터 집계하기: MIN / MAX

뉴욕숙소 가격의 최소 최대값 구하기

SELECT MIN(price) AS minimum_price, MAX(price) AS maximum_price
FROM data.airbnb_nyc

 

3. Aggregate 함수를 이용해 데이터 집계하기: SUM

뉴욕숙소들의 전체 리뷰수 구하기

SELECT SUM(number_of_reviews) AS sum_number_of_reviews
FROM data.airbnb_nyc

 

4. Aggregate 함수를 이용해 데이터 집계하기: AVG

• 비어 있는 데이터(NULL)은 제외하고 평균 계산

뉴욕숙소 평균 가격 구하기

SELECT AVG(price) AS average_price
FROM data.airbnb_nyc

 

5. Aggregate 함수를 이용해 데이터 집계하기: GROUP BY

이 모든걸 그룹별로 쪼개서 보고 싶을 때는
GROUP BY를 씁니다

SELECT 원하는 컬럼
FROM 조회하는 테이블
WHERE 필터링하고 싶은 조건
GROUP BY 묶어서 보고 싶은 컬럼
LIMIT 상위 n개 결과만 조회

지역(neighbourhood_group)별로 최소/최대/평균 숙소가격 구하기

SELECT neighbourhood_group,
	   MIN(price) AS minimum_price,
	   MAX(price) AS maximum_price,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group

지역(neighbourhood_group)과 세부지역(neighbourhood) 별로 방의 개수와 평균 숙소가격 구하기

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group, neighbourhood

• GROUP BY 뒤에 있는 컬럼과 SELECT 뒤의 컬럼은 같아야 한다
• 쪼개는 기준이 SELECT와 GROUP BY에 모두 들어가야 한다

 

6. 순서대로 정렬하고 싶을 때는 ORDER BY

순서대로 정렬하고 싶을 때는
ORDER BY를 씁니다

SELECT 원하는 컬럼
FROM 조회하는 테이블
WHERE 필터링하고 싶은 조건
GROUP BY 묶어서 보고 싶은 컬럼
ORDER BY 정렬해서 보고 싶은 컬럼
LIMIT 상위 n개 결과만 조회

순서 정렬해주기

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group, neighbourhood
ORDER BY COUNT(id)

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group, neighbourhood
ORDER BY COUNT(id) DESC

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group, neighbourhood
ORDER BY neighbourhood_group, COUNT(id) DESC

• 빅쿼리에서는 COUNT(id)대신 id_count를 써도되지만, DB에 따라 안될 수도 있다.
• SELECT 뒤에 오는 컬럼들은 순서대로 1, 2, 3, 4의 번호가 붙고, 이를 활용할 수 있다. DB에 따라 안될 수도 있다.

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY 1, 2
ORDER BY 1, 3

 

 

7. GROUP BY에 조건을 넣고 싶을 때 HAVING

[조건절 in SQL]

WHERE	HAVING
개별 행에 적용	그룹화된 결과에 적용
GROUP BY 되기 전에 작동	GROUP BY 된 후 작동
GROUP BY 전에 쓴다	GROUP BY 뒤에 쓴다

SELECT 원하는 컬럼
FROM 조회하는 테이블
WHERE 필터링하고 싶은 조건
GROUP BY 묶어서 보고 싶은 컬럼
HAVING GROUP BY 이후 필터링하고 싶은 조건
ORDER BY 정렬해서 보고 싶은 컬럼
LIMIT 상위 n개 결과만 조회

평균 숙소 가격이 150달러 미만인 지역만

SELECT neighbourhood_group,
	   neighbourhood,
	   COUNT(id) AS id_count,
	   AVG(price) AS average_price
FROM data.airbnb_nyc
GROUP BY neighbourhood_group, neighbourhood
HAVING AVG(price) < 150
ORDER BY neighbourhood_group, COUNT(id) DESC