[TIL/Today I Learned] DAY2

 

Subquery

한 번 연산하고, 다시 연산을 활용할 수 있는 문법 = subquery 문

 

음식 타입별 지역별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

• 음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05%
• 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08%
• 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
• 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%

select cuisine_type, total_quantity, count_res, case when count_res>=5 and total_quantity >=30 then 0.05 when count_res>=5 and total_quantity<30 then 0.08 when count_res<5 and total_quantity>=30 then 0.01 when count_res<5 and total_quantity<30 then 0.02 end rate from ( select cuisine_type, sum(quantity) total_quantity, count(distinct restaurant_name) count_res from food_orders group by 1 ) a

 

 

특정한 것만 세고 싶을 때 distinct 

---

JOIN

💡 주문을 한 사람을 확인하려면, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 해요

💡 주문 건별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠어요

 

• JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기
• 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기 (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

 

select f.order_id, f.customer_id, f.restaurant_name, f.price, c.name, c.age, c.gender from food_orders f left join customers c on f.customer_id=c.customer_id

여기서 f.customer_id=c.customer_id의 의미는

두 개 다 customer_id로 묶어 준다는 의미다.

 

---

오늘 어려웠던 문제 

 

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

* 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

select restaurant_name,           case when price<=5000 then 'price_group1'                    when price>5000 and price<=10000 then 'price_group2'                    when price>10000 and price<=30000 then 'price_group3'                    when price>30000 then 'price_group4' end price_group,          case when age <30 then 'age_group1'                   when age between 30 and 39 then 'age_group2'                   when age between 40 and 49 then 'age_group3'                   else 'age_group4'end age_group from ( select f.restaurant_name, avg(price) price, avg(age) age from food_orders f inner join customers c on f.customer_id=c.customer_id group by 1 ) a order by restaurant_name

 

일일이 범위 나눠주는 부분이 조금 복잡해서 헷갈렸음.

end 다음에 별명 적어주는 것 잊지 말기.

첫 번째 줄 restaurant_name은 사실 a.restaurant_name

오름차순: ㄱ 부터 내림차순: ㅎ부터

order by는 사실 1로 대체 가능. 

 

 

---

사용할 수 없는 데이터가 들어있거나, 값이 없는 경우에 어떻게 처리?

 

• [방법1] 없는 값을 제외해주기
• Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해줍니다. → 0으로 간주

select restaurant_name, avg(rating) average_of_rating, avg(if(rating<>'Not given', rating, null)) average_of_rating2 from food_orders group by 1

Not given이 아니면 rating으로. not given이면 null

즉 rating<>Not given이 참이면 rating 배출, 아니면 null

 

 

coalesce(age, 대체값)