꿈꾸는 개발자, DBA 커뮤니티 구루비

mysql(mariaDB)에서 윈도우함수를 이용해 월별 매출 실적구하는 예제 문의

by 루크 [MySQL] mysql mariadb 윈도우함수 월별 분기별 연도별 매출 실적 비교 [2020.06.09 16:39:39]

아래와 같이 일별, 부서(dept)별 매출 실적이 있는 table이 있습니다.

dt	dept	sales
2020-06-05	A	10
2020-06-01	A	20
2020-05-15	A	30
2020-05-10	B	20
2020-05-01	A	20
2020-04-30	A	10
2020-04-20	B	40
2020-04-10	A	30
2020-04-05	B	30
2020-03-25	A	50
2020-03-22	B	25
2020-03-15	A	60
2020-03-02	B	80
2020-02-23	A	20
2020-02-18	B	15
2020-02-16	A	30
2020-02-05	B	40
2020-01-26	A	10
2020-01-15	B	90

위 table에서 아래와 같은 월별 매출 결과를 얻고 싶어서
구글링을 열심히 하다보니 윈도우함수라는 것을 발견해서 나름 query를 만들어 봤는데
생각처럼 잘 안되네요. (당월=6월)

dept	전체매출합	2개월전 (4월)	1개월전 (5월)	당월 (6월)
A	290	40	50	30
B	340	70	20	0

아래는 제가 구현한 query에서 어느 부분이 잘못된 것인지 고수님들의 조언 부탁드립니다.
아직 초보라 몇일을 연구해봐도 못 찾겠습니다.
사용하는 DB는 mariaDB 10 입니다.

SELECT 
	dept, 
	SUM(sales) `전체매출합`,
	SUM(sales) over (ORDER BY dt RANGE BETWEEN INTERVAL '2' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING) `2개월전`,
	SUM(sales) over (ORDER BY dt RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '0' MONTH PRECEDING) `1개월전`,
	SUM(sales) over (ORDER BY dt RANGE BETWEEN INTERVAL '0' MONTH PRECEDING AND INTERVAL '0' MONTH FOLLOWING) `당월`
FROM (
	SELECT '2020-06-05' dt, 'A' dept, 10 sales UNION all
	SELECT '2020-06-01' dt, 'A' dept, 20 sales UNION all
	SELECT '2020-05-15' dt, 'A' dept, 30 sales UNION ALL
	SELECT '2020-05-10' dt, 'B' dept, 20 sales UNION ALL
	SELECT '2020-05-01' dt, 'A' dept, 20 sales UNION ALL
	SELECT '2020-04-30' dt, 'A' dept, 10 sales UNION ALL
	SELECT '2020-04-20' dt, 'B' dept, 40 sales UNION ALL
	SELECT '2020-04-10' dt, 'A' dept, 30 sales UNION ALL
	SELECT '2020-04-05' dt, 'B' dept, 30 sales UNION ALL
	SELECT '2020-03-25' dt, 'A' dept, 50 sales UNION ALL
	SELECT '2020-03-22' dt, 'B' dept, 25 sales UNION ALL
	SELECT '2020-03-15' dt, 'A' dept, 60 sales UNION ALL
	SELECT '2020-03-02' dt, 'B' dept, 80 sales UNION ALL
	SELECT '2020-02-23' dt, 'A' dept, 20 sales UNION ALL
	SELECT '2020-02-18' dt, 'B' dept, 15 sales UNION ALL
	SELECT '2020-02-16' dt, 'A' dept, 30 sales UNION ALL
	SELECT '2020-02-05' dt, 'B' dept, 40 sales UNION ALL
	SELECT '2020-01-26' dt, 'A' dept, 10 sales UNION ALL
	SELECT '2020-01-15' dt, 'B' dept, 90 sales
) d


감사합니다.

by pajama [2020.06.09 18:07:25]

sum case를 사용해봤습니다.

그리고 mariadb는 아직 윈도우 함수에서 datetime 연산이 안된다고 하네요.

https://jira.mariadb.org/browse/MDEV-9727

