2024-02-08 AI 코딩 TIL

4 주차

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

join에는 두 가지 기능이 있으며 left와 inner 두 가지가 있다.

-- 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.공통컬럼명

---

-- - [실습] 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

food_orders와 customers 테이블을 합쳐서 표기 하여 준다.

---

-- - 1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기 -- -- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율) -- *결제 정보가 없는 경우도 포함하여 조회 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

order_id의 공통으로 조인을 하고 필요한 컬럼은 조회

---

-- - 2) [실습] 고객의 주문 식당 조회하기 -- -- (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) -- *고객명으로 정렬, 중복 없도록 조회 SELECT DISTINCT c.name , c.age , c.gender , f.restaurant_name FROM food_orders f inner join customers c on f.customer_id = c.customer_id ORDER BY 1

DISTINCT를 맨앞에 붙여줌으로 같은 이름에 같은 내용이 반복되어 있는 것들은 제거를 해준다.

---

JOIN으로 두 테이블의 값을 연산하기

-- - [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기 -- -- (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) -- *수수료율이 있는 경우만 조회 SELECT f.order_id '주문 번호', f.restaurant_name '식당 이름', f.price '주문 가격', p.vat '수수료율', FORMAT(f.price*p.vat, 0.) '수수료' FROM food_orders f inner join payments p on f.order_id = p.order_id

각 테이블에서 필요한 값을 가지고 오고 각 테이블의 값을 연산하여 새로운 값을 만들어 낸다.

---

서브칼럼을

만들어서 계산하는 곳을 두 번 해야 하는데 순서를 잘 맞추어 주어야 한다.

---

만들다 보니 알게 된 것

GROUP BY = 그룹 짓기	ORDER BY = 순서대로
COUNT = 그룹 안에 횟수 세기	DESC = 역순으로 정렬하기
SUM = 그룹안에 총합 구하기	​
DISTINCT = 중복값 제거	​
AVG = 그룹안에 평균 값	​

각자 서로 잘사용 되는 명령어가 있다.

​

---

-- 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기 -- - 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과 -- - 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상 -- * 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬 SELECT restaurant_name '음식점', CASE when avg_price > 30000 then '30,000원 초과' when avg_price > 15000 then '30,000원' when avg_price > 7500 then '10,000원' ELSE '5000원' END '평균 음식 주문 금액 기준', CASE when avg_age >= 50 then '50대 이상' when avg_age >= 40 then '40대 이상' when avg_age >= 30 then '30대 이상' ELSE '~20대' END '평균 연령' FROM ( SELECT f.restaurant_name , AVG(c.age) avg_age, AVG(f.price) avg_price FROM food_orders f inner join customers c on f.customer_id = c.customer_id GROUP BY f.restaurant_name )a ORDER BY 1

먼저 식당 별이기 때문에 식당이름으로 그룹화를 해주고

그룹이 된 곳에서 평균 값을 구해준 다음에

평균 금액에 맞추어 잘 생각해서 분류를 해준다.

---

5 주차

조회한 데이터에 아무 값이 없다면 어떻게 해야 할까?

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

notgive 같이 문자들은 0으로 대신해서 계산하기 때문에

null로 변경해 주어야지 합산이나 평균에 영향을 주지 않는다.

---

select a.order_id, a.customer_id, a.restaurant_name, a.price, b.name, b.age, b.gender from food_orders a left join customers b on a.customer_id=b.customer_id where b.customer_id is not null

b.customer_id의 값 중에 null의 값이 있다면 빼도록 할 수 있다.

---

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 where b.age is null

coalesce(b.age, 20) null의 값이 있다면 대체값을 넣어준다.

---

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

일정범위에 나이만 정해주고 싶을 때

---

SQL로 Pivot Table 만들어보기

-- 2) [실습] 음식점별 시간별 주문건수 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 f.restaurant_name , SUBSTR( p.`time`, 1, 2) hh, COUNT(1) cnt_order FROM food_orders f inner join payments p on f.order_id = p.order_id WHERE SUBSTR( p.`time`, 1, 2) BETWEEN 15 and 20 GROUP BY f.restaurant_name, SUBSTR( p.`time`, 1, 2) )a GROUP BY 1 ORDER BY 7 DESC

max(if(hh='15', cnt_order, 0)) "15" 이 부문에 max값을 넣어주지 않으면 값이 이상하게 나오므로 알아둘

---

