[본캠프 30일차] chap 3의 시작, SQL 공부, 파이썬 공부

 

 

오늘부터 chapter 3에 접어들었다.

 

뭔가 '빨리 끝나면 좋겠다'는 마음으로 어쨌든 3월도 지나가는구나 싶어 기쁜 마음도 있고, '지금 내 수준으로 다른 걸 공부할 때가 아닌데' 하는 마음에서 벌써 다음 챕터로 넘어가는 게 애매한(?) 기분도 있다. 그래도 흘러가는 시간 속에서 그때그때 열심히 하는 수밖에 없는 것  같다. 아 근데 제가 이번에 조장이요...? 흐아

 

오늘 한 일은,

• 새로운 조의 조원들과 인사 나누기
• SQL 공부
  • [코드카타] 3문제 풀기 (104~106번)
  • [SQL 문제풀이 해설] 수강하기
• 파이썬 공부
  
  • [파이썬 문제풀이 해설] 수강하기
• [기초 통계학] 1주차 수강하기

---

SQL 공부①: [SQL 문제풀이 해설] 수강하기

사실 다 못 풀어서 해설 세션이 시작되는 11시 전까지 SQL과 파이썬 모두 필수 문제들만 열심히 풀어갔다.

 

난이도 : 쉬움

문제 1

-- 내가 작성한 쿼리
SELECT ad_id,
       ROUND((SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) / SUM(CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END))*100, 2)ctr
FROM ad_events
GROUP BY ad_id
ORDER BY ad_id

다음에는 SELECT절에 ctr처럼 길어지면 줄바꿔서 쓰자.

난이도 : 보통

문제 2

-- 내가 작성한 쿼리
SELECT co.customer_id
FROM customer_orders co LEFT JOIN products p ON co.product_id = p.product_id
GROUP BY co.customer_id
HAVING COUNT(DISTINCT p.product_category) >= 3

/*
더 나은 HAVING절 작성법
HAVING COUNT(DISTINCT p.product_category) >=
       (SELECT COUNT(DISTINCT product_category) FROM products)
*/

HAVING절에 숫자 3을 애초에 중복값 없이 카테고리를 조회하니 '가전제품, 식품, 패션'이 뜨길래 3개구나 싶어서 숫자 3을 적었는데 다음에는 유연한 방식으로 쿼리를 작성하자.

 

문제3

-- 내가 작성한(작성하다만..) 쿼리
SELECT user_id,
       post_date,
       AVG(post_count) OVER(PARTITION BY user_id, post_date) rolling_avg_3d
FROM blog_posts
GROUP BY user_id, post_date
ORDER BY user_id, post_date
-- 이거 아닌데.. 2일 전 것, 날짜 범위....

 

여기까지 적어두고 '행이 아니라 날짜로 3일치의 평균을 어떻게 적어줘야 하지..? 이게 난이도 보통...?' 하다가 조급한 마음으로 파이썬 문제를 풀러 갔다.

-- 튜터님의 정답 쿼리
SELECT user_id,
       post_date,
       AVG(post_count) OVER (PARTITION BY post_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) rolling_avg_3d
FROM blog_posts
GROUP BY user_id, post_date
ORDER BY user_id, post_date

그런데 문제 출제 의도는 윈도우 함수를 사용해서 일부 행끼리 계산할 수 있는지를 확인하고자 한 것이기에 행 기준으로 계산하면 된다고 하셨다.

---

파이썬 공부: [파이썬 문제풀이 해설] 수강하기

---

일단 문제에 대한 해설을 정리하기에 앞서 튜터님께서 설명하신 코드를 작성할 때 알아둬야 할 독스트링(docstring)을 비롯한 몇 가지 사항들을 기록해두려 한다.

[참고][Python] 독스트링 (Docstrings)

 

독스트링(docstring)

