[본캠프 33일차] SQL 공부, 파이썬 공부, 통계학 공부, 아티클 스터디②

 

열심히 닦자, 열심히... 버텨~~~

 

오늘 한 일은,

• SQL 공부
  • [코드카타] 3문제 풀기 (113~115번)
• 파이썬 공부
  • [코드카타] 알고리즘 1문제 풀기 (68번)
  • 수준별 학습반: [python standard] 1회차(이론), 2회차(실습) 수강하기
• 통계학 공부
  • [통계 라이브 세션] 2회차 수강하기
  • [기초 통계학] 4주차 수강하기
• 두 번째 아티클 스터디 진행하기 (누적은 22번째(11+4+5+2))

---

 

SQL 공부: [코드카타] SQL 문제 풀기 (113~115번)

113. (1978) Employees Whose Manager Left the Company

Find the IDs of the employees whose salary is strictly less than $30000 and whose manager left the company. When a manager leaves the company, their information is deleted from the Employees table, but the reports still have their manager_idset to the manager that left.
Return the result table ordered by employee_id.

SELECT employee_id
FROM Employees
WHERE (salary < 30000) AND -- 조건1. salary가 30000 미만
      ((manager_id) NOT IN ( -- 조건2. manager_id가 employee_id에 없을 것
        SELECT employee_id
        FROM Employees
        )) AND
      (manager_id IS NOT NULL) -- 조건3. manager_id가 NULL값이 아닐 것
ORDER BY employee_id

 

114. (626) Exchange Seats

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.
Return the result table ordered by id  in ascending order.

-- 처음 제출한 쿼리 (오답 처리: runtime error 발생(...당연함))
WITH s2 AS (
    SELECT *,
       LAG(student, 1, NULL) OVER(ORDER BY id) student_prev1,
       LEAD(student, 1, NULL) OVER(ORDER BY id) student_foll1
    FROM Seat
)

SELECT id,
       CASE WHEN id % 2 = 0 THEN student_prev1
       CASE WHEN ((id % 2 != 0) AND (student_foll1 IS NOT NULL)) THEN student_foll1
       CASE WHEN ((id % 2 != 0) AND (student_foll1 IS NULL)) THEN student END student
FROM s2

처음엔 위와 같이 쿼리를 작성해서 제출했다. 조건을 나름 신경써서 작성했는데 에러 메시지가 뜨길래 '조건 작성을 이런 식으로 하면 안 되나' 싶어서 바로 질문방으로 달려갔다. (..멈춰, 과거의 나)

 

250403 그렇다, 조건이고 뭐고가 아니라 기본적인 문법을 틀린 것이다...

-- 두 번째로 제출한 쿼리 (정답 처리됨)
WITH s2 AS (
    SELECT *,
       LAG(student, 1, NULL) OVER(ORDER BY id) student_prev1,
       LEAD(student, 1, NULL) OVER(ORDER BY id) student_foll1
    FROM Seat
)

SELECT id,
       CASE WHEN id % 2 = 0 THEN student_prev1
            WHEN ((id % 2 != 0) AND (student_foll1 IS NOT NULL)) THEN student_foll1
            WHEN ((id % 2 != 0) AND (student_foll1 IS NULL)) THEN student END student
FROM s2

그냥 첫 번째 작성한 쿼리에서 student 컬럼을 CASE WHEN 구문으로 작성할 때 CASE를 한 번만 썼어야 했는데 조건마다 CASE를 적어대서 오류가 뜬 것이다. 바보임... 기본을 까먹지 말자!

 

115. (1341) Movie Rating

Write a solution to:

• Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
• Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

250403 새로운 영단어 겟

-- 첫 번째 작성한 쿼리 (runtime error 발생)
WITH n AS (
    SELECT DISTINCT r.user_id,
           u.name,
           COUNT(r.user_id) OVER(PARTITION BY r.user_id) rating_cnt
    FROM MovieRating r LEFT JOIN Users u ON r.user_id = u.user_id
    ORDER BY rating_cnt DESC, name LIMIT 1
)

WITH m AS (
    SELECT DISTINCT r.movie_id,
           m.title,
           AVG(r.rating) OVER(PARTITION BY r.movie_id) average_rating
FROM MovieRating r LEFT JOIN Movies m ON r.movie_id = m.movie_id
WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
ORDER BY average_rating DESC, m.title LIMIT 1
)

