SQL - SUM, AVERAGE, COUNT, MIN, MAX, GROUP BY, ORDER BY, REPLACE, SUBSTRING, CONCAT, IF, CASE

Leehyun·2025년 2월 18일
sql내일배움캠프데이터분석부트캠프
0

내일배움캠프

목록 보기
3/14
post-thumbnail

엑셀 대신 SQL로 한번에 계산하기(SUM, AVERAGE, COUNT, MIN, MAX)

  • 합계 : SUM(컬럼)
  • 평균 : AVG(컬럼)
  • 예시
select sum(food_preparation_time) total_food_preparation_time,
       avg(delivery_time) avg_food_delivery_time
from food_orders
  • 전체 데이터 개수 구하기 : COUNT(컬럼) <컬럼명 대신 1 혹은 * 사용 가능>
  • 예시
select count(1) count_of_orders,
       count(distinct customer_id) count_of_customers
from food_orders
  • 최솟값: MIN(컬럼)
  • 최댓값: MAX(컬럼)
  • 예시
select min(price) min_price,
       max(price) max_price
from food_orders

실습 1

  • 문제 : 주문 금액이 30,000원 이상인 주문건의 갯수 구하기
  • 답
select count(order_id) count_of_orders
from food_orders
where price>=30000

실습 2

  • 문제 : 한국 음식의 주문 당 평균 음식가격 구하기
  • 답 
select avg(price) as average_price
from food_orders
where cuisine_type='Korean'

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

음식 종류별로 평균 음식 가격을 구하기 위해 where 절을 사용해서 수십개의 쿼리를 작성하는 것은 너무 비효율적 -> Group By 이용하자

  • 기본 구조
select 카테고리컬럼(원하는컬럼 아무거나),
       sum(계산 컬럼),
from
group by 카테고리컬럼(원하는컬럼 아무거나)
  • 예시 
// 음식점 별 주문 금액 최댓값 조회하기
select restaurant_name,
       max(price) "최대 주문금액"
from food_orders
group by restaurant_name
// 결제 타입 별 가장 최근 결제일 조회하기 
select pay_type "결제타입",
       max(date) "최근 결제일"  
from payments
group by pay_type

-> 최근 결제일이 궁금하다면 간단하게 max 쓰면 된다!

오름차순, 내림차순 (ORDER BY)

  • 오름차순 : 숫자가 점점 커지는 순서로 정렬 (ORDER BY 기본형!)
  • 내림차순 : 숫자가 점점 작아지는 순서로 정렬 (ORDER BY DESC)
  • 기본 구조
select 카테고리컬럼(원하는컬럼 아무거나),
       sum(계산 컬럼),
from
group by 카테고리컬럼(원하는컬럼 아무거나)
order by 정렬을 원하는 컬럼 (카테고리컬럼(원하는컬럼 아무거나), sum(계산 컬럼) 둘 다 가능)
  • 예시
select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type
order by sum(price)

  • 정렬 종류

  • 예시

// 음식점별 주문 금액 최댓값 조회 - 내림차순
select restaurant_name,
       max(price) "최대 주문금액"
from food_orders
group by restaurant_name

종합 (SUM, AVERAGE, COUNT, MIN, MAX, GROUP BY, ORDER BY)

  • 코드 작성 순서
select
from
where
group by
order by

order by는 마지막에 오름차순, 내림차순으로 출력할 지 결정하는 아이니까, 맨 아래에 온다고 생각하기!

숙제

  • 문제 : 음식 종류별 가장 높은 주문 금액과 가장 낮은 주문금액을 조회하고, 가장 낮은 주문금액 순으로 (내림차순) 정렬하기
  • 답안 :
select cuisine_type,
       min(price) min_price,
       max(price) max_price
from food_orders
group by cuisine_type
order by min(price) desc

업무에 필요한 문자 포맷이 다르다면? (REPLACE, SUBSTRING, CONCAT)

  • 특정 문자를 다른 문자로 바꾸기 : REPLACE
  • 기본형 : replace(바꿀 컬럼, 현재 값, 바꿀 값)
  • 예시 
// 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기
select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'
// 주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기
select addr "원래 주소",
       replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'
  • 원하는 문자만 남기기, 문자 빼기 : SUBSTRING
  • 기본형 : substr(조회 할 컬럼, 시작 위치, 글자 수) or substring(조회 할 컬럼, 시작 위치, 글자 수)
  • 예시
// 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정
select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
  • 문자 합치기 : CONCAT
  • 기본형 : concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
  • 붙일 수 있는 종류들 : 컬럼, 한글, 영어, 숫자, 특수문자
  • 예시
// 서울시에 있는 음식점은 ‘[서울] 음식점명’으로 수정
select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

실습 3

  • 문제 : 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력 : ‘서울’, ‘타입’, ‘평균 금액’)
  • 답
