[2024.07.26 TIL] sql join 및 pivot table

필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)

• left join
  LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회

• inner join
  INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회

• join의 기본 구조

  -- LEFT JOIN
  select 조회 할 컬럼
  from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
  
  -- INNER JOIN
  select 조회 할 컬럼
  from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

  공통컬럼은 묶어주기 위한 공통 값이므로 두 테이블의 컬럼명은 달라도 괜찮음
  ex) 테이블1.고객ID = 테이블2.고객아이디

join 해보기

• 주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
  (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)

  SELECT fo.order_id,
  		fo.customer_id,
  		fo.restaurant_name,
  		fo.price,
  		c.name,
  		c.age,
  		c.gender
  FROM food_orders fo left join customers c on fo.customer_id = c.customer_id

  

• 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
  (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
  *결제 정보가 없는 경우도 포함하여 조회

  SELECT fo.order_id,
  	   fo.restaurant_name,
  	   fo.price,
  	   p.pay_type,
  	   p.vat 
  FROM food_orders fo left join payments p on fo.order_id = p.order_id 

  

• 고객의 주문 식당 조회하기
  (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)
  *고객명으로 정렬, 중복 없도록 조회

  SELECT DISTINCT c.name,
  	   c.age,
  	   c.gender,
  	   fo.restaurant_name 
  FROM food_orders fo join customers c on fo.customer_id  = c.customer_id
  order by c.name

  

• 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
  (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
  *수수료율이 있는 경우만 조회

  SELECT fo.order_id,
  	   fo.restaurant_name,
  	   fo.price,
  	   p.vat,
  	   fo.price * p.vat charge
  FROM food_orders fo join payments p on fo.order_id = p.order_id

  

• 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
  (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
  할인 : 나이-500.005
  고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

  SELECT cuisine_type,
  	   sum(price) total_price,
  	   sum(price) - sum(discount_price) discounted_price,
  	   sum(discount_price) discount_price
  FROM 
  (
  select fo.cuisine_type,
  	   fo.price,
  	   fo.price * ((c.age - 50) * 0.005) discount_price
  from customers c join food_orders fo on c.customer_id = fo.customer_id
  where c.age >= 50
  ) join_discount
  group by 1
  order by 4 desc

  

• 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 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'
  	   		else 'price_group4' end '평균 주문 금액',
  	   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 '평균 연령'
  from
  (
  SELECT fo.restaurant_name,
  	   avg(price) price,
  	   avg(age) age
  FROM food_orders fo join customers c on fo.customer_id = c.customer_id
  GROUP by 1
  )a
  order by 1

  

조회한 데이터에 값이 없을 경우

• 테이블에 잘못된 값이 있을 경우

• join을 했을 때 값이 없는 경우

• 사용할 수 없는 데이터(데이터 타입이 다름)가 있거나 값이 없을 경우

방법 - 없는 값 제외하기

• Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외해 → 0으로 간주
• 명확하게 연상 지정 -> null 문법

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

방법 - 다른 값 대신 사용

• 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체

• 다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용
  다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
  null 값일 때 : coalesce(age, 대체값)

  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

  

조회한 데이터가 상식적이지 않은 값일 경우

• 상식적이지 않은 데이터의 예시

  케이스1 - 주문 고객의 나이

  보통 음식을 주문한 고객은 20세 이상인 성인인 경우가 많다. 하지만 데이터를 보면 2세와 같이 상식적이지 않은 값들을 확인할 수 있다.
  

  케이스2 - 결제 일자

  결제의 경우, 비교적 최근인 일자가 있어야 상식적일 것이다. 하지만, 데이터를 보면 1970년대와 같이 상식적이지 않은 값들을 확인할 수 있다
  

방법 - 조건문으로 값의 범위를 지정

• 상식적인 값 범위 지정

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

sql로 pivot table 만들기

• pivot table 구조
  2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미
  

음식점별 시간별 주문건수 pivot table 뷰 만들기

• (15~20시 사이, 20시 주문건수 기준 내림차순)
  MAX() - 집계함수가 NULL 값을 제외하고 처리 해주는 역할

  select restaurant_name,
        max(if(hh='15', cnt_order, 0)) "15",
        max(if(hh='16', cnt_order, 0)) "16",
        max(if(hh='17', cnt_order, 0)) "17",
        max(if(hh='18', cnt_order, 0)) "18",
        max(if(hh='19', cnt_order, 0)) "19",
        max(if(hh='20', cnt_order, 0)) "20"
  from 
  (
  select a.restaurant_name,
        substring(b.time, 1, 2) hh,
        count(1) cnt_order
  from food_orders a inner join payments b on a.order_id=b.order_id
  where substring(b.time, 1, 2) between 15 and 20
  group by 1, 2
  ) a
  group by 1
  order by 7 desc

  

성별, 연령별 주문건수 Pivot Table 뷰 만들기

• (나이는 10~59세 사이, 연령 순으로 내림차순)

  SELECT age,
  		max(if(gender = 'male', cnt_order, 0)) "male",
  		max(if(gender = 'female', cnt_order, 0)) "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) cnt_order
  FROM food_orders fo 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