Daily plan

🌞오전

- SQL 코드카타
- 9시 30분 QCC

🔥 오후

- QCC 해설
- 빅분기 D-1...........

🌝 저녁

- 오늘 하루를 빅분기에게 바칩니다....

---

SQL 코드카타

Q85 - Rising Temperature

select id
from(
    select id,
        temperature,
        lag(temperature) over(order by recordDate) yesterday_temp
    from weather
) a
where temperature>yesterday_temp

최대한 서브쿼리를 안 쓰는 방법으로 풀어내고 싶었는데,, 이 방법밖에 떠오르지 않았다.
근데 gpt한테 물어봤더니 얘도 서브쿼리 쓰는 거 보니까 걍 이렇게 풀면 될 거 같다ㅋ

WITH TempWithYesterday AS (
    SELECT id, temperature, 
           LAG(temperature) OVER(ORDER BY recordDate) AS yesterday_temp
    FROM weather
)
SELECT id 
FROM TempWithYesterday
WHERE temperature > yesterday_temp;

이건 gpt가 좋아하는 CTE 방식^^

CTE(공통 테이블 표현식) 사용 시 장점
- 복잡한 서브쿼리를 별도로 분리하여 가독성 향상
- 큰 테이블에서 여러 번 같은 서브쿼리를 실행할 경우 성능 최적화 가능
- 다른 조건이 추가될 때 유지보수성이 높아짐

---

QCC

Q1 - 임직원 로그인 빈도 분석

select unique_logins, 
    count(distinct employee_id) employee_count
from (select employee_id, 
    count(1) unique_logins
    from logins
    where login_result='SUCCESS'
      and date_format(login_time,'%Y%m%d') between '20230701' and '20230930'
    group by employee_id
  ) a
group by unique_logins
order by unique_logins

Q2 - 세번째로 높은 급여를 받는 직원

select e.employee_id, e.name, e.salary
from employee_salary e
  join (select salary, rank() over(order by salary desc) as salary_rank 
        from employee_salary
        group by salary) r
  on e.salary = r.salary
where salary_rank=3
order by employee_id

튜터님은 dense rank를 이용하셨다.
rank와 dense rank는 모두 중복 순위를 동일 순위로 표시한다는 점이 같지만,
rank는 중복 순위 다음을 중복 개수만큼 떨어진 순위로 부여하고
dense rank는 중복 순위 다음에 중복 개수와 상관없이 순차적인 순위를 부여한다는 점이 다르다.
여기서는 salary를 기준으로 같은 salary에는 같은 순위를 부여하고, 중복에 상관없이 순차적인 순위를 부여하면 되므로 dense rank가 더 적절할 것 같다.
(이 문제의 경우에서는 salary 기준으로 groupby해서 순위를 매기는 것과 마찬가지일 것 같음)

그리고 확실히 with문을 쓰는 게 깔끔한듯!
...이라고 n만번째 말하면서 절대 with구문 안쓰는 사람...

with salary_ranked as(
	select employee_id, name, salary,
    		dense_rank() over (order by salary desc) as rnk
    from employee_salary
)
select employee_id, name, salary
from salary_ranked
where rnk=3
order by employee_id

Q3 - 부서 간 메시지 비율 계산

select round(count(case when send_dep<>rec_dep then 1 end)/count(1),1) inter_department_msg_pct
from(
  select *,
    (select department 
    from employees e 
    where e.employee_id=m.sender_id) as send_dep,
    (select department 
    from employees e 
    where e.employee_id=m.receiver_id) as rec_dep
  from messages m) a
where send_dep is not null and rec_dep is not null

너무 지저분하게 푼 거 같아서 맘에 안 듦..
다시 봐도 from문에서 왜 저렇게 복잡하게 난리를 쳐놨는지 모르겠다.
문제 풀다가 어지러워진 정신 상태를 코드로 표현한 느낌..

select
  round(100.0 * SUM(case when e1.department != e2.department then 1 else 0 end) / count(*), 1) inter_department_msg_pct
