[TIL] AI 웹 개발 7기 사전 캠프 3-3

일일 알고리즘 코드카타

자연수 n을 뒤집어 각 자리 숫자를 원소로 가지는 배열 형태로 리턴해주세요. 예를들어 n이 12345이면 [5,4,3,2,1]을 리턴합니다. (n은 100억 이하 자연수)

def solution(n):
    answer = []
    for i in str(n):
        answer.append(int(i))
        answer.sort(reverse=True)
    return answer

예전에 풀었던 문제에서 숫자 n을 문자화한 다음 for문을 이용해서 하나하나 리스트 업하는 방법으로 풀어봤다. 문제에서 예시로 12345를 주었기 때문에 내림차순으로 이해하고 코드를 짜봤다. 예시대로 나왔지만 정답은 아니었다. 내림차순이 아니라 역배열을 해야했다.

def solution(n):
    answer = []
    for i in str(n):
        answer.append(int(i))
        answer.reverse()
    return answer

역배열을 해주는 reverse()를 써봤는데, 결과가 이해가 안되게 나왔다.

실행한 결괏값 [5,3,1,2,4]이 기댓값 [5,4,3,2,1]과 다릅니다.

def solution(n):
    num_list = []
    for i in str(n):
        num_list.append(int(i))
    answer = num_list.reverse()
    return answer

역배열이 아니라 순서가 뒤죽박죽 엉망이라 코드에 작업 순서대로 적어봤다. (append가 끝난 이후에 리스트를 reverse 하도록) 근데 null이 나와버렸다. (왜지?)

def solution(n):
    answer = []
    for i in str(n):
        answer.append(int(i))
    reversed_num = answer.reverse()
    return answer

찾아보니 reverse 함수에서 반환값은 null이 된다고 한다.

ex) rev = list.sort() 실행 시 rev에는 None값 저장 

따라서 원하는 반환값을 실행할 때는 list를 해줘야 한다고 해서 임의의 반환값을 나타내는 reversed_num을 만들고 answer에 reverse()를 붙여줬다. 그리고 answer로 실행하여 원하는 반환값을 받고자 했다.

def solution(n):
    answer = []
    for i in str(n):
        answer.append(int(i))
    answer.reverse()
    return answer

먼가 답이 석연치 않아서 순서가 뒤죽박죽 꼬인 부분을 찾아봤다. 만약 append(int(i))를 하는 동시에 reverse()를 한다면 순서가 꼬일 수도 있다고 생각했다. 그리고 answer.reverse()를 입력하고 answer를 출력해야하는 논리는 같으니 위 풀이처럼 새로운 코드를 적기보다 간단히 들여쓰기만 수정하면 내가 원하는 코드가 나올 것 같아서 실행해보니 역시나...

 

쉬운 길을 어렵게 돌아간 것 같다.

1. 문제를 충분히 이해하는 시간을 가지자.

2. 막혔을 때. 어렵게 돌아가려 하지말고 리프레쉬하고 생각해보자.

 

엑셀보다 쉽고 빠른 SQL 4주차

4-1 포맷 변경과 조건문 복습하고, 이번 수업 내용 맛보기

이번 수업에서 배울 내용 맛보기

-연산을 여러번 해야하는데, 쿼리문에 길게 쓰는 것밖에는 방법이 없을까?

-연산한 결과를 다른 연산이나 조건문에 사용하고 싶은데, 계속 반복해서 적어줘야할까?

-필요한 데이터가 여러 테이블에 나누어져 있는데 한 번에 조회해서 사용할 수는 없을까?

 

4-2 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)

1) Subquery 가 필요한 경우

- 여러번의 연산을 수행해야 할 때

- 조건문에 연산 결과를 사용해야 할 때

- 조건에 Query 결과를 사용하고 싶을 때

 

2) Subquery 문의 기본 구조

Sub 라는 명칭에서 알 수 있듯이, Query 안에 sub 로 들어간 구문

