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

Today's Goals

1. 코테 연습은 매일매일 - 코드카타 SQL & 파이썬

2. 참여 못했던 QCC 3회차 풀어보기

3. 통계학 개인과제

 

 

 

머신러닝 강의를 계속 들으려니 집중력이 떨어진다 ㅠㅠ

중간중간 QCC와 개인과제를 하면서 환기를 시켰다(?)

이번 QCC는 확실히 쉽지 않았다. 1시간 30분이 걸린,,,

QCC에 실시간으로 참여했으면 다 못풀었을 생각에 아찔했다.

 

 

 

 

 

1. 코드카타

 

파이썬

---

 

[영단어로 쓰여진 숫자들을 숫자형태로 바꾸기]

def solution(s):
    answer = ''
    num_str = ''
    num_str_list = ['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine']
    
    for i in s :
        if i.isnumeric() :
            answer += i
        else :
            num_str += i
            if num_str in num_str_list :
                answer += str(num_str_list.index(num_str))
                num_str = ''
    return int(answer)

• s 문자열의 예시는 'one3sevensixtwo48' 이런식
• 나의 첫 풀이는 문자를 차례대로 추출해서 리스트에 해당하는 문자가 있는 경우, 그에 해당하는 인덱스로 변환하는 코드를 작성하였음
• 인덱스를 이용하는 더 간단한 방법은 enumerate와 replace를 이용하는 방법이 있음
• 하지만 이는 인덱스 순서와 숫자값이 일치했기 때문에 가능, 만약 일치하지 않는 어떤 값으로 대체해야 한다면 딕셔너리 자료를 만들어 이용해야 함
• 아래 블로그 참고

https://velog.io/@jerry_bak/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-%EC%88%AB%EC%9E%90-%EB%AC%B8%EC%9E%90%EC%97%B4%EA%B3%BC-%EC%98%81%EB%8B%A8%EC%96%B4-python

[프로그래머스] 숫자 문자열과 영단어 python

https://soy3on.tistory.com/269

[프로그래머스] 숫자 문자열과 영단어 (python)

 

 

 

 

2. QCC 3회차

 

문제1

---

[문제1]

WITH order_by_customer AS (SELECT a.salesorderid, a.unitprice, a.orderqty, a.customerid, a.orderdate, 
                                  CONCAT(b.firstname, ' ', b.lastname) AS customer_name
                           FROM sales_order a LEFT JOIN customer b ON a.customerid = b.customerid)

SELECT customerid, customer_name, year(orderdate) AS `year`, sum(unitprice*orderqty) AS gmv 
FROM order_by_customer
WHERE customerid IN (SELECT customerid
                     FROM order_by_customer
                     GROUP BY customerid 
                     HAVING min(orderdate) LIKE '2011-12%')
GROUP BY customerid, year(orderdate)
ORDER BY customerid, `year`

• 문제 풀이에 사용될 주문 정보와 회원 정보를 join한 테이블을 따로 생성(회원 이름 컬럼까지 추가 생성)
• 2011년 12월에 첫 주문한 회원(회원별로 첫 주문날짜가 2011년 12월로 시작하는 회원id만 뽑아냄) 리스트에 해당하는 회원들만 필터링하여 연도별 총 주문금액 집계

 

 

문제2

---

[문제2]

WITH order_by_customer AS (SELECT a.salesorderid, a.unitprice, a.orderqty, a.customerid, a.orderdate, 
                                  CONCAT(b.firstname, ' ', b.lastname) AS customer_name
                           FROM sales_order a LEFT JOIN customer b ON a.customerid = b.customerid),

gmv_by_cus_year AS (SELECT customerid, customer_name, year(orderdate) AS `year`,
                           sum(unitprice*orderqty) AS gmv 
                    FROM order_by_customer
                    WHERE customerid IN (SELECT customerid
                                         FROM order_by_customer
                                         GROUP BY customerid 
                                         HAVING min(orderdate) LIKE '2011-12%')
                    GROUP BY customerid, year(orderdate)
                    ORDER BY customerid, `year`)

SELECT customerid, customer_name, round(sum(gmv)/(max(year)-min(year)+1) , 2) AS avg_yearly_gmv
FROM gmv_by_cus_year
GROUP BY customerid
ORDER BY customerid

• 문제1에서 구한 회원별 연도별 총 주문금액 테이블을 그대로 이용 > 추가 with절 테이블로 생성
• 없는 연도에 대해서 총 주문금액을 0으로 치고 회원별 연평균 총 주문금액을 구하려면 그냥 전체 주문금액의 합을 첫구매와 마지막구매 사이 기간으로 나눠주면 됨

 

 

문제3

---

[문제3]

WITH order_by_customer AS (SELECT a.salesorderid, a.unitprice, a.orderqty, a.customerid, a.orderdate, 
                                  CONCAT(b.firstname, ' ', b.lastname) AS customer_name
                           FROM sales_order a LEFT JOIN customer b ON a.customerid = b.customerid),
						   
