[스파르타 내일배움캠프 / 데이터 분석 트랙] TIL(Today I Learned)_3주차_24.12.11

Today's Goals

1. SQL 쿼리 작성 익숙해지기 - 코드카타 레벨5 끝내기

2. 파이썬 기억 돌려내 - 파이썬 강의

 

 

코드카타 4문제에 하루의 반을 쓴 것 같다.

조금만 테이블이 많아지고 복잡해지니 바로 멘붕

 

차근차근 논리를 따라가면서 풀자

 

 

1. 코드카타 SQL

 

[임의의 값을(예:hour 컬럼 생성) 순서대로 생성하려면 CTE말고도 방법이 있다]

• 시간대별 입양된 동물의 수를 세는데, 입양 건이 없는 시간대에는 0을 표기해주고 싶다면?

# CTE 사용
WITH RECURSIVE HOUR_CTE AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 
    FROM HOUR_CTE 
    WHERE HOUR < 23
    ),
    OUT_COUNT AS (
    SELECT HOUR(DATETIME) AS HOUR, COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR(DATETIME) ) 
    
SELECT a.HOUR, IF(b.COUNT IS NULL,0,b.COUNT) AS COUNT
FROM HOUR_CTE a LEFT JOIN OUT_COUNT b ON a.HOUR=b.HOUR
ORDER BY HOUR ASC;

• 나의 첫 풀이는 얼마전 배운 CTE 활용
• 하지만 다른 방법은 없을까 구글링 중, SQL도 변수에 값을 할당하고 반복문을 만들어주는 방법이 있다는 것을 찾음

# set 기본 사용
set @변수명 = 대입값;
select @변수명 := 대입값;

# set 사용
SET @HOUR = -1;
SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(HOUR(DATETIME)) 
    FROM ANIMAL_OUTS 
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT 
    FROM ANIMAL_OUTS
WHERE @HOUR < 23;

• 중요 포인트는, set절에서만 =가 대입연산자로 작용, 그 외에는 비교연산자로 취급되므로 대입의 읨로는 := 써주기

 

 

[특정 기간에 해당하지 않는 값만 추출한다? 날짜 조건 설정이 아주 중요하다]

• 11월 1일 ~ 11월 30일에 대여할 수 있는 차량을 구해야 하는 문제, 나의 날짜 조건 설정은 위와 같았음
• 계속된 오답 퍼레이드, 대체 왜?
• 위 조건에 해당하는 날짜를 모두 뽑아보니, 잠깐, 반납일이 모두 문제의 조건 안에 있네?
• 그렇다면, 10월~12월 기간 동안 대여 중인 경우도 해당 기간동안 대여할 수 없는 차량인데, 그런 차는 없는걸까, 필터링 되지 못한 걸까?

• 그래서 조건을 위와 같이 바꾸었더니, 대여일이 11월 이전인 것 뿐만 아니라 반납일이 11월 이후인 것도 모두 필터링됨

 

• 위의 두가지 조건은 날짜 범위를 인식하는게 아예 다름
• 첫번째 방법은 대여일과 반납일이 11-01~11-30 안에 있어야만 걸러지는 것, 즉 9월부터 12월까지 빌리거나 10월부터 1월까지 빌리거나 등등은 추출되지 않아서 문제의 조건 만족 불가능
• 두번째 방법은 11-01~11-30 기간 중 일부(하루 이틀이라도) START와 END 날짜 사이에 걸리면 모두 추출 됨

 

• 최종적으로 서브쿼리를 쓰는 방법과 with을 쓰는 방법 모두를 고민해봄

