[TIL] 내일배움캠프 백엔드 과정 2024.07.17 김현수

SQL로 한번에 계산하기

• 숫자 연산 종류

연산자	설명
+	더하기
-	빼기
*	ㅌ곱하기
/	나누기

 

---

SQL에서는 계산의 편의를 위해 함수를 제공한다!

• 합계 : SUM(컬럼)
• 평균 : AVG(컬럼)

ex) 상품 준비시간의 합계와 평균 구하기

select sum(food_preparation_time) totla_food_preperation_time,
	avg(delivery_time) avg_food_preparation_time
from food_oreders

---

전체 데이터의 갯수 구하기

 

현재 테이블이 몇 개의 데이터를 가지고 있는지, 몇 개의 값을 가지고 있는지도 구할 수 있습니다.

• 데이터의 갯수 : COUNT(컬럼) *컬럼 대신 1 혹은  * 사용가능.
  • ex) 주문 테이블의 전체 주문은  몇건인가요?
• 몇개의 값을 가지고 있는지 구할 때 : DISTINCT     
  • ex) 주문 테이블에서 주문을 한 고객은 몇명인가요?

ex) 주문 건수와 주문ㅇㄹ 한 고객 수 구하기

select count(1) count_of_orders,
     count(distinct customer_id) count_of_customers
from food_orders

---

데이터의 범위, 최솟값과 최댓값 구하기

 

최솟값 : MIN(컬럼)

최댓값 : MAX(컬럼)

---

 

GROUP BY로 범주별 연산 한번에 끝내기

 

여러번의  Query 없이, 카테고리를 지정하여 수식 함수로 연산을 할 수 있다.

----- GROUOP BY 기본 구조------
 select 카테고리 컬럼(원하는 컬럼 아무거나),
 		sum(계산 컬럼),
 from 테이블명
 group by 카테고리 컬럼(원하는 컬럼 아무거나)

 

---

ORDER BY로 결과 정렬하기

----- ORDER BY 기본 구조------
 select 카테고리 컬럼(원하는 컬럼 아무거나),
 		sum(계산 컬럼),
 from 테이블명
 group by 카테고리 컬럼(원하는 컬럼 아무거나)
 order by 정렬을 원하는 컬럼(카테고리 컬럼) / sum(계산 컬럼)

 

정렬의 종류

종류	구문	예시
오름차순	생략	order by sum(price)
내림차순	desc	order by sum(price) desc

 

---

필요한 문자 포맷이 다를 때, SQL로 가공하기

 

데이터에 잘못된 값이나 이저에 사용하던 값이 변경되어 다른 문자로 수정을 해야 할 때

 

특정 문자를 다른 문자로 바꾸기

 

replace(바꿀 컬럼, 현재 값, 바꿀 값)

 

ex) 예전에 '문곡리'라는 지명이 '문가리'로 바뀌었어요

replace(addr, '문곡리', '문가리')

select 컬럼
		replece(컬럼, 현재 값, 바꿀 값) // 별명 지정
from 테이블명
where 컬럼 like //바꾼 걸 보기 위해 작성

---

원하는 문자만 남기기

ex)전체 주소에서 앞부분인 '시 도' 부분만 필요합니다

 

substring (substr)

 ----기본 문법----
 
 substr(조회 할 컬럼, 시작 위치, 글자 수)

 

---

여러 컬럼의 문자를 합치기

 

ex) 서울시에 있는 음식점은 '[서울] 음식점 명' 이라고 수정하고 싶어요

 

concat

----기본 문법----

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3...........)

ex --------------------

select restaurant_name,
	addr,
    concat('[', substring(addr, 1, 2),']', restaurant_name)
from food_orders
where addr like '%서울%'

 

---

조건에 따라 포맷을 다르게 변경해야 한다면?

 

조건에 따라 다른 방법을 적용하고 싶을 때 - if 문 

----기본 문법----

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

 

---

조건을 여러가지 지정하고 싶을 때 -Case 문

*else는 조건에 부합하지 않는 것이 없을 때 생략 가능

 

----기본 문법----

case when 조건1 then 값(수식)1

         when 조건2 then 값(수식)2

         else 값(수식)3

end

 

---

SubQuery 문

Sub라는 명칭에서 알 수 있듯이, Query 안에 sub로 들어간 구문이라고 생각해 주시면 됩니다.

select column1, special_column
from
	( /* subquery */
	select column1, column2 special_column
    from table1
    ) a

select column1, column2
from table1
where column1 = (select col1 from table2)

 

---

Subquery 실습 1

-------------------------
Subquery 실습 
음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기
수수료 구간
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초과 3%



select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate<20000 then 0.01
            when price_per_plate<30000 then 0.02
            else 0.03 end ratio_of_add,
       price_per_plate
from
(
select restuarant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b

                         

---

                    

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

JOIN의 종류

LEFT JOIN : 공통 컬럼(키값)울 기준으로 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미합니다.

---기본 구조---
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명 = b.공통컬럼명

---실습 예제---
한국 음식의 주문별 결제 수단과 수수료율울 조회하기

select a.order_id,
       a.restaurant_name,
       a.price,
       b.pay_type,
       b.vat
from food_orders a left join paymnets b on a.order_id=b.order_id
where cuisine_type-"korean"

 

INNER JOIN : 공통 컬럼을 기준으로, 두 테이블 모두에 있는 값만 조회합니다

---기본 구조---
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼영

---실습 예제---
주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

select a.order_id,
       a.restaurant_name,
       a.price.
       b.vat,
       a.price*b.vat "수수료율
from food_orders a inner join payments b on a.order_id=b.order_id