MySQL/MariaDB :: 특정 날짜 사이의 년,월,주,일 Group By (with PHP)

---

특정 날짜 사이의 년,월,일 Group By 하는 포스팅은 꽤 많았지만,
특정 범위의 날짜만 구하는 것이 아니라, 넓은 범위의 날짜를 구한 후 Group By 하는 방식으로 되어 있었다.
나는 정확히 특정 범위의 날짜만 구해서 처리속도도 최소화 하고 싶었다.

---

기존에 사용했던 코드

select stat_date
from (
    select adddate('[시작날짜]',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) stat_date 
    from
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
    ) v
where stat_date between '[시작날짜]' and '[종료날짜]'
order by stat_date

외부 포스팅에서 가져와 활용했던 코드이다.
이 코드의 단점은 내가 원하지 않는 범위의 값까지도 연산에 활용된다는 점이다.
나는 딱 내가 원하는 범위의 값만 사용하길 원했기 때문에 코드를 변경하기로 함.

---

예제 코드

기초 코드

SELECT `date`
FROM (
    SELECT DATE_FORMAT(ADDDATE('2021-01-01', INTERVAL @num:=@num+1 DAY), '%Y-%m-%d') `date`
    FROM numbering, (SELECT @num:=-1) num
    LIMIT 12
) v
ORDER BY `date`

2021-01-01부터 2021-01-12까지 출력된다.
만약 LIMIT을 주지 않았다면 numbering에 있는 row의 수 만큼 날짜를 출력한다.

numbering table

우선 numbering 테이블이 필요하다. LIMIT이 없다면 numbering 테이블에 입력된 수 만큼 row를 출력하기 때문에 충분한 범위를 표현하기 위해서는 범위를 모두 표현할 수 있을 정도의 데이터가 numbering 테이블에 있어야 한다.

