[1/14] TIL - 코드카타, 기초 프로젝트, SQL (2,3주차)

코드카타

 

23.

def solution(num):
    count = 0
    
    while num != 1:
        if count >= 500:
            return -1
    
        if num % 2 ==0:
            num //= 2
        else:
            num = (num *3) + 1
            
        count += 1
        
    return count

 

 

 

---

 

 

 

기초 프로젝트

ppt: 그레이 블랙 심플한 마케터 포트폴리오 프레젠테이션 - Presentation

코드 : 기초 프로젝트_파이팅 2조! - Colab

 

 

 

 

---

[SQL 2주차]

 

1.  sum, average, count, min, max

 

1) 합계와 평균

select sum(food_preparation_time) total_food_preparation_time,
       avg(delivery_time) avg_food_delivery_time
from food_orders

 

2) 전체 데이터 개수

• 데이터 갯수: count (컬럼)  * 컬럼명 대신 1 or *
• 몇개의 값을 가지고 있는지 구할 때 : DISTINCT

 

3) max , min

select min(price) min_price,
       max(price) max_price
from food_orders

 

 

2. where 절 사용

한국 음식의 주문 당 평균 음식가격 구하기

select avg(price) as average_price
from food_orders
where cuisine_type='Korean'

 

 

3. GROUP BY 

음료 종류별 주문 금액 합계 구하기

select cuisine_type,
       sum(price) sum_of_price
from food_orders
group by cuisine_type

 

결제 타입별 가장 최근 결제일 조회하기

select pay_type "결제타입",
       max(date) "최근 결제일"
from payments
group by pay_type

 

 

4. 쿼리 정렬

- order by 사용

 

음식점별 주문 금액 최댓값 조회 ( 최댓값 기준으로 내림차순)

select restaurant_name,
       max(price) "최대 주문금액"
from food_orders
group by restaurant_name
order by max(price) desc

 

 

5.  기초 구조

select
from
where
group by
order by

 

 

 

 

 

[SQL 3주차]

 

1. REPLACE, SUBSTRING, CONCAT

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

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

 

주소의 ‘문곡리’ 를 ‘문가리’ 로 바꾸기

select addr "원래 주소",
       replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

2) 원하는 문자만 남기기

substr(조회 할 컬럼, 시작 위치, 글자 수)

 

서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

 

3) 여러 컬럼의 문자 합치기

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

 

• 붙일 수 있는 문자의 종류
  • 컬럼
  • 한글
  • 영어
  • 숫자
  • 기타 특수문자

 

서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

 

2.  문자 데이터를 바꾸고 GROUP BY 

'[지역(시도)] 음식점이름 (음식종류)' 컬럼을 만들고, 총 주문건수 구하기

select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
       count(1) "주문건수"
from food_orders
group by 1

 

 

3. IF,CASE

 

if 

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

 

잘못된 이메일 주소 (gmail) 만 수정을 해서 사용

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

 

 

case

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

 

주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

 

 

4. User Segmention 

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

 

 

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 음식 수 : 3개 이하이면 할증 없음 / 3개 초과이면 기본료 * 1.2)

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