SELECT name results
FROM n
UNION
SELECT title results
FROM m

가상 테이블로 각각 만들어서 UNION으로 합치려고 했지만 두 번째 WITH 구문에서 에러가 발생하면서 WITH 구문을 여러 개 사용한 쿼리는 수정하기로 했다. (사실 이렇게 WITH구문을 사용하는 게 어떤지 질문방에 물어보고 싶었지만 아까 너무 간단한 걸로 한 번 물어봐서 또 찾아가기 곤란한...)

 

-- 두 번째 작성한 쿼리 (오답 처리됨)
SELECT u.name results
FROM (
    SELECT DISTINCT r.user_id,
           u.name,
           COUNT(r.user_id) OVER(PARTITION BY r.user_id) rating_cnt
    FROM MovieRating r LEFT JOIN Users u ON r.user_id = u.user_id
    ORDER BY rating_cnt DESC, name LIMIT 1
) u
UNION
SELECT m.title results
FROM (
    SELECT DISTINCT r.movie_id,
           m.title,
           AVG(r.rating) OVER(PARTITION BY r.movie_id) average_rating
    FROM MovieRating r LEFT JOIN Movies m ON r.movie_id = m.movie_id
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    ORDER BY average_rating DESC, m.title LIMIT 1
) m

두 번째 쿼리는 WITH 구문으로 만든 가상의 테이블들을 바로 FROM절에 인라인 뷰로 작성해서 UNION으로 합친 것이다.

테스트 케이스는 통과해서 바로 제출했는데 회원 이름과 영화 이름이 같을 때 두 번 띄워야 하는데 한 줄만 띄우는 문제가 발생했다. 

 

-- 세 번째 작성한 쿼리 (정답 처리)
SELECT u.name results
FROM (
    SELECT DISTINCT r.user_id,
           u.name,
           COUNT(r.user_id) OVER(PARTITION BY r.user_id) rating_cnt
    FROM MovieRating r LEFT JOIN Users u ON r.user_id = u.user_id
    ORDER BY rating_cnt DESC, name LIMIT 1
) u
UNION ALL -- 두 번째 쿼리에서 UNION으로 합친 것을 UNION ALL로 수정함
SELECT m.title results
FROM (
    SELECT DISTINCT r.movie_id,
           m.title,
           AVG(r.rating) OVER(PARTITION BY r.movie_id) average_rating
    FROM MovieRating r LEFT JOIN Movies m ON r.movie_id = m.movie_id
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    ORDER BY average_rating DESC, m.title LIMIT 1
) m

두 테이블을 합칠 때 UNION ALL로 합치니 중복값을 제거하지 않고 결과 테이블을 조회하면서 정답으로 처리됐다.

---

파이썬 공부: [python standard] 1ᐧ2회차 수강하기

pandas 라이브러리로 데이터 프레임 구조 변형

.transpose() / .T

• 행과 열을 전환하는 메서드
• 데이터 프레임이 너무 컬럼이 많아서 중간에 생략되는 경우에 모든 컬럼을 확인하고자 할 때 사용함
• 데이터 크기가 클 경우 처리하는 데 시간이 오래걸릴 수 있음
  • => 이럴 경우에 head()로 잘라낸 일부에 .transpose() / .T를 사용하기!

pd.pivot_table(df, index= , columns= , values= , aggfunc= )

• 파라미터 소개
  • df : 피벗 테이블을 만들고자 하는 데이터프레임 이름
  • index : 축(제목열 자리)으로 사용할 컬럼 이름 (여러 개면 리스트로 입력)
  • columns : 제목행으로 사용할 컬럼 이름 (여러 개면 리스트로 입력) (생략 가능)
  • values : 값으로 이용할 컬럼 이름
  • aggfunc : values를 어떻게 연산할지 (여러 개면 리스트로 입력)
• index나 columns를 리스트로 입력 : 멀티 인덱스로 피벗테이블이 생성됨
  values를 리스트로 입력 : 그 값들로 각각 연산한 결과가 들어간 피벗 테이블이 생성됨
  • [참고] 판다스 - pivot_table (피벗 테이블2)
    ▲각 파라미터에 입력하는 아규먼트의 수에 따라 피벗 테이블이 어떤 식으로 달라지는지 이해하기 좋음

