MYSQL 날짜 형변환 과 데이터가 없어도 날짜 출력하는 방법

개발환경

    - Java8, Mybatis, SpringBoot, gradle, mariaDB, JSP

 

목적과 문제발생

     - 기간별 데이터를 출력해야하는데 데이터가 없는 기간은 조회되지 않았다.

 

해결방법 

     1. 출력할 날짜 템플릿을 만드는 쿼리를 작성한다.

     2. 모듈도 중복되서 출력되면 안되므로 기간 템플릿 쿼리와 조인하여 최종 템플릿 쿼리를 픽스한다.

     3. 템플릿 쿼리에 넣을 데이터를 가지고 있는 테이블을 조인한다.

 

테이블구조

하기는 문제해결 과정으로 쿼리 변천사이다. 

SELECT 
IFNULL(MONTH(B.접수일), 0) AS 접수일
, A.세부코드명 AS 모듈명
, A.세부코드 AS 세부코드
, COUNT(B.진행상태) AS 접수건수
FROM 
(SELECT * FROM 공통코드 WHERE 회사SEQ = #{회사SEQ}) A
LEFT OUTER JOIN 
SR_BOARD B 
ON 
A.세부코드 = B.모듈
<if test="진행상태 != null and 진행상태 != ''">
AND B.진행상태= #{진행상태}
</if>
<if test="startDate != null and startDate != '' and endDate != null and endDate != ''">
<![CDATA[	
AND DATE_FORMAT(B.접수일, '%Y-%m') >= #{startDate} 
AND DATE_FORMAT(B.접수일, '%Y-%m') <= #{endDate} 
]]>
</if>
GROUP BY 접수일, 모듈명
ORDER BY A.세부코드​

 

1차 작성 쿼리 // 문제점 - 데이터가 없는 날짜는 회신되지 않음

   

- IFNULL(Column명, "Null일 경우 대체 값") // ORACLE의 경우 NVL, MSSQL의 경우 ISNULL을 사용하여 NULL 체크후 대체값 적용

- 선택한 회사의 날짜별, 모듈별 데이터를 가져와 추가 데이터 가공없이 사용하기위해 NULL체크를 진행하였다. 

- <if test="진행상태 != null and 진행상태 != ''"> ~ </if>

- 이 쿼리는 접수, 완료건을 파라미터의 존재여부로 각각 조회하여, 접수 / 완료 건수를 추출한다.

 

2021.08.01 - [Programming/DataBase] - MYSQL 쿼리문 통계(일간/주간/월간/기간별), DATE_FORMAT() 알아보기

MYSQL 쿼리문 통계(일간/주간/월간/기간별), DATE_FORMAT() 알아보기

 

 

1번 코드
select 
	a.Date AS 접수일
	, B.모듈명
	, B.세부코드
	, CASE WHEN B.접수건수 IS NULL THEN 0 ELSE 1 END AS 접수건수
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
left outer JOIN 
(SELECT 
	IFNULL(DATE(B.접수일), 0) AS 접수일
	, A.세부코드명 AS 모듈명
   	, A.세부코드 AS 세부코드
	, COUNT(B.진행상태) AS 접수건수
FROM 
 	(SELECT * FROM 공통코드 WHERE 회사SEQ =2) A
	 	LEFT OUTER JOIN 
	 	SR_BOARD B 
	 	ON 
			A.세부코드 = B.모듈명
		GROUP BY 접수일, 모듈명 ORDER BY A.세부코드) B
on a.Date = DATE_FORMAT(B.접수일, '%Y-%m-%d')
where a.Date between '2021-05-01' and '2021-07-28'
GROUP BY a.Date
ORDER BY a.Date ;
--------------------------------------------------
2번 코드

select 
	DATE_FORMAT(a.Date,'%Y-%m') AS 접수일
	, B.모듈명
    , B.세부코드
	, CASE WHEN B.접수건수 IS NULL THEN 0 ELSE 1 END AS 접수건수
from (
select
    date_format(
        adddate('2021-05-01', INTERVAL @num:=@num+1 month),
        '%Y-%m-%d'
    ) date
FROM
	SR_BOARD
	,(select @num:=-1) num
limit
	12
	)a
left outer JOIN 
(SELECT 
	IFNULL(DATE_FORMAT(B.접수일, '%Y-%m'), 0) AS 접수일
	, A.세부코드명 AS 모듈명
  	, A.세부코드 AS 세부코드
	, COUNT(B.진행상태) AS 접수건수
FROM 
 	(SELECT * FROM 공통코드 WHERE 회사SEQ = 2) A
 	LEFT OUTER JOIN 
 	SR_BOARD B 
	ON 
	A.세부코드 = B.모듈
	GROUP BY B.접수일, 모듈
	ORDER BY A.세부코드) B
on DATE_FORMAT(a.Date,'%Y-%m') = DATE_FORMAT(B.RCEPT_DT, '%Y-%m')
where DATE_FORMAT(a.Date, '%Y-%m') between '2021-05' and '2021-07'
GROUP BY a.DATE, B.접수일, B.모듈
ORDER BY a.date;

 

2차 작성 쿼리 //

문제점 - 데이터가 없는 날짜도 나오긴 하지만, 날짜 중복(동일 날짜에 모듈 여러개일 가능성)이 반영되지 않음

 

날짜 템플릿을 만들기위해 구글링을 통해 두가지 방법을 찾았다.

<1번 코드>는 union all을 이용해 계속해서 날짜를 이어간 것이고, (현재날짜까지 날짜를 생성할수 있고 미래날짜 생성불가)

<2번 코드>는 변수 num을 선언하고 adddate(DATE_ADD의 별칭)를 통해 limit 으로 제한한 만큼 날짜를 생성하는 쿼리이다.

(미래 날짜도 생성 가능)

 

위와같이 조인 할 경우,

 

1번 코드는 데이터가 있는 날짜에 출력은 되나 해당 날짜에 여러개의 모듈이 있을때 한건만 반영되어 추출되었고,

2번 코드는 데이터 값이 있음에도 불구하고 모듈과 세부코드 컬럼값에 null을 반환했다

 

 SELECT
	A.DATE AS 접수일
	, A.세부코드명 AS 모듈
	, A.세부코드 AS 세부코드
	, IFNULL(B.CNT, 0) AS 접수건수
FROM (
	SELECT D.DATE
		,C.세부코드명
		,C.세부코드 
	FROM (
		SELECT DATE_FORMAT(DD.Date, '%Y-%m') AS DATE
		FROM (
			SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS Date
		    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
		    	cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
		    	cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
		    	cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
		) DD
		WHERE DATE_FORMAT(DD.DATE, '%Y-%m') BETWEEN #{startDate} AND #{endDate}	
		GROUP BY DATE_FORMAT(DD.Date, '%Y-%m')
	) D
	JOIN 공통코드 C
	ON 회사SEQ = #{회사SEQ}
	AND 세부코드 IS NOT NULL
) A
LEFT OUTER JOIN (
	SELECT
		DATE_FORMAT(접수일, '%Y-%m') AS 접수일
		, 모듈
		, COUNT(*) AS CNT
	FROM SR_BOARD
	WHERE DATE_FORMAT(접수일, '%Y-%m') BETWEEN #{startDate} AND #{endDate}
	 	<if test="진행상태 != null and 진행상태 != ''">
			AND 진행상태 = #{진행상태}
		</if>
	GROUP BY DATE_FORMAT(접수일, '%Y-%m'), 모듈
) B
ON A.DATE = B.접수일
AND A.세부코드 = 모듈

 

3차 작성 쿼리 // 원하는 데이터값 추출 완료(감격)

 

 

애초에 템플릿을 만들때 모듈과 날짜를 조인하면 동일 날짜에 모듈이 여러개여도 데이터가 모두 추출된다.

기본 템플릿을 잘 만들어서 대입해야되는 것이었다.

 

코드는 모두 월별로 출력되도록 작성했지만

DATE_FORMAT의 format을 '%Y-%m-%D'로 변경하면 일간, '%Y'는 연간, '%Y-%u'는 주간으로 출력할 수 있다.

 

 

---

1번, 2번 코드 출처 👇👇👇

1번 코드 https://oboki.net/workspace/data-engineering/database/mysql/with-recursive-row-generator/

2번 코드  https://www.python2.net/questions-1574989.htm