ex) (a+b)*2

이 수식처럼 연산한 결과를 한번 더 사용하는 것.

한번 쿼리를 사용하고 그 결과문을 다시 한번 사용하는 것을 서브쿼리라 한다.

SELECT price/quantity

FROM

(

SELECT price, quantity

FROM food_orders

) a #subquary문 이름

나눈 값만 출력 - price, quantity를 subquary에서 불러오고 다시 select에서 사용한 것

 

3) [실습] Subquery 문을 이용하여 연산문 적어보기

주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기

• select 기본문
• 가져올 컬럼 적기
• subquery 문으로 추가

Subquery 문 안을 수정해서, 음식 주문시간이 25분보다 초과한 시간을 가져오기

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time

#괄호 안 연산 결과를 가져와서 over_time이 0보다 큰 경우 출력, 0보다 작은 경우 0을 출력. (25분 초과가 안되는 경우도 있기 때문에)

from

(

select order_id, restaurant_name, food_preparation_time-25 over_time #25분 보다 초과된 시간을 구하기 위한 연산, over_time이라 별명

from food_orders

) a # 괄호 안 select from으로 데이터를 불러오는데 (order_id, restaurant_name, food_preparation_time-25 over_time)

 

4-3 [실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

1) [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기

(수수료 구간 -

~5000원 미만 0.05%

~20000원 미만 1%

~30000원 미만 2%

30000원 초과 3%)

select restaurant_name,

price_per_plate*ratio_of_add "수수료"

#3 2번의 연산 결과를 다시 사용. 수수료 비율을 곱해줘서 수수료를 구할 수 있다

from

(

select restaurant_name,

case when price_per_plate<5000 then 0.005

when price_per_plate between 5000 and 19999 then 0.01

when price_per_plate between 20000 and 29999 then 0.02

else 0.03 end ratio_of_add,

price_per_plate

from

(

select restaurant_name, avg(price/quantity) price_per_plate

from food_orders

group by 1

) a #1 food_orders에서 레스토랑 별로 단가의 평균을 구하는 것, 확인하기 위해서 괄호안만 선택해서 실행 가능

) b #2 1번의 연산 결과로 단가별 수수료 비율을 계산(case 적용)

# 가장 가운데 있는 괄호부터 보는 것이 좋다

 

2) [실습] 음식점의 지역(두글자만)과 평균 배달시간(20분 30분 30분초과)으로 segmentation 하기

SELECT restaurant_name,

sido,

avg_delivery_time,

CASE when avg_delivery_time <= 20 then '<=20'

when avg_delivery_time > 20 and avg_delivery_time <= 30 then '20<x<=30'

else '>30' end delivery_time_segment

FROM

(

SELECT restaurant_name,

SUBSTR(addr, 1, 2) sido,

AVG(delivery_time) avg_delivery_time

FROM food_orders

group by 1, 2

) a

 

4-4 [실습] 복잡한 연산을 Subquery 로 수행하기

1) [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고, 주문수량과 음식점수 별 수수료율을 산정하기

(음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.05% 음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.08% 음식점수 5개 미만, 주문수 30개 이상 → 수수료 1% 음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)

SELECT cuisine_type,

total_quantity,

count_res,

case when count_res>=5 and total_quantity>=30 then 0.005

when count_res>=5 and total_quantity<30 then 0.008

when count_res<5 and total_quantity>=30 then 0.01

when count_res<5 and total_quantity<=30 then 0.02 end rate

FROM

(

SELECT cuisine_type,

SUM(quantity) total_quantity,

COUNT(DISTINCT restaurant_name) count_res#음식 타입 별로 식당 이름이 몇개 있는지 값의 개수

FROM food_orders

group by 1

) a

 

2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

(할인조건 수량이 5개 이하 → 10%, 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% 이 외에는 일괄 1%)

select restaurant_name,

sum_price,

sum_quantity,

case when sum_quantity<=5 then 0.1

when sum_quantity>15 and sum_price>=300000 then 0.005

else 0.01 end discount_rate

from

(

select restaurant_name,

sum(price) sum_price,

sum(quantity) sum_quantity

from food_orders

group by 1 #범주 별 계산

) a

 

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

1) JOIN 이 필요한 경우

