[Postgresql] rolling window chart

우리 기업의 고객 수를 정확히 알고 싶다.

하지만 고객은 유입도 있고 이탈도 있어서, 특정 기간만을 봐서는 정확하게 집계할 수 없다.

따라서 고객은 여러 기간을 두고 봐야 하는데, 문제는 고객들 역시 패턴이 있다는 점이다.

지금 내가 소속된 회사같이 B2B라면, 고객들이 진출한 업계에 따라서 구매 패턴이 달라질 수가 있다.

예를 들어 스포츠나 레저 쪽 산업에 진출한 회사라면 당연히 계절마다 구매 패턴이 달라지게 될 것이다.

성장하는 회사라고 해도 직선의 그래프가 나오질 않으니 정확한 통계를 내려면 몇 년을 두고 봐야 할 텐데,

문제는 스타트업들의 경우에는 그 정도의 고객 데이터가 확보되지 않은 경우가 다반사라는 점이다.

어떻게 일부분의 데이터만 가지고도 전체 윤곽을 그려볼 수 있을까?

 

Rolling window chart

롤링 윈도우 차트, 또는 Rolling statistics 라고 하는 차트를 그려보면 이 상황에 도움이 될 수 있다.

이는 적은 데이터를 가지고도 효과적으로 현재의 성장세를 파악하는 데에 도움을 줄 수 있다.

Rolling window chart는 일정 기간 동안의 데이터를 이용해 현재의 통계를 계산하는 그래프로 정의되는데,

이해를 위해 예시를 들면 이렇다.

1. 오늘로부터 한 달 전까지의 날짜 동안의 결제 회원의 수
2. 어제로부터 한 달 전까지의 날짜 동안의 결제 회원의 수
3. …
4. 한 달 전의 날짜로부터 또 다시 한 달 전까지의 날짜 동안의 결제 회원의 수

일정 기간 ( = 하루 ) 이 중첩된, 동일한 기간 내의 사용자를 분석하기 때문에 적은 데이터로도 통계를 낼 수 있다.

이걸 SQL 로 작성하기 위해, 일단 아래와 같이 날짜를 출력해보자.

 

SELECT
    *
FROM
    generate_series('2022-12-01'::date, '2022-12-31'::date, '1 day'::interval);

• 2022-12-01 00:00:00+09
• 2022-12-02 00:00:00+09
• 2022-12-03 00:00:00+09
• 2022-12-04 00:00:00+09
• …
• 2022-12-31 00:00:00+09

이렇게 한 달 간의 날짜를 뽑았으면, 이 한 달간의 날짜로부터 조회하고 싶은 일정 기간을 정해야 한다.

그걸 뽑기 위해서는 위 쿼리를 감싸 서브 쿼리로 만든 다음, 그 서브 쿼리의 칼럼으로부터 일정 기간을 빼주면 된다.

 

SELECT
        DATE_TRUNC('day', "date"."day") + '-14 days' AS "startDate",
        "date"."day" AS "endDate"
    FROM (
        SELECT
            *
        FROM
      generate_series('2022-12-01'::date, '2022-12-31'::date, '1 day'::interval) AS "day") AS "date"

원래의 쿼리를 FROM의 서브 쿼리로 만든 다음, 각 row 로부터 2주 전의 날짜가 나오게 해주었다.

이제 조회를 할 시작 날짜와 마지막 날짜를 얻었으니 이 날짜 쿼리를 다시 FROM으로 감싸 조회를 하면 된다.

편의 상 위 쿼리를 WITH 문으로 감싸서 “days”라는 이름으로 식별하게끔 하겠다.

 

WITH "days" AS (
    SELECT
        DATE_TRUNC('day', "date"."day") + '-14 days' AS "startDate",
        "date"."day" AS "endDate"
    FROM (
        SELECT
            *
        FROM
      generate_series('2022-12-01'::date, '2022-12-31'::date, '1 day'::interval) AS "day") AS "date"
)
SELECT
    days.*,
    (
        SELECT
            COUNT(*)
        FROM (
            SELECT
                oi. "userId"
            FROM
                order_info AS oi -- 주문서를 의미하는 Table 이름, 주문 내역
            WHERE
                oi. "createAt" >= "days"."startDate" AND oi. "createAt" < "endDate"
            GROUP BY
                oi. "userId") AS sub)
    FROM
        "days"

이렇게 하면 각 날짜로부터 2주동안 결제를 한 유저의 수를 구할 수 있다.