sql, limit을 over에서 사용하기 - row proceding

7일까지의 통계를 매일마다 분석하기

  • https://leetcode.com/problems/restaurant-growth/
  • Customer 테이블이 있다.
  • 매일마다 해당일을 기준으로 7일 전까지의 통계를 내고 싶다. 원하는 통계 값은 총 판매량과 그것을 7로 나눈 평균값이다.
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         |
+--------------+--------------+----------------+
Explanation: 
1st moving average from 2019-01-01 to 2019-01-07 has an average_amount of (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
2nd moving average from 2019-01-02 to 2019-01-08 has an average_amount of (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
3rd moving average from 2019-01-03 to 2019-01-09 has an average_amount of (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
4th moving average from 2019-01-04 to 2019-01-10 has an average_amount of (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

해소

  • 보통 이와 같은 문제는 자기 자신을 join하여 푼다. 그리고 on 절에서 하루씩 어긋나게 만들면 된다.
  • 예를 들면 다음과 같다 : (on a.date = DATE_ADD(b.date, interval -1 day)
  • 하지만 기준이 7일이다. 셀프 조인을 7개나 하는 것은 좋아보이지 않았다.
  • 이를 서브쿼리로 해소할 수 있었다.
    • 판매일을 기준으로 group by 하였다.
    • 해당일로부터 7일 전까지의 레코드를 limit으로 추출 후 reduce하였다.
with tb as(
    select 
        visited_on
        , sum(amount) amount
        , row_number() over(order by visited_on asc) ranks
    from customer 
    group by visited_on
)
select
    visited_on
    , (select sum(amount) from (select t.amount from tb t where t.ranks <= tb.ranks order by t.ranks desc limit 7) tt) amount
    , round((select sum(amount) from (select t.amount from tb t where t.ranks <= tb.ranks order by t.ranks desc limit 7) tt) / 7, 2) average_amount
from tb
where tb.ranks>6;
  • 더 쉬운 방법이 없을까 찾아봤다.
  • row [int] preceding 이 위의 limit [int] 와 동일하게 동작한다.
  with tb as(
    select 
        visited_on
        , sum(sum(amount)) over(order by visited_on rows 6 preceding) amount
        , row_number() over(order by visited_on asc) ranks
    from customer 
    group by visited_on
)
select 
    visited_on
    , amount
    , round(amount/7, 2) average_amount
from tb
where ranks > 6

나아가며

  • 리트코드와 함께 쿼리 학습은 현 블로그로 마무리하였다.
  • with, over, rank, partition by 등 다양한 문법을 연습 문제를 통해 배웠고 적용할 수 있었다.
  • 하지만 책이나 강의에 따라 학습하지 않고 필요에 따라 학습하여 일관되고 통합적인 지식이 부족하다는 느낌을 받았다. 이것이 리트 코드로 학습하는 것을 종료한 이유다.
  • 장기적으로 깊이 있는 학습이 필요한 것 같다.
  • sql의 깊이를 더할 수 있는 좋은 시간이었다. 재밌었다.