[SQL] 전월 동일 시점 누적 데이터 비교 쿼리문(ft. Line Chart)

 

 

테이블명과 컬럼명은 회사마다 다릅니다. SQL 도 DBMS 에 따라 조금씩 표현 방법이 다릅니다. 어떤식으로 쿼리문을 작성하면 되는지 이해하며 보시고 상황에 맞게 수정해서 사용해보세요 :)

 

 

 

전월 동일 시점 누적 데이터 비교 

= 전월 같은 날 까지의 누적 데이터 비교

 

 

 

예를들어 오늘이 22.06.13 이고, 

22.06.01 ~ 22.06.12 사이의 누적 주문 데이터와
22.05.01 ~ 22.05.12 사이의 누적 주문 데이터를 비교해 보고 싶을 때 , 

(아직 오늘 데이터는 변하는 중이니까 어제까지의 데이터로 비교해보겠다)
 

 
즉 같은말로,  

이번달 1일부터 어제까지의 누적주문 금액과

전월 1일부터 전월 동일 까지의 누적 금액을 비교해 보고 싶을 때 ,



아래와 같은 쿼리문을 작성해주면 비교해 볼 수 있다.  

 

 

 

SELECT this_month.day, this_month_total_price, this_month_total_price_sum,  last_month_total_price, last_month_total_price_sum

FROM
((SELECT RIGHT(LEFT(od_dt,10),2) AS day
,sum(od_price) AS this_month_total_price
,sum(this_month_total_price) over (order by day rows unbounded preceding) AS this_month_total_price_sum  
FROM order_table
WHERE LEFT(od_dt,10) BETWEEN LEFT(current_date,7)+'-01' AND 'current_date'
GROUP BY RIGHT(LEFT(od_dt,10),2)
ORDER BY day ASC) AS this_month

JOIN
  
(SELECT RIGHT(LEFT(od_dt,10),2) AS day
,sum(od_price) AS last_month_total_price
,sum(last_month_total_price) over (order by day rows unbounded preceding) AS last_month_total_price_sum
FROM order_table
  
WHERE LEFT(od_dt,10) BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND DATE(ADD_MONTHS(current_date,-1))
GROUP BY RIGHT(LEFT(od_dt,10),2)
ORDER BY day ASC) AS last_month
  
ON this_month.day = last_month.day)

GROUP BY this_month.day,  this_month_total_price, this_month_total_price_sum, last_month_total_price, last_month_total_price_sum
ORDER BY this_month.day ASC


--od_dt 는 주문날짜
--od_price 는 주문금액 
--current_date 는 오늘 날짜 반환해주는 함수

 

 

 

 

결과 테이블 형식>

day	this_month_total_price	this_month_total_price_sum	last_month_total_price	last_month_total_price_sum
01	30000	30000	15000	15000
02	25000	55000	10000	25000
03	15000	70000	9000	34000
04	10000	80000	12000	46000
05	40000	120000	13000	59000
...	...	...	...	...

 

 

 

 

 

line chart 로 나타내면 이렇게 볼 수 있다. 

x 축이 누적 경과일,  y축이 주문 금액 

 

 

 

 

 

 

 

 

코드 설명 : 

 

 

1.  6번행
sum(this_month_total_price) over (order by day rows unbounded preceding)

 

rows unbounded preceding 은 현재 값과 현재 값 이전에 있는 모든 값을 사용한다는 것인데 앞에

sum(this_month_total_price)   가 있으니,   

 

해당 day 에 해당되는 this_month_total_price 값과 이전 날짜에 있는 모든 this_month_total_price 값을 sum 해준다는 의미

 

 

 

2.  ADD_MONTHS(current_date,-1)

 

current_date 는 오늘 날짜를 반환해주는 함수. ADD_MONTHS 는 '월' 부분에 숫자를 더해주고 빼주는 함수

 

ADD_MONTHS('2022-06-03', 2)  이렇게 하면 결과는 '2022-08-03' 

ADD_MONTHS('2022-06-13', -3)  이렇게 하면 결과는 '2022-03-13' 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

 

월별 누적 주문금액 total 만 비교해서 간단한게 테이블로 보고 싶다면?  

 

 

 

아래 표와 같이 간단하게 현 시점까지의 누적 데이터를 비교해서 보고싶을 땐 쿼리문이 좀더 간단해진다. 

 

 

 

 

 

예를 들어 오늘이 22.6.13 인 경우,

22.6.1 ~ 22.6.12 판매금액 total 과 

22.5.1 ~ 22.5.12 판매금액 total 을 비교해 보고싶을 때 

 

 

 

 

아래와 같이 쿼리문을 작성해 볼 수 있다. 

 

 

SELECT LEFT(od_dt,7) AS month
,sum(od_price) AS od_amount

FROM order_table

WHERE od_dt >= LEFT(current_date,7)+'-01' 
OR od_dt <  DATE(ADD_MONTHS(current_date,-1)) 
AND od_dt BETWEEN LEFT(ADD_MONTHS(current_date,-1),7)+'-01' AND 'current_date'

GROUP BY month
ORDER BY month ASC

 

 

 

 

코드 설명 : 

 

WHERE 조건문이 어떤 의미냐면,

 

current_date 오(늘 날짜)가  22.6.13 라고 가정하고 설명해보겠다.  

그리고 DB 데이터는 어제날짜까지의 데이터만 업데이트 되어있다고 가정한다. 

 

od_dt 가 22.05.01 ~ 22.06.13  사이일때 

od_dt 가  22.06.01 보다 크면 22.06.01 ~ 22.06.12 데이터 출력하고 

od_dt 가  22.05.13 보다 작으면  22.05.01 ~ 22.05.12 데이터 출력하라는 의미다