#최종 답
SELECT a.CAR_ID,
       a.CAR_TYPE,
       ROUND(a.DAILY_FEE * 30 *(100 - c.DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR a 
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY b ON a.CAR_ID = b.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN c ON a.CAR_TYPE = c.CAR_TYPE
WHERE a.CAR_ID NOT IN (SELECT CAR_ID
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01') 
      AND c.DURATION_TYPE LIKE '30%'
GROUP BY a.CAR_ID
HAVING a.CAR_TYPE IN ('세단', 'SUV') AND (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;


# with을 쓰는 방법도 있나?
WITH AVAILABE_CAR AS (SELECT CAR_ID
                       FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                       WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01')
                       
SELECT a.CAR_ID,
       a.CAR_TYPE,
       ROUND(a.DAILY_FEE * 30 *(1 - b.DISCOUNT_RATE/100)) AS FEE
FROM CAR_RENTAL_COMPANY_CAR a 
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN b ON a.CAR_TYPE = b.CAR_TYPE
WHERE (a.CAR_ID NOT IN (SELECT CAR_ID FROM AVAILABE_CAR)) AND (b.DURATION_TYPE LIKE '30%')
GROUP BY a.CAR_ID
HAVING a.CAR_TYPE IN ('세단', 'SUV') AND (FEE >= 500000 AND FEE < 2000000)
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

 

[SQL 쿼리를 좀 더 간단하게 작성해보자! - 이상/이하 숫자 범위를 나눌 때, 부등호를 다 쓸 필요 없다?]

# 처음 답
WITH CAR_HISTORY AS (SELECT a.HISTORY_ID, 
                            a.CAR_ID, 
                            b.CAR_TYPE, 
                            DATEDIFF(a.END_DATE,a.START_DATE)+1 AS RENTAL_DURATION, 
                            b.DAILY_FEE
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a 
                     JOIN CAR_RENTAL_COMPANY_CAR b 
                     ON a.CAR_ID = b.CAR_ID
                     WHERE CAR_TYPE='트럭'),
    
    TRUCK_PLAN AS (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN WHERE CAR_TYPE = '트럭'),
    
    CAR_HISTORY_DURATION_TYPE AS (SELECT HISTORY_ID, CAR_ID, CAR_TYPE, 
    							  RENTAL_DURATION, DAILY_FEE,
                                  CASE
                                  WHEN (RENTAL_DURATION < 7) THEN NULL
                                  WHEN (RENTAL_DURATION >=7 AND RENTAL_DURATION < 30) THEN '7일 이상'
                                  WHEN (RENTAL_DURATION >=30 AND RENTAL_DURATION < 90) THEN '30일 이상'
                                  WHEN (RENTAL_DURATION >=90) THEN '90일 이상'
                                  END AS DURATION_TYPE
                                  FROM CAR_HISTORY)
     
SELECT a.HISTORY_ID,
       IF(a.DURATION_TYPE IS NULL, ROUND(a.RENTAL_DURATION * a.DAILY_FEE), 
       ROUND(a.RENTAL_DURATION * a.DAILY_FEE * (1-b.DISCOUNT_RATE/100))) AS FEE
FROM CAR_HISTORY_DURATION_TYPE a LEFT JOIN TRUCK_PLAN b ON a.DURATION_TYPE=b.DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC;


# 가상 테이블을 줄이고, 숫자 범주를 나눌 때 좀 더 간단히!
WITH CAR_HISTORY_DURATION_TYPE AS (SELECT a.HISTORY_ID, 
                            a.CAR_ID, 
                            b.CAR_TYPE, 
                            DATEDIFF(a.END_DATE,a.START_DATE)+1 AS RENTAL_DURATION, 
                            b.DAILY_FEE,
                            CASE
                                  WHEN (DATEDIFF(a.END_DATE,a.START_DATE)+1 < 7) THEN NULL
                                  WHEN (DATEDIFF(a.END_DATE,a.START_DATE)+1 >=90) THEN '90일 이상'
                                  WHEN (DATEDIFF(a.END_DATE,a.START_DATE)+1 >=30) THEN '30일 이상'
                                  WHEN (DATEDIFF(a.END_DATE,a.START_DATE)+1 >=7) THEN '7일 이상'
                                  END AS DURATION_TYPE
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY a 
                     JOIN CAR_RENTAL_COMPANY_CAR b 
                     ON a.CAR_ID = b.CAR_ID
                     WHERE CAR_TYPE='트럭')
       
     
SELECT a.HISTORY_ID,
       IF(a.DURATION_TYPE IS NULL, ROUND(a.RENTAL_DURATION * a.DAILY_FEE), 
       ROUND(a.RENTAL_DURATION * a.DAILY_FEE * (1-b.DISCOUNT_RATE/100))) AS FEE
FROM CAR_HISTORY_DURATION_TYPE a 
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN b 
ON a.DURATION_TYPE=b.DURATION_TYPE AND a.CAR_TYPE=b.CAR_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC;

• 나는 처음 세개의 가상 테이블을 생성하고 문제를 풀었고, 대여 기간을 범주화 할때, 7일 이상- 30일 미만을 모두 표기해 주었음
• 하지만, 가상 테이블은 join을 사용한 하나로 충분, 7일 이상 범주를 나눌 때도 중간에 끊기는 숫자 없이 연속적인 범위에서 나뉘는 것이므로 7일 이상만 표기해주면 충분
• WHEN DATEDIFF(end_date, start_date) + 1 between 7 and 29 THEN '7일 이상' 와 같이 between 사용 방법도 있음

 

 

[GROUP BY 가 아니어도, 결과가 1행인 경우가 있다?]

# 오답
WITH USER_JOIN_2021 AS (SELECT USER_ID,JOINED,COUNT(DISTINCT USER_ID) AS TOTAL_USER_2021 
						FROM USER_INFO WHERE YEAR(JOINED)='2021')

SELECT YEAR(a.SALES_DATE) AS YEAR,
       MONTH(a.SALES_DATE) AS MONTH,
       COUNT(DISTINCT a.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT a.USER_ID) / b.TOTAL_USER_2021,1) AS PUCHASED_RATIO
FROM ONLINE_SALE a JOIN USER_JOIN_2021 b ON a.USER_ID=b.USER_ID
GROUP BY YEAR(a.SALES_DATE), MONTH(a.SALES_DATE)
ORDER BY YEAR ASC, MONTH ASC;


# 정답
SELECT YEAR(a.SALES_DATE) AS YEAR,
       MONTH(a.SALES_DATE) AS MONTH,
       COUNT(DISTINCT a.USER_ID) AS PURCHASED_USERS,
       ROUND(COUNT(DISTINCT a.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) 
                                          FROM USER_INFO 
                                          WHERE YEAR(JOINED)='2021')
             ,1) AS PUCHASED_RATIO
FROM ONLINE_SALE a JOIN USER_INFO b ON a.USER_ID=b.USER_ID
WHERE YEAR(b.JOINED)='2021'
GROUP BY YEAR(a.SALES_DATE), MONTH(a.SALES_DATE)
ORDER BY YEAR ASC, MONTH ASC;

• 오답에서, with에 만들어진 가상테이블은 count를 사용함으로써 group by처럼 count 계산을 위해 다른 모든 행이 하나의 행으로 묶여버림(최종적으로 1행짜리 테이블 생성)
• 이 가상 테이블 결과 id는 구매 이력에 없는 id이기 때문에, 구매이력 테이블과 join해도 결과값이 없음
• 혹시라도 가상테이블에서 출력된 하나의 id가 구매이력에 포함되어도, 해당 id에 대해서만 join이 되기 때문에 다른 2021년 가입 id 중 구매이력이 있는 결과가 모두 추출되지 않음

 

 

 

 

2. 파이썬 바보 탈출기

 

파이썬 종합반 강의

---

[함수는 무엇인가?]

• 긴 길이의 코드를 하나로 묶어 효과적으로 관리/활용하는 방법
• 머신러닝 혹은 딥러닝도 사실상 하나의 거대한 함수로 볼 수 있음

def 함수이름(매개변수1, 매개변수2, ...):
    # 함수 내부에서 수행할 작업
    return 결과값  # (선택적) 함수의 결과를 반환

 

 

[매개변수(parameter)와 인수(argument)]

• 매개변수 : 함수를 정의할 때, 함수에서 이용되는 값을 지정하는 변수(함수에서 사용될 변수의 대표 이름이라고 생각하자)
• 인수 : 매개변수로 전달할 값, 함수를 호출할 때, 매개변수에 넣으려는 실제 값임 (전달인자 라고 하기도 함)

# 매개변수(parameter) 예시
def greet(name):  # 여기서 'name'은 매개변수입니다.
    print("Hello, " + name + "!")
    
# 함수 호출할 때 전달되는 값이 인수(argument)입니다.
greet("Alice")  # 함수 호출 시 "Alice"는 greet 함수의 매개변수 'name'에 전달됩니다.

 

 

[위치 인수와 키워드 인수]

• 위치 인수 : 매개변수의 위치에 따라 인수가 전달되는 것(인수가 매개변수 순서대로 매핑됨)
• 키워드 인수 : 매개변수마다 특정 값을 할당하여 함수를 작동시킴(순서를 바꿔도 결과는 같음)

# 위치 인수
def greet(name, age):
    print("안녕하세요", name, "님! 나이는 ", age, "세입니다.")

# 위치 전달인자 사용
greet("철수", 30)  # 출력: 안녕하세요, 철수님! 나이는 30세입니다.


# 키워드 인수
def greet(name, age):
    print("이름:", name)
    print("나이:", age)

# 키워드 인수를 사용하여 함수 호출
greet(name="Alice", age=30)

 

 

[함수를 만들면서 기본값 설정하기]

• 함수를 정의할 때, 매개변수에 특정 값을 지정해주면 그것이 기본값이 됨
• 함수 사용 시 괄호 안에 인수가 없으면 기본값으로 계산되는 것

def greet(name="Guest", age=25):
    print("이름:", name)
    print("나이:", age)

# 기본값이 설정된 함수 호출
greet()

# 키워드 인수를 사용하여 함수 호출
greet(name="Alice", age=30)

# 일부 매개변수에만 키워드 인수 사용하여 호출
greet(name="Bob")

• 키워드 인수를 사용하여 기본값이 아닌 값으로 함수를 작동시킬 수 있고, 일부 매개변수에만 키워드 인수를 사용하면 따로 값을 지정하지 않은 매개변수는 기본값으로 적용됨

 

 

[가변인수]

• 몇개인지 모를 여러개의 인수를 받아 함수를 만들 수 있음
• args : 임의의 개수의 위치 인수 (함수 내에서 튜플로 위치 인수들을 받아 처리)
• *kwargs : 임의의 개수의 키워드 인수 (함수 내에서 딕셔너리로 키워드 인수들을 받아 처리)

# args
def sum_values(*args):
    total = 0
    for num in args:
        total += num
    return total

result = sum_values(1, 2, 3, 4, 5)
print("합계:", result)  # 출력: 합계: 15


# *kwargs
def print_info(**kwargs):
    for key, value in kwargs.items():
        print(f"{key}: {value}")

print_info(name="Alice", age=30, country="USA")

 

 

 

파이썬 라이브 강의

---

[반복문에서 사용되는 pass에 대해 더 이해하기]

• pass는 지금 당장은 이 코드를 쓸 생각이 없지만, 나중에 좀 써볼 예정이라고 할 때 사용할 수 있음(현재 코드가 비어 있다는 뜻)

for i in range(3):
pass

• pass 없이 코드를 실행하면 syntax 오류가 발생하지만, pass를 쓰면 오류는 나지 않고 출력값 없이 코드 실행됨