CREATE TABLE `numbering` (
  `numbering` INT(11) NOT NULL,
  PRIMARY KEY (`numbering`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO numbering
SELECT numbering
FROM (
SELECT (t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) as numbering 
FROM
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) v
ORDER BY numbering

총 100,000개의 데이터를 삽입합니다.

완성 코드

SELECT `date`
FROM (
    SELECT DATE_FORMAT(ADDDATE('2021-01-01', INTERVAL numbering DAY), '%Y-%m-%d') `date`
    FROM (SELECT numbering FROM numbering ORDER BY numbering LIMIT 31) numbering
) v
ORDER BY `date`

2021-01-01부터 2021-01-31까지 출력하는 예제코드이다.
INTERVAL로 날짜를 더하고, numbering테이블과 LIMIT으로 특정 범위 내의 날짜만 추출한다.
외부에서 가져올 데이터는 3개이다.

시작일, 시작일과 종료일의 차이, 년,월,주,일 구분

SELECT `date`
FROM (
    SELECT DATE_FORMAT(ADDDATE('[시작일]', INTERVAL numbering [년월일 구분]), '[년월일 구분]') `date`
    FROM (SELECT numbering FROM numbering ORDER BY numbering LIMIT [시작일과 종료일의 차이]) numbering
) v
ORDER BY `date`

---

활용하기

활용한 언어 조합은 PHP와 MariaDB

베이스 쿼리

베이스가 될 쿼리이며, 아래 쿼리에서 일,주,월,년 별로 SELECT나 GROUP BY를 수정하여 사용한다.

SELECT `date` AS stat_date
FROM (
    SELECT DATE_FORMAT(ADDDATE('${fr_date}', INTERVAL numbering day), '%Y-%m-%d') `date`
    FROM (SELECT numbering FROM numbering ORDER BY numbering LIMIT ${interval_limit}) numbering
) a

일별

PHP

PHP는 Query를 수행하기 전 시작일, 종료일, 시작일과 종료일의 차이를 계산하는 용도

$fr_date = "2022-12-01" #시작일
$to_date = "2022-12-12" #종료일

$interval_fr_date  = new DateTime($fr_date);
$interval_to_date = new DateTime($to_date);
$interval = $interval_fr_date->diff($interval_to_date);
$interval_limit = $interval->days; #fr_date ~ to_date 날짜 차이

MySQL/MariaDB

베이스 쿼리를 통계를 구하려는 table에 RIGHT JOIN을 해서 활용하면 된다.
단, 나는 날짜만 보여주지 않고 요일도 보여주고 싶어서 SELECT 를 약간 수정했다.

SELECT concat(stat_date,
            case weekday(stat_date)
                when '0' then ' (월)' when '1' then ' (화)' when '2' then ' (수)'
                when '3' then ' (목)' when '4' then ' (금)' when '5' then ' (토)'
                when '6' then ' (일)' 
            end
        ) AS stat_date
FROM (
    SELECT B.stat_date as stat_date, ...
    FROM `통계테이블` A
    RIGHT JOIN `베이스 쿼리` B ON (A.DATE = B.stat_date)
) STAT
GROUP BY stat_date
ORDER BY stat_date

주별

PHP

주차의 시작은 월요일이 기준이다.
기본적인 주차의 시작은 일요일이 시작이므로 getMondayOfWeek를 사용해 월요일 날짜를 추출할 필요가 없다.
주차의 시작을 월요일로 바꾸기 위해서 아래 MySQL/MariaDB 쿼리에서 GROUP BY할 때 시작값을 함께 변경해야 한다.

// 날짜의 월요일 추출
function getMondayOfWeek($target_date) {
  $minus_timestamp=0;
    $taget_time = strtotime($target_date); 
    $target_week = date("w", $taget_time);

    if ($target_week == 0) $target_week = 7;
    if ($target_week != 1) { 
        for ($i=$target_week;$i>1;$i--) $minus_timestamp = $minus_timestamp + 86400;
    }
    $taget_time = $taget_time-$minus_timestamp;
    $target_date = date("Y-m-d", $taget_time);

  return $target_date;
}

$fr_date = "2022-01-01" #시작일
$to_date = "2022-12-12" #종료일

$fr_date = getMondayOfWeek($fr_date);
$to_date_monday = getMondayOfWeek($to_date);
$to_date = date("Y-m-d", strtotime($to_date_monday." +6 day"));

$interval_fr_date  = new DateTime($fr_date);
$interval_to_date = new DateTime($to_date);
$interval = $interval_fr_date->diff($interval_to_date);
$interval_limit = $interval->days; #fr_date ~ to_date 날짜 차이

MySQL/MariaDB

SELECT도 2022-12-05(월) ~ 2022-12-11(일) 이렇게 보여주고 싶어서 SELECT부분도 수정했다.

주의할 점은 주차의 기준에 따라 WEEK 내의 파라미터 값을 변동해야 한다.

• 일~토 : WEEK(DATE, 0) (기본값)
• 월~일 : WEEK(DATE, 1)

SELECT concat(DATE_FORMAT(DATE_SUB(stat_date, INTERVAL (DAYOFWEEK(stat_date)-2) DAY), '%Y-%m-%d'), '(월) ~ ', DATE_FORMAT(DATE_SUB(stat_date, INTERVAL (DAYOFWEEK(stat_date)-8) DAY), '%Y-%m-%d'), '(일)') AS stat_date
FROM (
    SELECT B.stat_date as stat_date, ...
    FROM `통계테이블` A
    RIGHT JOIN `베이스 쿼리` B ON (A.DATE = B.stat_date)
) STAT
GROUP BY concat(YEAR(stat_date), WEEK(stat_date, 1))
ORDER BY stat_date

월별

PHP

일별과 같다.

MySQL/MariaDB

SELECT DATE_FORMAT(stat_date, '%Y-%m') AS stat_date
FROM (
    SELECT B.stat_date as stat_date, ...
    FROM `통계테이블` A
    RIGHT JOIN `베이스 쿼리` B ON (A.DATE = B.stat_date)
) STAT
GROUP BY DATE_FORMAT(stat_date, '%Y-%m')
ORDER BY stat_date

년별

PHP

일별과 같다.

MySQL/MariaDB

SELECT YEAR(stat_date) AS stat_date
FROM (
    SELECT B.stat_date as stat_date, ...
    FROM `통계테이블` A
    RIGHT JOIN `베이스 쿼리` B ON (A.DATE = B.stat_date)
) STAT
GROUP BY YEAR(stat_date)
ORDER BY stat_date

---

참고자료

🔥sql - How to get list of dates between two dates in mysql select query - Stack Overflow