Chapter.0 온보딩 주차(6)

TIL(Today I Learned)

---

1️⃣ SQL 강의 5주차 수강 및 4,5주차 과제

• 서로 다른 테이블에 저장된 데이터를 연결해 데이터 추출하기
• 실무에 자주 활용되는 함수 위주로 실습과 함께 SQL 개념을 복습

 

• 4주차 실습문제: 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
• 조건:
  - 평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
  - 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
  - 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
• 문항분석 : 서브쿼리를 통해 food_orders 테이블과 customers 테이블을 조인하여 각 식당별 평균 나이와 가격을 계산한 뒤 메인쿼리를 통해 그룹할당을 진행하면 문항에 따른 데이터를 출력할 수 있을 것이다.
• 출력 예시
  
• SQL 코드

select restaurant_name,
	case when avg_price <= 5000 then '5000원 이하'
		when avg_price > 5000 and avg_price <= 10000 then '~10000'
		when avg_price > 10000 and avg_price <= 30000 then '~30000'
		else '30000 초과' end price_group,
		age_group
FROM 
(select restaurant_name,
	avg_price,
	case when avg_age <= 29 then '20대'
		when avg_age <=39 then '30대'
		when avg_age <= 49 then '40대'
		else '50대 이상' end age_group
from
(
select f.restaurant_name,
	avg(f.price) avg_price,
	avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id = c.customer_id
group by restaurant_name 
) a
)b

그림 1: 출력된 데이터를 보면 평균에 따라 그룹화된 가게 별 가격과 나이가 출력된 것을 확인할 수 있다.

---

5주차 실습문제: 음식 타입별, 연령별 주문건수 pivot view 만들기

• 조건:
  - 음식 타입별, 연령별 주문건수 pivot view 만들어 보자. (연령은 10~59세 사이)
• 문항분석 : 서브쿼리로 food_orders와 customers 테이블을 조인하여, 연령대별로 그룹화하고, 각 그룹의 주문 횟수를 계산한 뒤 메인쿼리로 MAX 함수와 IF 문을 조합하여 각 연령대별 컬럼에 대한 최대 주문 횟수를 구하면 문항에 따른 데이터를 출력할 수 있을 것이다.
• 출력 예시
  
• SQL 코드

select cuisine_type,
	MAX(if(age_group='10대',cnt_order,0)) as'10대',
	MAX(if(age_group='20대',cnt_order,0)) as'20대',
	MAX(if(age_group='30대',cnt_order,0)) as'30대',
	MAX(if(age_group='40대',cnt_order,0)) as'40대',
	MAX(if(age_group='50대',cnt_order,0)) as'50대'
from
(select f.cuisine_type, 
	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 age_group,
	count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where c.age between 10 and 59
group by 1, 2) a
group by 1
order by 1

그림 2: 출력된 데이터를 보면 음식의 타입과 연령별로 Pivot View를 출력할 수 있다.

• 수정방안: SQL 코드를 보면 그룹화한 나이를 여러번 문자열로 지칭하는 것을 확인할 수 있는데, 정수로 지칭한 후 MAX()절에만 as "문자열"로 지칭하면 보다 간결하게 적을 수 있을 것이다.
• 수정 SQL 코드

select cuisine_type,
	MAX(if(age_group= 10,cnt_order,0)) as'10대',
	MAX(if(age_group= 20,cnt_order,0)) as'20대',
	MAX(if(age_group= 30,cnt_order,0)) as'30대',
	MAX(if(age_group= 40,cnt_order,0)) as'40대',
	MAX(if(age_group= 50,cnt_order,0)) as'50대'
from
(select f.cuisine_type, 
	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 age_group,
	count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where c.age between 10 and 59
group by 1, 2) a
group by 1
order by 1

---

💡 느낀점

• SQL 강의 수강 후 다양한 실습문제를 풀어보며, 서로 다른 테이블을 join을 통해 연결해 수식계산을 하는 등 다방면으로 활용이 가능할 수 있음을 느꼈습니다. 향후 학습을 통해 다양한 SQL이나 Python 함수를 찾아나가며 실용적이고 직관적인 코드를 완성하고 싶습니다.