- 주문 가격은 주문테이블, 결제 수단은 결제테이블에 있을 때 각각 확인해야 하는 것처럼 각각의 테이블에서 별건으로 확인 해야할 때가 있다.

 

2) JOIN 의 기본 원리와 종류

JOIN 은 기본적으로 엑셀의 Vlookup 과 유사

주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 한다.

(엑셀에서는 vlookup(고객ID, 고객 정보, 3, False))

JOIN 은 동일한 원리. 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것

이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼

즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것

 

조인은 하는 방법에 따라 여러가지

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

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

 

JOIN 의 기본 구조

select *

from food_orders left join payments on food_orders.order_id=payments.order_id

#order_id라는 공통 컬럼이 있다. 밑으로 내리면 결제정보가 없더라도 food_orders 데이터는 계속 된다.

 

select *

from food_orders inner join payments on food_orders.order_id=payments.order_id

#둘 다 가지고 있는 order_id만 출력

 

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

#join문에서도 별명 사용가능(서브쿼리 컬럼 테이블 다 가능)

 

4-6 [실습] JOIN 으로 두 테이블의 데이터 조회하기

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

where cuisine_type='Korean'

조회하려는 테이블 두개를 적고 조인문으로 묶고 어떤 컬럼으로 할지 뒤에 온절에 명시한다.

 

2) [실습] 고객의 주문 식당 조회하기

(조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) *고객명으로 정렬, 중복 없도록 조회

select distinct 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

 

4-7 [실습] JOIN 으로 두 테이블의 값을 연산하기

1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료) *수수료율이 있는 경우만 조회

select f.order_id,

f.restaurant_name,

f.price,

p.vat,

f.price * p.vat vat2

from food_orders f inner join payments p on f.order_id = p.order_id

 

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

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)

*할인 : 나이-50*0.005 * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

select cuisine_type, #서브쿼리가 a라는 데이터로 적용된 것이라 f.을 쓸 수 없다. 생략하거나 a.으로 쓴다.

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 sum(price * discount_rate) desc #3으로 써도 된다. desc는 내림차순

 

HW. 4주차 숙제 해설

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

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

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

1. SQL 기본구조 적어보기

2. 각 테이블에서 필요한 컬럼 정리하기

3. Join 문으로 데이터 조회문 적어보기

4. 데이터를 연산해보기

5. 연산한 데이터를 segmentation 에 활용하기

select restaurant_name,

case when price <= 5000 then '~5000'

when price > 5000 and price <= 10000 then '5000~10000'

when price > 10000 and price <= 30000 then '10000~30000'

when price > 30000 then '30000~' end gruop_price,

case when age < 30 then '20대'

when age >= 30 and age < 40 then '30대'

when age >= 40 and age < 50 then '40대'

else '50대이상' end group_age

from

(

select f.restaurant_name,

avg(price) price,

avg(age) age

from food_orders f inner join customers c on f.customer_id = c.customer_id

group by 1

) t

order by restaurant_name

 

- 서브쿼리를 작성할 때 함수를 쓰고자 한다면 f, c, 같은 소속을 안써도 된다? 쓰지말아야 한다?

- group by의 위치 때문에 계속 결과가 다르게 나와서 꽤나 고생했다. 맨 마지막에 쓰려고만 생각해서 범주별로 뽑아 줄 수 없는 서브쿼리 밖에 둬서 계속 결과가 하나만 나왔다. 논리를 생각하면서 풀어보자.

- 평균값을 구하고 별명을 지어줘야지 메인 쿼리를 작성할 때 오류를 줄일 수 있다. 쿼리를 작성할 때는 간단하게 하자.