.melt(id_vars= , values_vars= , var_name= , value_name= , col_level= , ignore_index= )

• 데이터프레임의 특정 컬럼을 축으로 값으로 설정한 컬럼들을 행으로 녹여내는 메서드
  [참고] 파이썬 판다스 melt() 사용법, pd.melt()
• 파라미터 소개
  • id_vars : 축으로 사용할 컬럼 이름(그대로 유지됨) 
  • value_vars : 축으로 들어간 컬럼의 하위 카테고리로 들어갈 컬럼 이름(녹아서 축 옆에 행으로 들어옴)
    • 생략 가능. 생략할 시에 모든 제목행이 축 옆에 하나하나 들어옴
    • 이때 제목행이 멀티 인덱스일 경우 variable이 그 층만큼 한 컬럼씩 들어옴
      (제목행이 2겹이면 variable도 2줄 생김)
  • var_name : 그 하위 카테고리가 들어있는 컬럼에 붙일 이름 (생략 시 variable로 이름이 붙음)
  • value_name : 해당 id와 variable에 해당하는 값이 들어갈 컬럼에 붙일 이름 (생략 시 value로 이름이 붙음)

.stack(level= , dropna= )

• 데이터프레임의 제목행을 인덱스의 하위 레벨로 변환하는 메서드
  • 멀티 인덱스를 처리하기 좋음
  • 한 번에 하나씩만 처리 가능하고 동시에 여러 개는 안 됨
• 파라미터 소개
  • level : 몇 번째 제목행을 인덱스의 하위 레벨로 불러올 것인지 (기본은 -1)
  • dropna : 결과에서 결측치를 제거할 것인지(True), 아닌지(False) (기본은 True)

.stack() 메서드 사용한 모습

.unstack(level= , dropna= )

• 데이터프레임의 인덱스를 제목행의 하위 레벨로 변환하는 메서드
  • 멀티 인덱스를 처리하기 좋음
  • 한 번에 하나씩만 처리 가능하고 동시에 여러 개는 안 됨
• 파라미터 소개
  • level : 몇 번째 인덱스를 제목행의 하위 레벨로 불러올 것인지 (기본은 -1)
  • dropna : 결과에서 결측치를 제거할 것인지(True), 아닌지(False) (기본은 True)

.unstack() 메서드를 사용한 모습

---

통계학 공부①: [기초 통계학] 4주차 수강하기

회귀(Regression)

선형 회귀①: 단순 선형 회귀

• 하나의 독립 변수(X)와 하나의 종속 변수(Y) 간의 관계를 직선으로 모델링하는 방법
  (ex. 광고비(X)와 매출(Y) 간의 관계를 분석: 현재의 광고비를 바탕으로 예상되는 매출 예측하기)
• 회귀식 : Y = β0 + β1X   (β0: 절편, β1: 기울기)
  1차 함수랑 똑같아! y = ax+b
• 독립 변수의 변화에 따라 종속 변수가 어떻게 변화하는지 설명하고 예측
• 간단하고 해석이 용이
• 데이터가 직선적 경향을 따를 때 사용 데이터가 선형적이지 않을 경우 적합하지 않음

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.linear_model import LinearRegression
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_squared_error, r2_score

# 예시 데이터 생성
np.random.seed(0)
X = 2 * np.random.rand(100, 1)
y = 4 + 3 * X + np.random.randn(100, 1)

# 데이터 분할
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 단순선형회귀 모델 생성 및 훈련
model = LinearRegression()
model.fit(X_train, y_train)

# 예측
y_pred = model.predict(X_test)

# 회귀 계수 및 절편 출력
print("회귀 계수:", model.coef_)
print("절편:", model.intercept_)

# 모델 평가
mse = mean_squared_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print("평균 제곱 오차(MSE):", mse)
print("결정 계수(R2):", r2)

# 시각화
plt.scatter(X, y, color='blue')
plt.plot(X_test, y_pred, color='red', linewidth=2)
plt.title('linear regeression')
plt.xlabel('X : cost')
plt.ylabel('Y : sales')
plt.show()

 

선형 회귀②: 다중 선형 회귀

