MySQL에서는 DATE() 함수와 GROUP BY 절을 함께 사용하면 datetime 컬럼의 값을 날짜 단위로 그룹화하고, 각 날짜에 해당하는 레코드(중복 값)의 개수를 손쉽게 집계할 수 있습니다. 이번 글에서는 실제 예제를 통해 그 과정을 단계별로 살펴보겠습니다.
1. 테이블 생성
먼저 승객 코드와 도착 일시를 저장할 테이블을 생성합니다.
mysql> create table DemoTable1496 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PassengerCode varchar(20), -> ArrivalDate datetime -> ); Query OK, 0 rows affected (0.85 sec)
2. 샘플 데이터 삽입
INSERT 명령을 사용해 테이블에 여러 레코드를 추가합니다. 의도적으로 같은 날짜와 같은 승객 코드가 반복되도록 데이터를 구성했습니다.
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-12 10:12:34');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202_John','2013-03-12 11:00:00');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('204','2013-03-12 10:12:34');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('208','2013-03-14 11:10:00');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-18 12:00:34');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable1496(PassengerCode,ArrivalDate) values('202','2013-03-18 04:10:01');
Query OK, 1 row affected (0.15 sec)
3. 전체 데이터 확인
SELECT 문으로 테이블에 저장된 모든 레코드를 조회해 보겠습니다.
mysql> select * from DemoTable1496;
실행 결과는 다음과 같습니다.
+----+---------------+---------------------+ | Id | PassengerCode | ArrivalDate | +----+---------------+---------------------+ | 1 | 202 | 2013-03-12 10:12:34 | | 2 | 202_John | 2013-03-12 11:00:00 | | 3 | 204 | 2013-03-12 10:12:34 | | 4 | 208 | 2013-03-14 11:10:00 | | 5 | 202 | 2013-03-18 12:00:34 | | 6 | 202 | 2013-03-18 04:10:01 | +----+---------------+---------------------+ 6 rows in set (0.00 sec)
4. 날짜별 그룹화 및 중복 값 개수 집계 쿼리
핵심은 DATE() 함수입니다. datetime 값에서 시간 정보를 제거한 날짜 부분만 추출한 뒤, 이를 기준으로 GROUP BY를 수행하고 COUNT()로 각 날짜의 레코드 수를 세면 됩니다. 여기서는 '202'라는 코드가 포함된 승객만 대상으로 조회하기 위해 LIKE와 CONCAT()을 함께 사용했습니다.
mysql> select date(ArrivalDate),count(ArrivalDate) from DemoTable1496
-> where PassengerCode like concat('%','202','%')
-> group by date(ArrivalDate);
위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.
+-------------------+--------------------+ | date(ArrivalDate) | count(ArrivalDate) | +-------------------+--------------------+ | 2013-03-12 | 2 | | 2013-03-18 | 2 | +-------------------+--------------------+ 2 rows in set (0.00 sec)
결과 해석
출력 결과를 보면 '202' 관련 레코드는 2013년 3월 12일에 2건, 2013년 3월 18일에 2건 존재함을 알 수 있습니다. 이처럼 DATE() 함수로 날짜 단위 그룹화를 하면 시간과 무관하게 하루 단위의 중복 발생 건수를 정확히 파악할 수 있습니다.
참고로 대량의 데이터를 다룰 때는 WHERE 절에서 컬럼에 함수를 적용하면 인덱스 활용이 어려워질 수 있으므로, 성능이 중요한 환경이라면 날짜 범위 조건(>=, <)을 함께 사용하는 것이 좋습니다.