qty_by_cus_year AS (SELECT customerid, customer_name, year(orderdate) AS `year`, sum(orderqty) AS total_qty,
                           rank() OVER (PARTITION BY customerid ORDER BY sum(orderqty) DESC, year(orderdate) DESC) AS ranking
                    FROM order_by_customer
                    WHERE customerid IN (SELECT customerid
                                         FROM order_by_customer
                                         GROUP BY customerid 
                                         HAVING min(orderdate) LIKE '2011-12%')
                    GROUP BY customerid, year(orderdate)
                    ORDER BY customerid, `year`)
					
SELECT customerid, customer_name, `year` AS max_qty_year, total_qty AS max_total_qty
FROM qty_by_cus_year
WHERE ranking = 1

• 최대 구매량 순위(동량일 경우 최근 년도의 순위가 더 높도록)를 구하는 윈도우 함수를 사용한 with절 테이블 생성
• 이 때, 윈도우 함수의 파티션과 정렬을 설정하기 위해서 기존 테이블에 있는 컬럼이 아닌, 앞서 select 절에서 명시된 컬럼을 사용할 수 있음
• 다만 집계함수에 의한 컬럼은 별칭이 아닌 집계함수 그 자체를 컬럼명 위치에 넣어줘야 함

 

 

 

 

3. 통계학 개인과제

---

[변동계수]

• 평균이 커지면 표준편차도 커지는 경향으로 인해, 같은 값을 비교하는 것임에도 스케일 차이로 비교가 힘들 수 있음(예 : 전세 보증금과 원룸 보증금의 비교는 돈 단위는 같지만 전세 보증금 값이 더 크기 때문에 비교가 어려움)
• 같은 스케일 상에서 데이터의 퍼짐 정도를 비교하기 위한 방법이 변동계수 > 두 데이터 중 변동계수가 클수록 데이터가 상대적으로 많이 퍼져있다, 즉, 들쭉날쭉하다 라고 해석 가능
• 변동계수 = 표준편차 / 평균 (*100은 생략 가능)

https://depotceffio.tistory.com/entry/%EB%B3%80%EB%8F%99%EA%B3%84%EC%88%98CV%EC%9D%98-%EC%9D%98%EB%AF%B8%EC%99%80-%EA%B3%84%EC%82%B0-%EB%B0%A9%EB%B2%95

변동계수(CV)의 의미와 계산 방법

 

 

[표준오차]

• 모집단에서 추출한 표본들의 평균 간의 편차, 즉 다양하게 추출된 표본 평균들의 차이
• 표준오차(평균의 표준편차) = 모집단 표준편차 / 표본 데이터 수의 제곱근

https://hweejin.tistory.com/entry/%ED%86%B5%EA%B3%84%EC%A0%81-%EA%B2%80%EC%A0%95%EC%97%90-%EC%95%9E%EC%84%9C-%ED%91%9C%EC%A4%80%EC%98%A4%EC%B0%A8-%EA%B0%9C%EB%85%90-%EC%9D%B4%ED%95%B4%ED%95%98%EA%B8%B0

통계적 검정에 앞서, '표준오차' 개념 이해하기

 

 

[신뢰구간이란? 신뢰구간을 계산하는 방법]

https://modern-manual.tistory.com/entry/95-%EC%8B%A0%EB%A2%B0%EA%B5%AC%EA%B0%84-%EA%B3%B5%EC%8B%9D-%EC%89%BD%EA%B2%8C-%EC%9D%B4%ED%95%B4%ED%95%98%EA%B8%B0

95% 신뢰구간 공식 쉽게 이해하기

• 평균 - z값 * 표준오차 ~ 평균 + z값 * 표준오차

 

 

[파이썬 라이브러리를 활용하여 z값 구하기]

• 정규분포의 z-score를 구하는 방법은 stats.norm.ppf > 누적분포함수의 역함수로, 주어진 확률에 해당하는 z값 반환
• 기본 사용법 : stats.norm.ppf(q, loc=0, scale=1),  loc과 sacle은 0과 1이 기본값(정규분포)
• z_value = stats.norm.ppf(1 - (1 - confidence_level) / 2) > q값을 이와 같이 구하는 이유
• 95% 신뢰수준이라는 것은, 평균을 기준으로 양측에서 95% 확률 범위 내에 있다는 뜻임
• 즉 양쪽 끝에서 각 2.5%에 해당하는 확률 범위를 제외
• 우측 범위에서 2.5%를 제외한 위치의 z값을 찾기 위해 작성한 코드

 

 

[stats 라이브러리를 활용하여 쉽게 신뢰구간 구하기]

• stats.norm.interval (z분포 사용) : 모집단의 표준편차를 알고 있을 때 사용 가능
• 기본 사용법 : stats.norm.interval(alpha(신뢰수준), loc=mean(표본평균), scale=standard_error(표준오차)) 
• stats.t.interval (t분포 사용) : 모집단의 표준편차를 모르고, 표본의 표준편차를 구할 수 있을 때 사용 가능
• 기본 사용법 : stats.t.interval(alpha(신뢰수준), df(자유도, 표본크기-1), loc(표본평균), scale(표준오차))