• 두 개 이상의 독립 변수(X1, X2, ..., Xn)와 하나의 종속 변수(Y) 간의 관계를 모델링하는 방법
  (ex. TV, 라디오, 신문의 광고비(X1, X2, X3)와 매출(Y) 간 관계를 분석: 현재의 광고비(TV, 라디오, 신문)를 바탕으로 예상되는 매출 예상하기)
• 회귀식: Y = β0 + β1X1 + β2X2 + ... + βnXn *이번엔 절편이 X의 개수만큼 여러 개
• 종속 변수에 영향을 미치는 여러 독립 변수가 있을 때 사용해, 여러 변수의 영향을 동시에 분석할 수 있음
• 변수들 간의 다중공선성 문제가 발생할 수 있음
  • 다중공선성(mulitcollinearity): 회귀분석에서 독립 변수들 간에 높은 상관관계가 있는 경우
    •  문제점
      • 독립 변수들이 서로 강하게 상관되어 있으면, 각 변수의 개별적인 효과를 분리해내기 어려워져 회귀의 해석을 어렵게 함
      • 실제로 중요한 변수가 통계적으로 유의하지 않게 나타날 수 있음
    •  진단법
      • 간단ver. : 상관계수를 계산해 상관계수가 높은 변수들을 확인하기 (ex. 상관계수가 0.7보다 높다거나)
      • 정확 ver. : 분산 팽창 계수(VIF)를 계산해 10보다 큰지 확인하기
    •  해결법
      • 간단ver. : 높은 계수를 가진 변수 중 하나를 제거하기
      • 효과 굿ver. : 차원 분석 방법을 적용 (ex. 주성분 분석(PCA))

# 예시 데이터 생성
data = {'TV': np.random.rand(100) * 100,
        'Radio': np.random.rand(100) * 50,
        'Newspaper': np.random.rand(100) * 30,
        'Sales': np.random.rand(100) * 100}
df = pd.DataFrame(data)

# 독립 변수(X)와 종속 변수(Y) 설정
X = df[['TV', 'Radio', 'Newspaper']]
y = df['Sales']

# 데이터 분할
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 다중선형회귀 모델 생성 및 훈련
model = LinearRegression()
model.fit(X_train, y_train)

# 예측
y_pred = model.predict(X_test)

# 회귀 계수 및 절편 출력
print("회귀 계수:", model.coef_)
print("절편:", model.intercept_)

# 모델 평가
mse = mean_squared_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)
print("평균 제곱 오차(MSE):", mse)
print("결정 계수(R2):", r2)

 

[추가] 범주형 변수의 회귀

• 범주형 변수의 종류
  • 순서가 있는 범주형 변수
    • ex. 옷 사이즈(S, M, L, XL…), 수능 등급(1등급, 2등급, 3등급…) 등
    • 순서가 잘 반영될 수 있는 숫자로 변환함
      (ex. S → 0, M → 1, L →2…)
  • 순서가 없는 범주형 변수
    • ex. 성별, 지역 등
    • 변수가 2개뿐 : 임의의 숫자로 변환해도 괜찮음
      변수가 3개 이상 : 원-핫 인코딩으로 변환해야 함 (원-핫 인코딩: 하나만 1이고 나머지는 0)
      (ex. 부산 = [1, 0, 0], 대구 = [0, 1, 0], 울산 = [0, 0, 1])
• ①범주형 변수를 찾아서 ②더미 변수로 변환하고 → ③회귀 분석 수행
  (ex. 성별(X1), 근무 경력(X2)와 연봉(Y) 간의 관계를 분석)

# 예시 데이터 생성
data = {'Gender': ['Male', 'Female', 'Female', 'Male', 'Male'],
        'Experience': [5, 7, 10, 3, 8],
        'Salary': [50, 60, 65, 40, 55]}
df = pd.DataFrame(data)

# 범주형 변수 더미 변수로 변환
# drop_first : 범주형 변수 중 1개는 제외 (∵마지막 변수 1개는 있으나 마나함)
df = pd.get_dummies(df, drop_first=True)

# 독립 변수(X)와 종속 변수(Y) 설정
X = df[['Experience', 'Gender_Male']]
y = df['Salary']

# 단순선형회귀 모델 생성 및 훈련
model = LinearRegression()
model.fit(X, y)

# 예측
y_pred = model.predict(X)

# 회귀 계수 및 절편 출력
print("회귀 계수:", model.coef_)
print("절편:", model.intercept_)

