개요
📌 SQL Challenge
📌 이동평균
-
이동평균?
단순히 날짜별 매출 리포트를 작성하면, 주말에 매출이 크게 변동하는 그래프 등의 경우,
매출이 상승하는 경향이 있는지, 하락하는 경향이 있는지 판단하기 어렵기 때문에 7일 동안의 평균 매출을 사용한 '7일 이동 평균'으로 표현하는 것이 더 좋다. -
이동평균법 (Movement Average Method)
-
이동평균선을 보면
추세를 알 수 있다 !
SQL로 어떻게 구하지 ?
2024.05.01. ~ 2024.05.08. /
2024.05.08. ~ 2024.05.15. /
이렇게 일주일씩 끊어서 7일 평균을 구하는 것이 아니고
2024.05.08. 이면 앞에서부터 지금까지 7일 간의 평균을 구하고,
2024.05.09. 이면 앞에서부터 지금까지 7일 간의 평균을 구해야 한다.
- 앞에서부터 지금까지 7일 간의 평균 ?
6일 전부터 현재까지의 평균과 같고, 이를 SQL WINDOW 함수로 표현하면,
=ROWS BETWEEN 6 PRECEDING AND CURRENT ROW가 된다.
ROWS UNBOUNDED PRECEDING: 무한한 행 전의
ROWS BETWEEN lower_bound(작은 숫자) AND upper_bound(큰 숫자)-
UNBOUNDED PRECEDING: 현재 행 이전의 모든 행 -
n PRECEDING: 현재 행 기준 n번째 이전까지 모든 행 -
CURRENT ROW: 현재 행 -
n FOLLOWING: 현재 행 기준 n번째 이후까지 모든 행 -
UNBOUNDED FOLLOWING: 현재 행 이후의 모든 행
예) 아래 purchase_log 테이블에서
- 날짜별로
GROUP BY해주고, 구매 양의 합의 평균을 구하는데 날짜 순으로 정렬해 이를
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW로 7일 단위로 끊을 수 있다.
# 7일 이동 평균 구하기
SELECT dt
,SUM(purchase_amount)
,AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW) AS seven_day_avg
FROM purchase_log
GROUP BY dt
ORDER BY dt ;- 같은 7일 이동평균이더라도 계산할 앞의 6일이 없는 경우,
CASE 구문(seven_day_avg_strict 컬럼)으로 NULL 처리 할 수 있다.
# 7일 이동 평균 구하기 ( Strict 버전 )
SELECT dt
,SUM(purchase_amount)
,AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND
CURRENT ROW) AS seven_day_avg
,CASE WHEN
7 = COUNT(*) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
THEN AVG(SUM(purchase_amount)) OVER(ORDER BY dt ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW) END AS seven_day_avg_strict
FROM purchase_log
GROUP BY dt
ORDER BY dt ;- 적용 과제
트위터에서 실제 SQL 인터뷰에 나왔던 문제 풀기 - 문제 풀기
SELECT user_id, tweet_date, ROUND(AVG(tweet_count) OVER(PARTITION BY user_id ORDER BY tweet_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) FROM tweets GROUP BY user_id, tweet_date, tweet_count
📌 비율 구하기
구하고 싶은 집단 수 / 전체 집단 수 = 비율
(구하고 싶은 집단 수 / 전체 집단 수) * 100 = 백분율
틱톡에서 실제 SQL 인터뷰에 나왔던 Activation 문제 풀기
- 문제 풀기
# 곱하기 1.0 을 해주는 이유에 대해 기억하기! SELECT ROUND(COUNT(DISTINCT CASE WHEN signup_action = 'Confirmed' THEN user_id ELSE NULL END) * 1.0 / COUNT(DISTINCT user_id),2) FROM emails e LEFT JOIN texts t ON e.email_id = t.email_id
- 1.0을 곱해주는 이유?
SQL에서 정수끼리 나누기를 하면 결과가 정수로 반환,
소수점 이하 부분이 버려져 정확한 비율을 얻을 수 없는 상황이 생길 수 있기 때문에
정수에 실수 1.0을 곱하여 암시적 형 변환(실수로 변환)을 통해 소수점 표현이 가능하다!
+) 과제
Verizon에서 실제 SQL 인터뷰에 나왔던 Percentage 문제 풀기
- 문제 풀기
# WITH 문으로 발신자, 수신자 각각의 id와 국가 조회하는 테이블 생성 WITH caller_info AS ( SELECT caller_id, country_id caller_country_id FROM phone_info ), receiver_info AS ( SELECT caller_id, country_id receiver_country_id FROM phone_info ) # 통화기록 테이블과 각 발신자, 수신자 국가를 조인하여 # 국가가 같지 않으면 국제전화 (1), 같으면 (0) 으로 출력하여 합계 구하기 # 통화기록의 caller_id 의 갯수로 나누고 100을 곱하여 백분율로 표현, 소수점 1자리 반올림 SELECT ROUND(SUM(CASE WHEN ci.caller_country_id != ri.receiver_country_id THEN 1 ELSE 0 END) / COUNT(ci.caller_id) * 100.0 , 1) international_calls_pct FROM phone_calls pc LEFT JOIN caller_info ci ON pc.caller_id = ci.caller_id LEFT JOIN receiver_info ri ON pc.receiver_id = ri.caller_id