FROM messages m
inner join employees e1 on m.sender_id = e1.employee_id
inner join employees e2 on m.receiver_id = e2.employee_id

이렇게만 하면 훨씬 간단한 것을...

Q4 - (도전) 광고 성과 Attribution 분석

얘는 시간 안에 못 풀었다...ㅜ

---

Python Standard 2회차

- python의 window function(shift, rolling, expanding)을 이해한다.
- 시계열 데이터를 활용한 파생 변수(이동 평균, 누적 합) 생성을 실습한다.
- 데이터 타입별 다양한 상관관계를 살펴보고 실습한다.

Shift

• 시계열 데이터의 데이터나 인덱스를 원하는 기간만큼 shift하는 메서드
• 문법: df.shift(periods=기간, freq=None, axis=0, fill_value='비었음')
  • periods: 이동할 기간 (음수or양수로 입력)
  • freq: 선택 매개변수 (Y, M, D, H, T, S, Timestamp, 'Infer' 등이 위치)
  • fill_value: shift로 인해 생긴 결측치를 대체할 값 지정
  • axis: 연산할 축방향 설정 (0:행 / 1:열)

df.shift(1)  # 뒤의 날짜 데이터를 앞으로 한 칸 땡김
df.shift(-1) # 앞의 날짜 데이터를 뒤로 한 칸 땡김
df.shift(periods=3, freq='D')  # 3일 이동
df.shift(periods=3, freq='infer') # df의 날짜간경르 분석해서 적당한 주기를 이동

Rolling

• 데이터프레임 내 열에 대하여 일정 크기의 window(범위)를 지정하고, 그 window안의 값을 추가 연산으로 계산하는 메서드
• 문법: df.rolling(window, min_periods=None, center=False, win_type=None, on=None, axis=0, closed=None, method='single')
  • window: 계산할 window의 크기 (열 기준으로 계산할 경우 행의 수를 의미)
  • min_periods: 계산할 최소 크기(기간) (기본적으로 window 크기와 동일함)
  • center: 계산을 중간 행에서 할 지 결정하는 파라미터 (기본값은 False/ True로 할 경우 중간 행을 기준으로 계산함)
  • win_type: triang/ gaussian 등 가중치를 넣어 계산할 경우 계산 방식
  • on: 시계열 인덱스나 시계열과 유사한 열이 있을 경우 이 열을 기준으로 rolling을 수행할 수 있음
  • axis: 연산할 축 방향 설정 (0:행 / 1:열)
  • closed: 연산이 닫히는 방향 설정 (left, right, both, neither)
  • method:{'single'/'table'} numba를 이용해 테이블 계산을 진행하여 속도를 높일지의 여부 (현재 'single'만 사용 가능)

df.rolling(window=3).mean()  # 3일 이동 평균 구하기
df.rolling(window=3).sum()  # 3일 누적합 구하기
df.rolling(window=3, center=True, closed='left').mean() 
  # 3일 이동평균을 중간 행 기준으로 계산하고, 왼쪽 값을 포함하여 계산

Expanding

• 행이나 열의 값에 대해 누적으로 연산을 수행하는 메서드
• 문법: df.expanding(min_periods=1, axis=0, method='single').추가메서드()
  • min_periods: 연산을 수행할 요소의 최소 갯수로, 이보다 작으면 NaN 출력
  • axis: 연산할 축 방향 설정 (0:행/1:열)
  • method: 연산방식
    • single: 연산을 한 줄씩 수행
    • table: 전체 테이블에 대해서 롤링 수행
    • 기본값은 single이며, 롤링 연산할 경우 numba 라이브러리를 추가로 import 해야 함

데이터 타입별 상관관계

연속형-연속형

• Pearson correlation (피어슨 상관계수)
  • 가장 일반적인 형태의 상관관계 분석
  • -1~+1 사이의 결과값을 가지며, 절대값이 1에 가까울수록 높은 상관관계를 가짐
  • df.corr(method='pearson')

