데이터 분석 공부 일지 5일차 250425

5-1 복습, 학습 할 내용

서브쿼리, join 복습

 

예상하지 못한 값 대처, 피벗테이블

 

5-2

** 꿀팁 **

괄호는 함께 ()를 적어둬야 나중에 복잡해져도 안 헷갈림  

 

코드스니펫 없는 값 제외해주기

# food_orders에는 customer_id정보가 있지만
# customers 테이블을 left join하니 customer_id가 null값인 경우가 있음 (일치하지 않음)
# 그래서 where b.customer_id is not null을 사용해서 null값이 아닌 것들 출력

# *** null 값이 있다고 무조건 제외시켜서 계산하면 안된다. ***
# *** 상황에 따라 null값을 포함하고 계산해야 할 수도 있다. ***


select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null

 

null 값을 대체 하는 법- if 혹은 coalesce  

- if(조건, 대체 값, 아닌 경우 값)

- coalesce( null 값 컬럼, 대체 값, )

 

- 사용예시

select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       # if문으로 바꾼다면
       # if(b.age is null, 20, b.age) as "null 제거"
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null

 

- 결과 (null값을 포함해서 계산)

 

 

5-3 상식적이지 않은 데이터가 있는 경우

케이스 1 = 음식 주문 고객의 나이가 2세 ?

케이스 2 = 결제일이 1970년대 ? 

 

이럴 경우 어느 정도 상식수준으로 맞춰주기

 

사용예시

# case를 사용해서 Segment화 

select customer_id, name, email, gender, age,
       case when age<15 then 15
            when age>80 then 80
            else age end "범위를 지정해준 age"
from customers

 

5-4 pivot table

2개 이상 테이블을 보기좋게 배열

 

피벗뷰 깔끔하게 보기위해 max를 붙인다 (완벽히 이해하려면 아직 힘드니 max를 쓴다는 것만 기억하자)

 

실습 1 음식점별 시간대별 주문 건수

 

• food_orders와 payments 테이블을 조인하여
  15시부터 20시까지의 시간대별 주문 건수를
  음식점별로 피벗 형태로 출력하는 실습을 진행했다.

 

• SUBSTR(time, 1, 2)를 사용해 시간(hour)만 추출하고,
  GROUP BY restaurant_name, order_time 으로 시간대별 집계를 만들었음.

 

• 이후 MAX(IF(...)) 구문을 사용하여 가로 형태의 피벗 테이블을 구성했다.

 

select restaurant_name,
	max(if(order_time = '15', total_count, 0)) "15시",
	max(if(order_time = '16', total_count, 0)) "16시",
	max(if(order_time = '17', total_count, 0)) "17시",
	max(if(order_time = '18', total_count, 0)) "18시",
	max(if(order_time = '19', total_count, 0)) "19시",
	max(if(order_time = '20', total_count, 0)) "20시"
from
(
SELECT restaurant_name, count(fo.order_id ) as total_count, SUBSTR(p.time, 1, 2) as order_time
from food_orders fo join payments p on fo.order_id = p.order_id 
where SUBSTR(p.time, 1, 2) between 15 and 20
group by restaurant_name, order_time 
) a
GROUP BY restaurant_name 
order by max(if(order_time = '20', total_count, 0)) desc

해설과 조금 다르지만 결과 값이 맞게 나옴

Order by에서 "20시" 했다가 내림차순 정렬이 안됌

셀렉트절 순서로 표기하거나, 영어로 해야 되는 것 같음 혹은 지금처럼 그냥 max로 시작되는 쿼리를 입력

 

 

 

 

실습 2 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

 

 

select age_segment,
	MAX(if(gender = 'male', order_count, 0)) as male,
	MAX(if(gender = 'female', order_count, 0)) as female
from
(
select
	case
		when c.age between 10 and 19 then "10대"
		when c.age between 20 and 29 then "20대"
		when c.age between 30 and 39 then "30대"
		when c.age between 40 and 49 then "40대"
		when c.age between 50 and 59 then "50대"
	end as age_segment,
	 c.gender,
COUNT(fo.order_id) order_count
from customers c join food_orders fo on c.customer_id = fo.customer_id 
where c.age between 10 and 59
group by c.gender , age_segment
) t
group by age_segment
order by age_segment desc

 

이 부분에서 처음 만든 쿼리가 강의 해설의 결과 값과 달라서 시간을 많이 썼다.

 

서브쿼리 절에 group by c.gender, age_segment 부분을 

맨 처음 작성했던 쿼리에서 group by c.gender, age로 작성했는데

 

case문에 별칭을 age라고 붙였어도

age = 기존 테이블에 있는 age (10대,20대로 분류하지 않은 나이)

MySQL상에서 혼란을 불러올 수 있음

 

age라는 이름으로 GROUP BY를 한다면 

• 23/male
• 24/male
• 25/female
• 26/male
• 26/female
• 27/male

이런 식으로 묶여서 원하던 결과 값이 나오지 않았다.

 

age_segment로 묶을 경우 혼동 될 수 있는 컬럼명이 없기때문에

10대/male

10대/female

 

이런 식으로 묶여서 원하는 값이 나온다

 

*** 그래서 결국 별칭을 붙일 때는 최대한 기존에 존재하는 이름말고 다르게 붙여줘야 한다. ***

 

*** 추가로 처음 결과 값이 다르게 나온 쿼리에서 제대로 작동시키려면

GROUP BY 에 age 대신 case문을 통으로 넣거나, 숫자로 셀렉트절을 선택해서 넣어 주면 된다. ***

 

ChatGPT의 도움을 받자면

내부 GROUPING 단위가 나이대가 아닌 원래 나이 (c.age) 기반으로 쪼개질 수도 있음

어떤 상황이냐면요:

1. MySQL은 GROUP BY에서 alias age를 처리할 때,
   내부적으로 c.age가 아닌 '20대', '30대'라는 리터럴 기준으로 인식함
2. 그런데 이 alias는 SELECT 절의 산물이라
   GROUP BY에서 표현식이 정확히 일치하지 않으면 그룹이 엉킵니다
3. 그 결과 → 같은 ‘20대’인데도 20, 21, 22... 각각 따로 그룹이 잡히는 일이 생깁니다!

👉 즉, ‘20대’ 그룹이 실제로는 여러 줄로 분산됨
👉 바깥에서 MAX()를 취하면, 그 중 한 줄(가장 큰 값)만 출력됨
👉 ❌ 나머지 값은 누락 → 총계가 줄어드는 결과 발생

 

오늘은 5-4 실습 2번에 3시간정도 매달린거 같다.

처음 잘못나오고 분석해보고 모르겠어서 다시 다 지우고 하나하나 다시 해서 맞는 결과 값이 나오니

대체 처음에 뭐가 잘못된건지 AI를 활용해 찾아보려고 했는데 AI도 명쾌하게 답을 찾지 못해서 

 

오늘 목표였던 5주차 완강을 하지 못했다.

 

그래도 이해가 되지 않았던 부분을 스스로 끊임없이 해결하려고 노력하다 보니

SQL이 어떻게 작동되는지, 별칭에는 절대 기존 이름과 중복되지 않아야 한다거나 하는 부분 등을 알게되어서 다행이다.