231204

[sql 4-5]

* join - 필요한 데이터가 서로 다른 테이블에 있을 때 조회

SELECT * from food_orders(a 테이블에 해당) left join payments(b테이블에 해당)  on food_orders.order_id (=food_orders의 order_id 를 사용할 것임. 테이블명과 사용할 컬럼을 묶어줌) =payments.order_id 두테이블에서 order_id컬럼을 통해 묶어줌

 

left join 공통 컬럼을 기준으로 a 테이블의 모든 데이터, b 테이블은 공통 데이터만

inner join 교집합만

 

불러올 때, 어느 테이블에서 가져오는 지 명시 like => f.order_id (from food_orders f(별칭))

 

[sql 4-6]

실습2 고객의 주문 식당 조회하기/ 고객이름, 연령, 성별, 주문식당/ 고객명으로 정렬, 중복없도록 조회

SELECT distict c.name , c.age , c.gender , f.restaurant_name from food_orders f left join customers c on f.customer_id = c.customer_id order by c.name ==고객명으로 정렬

 

중복이 없도록 = 값의 갯수를 세는 것과 동일

* 맨 앞에 distinct를 붙임 = 중복값을 없애줌

 

[sql 4-7]

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

 

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

조회컬럼=음식타입, 원래 가격, 할인 적용 가격, 할인 가격/

할인 나이-50*0.05/

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

SELECT cuisine_type, sum(price) price, sum(price*discount_rate) discounted_price from (SELECT f.cuisine_type , f.price , c.age , (c.age - 50)*0.005 discount_rate 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(큰 순서. 내림차순)==할인금액이 큰 순서로 정렬. 세번 째 컬럼을 기준으로 정렬

 

* Group by 는 where 절보다 아래에 작성

 

[sql 4-8]

숙제

식당별 평균 음식 주문 금액과 주문자의 평균연령을 기반으로 세그멘테이션 하기/

평균음식주문금액 기준 5000 10000 30000 30000초과/

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

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

 

select case when avg_price<=5000 then '5,000이하' when avg_price<=10000 then '10,000이하' when avg_price<=30000 then '30,000이하' else '30,000초과' end "평균주문금액" , restaurant_name , avg_price, case when avg_age<30 then "30대이하" when  avg_age<40 then "30대" when avg_age<50 then "40대" else "50대 이상" end "평균연령", avg_age from (select f.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 f.restaurant_name )a order by restaurant_name

 

[sql 5주차]

- 숫자 계산 시 not given 존재 => 0으로 간주됨. 이를 방지하기 위해 not given 을 null 값으로 변경

avg(if (rating<>'Not given',rating, null))

<>아니다. Not given 값이 아닐 경우 원래대로 rating 사용 not given이면 null 값

 

-null 값을 제외

where b.customer_id is not null

 

- 다른 값으로 대체

coalesce(b.age, 20)

null인 경우 20으로 대체

 

-이상값 처리 방법

하한 상한을 고정값으로 대체, 그 외는 그대로 사용

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

 

- sql로 피벗 테이블 만들기

 

 

음식점별 시간별 주문건수 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

 

행 축 = 첫 줄에 명시

연산자가 있는 경우 group by 필수

 

성별, 연령별 주문

건수 나이 10-59세 사이. 연령순 내림차순

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 == b.gender, count(1) order_count   ) t group by 1 order by age DESC

 

*window 함수

순위 매기기/ 누적합

 

-     음식 타입별 주문 건수가 가장 많은 상점 3개씩 조회하기

1)   음식 타입별, 음식점별 주문 건수 집계

2)   Rank 함수 적용

3)   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

각 음식점의 주문건이 해당 음식타입에서 차지하는 비율을 구하고, 주문건이 낮은 순으로 정렬했을 때 누적합 구하기.

 

1)     음식타입별, 음식점별 주문건수 집계하기

2)     카테고리별 합, 카테고리별 누적합 구하기

3)     각음식점이 차지하는 비율 구하고, 음식점별 주문건수 오름차순으로 정렬하기

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

 

- 년도별 3월의 주문건수 구하기

select date_format(date(date), '%Y') "년", date_format(date(date), '%m') "월", DATE_FORMAT(date(date), '%Y%m') "년월", count(1) "주문건" from food_orders f inner join payments p on f.order_id =p.order_id where DATE_FORMAT(date(date), '%m') = '03' group by 1,2,3 order by 1

 

sql 강의가 끝났다만 그냥 내용이 스쳐 지나간 것과 같아서 꾸준히 복습을 해야겠다

+ 한번에 이해가지 않는 부분들 = 피벗, 누적합 재차 학습할 필요.

단순 나열이 아닌 til 작성법에 익숙치 않음. 타 개발 블로그 참조해가며 발전 시킬 필요.