SQL ) AVG() 에서 발생한 문제와 해결

다음은 4주차의 마지막 문제입니다.

1. 서브쿼리로 평균 음식 주문금액, 평균 연령을 구해보기.
2. 데이터가 있는 테이블만 조회하기 때문에 INNER JOIN 식당이름으로 오름차순...


	SELECT 
		f.restaurant_name,
		f.price,
		f.quantity,
		c.age,
		AVG( f.price/f.quantity ) AS avg_price,
		AVG( c.age) "avg_age"
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id

첫번째 문제... 1개만 뜨는 문제
알고 보니 restaurant_name 별로 그룹을 안해줘서 생기는 문제였다.

	SELECT 
		f.restaurant_name,
		f.price,
		f.quantity,
		c.age,
		AVG( f.price/f.quantity ) AS avg_price,
		AVG( c.age) "avg_age"
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
	GROUP BY 1

다음과 같이 수정 후 커밋해보면?

그렇게 다음 조건 까지 완성 후

SELECT
	b.restaurant_name,
	b.avg_price,
	b.avg_age,
	CASE WHEN b.avg_price <= 5000 THEN "price_group1"
		 WHEN b.avg_price <= 10000 THEN "price_group2"
		 WHEN b.avg_price <= 30000 THEN "price_group3"
		 WHEN b.avg_price >30000 THEN "price_group4"
		 END "price_group",
	CASE WHEN b.avg_age < 30 THEN "age_group1"
		 WHEN b.avg_age BETWEEN 30 AND 39 THEN "age_group2"
		 WHEN b.avg_age BETWEEN 40 AND 49 THEN "age_group3"
		 WHEN b.avg_age >= 50 THEN "age_group4"
		 END "age_group"
FROM(
	SELECT 
		f.restaurant_name,
		f.price,
		f.quantity,
		c.age,
		AVG( f.price/ f.quantity ) AS avg_price,
		AVG( c.age) "avg_age"
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
	GROUP BY f.restaurant_name
	ORDER BY 1
) b
ORDER BY 1

실행해보면?

price_group 이 뭔가 이상하다......
그룹이 잘못되었다면 평균 가격이 이상하다는 뜻.

	정확한 값을 찾기위해 1차적인 값을 구해보기로 했다.
	SELECT 
		f.restaurant_name,
		f.price,
		f.quantity,
		SUM(f.price) total_price,
		SUM(f.quantity) total_quantity
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
	GROUP BY f.restaurant_name
	ORDER BY f.restaurant_name ASC

	임의의 값이 1개 나온다.

이제 AVG 함수를 써서 구해보자
	SELECT 
		f.restaurant_name,
		f.price,
		f.quantity,
		SUM(f.price) total_price,
		SUM(f.quantity) total_quantity,
		AVG(f.price/f.quantity) avg_price
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
	GROUP BY f.restaurant_name
	ORDER BY f.restaurant_name ASC

값이 이상하다.
1번째 컬럼을 보면 88570 / 16 = 5,535.625 이 나와야 정상이지만 5,442.5 가 나오는 문제가 생긴다.
이 말은 즉, '12 Chairs' 값 중 랜덤의 값 price와 랜덤의 quantity 를 가져와서 나눠줬다는 뜻이 된다.

드디어 정상적으로 출력된다.
원래 값은 아래와 같다.

정상적인 평균을 구했으니

SELECT
	b.restaurant_name,
	CASE WHEN b.avg_price <= 5000 THEN "price_group1"
		 WHEN b.avg_price <= 10000 THEN "price_gorup2"
		 WHEN b.avg_price <= 30000 THEN "price_group3"
		 WHEN b.avg_price >30000 THEN "price_group4"
		 END "price_group",
	CASE WHEN b.avg_age < 30 THEN "age_group1"
		 WHEN b.avg_age BETWEEN 30 AND 39 THEN "age_group2"
		 WHEN b.avg_age BETWEEN 40 AND 49 THEN "age_group3"
		 WHEN b.avg_age >= 50 THEN "age_group4"
		 END "age_group"
FROM(
	SELECT 
		f.customer_id,
		f.restaurant_name,
		AVG(c.age) as avg_age,
		SUM(f.price) / SUM(f.quantity) AS avg_price
	FROM food_orders f INNER JOIN customers c ON f.customer_id = c.customer_id
	GROUP BY restaurant_name
	) b
ORDER BY restaurant_name ASC

서브쿼리의 값을 다시 옮겨서 실행

?? 그래도 값이 달라? 왜 틀렸지?

정 안되겠어서 찾아보니... 저 사진은 예전 데이터였다... 즉, 지금과 데이터가 달랐다는 소리...
그래도 덕분에 중요한걸 알아냈다... ㅜㅜ
AVG() 함수에서 age 컬럼은 따로 연산할 필요가 없어서 정상적으로 나왔지만,
PRICE / QUANTITY 는 따로 연산이 필요함에도 불구하고 AVG() 함수를 써서 사용하려고 하니 컬럼의 값중 랜덤의 값을 가져와서 평균을 구했다는 걸 ...
깨달은 사실...

• 너무 함수를 믿고 쿼리문을 작성하지말자...
• 내가 잘했어도 사진과 다르다고 내가 완전히 틀렸다는 생각은 버리자...