240628_SQL, JOIN, 문제, 명령어

강의 : [KDC] 엑셀보다 쉽고 빠른 SQL - 4주차

 

[실습] JOIN으로 두 테이블의 데이터 조회하기

 

1. 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)

    *결제 정보가 없는 경우도 포함하여 조회

• 흐름 정리해보기
  • 어떤 테이블에서 데이터를 뽑을 것인가 -> food_orders, payments
  • 어떤 컬럼을 이용할 것인가 -> cuisine_type, order_id, restaurant_name, price, pay_type, vat
  • 어떤 조건을 지정해야 하는가 -> where cuisine_type='Korean'
  • 어떤 함수(수식)을 이용해야 하는가 -> (결제 정보가 없는 경우도 포함)left join payments (on order_id)
• 전체 구조

select f.order_id, 
       f.restaurant_name, 
       f.price, 
       p.pay_type, 
       p.vat
from food_orders f left join payments p on f.order_id=p.order_id 
where cuisine_type='Korean'

 

 

2. 고객의 주문 식당 조회하기

    (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당)

     *고객명으로 정렬, 중복 없도록 조회

• 흐름 정리해보기
  • 어떤 테이블에서 데이터를 뽑을 것인가 -> customers, food_orders
  • 어떤 컬럼을 이용할 것인가 -> name, age, gender, restaurant_name
  • 어떤 조건을 지정해야 하는가 ->  없음
  • 어떤 함수(수식)을 이용해야 하는가 -> left join food_oreders (on customer_id), (정렬)order by, (중복제거)distinct
• 전체 구조

select distinct c.name, 
       c.age, 
       c.gender, 
       f.restaurant_name
from customers c left join food_orders f on c.customer_id=f.customer_id
order by c.name

 

 

---

[실습] JOIN으로 두 테이블의 값을 연산하기

1. 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)

    *수수료율이 있는 경우만 조회

• 흐름 정리해보기
  • 어떤 테이블에서 데이터를 뽑을 것인가 -> food_orders, payments
  • 어떤 컬럼을 이용할 것인가 -> order_id, restaurant_name, price, vat
  • 어떤 조건을 지정해야 하는가 -> 없음
  • 어떤 함수(수식)을 이용해야 하는가 -> (수수료율이 있는 경우만 조회=공통점)inner join, price*vat
• 전체 구조

select f.order_id,
		f.restaurant_name,
		f.price,
		p.vat,
		f.price*p.vat "수수료"
from food_orders f inner join payments p on f.order_id=p.order_id

 

2. 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을

   구하기

    (조회 컬럼 : 음식타입, 원래 가격, 할인 적용 가격, 할인 가격)

    *할인 : 나이-50*0.005

    *고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

• 흐름 정리해보기
  • 어떤 테이블에서 데이터를 뽑을 것인가 -> food_orders, customers
  • 어떤 컬럼을 이용할 것인가 -> cusine_type, price, age
  • 어떤 조건을 지정해야 하는가 -> 50세 이상
  • 어떤 함수(수식)을 이용해야 하는가 -> left join customers (on customer_id), (할인)age-50*0.005, order by desc
• 전체 구조

SELECT cuisine_type,
		sum(price) price,
		sum(price*dis_price) disprice
from
(select f.cuisine_type, 
		f.price,
		(c.age-50)*0.005 dis_price
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.age>=50
) a
group by 1
order by 3 desc

 

---

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

• 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
• 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
• 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

• 흐름 정리해보기
  • 어떤 테이블에서 데이터를 뽑을 것인가 -> food_orders, customers
  • 어떤 컬럼을 이용할 것인가 -> restaurant_name, price, age, 
  • 어떤 조건을 지정해야 하는가 -> 없음
  • 어떤 함수(수식)을 이용해야 하는가 -> (두 테이블 모두에 데이터가 있는 경우)inner join (on customer_age)id), avg(),order by asc
• 전체 구조

SELECT restaurant_name,
		case when avg_price<=5000 then 'price_group1'
			when avg_price<=10000 then 'price_group2'
			when avg_price<=30000 then 'price_group3'
			else 'price_group4' end price_group,
		case when avg_age between 20 and 29 then 'age_group1'	
			when avg_age between 30 and 39 then 'age_group2'
			when avg_age between 40 and 49 then 'age_group3'
			else 'age_group4' end age_group
from
(SELECT 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 1
)a
order by 1

 

---

강의 : 강의 : [KDC] 엑셀보다 쉽고 빠른 SQL - 5주차

 

조회한 데이터에 아무 값이 없을때 

1. 없는 값 제외하기 : IS NOT NULL

2. 다른 값을 대신 사용하기

• 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
• null 값일 때 : coalesce(age, 대체값)

 

조회한 데이터가 상식적이지 않은 값을 가지고 있다

조건문으로 값의 범위를 지정하기

조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정해 줄 수 있습니다. → 상식적인 수준 안에서 범위를 지정해줍니다.

select name, age,
		case when age<15 then 15
		when age>=80 then 80
		else age end re_age
from customers

 

SQL 로 Pivot Table 만들어보기

Pivot table 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.

예시	1시	2시	3시	4시
6월 24일	17	20	24	5
6월 25일	7	23	20	40

 

예시

#음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)
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

 

 

업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)

Window Function 의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

• window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
• argument : 함수에 따라 작성하거나 생략합니다.
• partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사하다고 생각해주시면 됩니다.
• order by : window function 을 적용할 때 정렬 할 컬럼 기준을 적어줍니다.

[실습] N 번째까지의 대상을 조회하고 싶을 때, Rank

• Rank 는 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능입니다.
• 예를 들어, 주문 건수별 순위 매기기, 결제 시간이 빠른 순으로 순위 매기기 등이 가능합니다.
• [실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

SELECT cuisine_type,
		restaurant_name,
		cnt_order,
		ranking "순위"
from 
(select cuisine_type,
		restaurant_name,
		cnt_order,
		rank() over(partition by cuisine_type order by cnt_order desc) ranking
from
(select cuisine_type,
		restaurant_name,
		count(1) cnt_order
from food_orders
group by 1,2
) a)b
where ranking <=3

 

 

[실습] 전체에서 차지하는 비율, 누적합을 구할 때, Sum

• Sum 은 앞서 배운 합계를 구하는 기능과 동일합니다.
• 다만, 누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용할 수 있습니다.
• [실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기

select cuisine_type,
		restaurant_name,
		cnt_order,
		sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
		sum(cnt_order) over (partition by cuisine_type order by cnt_order ) cum_cuisine
from		
(select cuisine_type,
		restaurant_name,
		count(1) cnt_order
from food_orders
group by 1,2
)a
order by cuisine_type, cnt_order

 

 

포맷함수로 날짜를 지정하거나 조건에 날짜를 사용해야할 때

날짜 데이터의 이해

• 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있다.
• 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있다.

select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments