강의 : [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
'내배캠_Data_3기 > TIL' 카테고리의 다른 글
240701_라이브세션 복습(2), SQL, UNION, JOIN, 파이썬 (0) | 2024.07.01 |
---|---|
240630_라이브세션 복습(1), SQL, 명령어 (0) | 2024.06.30 |
240627_SQL, Subquery, JOIN, INNER JOIN,LEFT JOIN, RIGHT JOIN (0) | 2024.06.27 |
240626(2)_SQL, 강의, 실습, WHERE, DISTINCT, COUNT, AVG, (0) | 2024.06.26 |
240626(1)_SQL, 문제풀이, SQL특강 1회차, CASE WHEN, IF (0) | 2024.06.26 |