SELECT
    dept,
    SUM(sales) '전체매출합',
    SUM(CASE WHEN DATE_FORMAT(NOW() - INTERVAL 2 MONTH,'%Y-%m') = DATE_FORMAT(STR_TO_DATE(dt,'%Y-%m'),'%Y-%m') THEN sales ELSE 0 END) '2개월전',
    SUM(CASE WHEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y-%m') = DATE_FORMAT(STR_TO_DATE(dt,'%Y-%m'),'%Y-%m') THEN sales ELSE 0 END) '1개월전',
    SUM(CASE WHEN DATE_FORMAT(NOW() - INTERVAL 0 MONTH,'%Y-%m') = DATE_FORMAT(STR_TO_DATE(dt,'%Y-%m'),'%Y-%m') THEN sales ELSE 0 END) '당월'
FROM (
    SELECT '2020-06-05' dt, 'A' dept, 10 sales UNION all
    SELECT '2020-06-01' dt, 'A' dept, 20 sales UNION all
    SELECT '2020-05-15' dt, 'A' dept, 30 sales UNION ALL
    SELECT '2020-05-10' dt, 'B' dept, 20 sales UNION ALL
    SELECT '2020-05-01' dt, 'A' dept, 20 sales UNION ALL
    SELECT '2020-04-30' dt, 'A' dept, 10 sales UNION ALL
    SELECT '2020-04-20' dt, 'B' dept, 40 sales UNION ALL
    SELECT '2020-04-10' dt, 'A' dept, 30 sales UNION ALL
    SELECT '2020-04-05' dt, 'B' dept, 30 sales UNION ALL
    SELECT '2020-03-25' dt, 'A' dept, 50 sales UNION ALL
    SELECT '2020-03-22' dt, 'B' dept, 25 sales UNION ALL
    SELECT '2020-03-15' dt, 'A' dept, 60 sales UNION ALL
    SELECT '2020-03-02' dt, 'B' dept, 80 sales UNION ALL
    SELECT '2020-02-23' dt, 'A' dept, 20 sales UNION ALL
    SELECT '2020-02-18' dt, 'B' dept, 15 sales UNION ALL
    SELECT '2020-02-16' dt, 'A' dept, 30 sales UNION ALL
    SELECT '2020-02-05' dt, 'B' dept, 40 sales UNION ALL
    SELECT '2020-01-26' dt, 'A' dept, 10 sales UNION ALL
    SELECT '2020-01-15' dt, 'B' dept, 90 sales
) d
GROUP BY dept
;

by 마농 [2020.06.09 18:08:41]

WITH t AS
(
SELECT '2020-06-05' dt, 'A' dept, 10 sales
UNION ALL SELECT '2020-06-01', 'A', 20
UNION ALL SELECT '2020-05-15', 'A', 30
UNION ALL SELECT '2020-05-10', 'B', 20
UNION ALL SELECT '2020-05-01', 'A', 20
UNION ALL SELECT '2020-04-30', 'A', 10
UNION ALL SELECT '2020-04-20', 'B', 40
UNION ALL SELECT '2020-04-10', 'A', 30
UNION ALL SELECT '2020-04-05', 'B', 30
UNION ALL SELECT '2020-03-25', 'A', 50
UNION ALL SELECT '2020-03-22', 'B', 25
UNION ALL SELECT '2020-03-15', 'A', 60
UNION ALL SELECT '2020-03-02', 'B', 80
UNION ALL SELECT '2020-02-23', 'A', 20
UNION ALL SELECT '2020-02-18', 'B', 15
UNION ALL SELECT '2020-02-16', 'A', 30
UNION ALL SELECT '2020-02-05', 'B', 40
UNION ALL SELECT '2020-01-26', 'A', 10
UNION ALL SELECT '2020-01-15', 'B', 90
)
SELECT dept
     , SUM(sales) sales_tot
     , IFNULL(SUM(CASE m WHEN 2 THEN sales END), 0) sales_2
     , IFNULL(SUM(CASE m WHEN 1 THEN sales END), 0) sales_1
     , IFNULL(SUM(CASE m WHEN 0 THEN sales END), 0) sales_0
  FROM (SELECT dept, dt, sales
             , TIMESTAMPDIFF( MONTH
                            , DATE_FORMAT(dt   , '%Y-%m-01')
                            , DATE_FORMAT(NOW(), '%Y-%m-01')
                            ) m
          FROM t
        ) a
 GROUP BY dept
;

by 아발란체 [2020.06.09 19:18:44]

와.. MariaDB가 이제 윈도우 함수도 지원을 하다니... 덕분에 좋은 정보 알아갑니다.

https://mariadb.com/kb/en/window-frames/

SELECT 
	dept, SUM(sales), 
	SUM(CASE WHEN((DATE_FORMAT(NOW(), '%Y%m') - dt) = 2) THEN sales ELSE 0 END) AS B2, #2개월전
	SUM(CASE WHEN((DATE_FORMAT(NOW(), '%Y%m') - dt) = 1) THEN sales ELSE 0 END) AS B1, #1개월전
	SUM(CASE WHEN((DATE_FORMAT(NOW(), '%Y%m') - dt) = 0) THEN sales ELSE 0 END) AS B0 #당월
FROM (
	
	SELECT DATE_FORMAT(dt, '%Y%m') + 0 as dt, dept, sales FROM (
		SELECT '2020-06-05' dt, 'A' dept, 10 sales UNION all
	    SELECT '2020-06-01' dt, 'A' dept, 20 sales UNION all
	    SELECT '2020-05-15' dt, 'A' dept, 30 sales UNION ALL
	    SELECT '2020-05-10' dt, 'B' dept, 20 sales UNION ALL
	    SELECT '2020-05-01' dt, 'A' dept, 20 sales UNION ALL
	    SELECT '2020-04-30' dt, 'A' dept, 10 sales UNION ALL
	    SELECT '2020-04-20' dt, 'B' dept, 40 sales UNION ALL
	    SELECT '2020-04-10' dt, 'A' dept, 30 sales UNION ALL
	    SELECT '2020-04-05' dt, 'B' dept, 30 sales UNION ALL
	    SELECT '2020-03-25' dt, 'A' dept, 50 sales UNION ALL
	    SELECT '2020-03-22' dt, 'B' dept, 25 sales UNION ALL
	    SELECT '2020-03-15' dt, 'A' dept, 60 sales UNION ALL
	    SELECT '2020-03-02' dt, 'B' dept, 80 sales UNION ALL
	    SELECT '2020-02-23' dt, 'A' dept, 20 sales UNION ALL
	    SELECT '2020-02-18' dt, 'B' dept, 15 sales UNION ALL
	    SELECT '2020-02-16' dt, 'A' dept, 30 sales UNION ALL
	    SELECT '2020-02-05' dt, 'B' dept, 40 sales UNION ALL
	    SELECT '2020-01-26' dt, 'A' dept, 10 sales UNION ALL
	    SELECT '2020-01-15' dt, 'B' dept, 90 sales
	) ST

) TT
GROUP BY	
	dept
;

기존 조회건수로 윈도우 함수 사용시


	SELECT
	    DISTINCT dept, dt,
	    SUM(sales) OVER(PARTITION BY dept, dt) AS val
	FROM (
		SELECT DATE_FORMAT(dt, '%Y%m') + 0 as dt, dept, sales FROM (
			SELECT '2020-06-05' dt, 'A' dept, 10 sales UNION all
		    SELECT '2020-06-01' dt, 'A' dept, 20 sales UNION all
		    SELECT '2020-05-15' dt, 'A' dept, 30 sales UNION ALL
		    SELECT '2020-05-10' dt, 'B' dept, 20 sales UNION ALL
		    SELECT '2020-05-01' dt, 'A' dept, 20 sales UNION ALL
		    SELECT '2020-04-30' dt, 'A' dept, 10 sales UNION ALL
		    SELECT '2020-04-20' dt, 'B' dept, 40 sales UNION ALL
		    SELECT '2020-04-10' dt, 'A' dept, 30 sales UNION ALL
		    SELECT '2020-04-05' dt, 'B' dept, 30 sales UNION ALL
		    SELECT '2020-03-25' dt, 'A' dept, 50 sales UNION ALL
		    SELECT '2020-03-22' dt, 'B' dept, 25 sales UNION ALL
		    SELECT '2020-03-15' dt, 'A' dept, 60 sales UNION ALL
		    SELECT '2020-03-02' dt, 'B' dept, 80 sales UNION ALL
		    SELECT '2020-02-23' dt, 'A' dept, 20 sales UNION ALL
		    SELECT '2020-02-18' dt, 'B' dept, 15 sales UNION ALL
		    SELECT '2020-02-16' dt, 'A' dept, 30 sales UNION ALL
		    SELECT '2020-02-05' dt, 'B' dept, 40 sales UNION ALL
		    SELECT '2020-01-26' dt, 'A' dept, 10 sales UNION ALL
		    SELECT '2020-01-15' dt, 'B' dept, 90 sales
		) ST
	) MT

Window Frames

Some window functions operate on window frames.

by 루크 [2020.06.09 21:15:00]

아..아직 mariaDB에서는 윈도우함수에 날짜연산이 안되었군요...
오늘도 하나 배워갑니다. 위에 답변해주신 모든분께 감사드립니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변