[MYSQL] 쿼리문을 이용한 매시간대별 방문 통계 구하기

웹사이트나 앱의 운영에서 사용자의 방문 시간대별 통계를 파악하는 것은 중요합니다. 이를 통해 사용자의 활동 패턴을 이해하고, 서비스의 효율적인 운영이 가능합니다. MYSQL에서는 쿼리문을 활용하여 간단하게 이러한 통계를 구할 수 있습니다.

 

0~23까지의 시간 배열 생성하기

우선적으로 0~23시까지의 시간대를 나타내는 배열을 생성합니다. DUAL을 사용하여 순차적으로 증가하는 값을 만들어냅니다.

SELECT @N := @N +1 AS n
from visitorTable, (select @N:=-1 from DUAL ) NN
LIMIT 24

 

시간대별 방문 통계 구하기

앞의 글에서 사용했던 시간대별 방문 통계를 구합니다.

시간대별방문통계방법보기 ➔

SELECT HOUR(vdate) AS hh, COUNT(vdate) AS cnt 
FROM visitorTable
WHERE vdate BETWEEN '2021-01-01 00:00:00' AND DATE_FORMAT(now(), '%Y-%m-%d 23:59:59')
GROUP BY hh
ORDER BY hh ASC

 

두 쿼리문 합치기

위에서 생성한 시간대 배열과 방문 통계를 JOIN하여 최종 결과를 얻습니다.

SELECT *
FROM
(SELECT @N := @N +1 AS n
 from visitorTable, (select @N:=-1 from DUAL ) NN
 LIMIT 24) AS T
left JOIN 
(SELECT HOUR(vdate) AS hh, COUNT(vdate) AS cnt 
 FROM visitorTable 
 WHERE vdate BETWEEN DATE_FORMAT(now(), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(now(), '%Y-%m-%d 23:59:59')
 GROUP BY hh) AS dayT
 ON T.n = dayT.hh

그러면 결과는 다음 표와 같이 나옵니다.

n	hh	cnt
0	(NULL)	(NULL)
1	(NULL)	(NULL)
2	(NULL)	(NULL)
3	(NULL)	(NULL)
4	4	56
5	(NULL)	(NULL)
6	(NULL)	(NULL)
7	(NULL)	(NULL)
8	(NULL)	(NULL)
9	(NULL)	(NULL)
10	(NULL)	(NULL)
11	(NULL)	(NULL)
12	(NULL)	(NULL)
13	(NULL)	(NULL)
14	(NULL)	(NULL)
15	15	76
16	16	34
17	17	123
18	18	54
19	19	34
20	20	21
21	(NULL)	(NULL)
22	(NULL)	(NULL)
23	(NULL)	(NULL)

 

NULL 값 처리하기

결과에서 방문자가 없는 시간대는 NULL로 표시됩니다. 이를 IFNULL 함수를 사용하여 0으로 바꿀 수 있습니다.

IFNULL함수사용방법보기 ➔

 

결론

MYSQL 쿼리문을 활용하면 원하는 시간대별 방문 통계를 간단하게 구할 수 있습니다. 특히 여러 테이블 혹은 배열과의 JOIN을 통해 보다 구체적이고 효율적인 분석 데이터를 얻을 수 있습니다. 이를 통해 서비스 운영에 있어 중요한 인사이트를 얻을 수 있습니다.