MariaDB MySQL 일요일 또는 월요일 기준 주별 월별 통계

Database

MariaDB MySQL 일요일 또는 월요일 기준 주별 월별 통계

카피코딩 2024. 5. 5. 22:55

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

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

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

SELECT *
FROM test.statistics
ORDER BY INSERT_DT

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

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

// 주간통계
SELECT DATE_FORMAT(DATE_SUB(INSERT_DT, INTERVAL (DAYOFWEEK(INSERT_DT)-2) DAY), '%Y/%m/%d') as start,
       DATE_FORMAT(DATE_SUB(INSERT_DT, INTERVAL (DAYOFWEEK(INSERT_DT)-8) DAY), '%Y/%m/%d') as end,
       DATE_FORMAT(INSERT_DT, '%Y%U') AS `week`,
       sum(VISIT_CNT)
FROM test.statistics
GROUP BY week;

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

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

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

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

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

        select
        ADDDATE( INSERT_DT, - WEEKDAY(INSERT_DT) + 0 ) AS MONDAY
        ,ADDDATE( INSERT_DT, - WEEKDAY(INSERT_DT) + 6 ) AS SUNDAY
        ,INSERT_DT
        ,VISIT_CNT
        FROM test.statistics
        ORDER BY INSERT_DT

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

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

SELECT
dd.monday
,dd.sunday
,sum(VISIT_CNT)

FROM
(
        select
        ADDDATE( INSERT_DT, - WEEKDAY(INSERT_DT) + 0 ) AS MONDAY
        ,ADDDATE( INSERT_DT, - WEEKDAY(INSERT_DT) + 6 ) AS SUNDAY
        ,INSERT_DT
        ,VISIT_CNT
        FROM test.statistics
        ORDER BY INSERT_DT
) DD
GROUP BY DD.Monday

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

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

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

        SELECT
            DATE_FORMAT(INSERT_DT, '%Y%m') AS YM
               , VISIT_CNT
        FROM test.statistics
        ORDER BY INSERT_DT

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

SELECT dd.YM
    , SUM(dd.VISIT_CNT) AS SM
FROM(
        SELECT
            DATE_FORMAT(INSERT_DT, '%Y%m') AS YM
               , VISIT_CNT
        FROM test.statistics
        ORDER BY INSERT_DT
)DD
GROUP BY DD.YM

- copy coding -

저작자표시 비영리 변경금지