select substring(addr, 1, 2) "시도",
       cuisine_type "음식 종류",
       avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2

🌝 group by 뒤에는 컬럼명을 써도 되지만, select에 쓴 순서대로 숫자로 표현도 가능하다!

  • 문제 : 이메일 도메인별 고객 수와 평균 연령 구하기 ( 이메일 : 8자리 + @~~ 모양이라고 가정)
  • 답
select substring(email, 10) "도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1
  • 문제 : ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
  • 답
select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
       count(1) "주문건수"
from food_orders
group by 1

concat 쓸 때 작은 따옴표 사용에 대해 주의하자! 잊지마..

조건이 필요하다면? (IF, CASE)

  • IF(가장 기본적인 조건지정문)
  • 기본형 : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
  • 예시 
// 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
//  ‘문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정
select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

중요

// 위에 있는 실습 3에서 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용(substr할 때 발생)

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

앞선 실습 3에서 gmail.com 사용자의 이메일 도메인이 mail.com 으로 출력된 부분을 수정하는 예시임!
(gmail은 다른 메일과 다르게 substr할때 원하는 모양으로 안 짤릴 수 있으니 출력 확인하고 보완하는 과정 필요할 수도 있음!)

  • 조건을 여러가지 지정 : CASE
  • 기본형 
case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end
  • 예시
// 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders
  • 조건을 사용할 수 있는 경우
  1. 새로운 카테고리 만들기 (Like 10대 여성, 10대 남성...)
  2. 연산식을 적용할 조건 지정하기 (Like 현금 및 카드 수수료)
  3. 다른 문법 안에서 적용하기 (Like 다른 문법 안에 if, if 안에 다른 문법)

실습 4

  • 문제 : 10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)
  • 답
select name,
       age,
       gender,
       case when (age between 10 and 19) and gender='male' then "10대 남자"
            when (age between 10 and 19) and gender='female' then "10대 여자"
            when (age between 20 and 29) and gender='male' then "20대 남자"
            when (age between 20 and 29) and gender='female' then "20대 여자" 
            end "그룹" 
from customers
where age between 10 and 29

🌝 BETWEEN은 이상, 이하 관계이다!! (BETWEEN A and B -> A<=x<=B)

  • 문제 : 음식 단가, 음식 종류 별로 음식점 그룹 나누기
    (Korean = 한식 ,Japanese, Chinese, Thai, Vietnamese, Indian = 아시아식, 그외 = 기타)
    (가격 = 5000 미만, 5000 이상 15000 미만, 15000 이상)
  • 답 
select restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
            when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"
from food_orders

실습 5

  • 문제 : 지역과 배달시간을 기반으로 배달수수료 구하기 (식당 이름, 주문 번호 함께 출력)
    (지역 : 서울, 기타 - 서울일 때는 수수료 계산 * 1.1, 기타일 때는 곱하는 값 없음
    시간 : 25분, 30분 - 25분 초과하면 음식 가격의 5%, 30분 초과하면 음식 가격의 10%)
  • 답
select restaurant_name,
       order_id,
       delivery_time,
       price,
       addr,
       case when delivery_time>25 and delivery_time<=30 then price*0.05*(if(addr like '%서울%', 1.1, 1))
            when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
            else 0 end "수수료"
from food_orders
  • 문제 : 주문 시기와 음식 수를 기반으로 배달할증료 구하기 (주문 시기 : 평일 기본료 = 3000 / 주말 기본료 = 3500
  • 답
select order_id,
       price,
       quantity,
       day_of_the_week,
       if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"
from food_orders

SQL Data Type 오류 해결하기

숫자로 보이는 데이터도, 알고보면 숫자처럼 보이는 문자 데이터일 수 있음!! (데이터 타입 항상 확인하기)
그럼 변환하고 싶다면? CONCAT 이용!

// 숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 

// 문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

숙제

  • 문제 : 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요. 주중 : 25분 이상, 주말 : 30분 이상
  • 답
select order_id, restaurant_name, day_of_the_week, delivery_time,
case when day_of_the_week = 'weekday' then if(delivery_time >= 25,'Late' ,'On-time')
     when day_of_the_week = 'weekend' then if(delivery_time >= 30,'Late' ,'On-time')
end '지연여부'
from food_orders

캠프 2일차라 그런지 오늘 너무 많이 피곤해서 집중을 첫 날에 비해 잘 못한거 같다 :( 그래도 계획된거는 다했으니 chill한 하루라고 생각하자. 오늘은 푹 자고 내일 다시 좋은 컨디션 chill하자 :)

profile
Leehyun
짱구가 코딩을..?
이전 포스트

SQL - SELECT, FROM, WHERE을 이용한 데이터 조회 및 필터링

다음 포스트

SQL - SUBQUERY, JOIN

0개의 댓글