[SQL] 분기별 결산 확인

0. 목표

 - 분기별 결산 확인

1. 실습

 1) 테이블 생성

CREATE TABLE sql_test.quarter_test
(
	id int,
    dated datetime
);

 2) 데이터 삽입

INSERT INTO sql_test.quarter_test(id, dated) VALUES(1, '2020-01-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(2, '2020-02-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(3, '2020-05-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(4, '2020-08-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(5, '2020-11-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(6, '2021-01-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(7, '2021-02-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(8, '2021-03-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(9, '2021-07-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(10, '2021-08-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(11, '2021-09-01 12:12:12');
INSERT INTO sql_test.quarter_test(id, dated) VALUES(12, '2021-10-01 12:12:12');

 3) 분기별 결산

SELECT YEAR(dated), QUARTER(dated), COUNT(id) AS CNT 
FROM sql_test.quarter_test
GROUP BY 1, 2;

 4) 최근 분기 결과만 출력

  - 한달 31일 계산으로 분기(3개월) 이전부터 계산 

  - 내림차순으로 최근 데이터 1만 출력

SELECT YEAR(dated), QUARTER(dated), COUNT(id) AS CNT 
FROM sql_test.quarter_test
WHERE 
	dated < CURDATE()
    AND dated >= DATE_ADD(CURDATE(), INTERVAL -93 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC, 2 DESC
LIMIT 1;

2. 참고

 - MYSQL - QUARTER 함수