시간 윈도우 롤링 - 희소 데이터와 시간 윈도우 롤링

희소 데이터와 시간 윈도우 롤링

date_dim 데이터를 살펴보자.

SELECT * FROM date_dim

date_dim 테이블의 날짜 컬럼인 date 범위는 다음과 같다.

SELECT MIN(date), MAX(date)
FROM date_dim

retail_sales 테이블에서 Women store와 1월 7월에 해당하는 날짜와 매출을 조회한다.

SELECT sales_month, sales
FROM retail_sales
WHERE kind_of_business = 'Women''s clothing stores'
	AND DATE_PART('month', sales_month) IN (1, 7)

• date_dim 테이블과 위에서 조회한 테이블을 JOIN 한다.
• JOIN 기준은 date를 포함하여 이전 11달까지의 날짜로 한다.
• date와 first_day_of_month가 같은 경우와 date 컬럼도 sales_month의 컬럼에 맞춰 1993-01-01부터 2020-12-01까지의 날짜만 조회한다.

  SELECT A.date, B.sales_month, B.sales
  FROM date_dim AS A
    JOIN (SELECT sales_month, sales
          FROM retail_sales
          WHERE kind_of_business = 'Women''s clothing stores'
          AND DATE_PART('month', sales_month) IN (1, 7)
  ) AS B ON B.sales_month BETWEEN A.date - INTERVAL '11 month' AND A.date
  WHERE A.date = A.first_day_of_month
    AND A.date BETWEEN '1993-01-01' AND '2020-12-01'
  ORDER BY A.date ASC, B.sales_month ASC;
  

  

• 위 쿼리에서 date별로 매출 평균을 계산한다.
• COUNT() 함수를 통해 record 개수를 센다. 1월과 7월만을 포함했기 때문에 앞서 했던 12개가 아닌 2개가 존재한다.

  SELECT
    A.date,
    ROUND(AVG(B.sales), 2) AS moving_avg,
    COUNT(B.sales) AS records
  FROM date_dim AS A
    JOIN (SELECT sales_month, sales
          FROM retail_sales
          WHERE kind_of_business = 'Women''s clothing stores'
          AND DATE_PART('month', sales_month) IN (1, 7)
  ) AS B ON B.sales_month BETWEEN A.date - INTERVAL '11 month' AND A.date
  WHERE A.date = A.first_day_of_month
    AND A.date BETWEEN '1993-01-01' AND '2020-12-01'
  GROUP BY A.date
  ORDER BY A.date ASC;
  

  

1993-01-01부터 2020-12-01까지의 고유한 날짜 테이블을 조회한다.

SELECT DISTINCT(sales_month) 
FROM retail_sales
WHERE sales_month BETWEEN '1993-01-01' AND '2020-12-01'
ORDER BY sales_month;

• 위에서 조회한 테이블과 retail_sales 테이블을 JOIN한다.
• B 테이블의 날짜는 A 테이블의 날짜를 포함하여 이전 11개월까지의 날짜를 기준인 것과, 비즈니스 종류를 Women store인 경우만을 JOIN한다.
• A 테이블의 날짜를 그루핑하며 각 날짜별 매출 합계를 계산한다.

  SELECT A.sales_month, ROUND(AVG(B.sales), 2) AS moving_avg
  FROM(
    SELECT DISTINCT(sales_month) 
    FROM retail_sales
    WHERE sales_month BETWEEN '1993-01-01' AND '2020-12-01'
  ) AS A JOIN retail_sales AS B
    ON B.sales_month BETWEEN A.sales_month - INTERVAL '11 month' AND A.sales_month
        AND B.kind_of_business = 'Women''s clothing stores'
  GROUP BY A.sales_month
  ORDER BY A.sales_month ASC;