개요
사용자 로그인 기록이 저장된 테이블에서 시간(Hour) 단위로 데이터를 그룹화하고, 각 시간대별로 가장 마지막에 로그인한 사용자의 기록만 가져오고 싶은 경우가 있습니다. 이럴 때는 서브쿼리(Subquery)와 JOIN 조건을 함께 활용하면 간단하게 해결할 수 있습니다.
기본 쿼리 문법
먼저 전체적인 문법 구조는 다음과 같습니다. 내부 서브쿼리에서 시간별 최대 타임스탬프를 구하고, 이를 원본 테이블과 조인하여 해당 레코드를 추출하는 방식입니다.
SELECT yourTablevariableName.*
FROM
(
SELECT MAX(UNIX_TIMESTAMP(yourDateTimeColumnName)) AS anyAliasName
FROM getLatestHour
GROUP BY HOUR(UserLoginDateTime)
) yourOuterVariableName
JOIN yourTableName yourTablevariableName
ON UNIX_TIMESTAMP(yourDateTimeColumnName) = yourOuterVariableName.yourAliasName
WHERE DATE(yourDateTimeColumnName) = 'yourDateValue';
예제 테이블 생성하기
문법과 실행 결과를 이해하기 위해 예제용 테이블을 먼저 만들어 보겠습니다. 아래 쿼리는 사용자 ID, 이름, 로그인 일시를 저장하는 테이블을 생성합니다.
mysql> create table getLatestHour
-> (
-> UserId int,
-> UserName varchar(20),
-> UserLoginDateTime datetime
-> );
Query OK, 0 rows affected (0.68 sec)
테스트 데이터 삽입
이제 INSERT 명령어를 사용해 사용자별 로그인 날짜와 시간이 포함된 레코드를 몇 개 추가합니다.
mysql> insert into getLatestHour values(100,'John','2019-02-04 10:55:51');
Query OK, 1 row affected (0.27 sec)
mysql> insert into getLatestHour values(101,'Larry','2019-02-04 12:30:40');
Query OK, 1 row affected (0.16 sec)
mysql> insert into getLatestHour values(102,'Carol','2019-02-04 12:40:46');
Query OK, 1 row affected (0.20 sec)
mysql> insert into getLatestHour values(103,'David','2019-02-04 12:44:54');
Query OK, 1 row affected (0.17 sec)
mysql> insert into getLatestHour values(104,'Bob','2019-02-04 12:47:59');
Query OK, 1 row affected (0.15 sec)
저장된 전체 레코드 확인
SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from getLatestHour;
실행 결과는 다음과 같습니다.
+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime |
+--------+----------+---------------------+
| 100 | John | 2019-02-04 10:55:51 |
| 101 | Larry | 2019-02-04 12:30:40 |
| 102 | Carol | 2019-02-04 12:40:46 |
| 103 | David | 2019-02-04 12:44:54 |
| 104 | Bob | 2019-02-04 12:47:59 |
+--------+----------+---------------------+
5 rows in set (0.00 sec)
시간별 그룹화 후 최근 한 시간 기록 조회하기
아래가 핵심 쿼리입니다. HOUR() 함수로 로그인 시간을 기준으로 데이터를 그룹화하고, 각 그룹에서 MAX(UNIX_TIMESTAMP()) 값, 즉 가장 늦은 로그인 시각을 가진 레코드만 최종적으로 반환합니다.
mysql> SELECT tbl1.*
-> FROM (
-> SELECT MAX(UNIX_TIMESTAMP(UserLoginDateTime)) AS m1
-> FROM getLatestHour
-> GROUP BY HOUR(UserLoginDateTime)
-> ) var1
-> JOIN getLatestHour tbl1
-> ON UNIX_TIMESTAMP(UserLoginDateTime) = var1.m1
-> WHERE DATE(UserLoginDateTime) = '2019-02-04';
실행 결과
+--------+----------+---------------------+
| UserId | UserName | UserLoginDateTime |
+--------+----------+---------------------+
| 100 | John | 2019-02-04 10:55:51 |
| 104 | Bob | 2019-02-04 12:47:59 |
+--------+----------+---------------------+
2 rows in set (0.05 sec)
결과 분석
출력 결과를 보면 10시 대에는 John(10:55:51)이, 12시 대에는 Bob(12:47:59)이 각각 해당 시간대에서 가장 늦게 로그인한 사용자임을 알 수 있습니다. Larry, Carol, David처럼 같은 시간대(12시)에 로그인했지만 더 이른 시각에 접속한 사용자들은 제외되었습니다.
이처럼 서브쿼리로 시간 단위 최대 타임스탬프를 먼저 계산한 뒤 원본 테이블과 조인하면, 시간대별 최신 로그인 기록만 깔끔하게 추출할 수 있습니다. 접속 로그 분석이나 실시간 모니터링 대시보드를 만들 때 유용하게 활용할 수 있는 패턴입니다.