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

일일 알고리즘 코드카타

Q. 문자열 s를 숫자로 변환한 결과를 반환하는 함수, solution을 완성하세요.

제한 조건

- s의 길이는 1 이상 5이하입니다.

- s의 맨앞에는 부호(+, -)가 올 수 있습니다.

- s는 부호와 숫자로만 이루어져있습니다.

- s는 "0"으로 시작하지 않습니다.

def solution(s):
    answer = int(s)
    return answer

설마 이거겠어? 라고 입력한 코드가 답이었다. 왜지?

 

Q. 임의의 양의 정수 n에 대해, n이 어떤 양의 정수 x의 제곱인지 아닌지 판단하려 합니다.
n이 양의 정수 x의 제곱이라면 x+1의 제곱을 리턴하고, n이 양의 정수 x의 제곱이 아니라면 -1을 리턴하는 함수를 완성하세요.

def solution(n):
    x = n ** (1/2)
    if x % 1 == 0:
        return (x+1) ** 2
    else:
        return -1

루트를 이용하면 쉽게 할 수 있다고 생각했다. n제곱근을 구하는 연산자는 **(1/n) 이므로 이용해본 결과 잘 나왔다.

 

엑셀보다 쉽고 빠른 SQL - 5주차

5-1 Subquery, JOIN 복습하고, 이번 수업 내용 맛보기

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

- 예상하지 못한 값이 Query 결과에 나올 때 어떻게 처리해야 할까?

- 엑셀에서 해야하는 Pivot, SQL 로 한 번에 구현할 수 있을까?

- SQL 로 이런 것까지 할 수 있었다니! 업무 시간이 엄청 줄어들 것 같은데!

 

5-2 조회한 데이터에 아무 값이 없다면 어떻게 해야할까?

1) 데이터가 없을 때의 연산 결과 변화 케이스

- 테이블에 잘못된 값

- JOIN 을 했을 때 값이 없는 경우

 

2) [방법1] 없는 값을 제외해주기

- Mysql 에서는 사용할 수 없는 값일 때 해당 값을 연산에서 제외 → 0으로 간주

 

select restaurant_name,

avg(rating) avg_rating,

avg(if(rating<>'Not given', rating, null)) avg_rating2 #null: 데이터 없음 if문에서 유용

from food_orders

group by 1

 

- 따라서, 명확하게 연산을 지정해주기 위해 null 문법을 이용(상황에 따라 잘 사용해야함)

- null 제거를 했을 때 (join 시에는 inner join 과 동일함)

select a.order_id,

a.customer_id,

a.restaurant_name,

a.price,

b.name,

b.age,

b.gender

from food_orders a left join customers b on a.customer_id=b.customer_id

where b.customer_id is not null #null이 아닌 것을 다 출력

 

3) [방법2] 다른 값을 대신 사용하기

- 사용할 수 없는 값 대신 다른 값을 대체해서 사용하는 방법

- 데이터 분석 시에는 평균값 혹은 중앙값 등 대표값을 이용하여 대체

- 다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용

• 다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)
• null 값일 때 : coalesce(age, 대체값)

- null 을 다른 값으로 대체한 쿼리문을 실행

customer 테이블에 없는 데이터 중에 age 만 20으로 채워진 것을 확인

select a.order_id,

a.customer_id,

a.restaurant_name,

a.price,

b.name,

b.age,

coalesce(b.age, 20) "null 제거", #2 b.age가 null이면 20으로 대체

b.gender

from food_orders a left join customers b on a.customer_id=b.customer_id

where b.age is null #1age에서 null만 출력

 

5-3 조회한 데이터가 상식적이지 않은 값을 가지고 있다면 어떻게 해야할까?

1) 상식적이지 않은 데이터의 예시

- 데이터가 비어있는 경우도 있지만, 상식적이지 않은 경우도 있습니다.

- 케이스1 - 주문 고객의 나이

- 케이스2 - 결제 일자

 

2) [방법] 조건문으로 값의 범위를 지정하기

- 조건문으로 가장 큰 값, 가장 작은 값의 범위를 지정 (상식적인 수준에서)

- 위의 나이의 경우 아래와 같은 범위를 지정

- 범위를 지정해준 결과, 15세 미만이거나 80세 초과인 경우 15, 80으로 각각 대체된 것을 확인

select name,

age,

case when age<15 then 15

when age>=80 then 80

else age end re_age

from customers

 

5-4 [실습] SQL 로 Pivot Table 만들어보기