연속형-범주형(이분형)

• Point-Biserial Correlation
  • 이분형 변수는 '네/아니오'와 같이 2가지로 나뉘는 방식을 의미하는데, 이를 0과 1로 코딩한 후 피어슨 상관계수로 계산한 것을 point-biserial correlation이라고 함
  • -1~+1 사이의 결과값을 가지며, 절대값이 1에 가까울수록 높은 상관관계
  • python scipy 라이브러리의 pointbiserialr 함수를 이용하여 계산 가능

연속형-범주형(3개 이상)

• Polyserial correlation
  • 범주형 변수가 순서형인 경우에 적합
  • 실제 현업에서 각 변수들의 관계를 모두 설명하기 어렵다는 한계점을 가짐
  • 현재 python에서 지원 X
  • -1~+1 사이의 결과값을 가지며, 절대값이 1에 가까울수록 높은 상관관계
• ANOVA검정 (분산 분석)
  • 범주형 변수가 명목형인 경우에 적합
  • 범주형 변수에 따라 연속형 변수의 평균에 차이가 있는지 검정
  • python scipy 라이브러리의 f_oneway함수를 통해 진행 가능
  • F-Statistic과 p-value를 도출할 수 있음
    • F<1: 무의미 / 1<=F<3: 거의 무의미 / 3<=F<10: 경우에 따라 유의미 / 10<=F<50: 유의미(강한 차이) / F>50: 거의 확실한 유의미(아주 강한 차이) / F>=100: 확실한 유의미
• 연속형 변수와 범주형 변수에 대한 상관관계 해석 시에는 단순 통계량뿐만 아니라 p-value를 함께 확인해야 함

범주형-범주형

• Phi(φ) coefficient (파이 상관계수)
  • 파이 상관계수는 두 범주형 변수를 0과 1로 코딩한 후 피어슨 상관계수를 구하는 방식
  • 각 범주형 변수가 2가지 값을 가지는 경우에만 사용 가능
  • 두 변수 모두 순서가 없는 명목형이면서 이분형이어야 한다는 한계점
• Cramer's V
  • 두 범주형 변수 중 하나의 변수가 3개 이상의 범위를 가지는 경우 사용 가능
  • 범주형 변수 값들을 LabelEncoding하고 함수를 정의한 뒤 혼동행렬을 생성하는 방식
  • Cramer's V 계수는 0과 1사이의 값을 가지며 1에 가까울수록 강한 상관관계를 나타냄
  • python scipy 라이브러리와 sklearn 라이브러리로 구현 가능

---

일기

드디어 오늘 대용님 웃기기 성공했다,, 뿌듯함 max
공부나 시험에는 승부욕이 전혀 없는데 이상하게 이런거엔 목숨걸게 되는 이유가 뭘까..

한동안 SQL 손 놓고 있었더니 감 다 잃었다. 다시 코드카타 열심히 풀자! ㅠ.ㅠ

이번주에 햇빛을 너무 안봐서 그런지 정신 상태가 좀 이상했다. 잠도 쏟아지고 글자를 읽어도 안 읽히고,, 하루에 10분이라도 나가서 산책을 하던지 햇빛을 보고 와야겠다.

그리고 이제 진짜 일찍 자기!!!
11시 되기도 전에 자던 시절에는 정신이 정말 건강한 게 느껴졌었는데 요즘 새벽 2~3시에 자는 게 습관이 되더니 멘탈이 불안정해지고 있는 거 같음,,

내일 빅분기 시험인데 기대가 없어서 불안하지도 않다ㅋ
ADsP때도 딱 이런 기분이었던 거 같은데,, 그때처럼 하늘이 내 편이 들어주길 바라지만 너무 양심없는 거 같아서 걍 해탈함ㅎ 그래도 끝까지 화이티이잉!

• 주말에 파이썬 스탠다드 실습 복습하고
• 밀린 통계 강의 들어야지....^^