Pori_IT

문제

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일전) 동안 고객이 지불한 금액의 이동 평균을 계산할것.

소수점 이하 두 자리까지 반올림 해야한다.

visted_on으로 오름차순 정렬한다.

테이블 & 예제

[Customer]

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
In SQL,(customer_id, visited_on) is the primary key for this table.
This table contains data about customer transactions in a restaurant.
visited_on is the date on which the customer with ID (customer_id) has visited the restaurant.
amount is the total paid by a customer.

[Example]

Input: 
Customer table:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         | 
| 1           | Jhon         | 2019-01-10   | 130         | 
| 3           | Jade         | 2019-01-10   | 150         | 
+-------------+--------------+--------------+-------------+
Output: 
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+

풀이

우선 한 날짜에 여러명이 방문하는 경우가 있다. 따라서 이를 해결하기위해 visited_on으로 group을 묶어야한다.

WITH
CTE1 AS (
    SELECT visited_on, SUM(amount) AS s_amount
    FROM Customer
    GROUP BY visited_on
),

amount의 합으로 계산하면 되니까 SUM(amount)를 적용해주었다.

이제 자기 자신으로부터 6일 전의 값까지의 합을 구해야한다. (총 7일) 이를 위해서 ROWS를 이용해준다.

CTE2 AS (
    SELECT visited_on,
        SUM(s_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
    FROM CTE1

여기서 문제가 발생한다. 예제 결과를 보면 6일전의 날짜에 값이 하나라도 없다면? 출력하지않는다. -> 01.07부터 값이 출력된다.

이를 위해서 OFFSET이라는 방법을 사용한다.

Limit과 함께 사용하는데 OFFSET을 이용하면 가져올 데이터의 초기 위치 값을 지정 가능하다.https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-LIMIT-OFFSET

그렇게 OFFSET을 지정하고, 평균을 해서 결과를 내야하기 때문에 마지막에 연산을 걸어주면? 이제 원하는 결과를 얻을 수 있다.

WITH
CTE1 AS (
    SELECT visited_on, SUM(amount) AS s_amount
    FROM Customer
    GROUP BY visited_on
),
CTE2 AS (
    SELECT visited_on,
        SUM(s_amount) OVER (ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS amount
    FROM CTE1
    LIMIT 100000
    OFFSET 6
)
SELECT visited_on, amount, ROUND(amount/7,2) AS average_amount
FROM CTE2