Copy Coding

일반적으로 주별 통계를 구하기 위해서 date_format에 %U를 사용하는 경우가 있습니다.  만약 1개월 또는 년만 간의 통계를 작성하는 경우에는 이 기호를 사용해도 되지만 연속된 기간에 대한 데이터를 사용하여 통계를 작성하게 되면 년도가 넘어서는 기간에서는 통계가 제대로 나타나지 않게 됩니다.

테스트를 진행하기 위해 2023년에서 2024년을 넘어서 계속해서 주별 통계를 구해보도록 합니다.

일단 DB에 어떤 데이터들이 들어있는지 확인해 봅니다.

위와 같이 2023년 11월, 12월 그리고 2024년 1월에 데이터가 들어있습니다.

이제 '%Y%U'를 사용해서 일반적인 방법으로 통계를 작성해 보겠습니다.

위의 쿼리는 주의 시작을 일요일로 하고 토요일 종료하는 경우의 통계입니다.

조회 결과에서 week 컬럼을 보면 년(year)+주번호(week number)로 잘 나와있지만 첫번째 컬럼의 start에는 날짜가 2023년을 넘어가면서 2023년의 마지막 주와 2024년의 첫번째 주의 시작이 같은 주에 맞물리게 되어 결과값이 중복되게 나오게 됩니다.

이번에는 주번호(week number)를 무시하고 통계를 구해보도록 하겠습니다.

여러 방법이 있겠지만 2단계를 통하여 구하도록 합니다.

먼저 입력된 날짜(INSERT_DT)의 시작 주일(이번에는 한주의 시작을 월요일로)인 월요일과 주의 마지막 요일인 일요일의 날짜 컬럼을 추가해서 목록을 조회합니다.

DB에 있는 데이터의 입력일을 기준으로 월요일과 일요일의 날짜 컬럼을 구하여 조회가 되었습니다.

각각의 데이터에 대한 주의 시작일과 종료일을 구했으니 다음은 그냥 월요일끼리 그룹으로 묶어주기만 하면 되겠죠

이렇게 처음에 구한 목록을 월요일로 그룹화 하면 간단하게 주별 통계를 구할 수 있습니다.

동일한 데이터에 대해 월간 통계도 비슷한 방법을 사용하면 쉽게 구할 수 있습니다.

먼저 날짜에 해당하는 년+월의 컬럼을 추가합니다.

그리고 구해진 년월을 그룹으로 묶어주면 월별 통계를 구할 수 있습니다.

- copy coding -

오라클의 Job Schedule과 동일하게 MariaDB에서도 Procedure가 있다면 별도의 언어를 사용하여 배치 프로그램을 생성하지 않고 MariaDB 이벤트 스케줄러를 사용하여 배치 처리를 할 수 있습니다.

Procedure를 사용해야 하기 때문에 JDBC 방식처럼 복잡한 작업이 동반되는 업무에는 적합하지 않고 단순히 DB들을 이용하여 작업하는 경우에는 작업 속도가 빠릅니다.

먼저 Event Scheduler를 사용하려면 DB에 기능이 활성화 되어있는지 쿼리를 이용하여 확인을 해야합니다.

SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler';

만약 위에 보이는 것처럼 OFF 상태라면 다음 쿼리로 활성화시켜줍니다.

SET GLOBAL event_scheduler = ON;

다시 한번 쿼리가 잘 적용되어 활성화 되었는지 확인합니다.

SHOW VARIABLES WHERE VARIABLE_NAME = 'event_scheduler';

이상 없이 적용 되었네요.

이제 DB에 Event를 생성합니다.

쿼리는 아래와 같이 CREATE 명령을 사용하며 마지막에 실행되어야 하는 Procedure를 호출합니다.

테스트를 위해 1분에 한번씩 작동하도록 하였는데(every 1 MINUTE)

반복 주기는 아래와 같이 다양하게 선택해서 설정이 가능 합니다.

{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

여기서 하나를 선택해서 반복 주기를 설정할 수 있습다.

이벤트 생성이 잘 되었는지 아래 명령을 이용하여 확인해봅니다.

SHOW EVENTS;

이벤트가 잘 생성 되었습니다.

만약 이벤트를 삭제하려면 Table 삭제와 비슷한 명령어를 사용합니다.

DROP EVENT EV_STATISTIC;

DB Tool을 이용하여 Event를 찾아보면 아래처럼 확인이 가능 합니다.

저는 DBeaver를 사용중 입니다.

Interval Field 부분을 DAY로 변경하고 Comment를 수정하면 매일 1회 작동하는 배치로 변경이 가능 합니다.

- copy coding -

프로그램 할 때 query log가 안보이면 제대로 실행이 되고 있는 건지 디버깅도 힘들고 답답하기 때문에 로그 보는 설정을 먼저 진행하게 됩니다.  오늘 진행하는 작업은 대부분의 Database가 동일한 방법으로 작업이 가능 합니다.

먼저 maven을 이용하여 log4j2를 추가 합니다.

pom.xml 

그리고 추가한 라이브러리가 작동할 수 있도록 application.properties에

spring.datasource.driverClassName=net.sf.log4jdbc.sql.jdbcapi.DriverSpy

를 추가하고 source url에 log4jdbc 를 추가 해줍니다. 

application.properties

다른 데이터 베이스를 사용 하는 경우에도 첫 번째 라인인 DriverSpy는 추가해 주고 url 부분을 다음처럼 변경을 합니다.

spring.datasource.url=jdbc:log4jdbc:oracle:thin:@localhost:1521/xe

spring.datasource.url=jdbc:log4jdbc:mariadb://localhost:3306/testdb

spring.datasource.url=jdbc:log4jdbc:mysql://localhost/testdb

그리고 실행을 하면 아래 그림처럼

모든 값들이 정리되지 않고 나오게 되는데 이제 정리를 하기 위한 설정을 추가해 줍니다.

src/main/resources 폴더에 properties 파일을 하나 생성 합니다.

log4jdbc.log4j2.properties

src/main/resources 폴더에 추가로 xml 파일을 생성 합니다.

logback.xml

이제 다시 실행을 하고 console을 보게 되면 

보기 좋게 형식을 갖추어 출력이 됩니다.

- copy coding -