mysql에서 사용할 수 없는 값이 있을 때 / Pivot Table처럼 만들기

SELECT 
	RESTAURANT_NAME,
	AVG(RATING) AS AVG_RATING,
	AVG(IF(RATING <> 'Not given', RATING, NULL)) AS AVG_RATING2
FROM food_orders
GROUP BY 1

평균 값이 다르게 나오는데 그 이유는 Not given 같이 계산할 수 없는 값일땐 mysql에서는 알아서 0으로 처리하기 때문에 평균을 구할때 총합/총 개수 에서 총 개수가 포함되기 때문이고

우리가 if문을 사용하여 사용할 수 없는 값을 null 처리 해버리면 아예 없는 데이터 취급이 되기 때문에 평균 구할때 총 개수가 적어져 AVG_RATING2의 값이 더 높게 나온 것이다. 

 

• COALESCE

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

원래는 두 컬럼을 병합하여

A 컬럼만 NULL이라면 B컬럼의 값을,

B 컬럼만 NULL이라면 A컬럼의 값을,

A, B 컬럼 둘다 값이 있을 때는 A컬럼의 값을,

A, B 다 NULL 이라면 NULL을 보여주는 함수이지만

SELECT COALESCE(컬럼, 값) 이렇게 쓰면 NULL 대신 표기할 값을 보여주는 방식으로 사용 가능하다.

 

Pivot Table 뷰 만들기

오라클은 제공한다는데 mysql은 제공 안함..그러므로 이 방법은 mysql 방법이다.

베이스 데이터가 될 테이블이 서브쿼리로 가야하고 본 쿼리에서는 무조건 max(if())를 사용해야한다.

본 쿼리에서 기준이 될 행을 맨 첫번째에 써주고 나머지 컬럼을 추가해 나가면 된다.

 

성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순) 이라는 문제가 있다

SELECT
	AGE,
	MAX(IF(GENDER = 'male', CNT_ORDER, 0)) AS 'MALE',
	MAX(IF(GENDER = 'female', CNT_ORDER, 0)) AS 'FEMALE'
FROM 
(
SELECT
	GENDER,
	CASE 
		WHEN AGE BETWEEN 10 AND 19 THEN 10
		WHEN AGE BETWEEN 20 AND 29 THEN 20
		WHEN AGE BETWEEN 30 AND 39 THEN 30
		WHEN AGE BETWEEN 40 AND 49 THEN 40
		WHEN AGE BETWEEN 50 AND 59 THEN 50
	END AGE,
	COUNT(1) AS CNT_ORDER 
FROM food_orders FO INNER JOIN customers C ON FO.customer_id = C.customer_id
WHERE AGE BETWEEN 10 AND 59
GROUP BY 1, 2
) A
GROUP BY 1
ORDER BY 1 DESC

서브쿼리문에 베이스 데이터가 될 성별, 연령별 주문 건수를 해결하기 위해

gender를 넣고

나이는 case when 문으로 연령대 별로 정리.

count(1)은 주문 건수이다.

나이는 10~59세 사이라 했으니 where 절로 서브쿼리에서 조건 해결

 

본쿼리에 가서 기준이될 age 행을 먼저 써주고

추가할 컬럼인 성별, 연령별 주문건수 컬럼을 만들어 준다.

연령이 기준이니 gourp by 1 해주고 연령 순으로 내림차 순이니 order by 1 desc 해준다.