-- [실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순) SELECT gender, MAX(if(age_s='10',cnt_order,0)) '10대', MAX(if(age_s='20',cnt_order,0)) '20대', MAX(if(age_s='30',cnt_order,0)) '30대', MAX(if(age_s='40',cnt_order,0)) '40대', MAX(if(age_s='50',cnt_order,0)) '50대' FROM ( SELECT c.gender , CASE when c.age < 20 then '10' when c.age < 30 then '20' when c.age < 40 then '30' when c.age < 50 then '40' ELSE '50' END age_s, 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

행이 나이 열이 성별이 되었다

---

select age, max(if(gender='male', order_count, 0)) male, max(if(gender='female', order_count, 0)) female from ( select b.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) order_count from food_orders a inner join customers b on a.customer_id=b.customer_id where b.age between 10 and 59 group by 1, 2 ) t group by 1 order by age

반대로 행이 성별 열이 나이가 되었다

어느 부분에 무엇을 적어야 행이 되고 열이 되는지를 잘 기억해 두면 좋을 것 같다.

---

업무 시작을 단축시켜 주는 마법의 문법

(Window Function - RANK, SUM)

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

-- 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기 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 fo group by cuisine_type,restaurant_name )a

rank()와 over()는 항상 같이 따라다닌다.

rank로 새로운 칼럼은 생성해 준다고 알아두자

2. 파티션 설정을 해주면 각 음식점 안에서 순위를 나누어준다.

---

-- 음식 타입별로 주문 건수가 가장 많은 상점 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 fo group by cuisine_type,restaurant_name )a )b WHERE ranking<=3

추가로 1,2,3위만 나오게 하기 위해서

---

-- [실습] 각 음식점의 주문건이 해당 음식 타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적 합 구하기 SELECT cuisine_type , restaurant_name , cnt_order, SUM(cnt_order) OVER(PARTITION BY cuisine_type) sum_cu, SUM(cnt_order) OVER(PARTITION BY cuisine_type ORDER BY cnt_order) cum_cu FROM ( SELECT cuisine_type , restaurant_name , COUNT(1) cnt_order FROM food_orders fo group by cuisine_type,restaurant_name )a ORDER BY 1,3

SUM(cnt_order) OVER(PARTITION BY cuisine_type) sum_cu

= cuisine_type이 같다면 모든 카운트를 합하라

​

SUM(cnt_order) OVER(PARTITION BY cuisine_type ORDER BY cnt_order) cum_cu

= cuisine_type 이 같되 ORDER BY cnt_order로 작은 순서부터 누적으로 합산을 해서 가라 (숫자가 같다면 한 번에 계산)

---

날짜 포맷과 조건까지 SQL로 한 번에 끝내기 (포맷 함수)

select date(date) date_type, date from payments

1번은 날짜 데이터, 2번은 문자 데이터이다.

2. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기 1. 년 : Y (4자리), y(2자리) 2. 월 : M, m 3. 일 : d, e 4. 요일 : w

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

date_format을 이용해서 내가 필요한 것만 빼서 쓸 수 있다.

---

-- 년도별 3월의 주문건수 구하기 SELECT Year, Mouth, cnt FROM ( SELECT DATE_FORMAT(date(p.`date`), '%Y') Year, DATE_FORMAT(date(p.`date`), '%m') Mouth, COUNT(1) cnt FROM food_orders f inner join payments p on f.order_id = p.order_id GROUP by 1,2 )a where Mouth='03' order by 1

각 년도에 맞추어 3월만 필터링해서 카운트를 해준다.

-- 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이) SELECT cuisine_type, MAX(IF(age_s='10',cnt_order,0)) '10대', MAX(IF(age_s='20',cnt_order,0)) '20대', MAX(IF(age_s='30',cnt_order,0)) '30대', MAX(IF(age_s='40',cnt_order,0)) '40대', MAX(IF(age_s='50',cnt_order,0)) '50대' FROM ( SELECT f.cuisine_type , CASE when c.age < 20 then '10' when c.age < 30 then '20' when c.age < 40 then '30' when c.age < 50 then '40' ELSE '50' end age_s, 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 2 DESC

나이에 맞는 음식 취향 조사

---

-- 음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이) SELECT cuisine_type, MAX(IF(age_s='10',cnt_order,0)) '10대', MAX(IF(age_s='20',cnt_order,0)) '20대', MAX(IF(age_s='30',cnt_order,0)) '30대', MAX(IF(age_s='40',cnt_order,0)) '40대', MAX(IF(age_s='50',cnt_order,0)) '50대', SUM(cnt_order) '총합' FROM ( SELECT f.cuisine_type , CASE when c.age < 20 then '10' when c.age < 30 then '20' when c.age < 40 then '30' when c.age < 50 then '40' ELSE '50' end age_s, 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 SUM(cnt_order) DESC

더욱 깔끔하게 가능하다.

​