[MYSQL] 시간대별 방문자 통계 구하기

웹사이트나 앱에서 사용자의 방문 패턴을 파악하는 것은 중요합니다. 특히 어느 시간대에 사용자들이 가장 활발히 활동하는지, 혹은 방문하지 않는지를 알아내는 것은 서비스 운영에 있어 큰 도움이 됩니다. MYSQL을 사용하여 시간대별 방문자 통계를 쉽게 구할 수 있습니다.

 

간단한 시간대별 방문자 통계

• 방문시간을 기록한 `vdate` 컬럼을 기준으로 시간대별 방문자 수를 집계합니다.
• `vdate` 컬럼에는 `2021-01-04 00:04:45` 와 같은 형식으로 데이터가 저장되어 있습니다.

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

• 이 쿼리의 결과로는 방문 기록이 있는 시간대만 반환됩니다.
• 가령 방문시간이 0시에 5, 4시에 2, 17시에 6 이라면 아래 표와 같은 결과가 나옵니다.

hh	cnt
0	5
4	2
17	6

 

반복문을 활용한 전체 시간대별 통계

• 0~23까지의 모든 시간대에 대한 방문 통계를 얻기 위해 반복문을 활용할 수 있습니다.
• 하지만, MYSQL에서는 반복문을 사용하기 복잡하므로 다른 방법을 고려하는 것이 좋습니다.

숫자연속증가시키는방법 ➔

 

JOIN을 활용한 전체 시간대별 통계

• 우리가 필요한건 0~23 까지의 테이블이기 때문에, 반복문 돌면서 해당시간대에 방문카운트를 넣어주면 원하는 결과를 얻을 수 있습니다.

SELECT T.hh, IFNULL(dayT.cnt, 0) AS cnt
FROM (SELECT 0 AS hh UNION SELECT 1 UNION SELECT 2 ... UNION SELECT 23) AS T
LEFT JOIN (
  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
) AS dayT ON T.hh = dayT.hh
ORDER BY T.hh

• 0~23까지의 시간대를 생성하고 기존의 통계 결과와 JOIN하여 전체 시간대별 통계를 얻을 수 있습니다.
• 이 방법을 사용하면 0~23까지의 모든 시간대에 대한 방문 통계를 얻을 수 있습니다.

hh	cnt
0	5
1	0
2	0
3	0
4	2
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	6
18	0
19	0
20	0
21	0
22	0
23	0

 

쿼리문으로 매시간대별 방문통계 구하는 방법 ➔

 

결론

MYSQL을 활용하면 시간대별 방문자 통계를 쉽게 얻을 수 있습니다. JOIN을 활용하면 모든 시간대에 대한 통계를 한 번의 쿼리로 구할 수 있어 효율적입니다. 이를 통해 서비스의 방문 패턴을 파악하고, 더 나은 서비스 제공을 위한 기반을 마련할 수 있습니다.