* 기간에 대해 수정 필요시
시작부분 : 20250201 수정
끝부분 : 20251231 수정

SELECT DATE_FORMAT(`TIMESTAMP`, '%Y-%m-%d') AS "day", COUNT(*) AS "visit_count"
FROM `COUNT_ONCE`
WHERE `TIMESTAMP` >= 20251201 AND `TIMESTAMP` <= 20251231
GROUP BY `day`
UNION ALL
SELECT 'TOTAL' AS "day", COUNT(*) AS "visit_count"
FROM `COUNT_ONCE`
WHERE `TIMESTAMP` >= 20251201 AND `TIMESTAMP` <= 20251231
;



-------------------------------------

SELECT
DATE_FORMAT(`TIMESTAMP`, '%Y-%m') AS `month`,
COUNT(*) AS `visit_count`
FROM
`COUNT_ONCE`
WHERE `TIMESTAMP` >= 20250101 AND `TIMESTAMP` <= 20251231
GROUP BY
`month`
UNION ALL
SELECT
'TOTAL' AS `month`,
COUNT(*) AS `visit_count`
FROM `COUNT_ONCE` WHERE `TIMESTAMP` >= 20250101 AND `TIMESTAMP` <= 20251231;


-------------------------------------

SET @start_date = '2025-01-01';
SET @end_date = '2025-12-31';

SELECT
m.NAME AS 메뉴명,
c.path AS 경로,
COUNT(*) AS 접속수
FROM (
SELECT
IP,
PATH,
DATE_FORMAT(STR_TO_DATE(TIMESTAMP, '%Y%m%d%H%i%s'), '%Y-%m') AS 월,
DATE(STR_TO_DATE(TIMESTAMP, '%Y%m%d%H%i%s')) AS 날짜
FROM
COUNT
WHERE
STR_TO_DATE(TIMESTAMP, '%Y%m%d%H%i%s') BETWEEN @start_date AND @end_date
AND PATH LIKE '%/kr/%'
GROUP BY
IP, PATH, 날짜
) AS c
JOIN
MENU m ON c.PATH = m.PATH
GROUP BY
m.NAME
ORDER BY
m.NAME;