1) Pivot table 구조 소개

- Pivot table 이란? : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미

- Pivot table 의 기본 구조

- Pivot table 의 예시 : 집계 기준 : 일자, 시간

- 베이스 데이터 만들고 그 것을 이용하여 Pivot Table 뷰를 만든다.

select * from food_orders

 

 

select * from payments #select * from을 한줄아상 띄우면 서로 다른 쿼리문으로 인식 #각 쿼리문에 커서를 두면 그 쿼리문이 실행된다.

 

2) [실습] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

1. 음식점별, 시간별 주문건수 집계하기

2. Pivot view 구조 만들기

select f.restaurant_name,

substr(p.time, 1, 2) hh,

count(1) cnt_order

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

where substr(p.time, 1, 2) between 15 and 20

group by 1, 2 #베이스 데이터를 먼저 만들어 준다

 

select restaurant_name, #행축

max(if(hh='15', cnt_order, 0)) "15", #여기부터 한 줄씩 컬럼이 추가된다

max(if(hh='16', cnt_order, 0)) "16", #16시 일 때 주문건수

max(if(hh='17', cnt_order, 0)) "17", #max 최대값을 이용해야한다

max(if(hh='18', cnt_order, 0)) "18", #'15' 명칭을 붙여준다

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 #max sum같이 계산이 들어가면 group by를 써준다

order by 7 desc #마지막 조건

 

3) [실습] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

1. 성별, 연령별 주문건수 집계하기

2. Pivot view 구조 만들기

select age,

max(if(gender='male', cnt_order, 0)) "male", #성별이 남성일 때 주문건수 넣고 아니면 0

max(if(gender='female', cnt_order, 0)) "female" #피벗 테이블을 만들 때 max

from

(

select 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) cnt_order #주문건수

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

where age between 10 and 59

group by 1, 2 #gender age 기

) a

group by 1 #max를 썼기 때문에

order by 1 descㅓ

 

5-5 업무 시작을 단축시켜 주는 마법의 문법 (Window Function - RANK, SUM)

1) Window Function 의 사례와 기본 구조

- Window Function 은 각 행의 관계를 정의하기 위한 함수로 그룹 내의 연산을 쉽게 만들어줍니다.

• 한식 식당 중에서 주문건수가 많은 순으로 순위를 매기고 싶은데요, 가능할까요?
• 한식 식당 전체 주문건수 중에서 A 식당이 차지하는 비율을 알고 싶은데 가능할까요?
• 2건 이상 주문을 한 소비자 중에, 처음 주문한 식당과 2번째로 주문한 식당을 같이 조회할 수 있을까요?

- 기본 SQL 구조로 해결하기 위해서는 복잡하게 Subquery 문을 이용하거나, 여러번의 연산을 수행해줘야 하지만, 자체적으로 제공해주는 기능을 이용하면 조금 더 편리 → 바로 이 기능들이 Window function

 

- Window Function 의 기본 구조

window_function(argument) over (partition by 그룹 기준 컬럼 order by 정렬 기준)

• window_function : 기능 명을 사용해줍니다. (sum, avg 와 같이 기능명이 있습니다)
• argument : 함수에 따라 작성하거나 생략합니다.
• partition by : 그룹을 나누기 위한 기준입니다. group by 절과 유사
• order by : window function 을 적용할 때 정렬 할 컬럼 기준을 작

2) [실습1] N 번째까지의 대상을 조회하고 싶을 때, Rank

- Rank 는 이름에서 유추할 수 있듯이 ‘특정 기준으로 순위를 매겨주는’ 기능

[실습] 음식 타입별로 주문 건수가 가장 많은 상점 3개씩 조회하기

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

2. Rank 함수 적용하기

3. 3위까지 조회하고, 음식 타입별, 순위별로 정렬하기

select cuisine_type, #3위까지만 조회하기 위해 한번도 서브쿼리를 이용

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 #rank함수는 ()안에 아무것도 안적어도 된다

from #rank() over()는 쌍이다 over 뒤에는 구분을 나누는 partition을 써야한다(음식타입별로 음식을 구할건데 주문건수가 많은 순서로)

(

select cuisine_type,

restaurant_name,

count(1) cnt_order

from food_orders

group by 1, 2

) a

) b

where ranking<=3

 

3) [실습2] 전체에서 차지하는 비율, 누적합을 구할 때, Sum

- Sum 은 앞서 배운 합계를 구하는 기능과 동일. 누적합이 필요하거나 카테고리별 합계컬럼와 원본 컬럼을 함께 이용할 때 유용하게 사용

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

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

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

 

