계절성 비교 - 다중 구간 비교

다중 구간 비교

• Book stores에 해당하는 데이터만 조회한다.
• LAG 함수를 사용하여 같은 달에 해당하는 현재, 1년 전, 2년 전, 3년 전 매출 컬럼을 생성한다.
• ex) 95년도 1월, 94년도 1월, 93년도 1월, 92년도 1월

  SELECT
    sales_month,
    sales,
    LAG(sales, 1) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales1,
    LAG(sales, 2) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales2,
    LAG(sales, 3) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales3
  FROM retail_sales
  WHERE kind_of_business = 'Book stores';
  

  

• 위에서 생성한 쿼리를 FROM절에 서브쿼리로 사용한다.
• 각 날짜의 매출과 작년부터 3년전까지의 매출의 평균을 비교하여 비율을 계산한다.

  SELECT
    sales_month,
    sales,
    ROUND(((sales/((prev_sales1 + prev_sales2 + prev_sales3)/3))-1)*100, 2) AS pct_of_3_prev
  FROM(
    SELECT
    sales_month,
    sales,
    LAG(sales, 1) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales1,
    LAG(sales, 2) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales2,
    LAG(sales, 3) OVER(PARTITION BY DATE_PART('month', sales_month)
                       ORDER BY sales_month ASC) AS prev_sales3
    FROM retail_sales
    WHERE kind_of_business = 'Book stores'
  ) AS sub;
  

  

• 아래 쿼리에 주의한다.
• 쉽게 말하면 현재 행에서 전전전 행부터 전행까지릐 rows에 해당한다.

  ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
  

• 1995-01-01에 대한 pct_of_prev_3의 계산식은 다음과 같다. (790 + 998 + 1053) / 3 = 1119.67
• 만약 ROWS ~ PRECEDING을 넣어주지 않는다면, 누적 평균값을 계산하게 된다.

  SELECT
    sales_month,
    sales,
    AVG(sales) OVER(PARTITION BY DATE_PART('month', sales_month)
                    ORDER BY sales_month
                    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS pct_of_prev_3
  FROM retail_sales
  WHERE kind_of_business = 'Book stores';
  

  

• 위 쿼리가 이해가 되었다면 이어서 진행한다.
• 각 날짜의 매출과 작년부터 3년전까지의 매출의 평균을 비교하여 비율을 계산한다.
• LAG 함수를 사용해 구했던 비율과 같은 결과가 나타난다.
• 단, 1992-01-01부터 1994-01-01까지가 다르게 나타난다. LAG 함수를 사용하면 NULL인 부분이 포함된 계산 결과는 NULL로 나타난다. 현재 쿼리에서는 처음 이전 행이 존재하지 않을 경우에만 NULL이고, 3년치의 매출이 아니더라도 평균을 계산했기 때문이다.

  SELECT
    sales_month,
    sales,
    ROUND((sales / AVG(sales) OVER(PARTITION BY DATE_PART('month', sales_month)
                    ORDER BY sales_month
                    ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)-1)*100, 2) AS pct_of_prev_3
  FROM retail_sales
  WHERE kind_of_business = 'Book stores';