# 모델 평가
mse = mean_squared_error(y, y_pred)
r2 = r2_score(y, y_pred)
print("평균 제곱 오차(MSE):", mse)
print("결정 계수(R2):", r2)

 

비선형 회귀①: 다항 회귀

• 독립 변수와 종속 변수 간의 관계가 선형이 아닐 때 사용
  (ex. 면적(X)과 가격(Y) 간 비선형의 관계를 분석할 때: 주택 가격 예측하기)
• 데이터가 곡선적 경향을 따를 때
• 고차 다항식의 경우 과적합(overfitting) 위험이 존재
  학습 데이터에만 너무 잘 맞아서 새로운 데이터에 대한 예측(=일반화)이 떨어지는 것

from sklearn.preprocessing import PolynomialFeatures

# 예시 데이터 생성
np.random.seed(0)
X = 2 - 3 * np.random.normal(0, 1, 100)
y = X - 2 * (X ** 2) + np.random.normal(-3, 3, 100)
X = X[:, np.newaxis]

# 다항 회귀 (2차)
# PolynomialFeatures()를 사용해 차수를 입력해주면 됨
polynomial_features = PolynomialFeatures(degree=2)
X_poly = polynomial_features.fit_transform(X)

model = LinearRegression()
model.fit(X_poly, y)
y_poly_pred = model.predict(X_poly)

# 모델 평가
mse = mean_squared_error(y, y_poly_pred)
r2 = r2_score(y, y_poly_pred)
print("평균 제곱 오차(MSE):", mse)
print("결정 계수(R2):", r2)

# 시각화
plt.scatter(X, y, s=10)
# 정렬된 X 값에 따른 y 값 예측
sorted_zip = sorted(zip(X, y_poly_pred))
X, y_poly_pred = zip(*sorted_zip)
plt.plot(X, y_poly_pred, color='m')
plt.title('polynomial regerssion')
plt.xlabel('area')
plt.ylabel('price')
plt.show()

 

비선형 회귀②: 스플라인 회귀

• 독립 변수와 종속 변수 간의 관계가 선형이 아닐 때 사용하는데, 구간별로 다른 회귀식을 적용해 복잡한 관계를 모델링
• 데이터가 국부적으로 다른 패턴을 보일 때 사용
• 구간마다 다른 다항식을 사용하여 전체적으로 매끄러운 곡선을 생성

---

 

아티클 스터디②: 알라미의 A/B 테스팅 일지 #1

오늘 읽은 아티클:

알라미의 A/B 테스팅 일지 #1

 

250403 오늘 아티클 스터디를 하며 적은 개인 요약 중 일부

 

오늘은 아티클 스터디로 앱 '알라미'를 운영하는 회사의 A/B 테스팅 일지를 읽었다. 가설 설정에 따라 어떻게 조건을 달리 했을 때 수치로 이렇게 달라졌어요~를 여러 사례들을 담고 있어서 재밌었다. 앱 설명에 들어가는 스크린샷에 대해 A/B 테스트의 경우에도 그냥 '변기보단 세면대가 낫지 않아?' 하고 바꿀 수도 있는 부분인데 이렇게 A/B 테스트를 통해 일리가 있는 제안인지 실험해보는 과정이 재밌었다.

그리고 글에서 소개한 사례 중 앱 내 보상형 광고의 문구에 대한 실험도 인상적이었다. 보상이 클수록 전환률이 더 좋을 것이라는 예측과는 달리 실제로는 어떤 시점까진 보상이 늘어나는 만큼 전환률도 올라가지만 특정 시점에 이르면 보상이 커진다고 전환률도 더 이상 커지지 않는 시점이 있기 때문에 실험을 통해 적절한 정도를 찾아냈다. 이렇게 비용 대비 효용을 최적화해나가는 과정이 흥미로웠다.

 

하지만 주의할 점에서도 항상 유념할 점은 우리에게 주어진 자원은 제한되어 있기 때문에, 물론 A/B 테스트를 안 하는 것보다 해보고서 더 나은 방향을 찾아나가는 것도 중요하지만, 기한과 예산을 고려했을 때 '이것이 과연 실험해볼 만한 가치가 있는 가설인지' 점검하는 자세를 잊지 말아야겠다는 생각도 하게 됐다.