이 외의 Window Function 을 알고 싶다면, 여러가지를 검색해서 사용해보는 것을 추천

select cuisine_type,

restaurant_name,

cnt_order,

sum(cnt_order) over(partition by cuisine_type) sum_cuisine, #cuisine_type별 함계 #gruop by를 쓰지 않아도 합계를 구할 수 있음 = 윈도우 함수

sum(cnt_order) over(partition by cuisine_type order by cnt_order) cum_cuisine #누적합에선 1번 값, 1번+2번 값, 1번+2번+3번 값처럼 순차적으로 내려온다(order by)

from #cuisine_type으로 덩어리는 묶어주고 order by로 순차적으로 누적합을 구한다.

(

select cuisine_type,

restaurant_name,

count(1) cnt_order

from food_orders

group by 1, 2

) a

order by cuisine_type, cnt_order

 

5-6 날짜 포맷과 조건까지 SQL 로 한 번에 끝내기 (포맷 함수)

1) 날짜 데이터의 이해

- 문자타입, 숫자타입과 같이 날짜 데이터도 특정한 타입을 가지고 있다.

- 년, 월, 일, 시, 분, 초 등의 값을 모두 갖고 있으며 목적에 따라 ‘월’, ‘주’, ‘일’ 등으로 포맷을 변경할 수도 있다.

select date,

date(date) change_date #문자형의 데이터를 날짜형으로 변경 date()함수 이용

from payments

 

2) [실습1] 날짜 데이터의 여러 포맷

1. yyyy-mm-dd 형식의 컬럼을 date type 으로 변경하기

2. date type 을 date_format 을 이용하여 년, 월, 일, 주 로 조회해보기

1. 년 : Y (4자리), y(2자리)
2. 월 : M, m
3. 일 : d, e
4. 요일 : w

select date(date) date_type, #date()를 이용 문자->날짜

date_format(date(date), '%Y') "년", # date_format(): date()함수에 형식을 정해준다

date_format(date(date), '%m') "월", # date라는 컬럼을 date() 함수로 날짜로 바꾸고

date_format(date(date), '%d') "일", # %Y: 데이트 포멧이라는 함수를 이용하려 date(date)을 연도만 남겨준다.

date_format(date(date), '%w') "요일" # %w: 요일 0:일요일 1:월요일

from payments

 

4) [실습2]

1. 년도, 월을 포함하여 데이터 가공하기

2. 년도, 월별 주문건수 구하기

3. 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' #3월만 필요하기 떄문에 #문자로 바뀌었기 때문에 ''

group by 1, 2, 3 #년/월/년월로 세워줘야하기 때문

order by 1 #연도별 정렬

 

HW. 5주차 숙제 해설

음식 타입별, 연령별 주문건수 pivot view 만들기 (연령은 10~59세 사이)

1. SQL 기본구조 작성하기
2. Pivot view 를 만들기 위해 필요한 데이터 가공하기
3. Pivot view 문법에 맞추어 수정하기

select cuisine_type,
       max(if(age=10, cnt_order, 0)) "10대",
       max(if(age=20, cnt_order, 0)) "20대",
       max(if(age=30, cnt_order, 0)) "30대",
       max(if(age=40, cnt_order, 0)) "40대",
       max(if(age=50, cnt_order, 0)) "50대"
from 
(
select f.cuisine_type,
       case when c.age between 10 and 19 then 10
            when c.age between 20 and 29 then 20
            when c.age between 30 and 39 then 30
            when c.age between 40 and 49 then 40
            when c.age between 50 and 59 then 50 end age,
       count(1) cnt_order
from food_orders f inner join customers c on f.customer_id = c.customer_id
where c.age between 10 and 59
group by 1, 2
) a
group by 1

- 서브 쿼리문 작성까지는 쉬웠는데 pivot table이라는 개념이 아직은 생소해서 어려움을 겪었다.

- 4주차, 5주차 SQL 강의는 SQL이 익숙해 질 때 1번 더 들어야겠다.

- 괄호를 미리 써놓지 않아서 수정해야하는데 그 위치를 찾는게 너무 힘들었다.

- 그동안 SQL TIL을 작성할 때는 무작정 코드를 복사/붙여넣기만 해서 줄글이 되었는데, 오늘 매니져님께서 알려주신 방법으로 코드블럭을 사용해 봤다. 앞으로 잘 이용해 보자.