[PostgreSQL] (Medium) Tweets' Rolling Averages [Twitter SQL Interview Question]

문제:

https://datalemur.com/questions/rolling-average-tweets

 

나의 오답 풀이

SELECT user_id
     , tweet_date
     , ROUND((tweet_count + COALESCE(lag1, 0) + COALESCE(lag2, 0)) / 3.0, 2) AS rolling_avg_3d
FROM (
  SELECT *
       , lag(tweet_count, 1) OVER (PARTITION BY user_id ORDER BY tweet_date) AS lag1
       , lag(tweet_count, 2) OVER (PARTITION BY user_id ORDER BY tweet_date) AS lag2
  FROM tweets) sub;

1. mysql에서 IFNULL 함수를 postgreSQL에선 coalesce로 쓴다.

 

2. 평균을 구할 때 이전 값이 없을 경우 (NULL) 나누는 값이 3이 아니라 2 또는 1이어야 하는데 나는 전부 3으로 해버려서 틀렸음!

그냥 AVG() 함수를 쓰면 NULL은 알아서 제외하고 집계해서 상관없을 텐데

나는 가로로 더해버려서 AVG()를 쓸 수 없다. 

또 COALESCE() 처리를 해주지 않으면 더하는 값 중에 널 값이 존재하면 결과도 널로 나와서 문제가 됨

 

그래서 찾아보니 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 라는 쿼리를 AVG() 함수 안에 추가하면 이동 평균을 구할 수 있다! 

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) AS rolling_avg_3d
FROM tweets;

 

아주 깔끔쓰

AVG 함수에도 OVER()를 같이 쓸 수 있는지도 처음 알았다 굿굿