• 클래스, 함수 등의 목적과 이를 사용할 때 필요한 세부 정보를 작성
• 큰 따옴표 3개(""") 혹은 작은 따옴표 3개(''')로 열고 닫음 PEP 8에선 큰 따옴표 3개를 권장함
• 함수의 독스트링의 경우,
  • 첫 줄에는 함수의 목적을 밝힘
  • 그 다음부터는 세부적인 함수의 동작에 대한 설명(인자, 반환값 등)을 작성함
• 이렇게 작성한 독스트링은 __doc__ 로 확인 가능함

help()와 dir()

[참고] [빅데이터분석기사 실기] help(), dir() 활용하기

• help() : 어떤 함수나 클래스의 사용 방법을 알고 싶을 때 사용함
• dir() : 객체, 모듈 등이 가진 속성 및 메서드를 리스트 형태로 확인하고자 할 때 사용함

---

 

문제1

def get_len(input_list) :
    cnt = 0
    for i in input_list:
        cnt += 1
    return cnt

 

문제2

def get_count(input_list, find_value) :
    cnt = 0
    for i in input_list:
        if i == find_value:
            cnt += 1
    return cnt

 

문제3

def get_min_max(input_list) :

    if not input_list: # IndexError가 발생하지 않도록 작성해야 함
        return None

    min = input_list[0]
    max = input_list[0]
    for i in input_list:
        if i < min:
            min = i
        if i > max:
            max = i

    return (min, max)

IndexError가 발생하지 않도록 처음에 if문을 추가해줘야 한다. 

 

문제4

def get_sum(input_list) :
	sum = 0
    for i in input_list:
        sum += i
    return sum

 

문제5

def get_avg(input_list) :
	if not input_list: # 분모가 0일 때를 고려해 해당 if문 추가
    	return None

    avg = get_sum(input_list) / get_len(input_list)
    return avg

 

문제6

def get_std(input_list) :
	if not get_len(input_list) <= 1:
        return 0

    avg = get_avg(input_list)

    diff_list = []
    for i in input_list:
        diff = i - avg
        diff_list.append(diff ** 2)
    variance = get_avg(squared_diff_list)

    return variance ** 0.5

 

문제7

여기서부터 머리가 아파지기 시작했다.

..sort() 안 쓰고 정렬하는 법..? 그런 게.. 있기야 있겠지만..... 흐음

 

def get_median(input_list) :
	for i in range(get_len(input_list)):
    	idx = i
        for j in range(i+1, get_len(input_list)):
        	if input_list[j] < input_list[idx]:
            	idx = j
                
    len = get_len(input_list)
    
    if len % 2 == 0:
    	num1 = input_list[len//2 - 1]
        num2 = input_list[len//2]
        median = (num1+num2) / 2
    else:
    	median = input_list[len//2]
    
    return median

 

---

SQL 공부②: [코드카타] SQL 문제 풀기 (104~106번)

104. (619) Biggest Single Number

A single number is a number that appeared only once in the MyNumbers table.
Find the largest single number. If there is no single number, report null.

-- 시도(1) 정답 처리됨
SELECT MAX(s.num) num
FROM (
    SELECT num
    FROM MyNumbers
    GROUP BY num
    HAVING COUNT(num) < 2) s

 

105. (1045) Customers Who Bought All Products

Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.
Return the result table in any order.

-- 시도(1) 에러 (Runtime Error: Invalid use of group function)
SELECT customer_id
FROM Customer
WHERE COUNT(DISTINCT product_key) = (
    SELECT COUNT(DISTINCT product_key)
    FROM Product)

에러 메시지에 그룹 어쩌구~ 하고 뜨길래 WHERE절에 쓴 내용을 GROUP BY절을 작성하고서 HAVING절로 옮겼다.

 

-- 시도(2) 정답 처리됨
SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (
    SELECT COUNT(DISTINCT product_key)
    FROM Product)

 

106. (1731) The Number of Employees Which Report to Each Employee

For this problem, we will consider a manager an employee who has at least 1 other employee reporting to them.
Write a solution to report the ids and the names of all managers, the number of employees who report directly to them, and the average age of the reports rounded to the nearest integer.
Return the result table ordered by employee_id.

-- 시도(1) 틀림 (결과 테이블 이상함)
SELECT employee_id,
       name,
       SUM(CASE WHEN employee_id = reports_to THEN 1 ELSE 0 END) reports_count,
       AVG(CASE WHEN employee_id = reports_to THEN age ELSE 0 END) average_age
FROM Employees
WHERE employee_id IN (
    SELECT reports_to
    FROM Employees
)

reports_count와 average_age에서 문제에서 요구한 대로 계산되어 조회되고 있지 않다.

혹시나 싶어서 'GROUP BY employee_id, name'을 추가해도 결과 테이블은 동일했다.

 

-- 시도(2) 정답 처리됨
SELECT e.employee_id,
       e.name,
       m.reports_count,
       m.average_age
FROM Employees e 
    LEFT JOIN (
        SELECT reports_to,
            COUNT(reports_to) reports_count,
            ROUND(AVG(age)) average_age
        FROM Employees
        GROUP BY reports_to) m 
    ON e.employee_id = m.reports_to
WHERE e.employee_id IN (
    SELECT reports_to
    FROM Employees
)
ORDER BY e.employee_id

짧게 작성하고 싶었지만 결국 방법을 모르겠어서 JOIN을 하여서 쿼리를 작성했다.

---

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

통계가 중요한 이유

• 데이터를 이해하고 해석할 수 있음
• 추론하여 결론을 도출하는 과정을 도움
• 통계를 사용하는 예시
  • 고객 설문조사
    : 설문조사를 시행해 고객의 불만 사항을 파악하고 이를 개선하는 데 활용함
  • 고객 세그먼트별 상품 추천
    : 고객을 유형별로 나누어 특징을 파악하여 각 유형에 맞는 상품을 추천함

기술 통계 vs 추론 통계

• 기술 통계: 기술적인 계산을 통해 데이터를 요약함
• 추론 통계: 데이터를 기반으로 가설을 세워서 그를 검증해나가면서 추론 작업을 해나감

기술 통계

• 데이터를 요약하고 설명하는 통계 방법으로 데이터를 특정 대표값으로 요약함
• 주로 평균, 중앙값, 분산, 표준편차 등을 사용
  • 평균(mean)
    • 모든 데이터를 더한 후 데이터의 개수로 나눔
    • 데이터의 일반적인 경향을 파악하는 데 유용함
  • 중앙값(median)
    • 모든 데이터를 크기 순서대로 정렬했을 때 중앙에 위치한 값
    • 이상치에 영향을 덜 받기 때문에 데이터의 중심 경향을 나타내는 또 다른 방법
  • 분산(variance)
    • 값들이 평균으로부터 얼마나 떨어져 있는지를 나타낸 척도
    • 각 데이터 값에서 평균을 뺀 값을 제곱한 후, 이들로 평균을 구한 것
    • 분산이 큼 = 데이터가 평균으로부터 넓게 퍼져 있음
      분산이 작음 = 데이터가 평균으로부터 가까이 모여 있음
  • 표준편차(standard deviation)
    • 분산과 비슷하게 값들이 평균으로부터 얼마나 떨어져 있는지를 나타낸 척도
    • 분산에 제곱근을 취한 것이 표준편차 분산에 비해 표준편차가 더 직관적임

추론 통계

• 데이터의 일부를 가지고 데이터 전체를 추정함
• <cf 1> 신뢰 구간(confidence interval)
  • 모집단의 평균이 특정 범위 내에 있을 것이라는 확률을 나타냄
  • 일반적으로 95% 신뢰구간을 사용
    (95% 신뢰구간: 모집단 평균이 95% 확률로 이 구간 내에 있음)
• <cf 2> 가설 검정(hypothesis testing)
  • 모집단에 대한 가설을 검증하기 위해 사용
  • 일반적으로 2가지 가설이 있음; 귀무가설(H0), 대립가설(H1)
    • 귀무가설(歸無假說, a null hypothesis): 검증하고자 하는 가설이 틀렸음을 나타내는 기본 가설
    • 대립가설(alternative hypothesis): 귀무가설에 대립하는 가설로, 귀무가설이 기각될 시 받아들여지는 가설
  • p-value를 통해 귀무가설을 기각할지를 정함

 

파이썬으로 통계 분석하기

사용한 라이브러리: pandas, numpy, matplotlib, seaborn

 

① 위치 추정: 데이터의 중심을 확인하기

• 평균, 중앙값 등

data = [85, 90, 78, 92, 88, 76, 95, 89, 84, 91]

mean = np.mean(data) # 평균
median = np.median(data) # 중앙값

print(f"평균: {mean}, 중앙값: {median}")

② 변이 추정: 데이터들이 서로 얼마나 다른지 확인하기

• 분산, 표준편차, 범위(최댓값-최솟값) 등

variance = np.var(data) # 분산
std_dev = np.std(data) # 표준편차
data_range = np.max(data) - np.min(data) # 범위

print(f"분산: {variance}, 표준편차: {std_dev}, 범위: {data_range}")

③ 데이터 분포 탐색: 데이터가 어떤 값들로 이뤄져 있는가

• 히스토그램, 상자 그림 등으로 시각화하여 확인함

plt.hist(data, bins=5) # 히스토그램
plt.title('histogram')
plt.show()

plt.boxplot(data) # 상자 그림
plt.title('boxplot')
plt.show()

④ 이진 데이터와 범주 데이터 탐색: 데이터들이 서로 얼마나 다른가

• 이진 데이터와 범주 데이터 알아보기
  • 수치형 데이터(numerical data) : 숫자로 표현되는 데이터로, 사칙연산을 비롯한 계산이 가능함
  • 범주형 데이터(categorical data) : 특정한 범주로 구분되는 데이터로, 숫자로 표시될 수도 있으나 계산이 불가능함
  • 이진 데이터(binary data) : 기본 단위가 2개의 상태만 가지는 데이터
• 주로 최빈값을 사용하고, 원 그래프와 막대 그래프 등으로 시각화함

satisfaction = ['satisfaction', 'satisfaction', 'dissatisfaction', 
'satisfaction', 'dissatisfaction', 'satisfaction', 'satisfaction', 
'dissatisfaction', 'satisfaction', 'dissatisfaction']
satisfaction_counts = pd.Series(satisfaction).value_counts() # 각 값의 개수를 구함

satisfaction_counts.plot(kind='bar') # 막대 그래프
plt.title('satisfaction distribution')
plt.show()

⑤ 상관관계: 데이터들끼리 서로 관련 있는지 확인하기

• 상관관계:
• 상관계수: 두 변수 간 통계적 관계를 표현하기 위해 특정한 상관 관계의 정도를 수치적으로 나타낸 계수
  • 상관계수가 -1 / 1에 가까움 : 강력한 상관관계를 가짐(1: 양의 상관관계, -1: 음의 상관관계)
  • 상관계수가 -0.5 / 0.5에 가까움 : 중간 정도의 상관관계를 가짐
  • 상관계수가 0에 가까움 : 상관관계가 없음

study_hours = [10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
exam_scores = [95, 90, 85, 80, 75, 70, 65, 60, 55, 50]
correlation = np.corrcoef(study_hours, exam_scores)[0, 1] # 상관계수 구하기

print(f"공부 시간과 시험 점수 간의 상관계수: {correlation}")

plt.scatter(study_hours, exam_scores) # 산점도
plt.show()

• 상관관계 vs 인과관계
  • 상관관계 : 두 변수 관의 관계를 나타냄
  • 인과관계 : 한 변수가 다른 변수에 미치는 영향을 나타냄

⑥ 다변량 분석: 여러 데이터들끼리 서로 관련이 있는지 확인하기

• .pairplot()과 .heatmap()으로 시각화함

data = {'TV': [230.1, 44.5, 17.2, 151.5, 180.8],
        'Radio': [37.8, 39.3, 45.9, 41.3, 10.8],
        'Newspaper': [69.2, 45.1, 69.3, 58.5, 58.4],
        'Sales': [22.1, 10.4, 9.3, 18.5, 12.9]}
df = pd.DataFrame(data)

sns.pairplot(df) # 모든 관계를 산점도로 그리기
plt.show()

df.corr()

sns.heatmap(df.corr()) # 히트맵