문제 116번

You are the restaurant owner and you want to analyze a possible expansion (there will be at least one customer every day).
Compute the moving average of how much the customer paid in a seven days window (i.e., current day + 6 days before). average_amount should be rounded to two decimal places.
Return the result table ordered by visited_on in ascending order.

(링크)

데이터 이해 및 구상

날짜별로 7일(당일+이전6일)간 customer들이 지불한 총 금액과 평균 금액을 소수점 2째자리까지 구해야 하며, visited_on의 오름차순 정렬해야 한다.

날짜 조건을 고려하면 최소한 7일 간의 데이터가 필요하며, 이전에 6일의 데이터가 없는 날짜는 계산에 포함하지 않는다

즉 visited_on(날짜 데이터)를 기준으로 그 전 6일까지의 amount의 합을 구하고,
이를 output에 출력해야 한다

단순히 LAG() 함수를 쓰기에는 같은 날 '적어도 한 명'의 고객이 오기에, 2명 이상이 오면 날짜를 전부 포함하지 못하게 된다
-> 날짜를 기준으로 GROUP BY를 해서 sum(amount)를 만들면 날짜마다 sum(amount)를 뽑을 수 있고, LAG() 함수도 수월하게 사용할 수 있다

코드

지저분하긴 하지만, 일단 내 힘으로 짜낸 코드는 아래와 같다

WITH make_lag AS (
SELECT
    visited_on
    , sum_amount as 'lag_0'
    , lag(sum_amount, 1) over () as 'lag_1'
    , lag(sum_amount, 2) over () as 'lag_2'
    , lag(sum_amount, 3) over () as 'lag_3'
    , lag(sum_amount, 4) over () as 'lag_4'
    , lag(sum_amount, 5) over () as 'lag_5'
    , lag(sum_amount, 6) over () as 'lag_6' # check if null or not
FROM (
    SELECT
        distinct visited_on
        , sum(amount) over (partition by visited_on) as 'sum_amount'
    FROM
        customer
    ) aa
)
SELECT
    visited_on
    , (lag_0 + lag_1 + lag_2 + lag_3 + lag_4 + lag_5 + lag_6) as 'amount'
    , round((lag_0 + lag_1 + lag_2 + lag_3 + lag_4 + lag_5 + lag_6) / 7, 2) as 'average_amount'
FROM
    make_lag
WHERE
    lag_6 is not null
;

visited_on으로 GROUP BY를 한 뒤로는 무식하게 LAG() 함수를 6번 활용했고,
이 중 LAG_6는 이 값이 null이라면 그 전도 전부 null이므로, visited_on의 시작 시점을 결정하게 해주는 용으로 활용했다

추후 re 풀어보았다

아래 모범 답안에서도 그렇지만, 윈도우 함수를 사용하면 이동 평균을 쉽게 구할 수 있다.
유사 문제로 Advent of SQL 14번도 있으니 참고

SELECT
    visited_on
    , amount
    , average_amount
FROM (
    # 이동합, 이동평균 계산을 윈도우 함수를 사용하여 계산
    # (rows BETWEEN 6 preceding AND current row)
    #   1) rows BETWEEN a AND b : a에서 b까지의 행들 (양 끝 포함)
    #   2) 6 preceding : 현재 행으로부터 6행 전
    #   3) current row : 현재 행
    SELECT
        visited_on
        , sum(amount) over (rows between 6 preceding and current row) as "amount"
        , round(avg(amount) over (rows between 6 preceding and current row), 2) as "average_amount"
        , row_number() over () as "rn"
    FROM (
        # 각 요일마다 amount의 합이 필요하므로 우선적으로 계산
        SELECT
            visited_on
            , sum(amount) as "amount"
        FROM
            customer
        GROUP BY
            visited_on
        ) a
) aa
# 본래 이동 평균은 1번째 행부터 누적합을 계산하지만, 해당 문제는 7일의 누적합이 발생한 시점부터를 요구하므로 row_number >= 7인 경우만 사용
WHERE 
    rn >= 7
;

모범 답안

DATE_SUB, DATE_ADD를 활용하는 방법

SELECT
    visited_on,
    (
        SELECT SUM(amount)
        FROM customer
        WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
    ) AS amount,
    ROUND(
        (
            SELECT SUM(amount) / 7
            FROM customer
            WHERE visited_on BETWEEN DATE_SUB(c.visited_on, INTERVAL 6 DAY) AND c.visited_on
        ),
        2
    ) AS average_amount
FROM customer c
WHERE visited_on >= (
        SELECT DATE_ADD(MIN(visited_on), INTERVAL 6 DAY)
        FROM customer
    )
GROUP BY visited_on;

날짜 조건

일단 WHERE절부터.
주어진 데이터에서 가장 작은 날짜값인 MIN(visited_on)보다 6일 큰 날짜값을 SELECT하고, 이보다 같거나 큰 데이터에 대해서만 조회하는 것으로 날짜 조건을 만족했다

amount 계산

amount를 구할 때는, visited_on과 그 6일 전 사이의 visited_on에 대해 SUM()을 활용했다
또한 이걸 7로 나누면 바로 average_amoun가 나온다

윈도우 함수의 preceding을 활용하는 방법

select *
from (
select 
    c.visited_on, 
    sum(c.amount) over (order by visited_on rows 6 preceding) as amount,
    round(avg(sum(c.amount)) over (order by visited_on rows 6 preceding),2) as average_amount
from (select visited_on, sum(amount) as amount from customer group by 1) c
group by 1
) cc
where cc.visited_on >= (select min(visited_on) from customer) + interval 6 day

윈도우 함수에서 파티션을 나눌 때 원하는 범위까지 지정해줄 수 있다
앞선 예시에서 봤듯 WHERE절에서 MIN(visited_on)으로 조건만 잘 걸어준다면,
나머지는 rows 6 preceding을 활용하여 조회하고 있는 visited_on과 그 상위 6개 row까지를 하나의 파티션으로 묶어서 계산을 진행할 수 있다.