在MySQL中分别按天、周、月、季度和年统计所有 API 的访问量,并找出一年中访问量最高的月份、天和小时。
1. 按天统计所有 API 的访问量
SELECT
api_name,
DATE(access_time) AS day,
COUNT(*) AS visit_count
FROM
api_logs
GROUP BY
api_name,
DATE(access_time)
ORDER BY
api_name,
day;
2. 按周统计所有 API 的访问量
SELECT
api_name,
YEARWEEK(access_time, 1) AS week,
-- 使用 ISO 周,周一作为一周的开始
COUNT(*) AS visit_count
FROM
api_logs
GROUP BY
api_name,
YEARWEEK(access_time, 1)
ORDER BY
api_name,
week;
3. 按月统计所有 API 的访问量
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m') AS month,
COUNT(*) AS visit_count
FROM
api_logs
GROUP BY
api_name,
DATE_FORMAT(access_time, '%Y-%m')
ORDER BY
api_name,
month;
4. 按季度统计所有 API 的访问量
SELECT
api_name,
CONCAT(YEAR(access_time), ' Q', QUARTER(access_time)) AS quarter,
COUNT(*) AS visit_count
FROM
api_logs
GROUP BY
api_name,
YEAR(access_time),
QUARTER(access_time)
ORDER BY
api_name,
quarter;
5. 按年统计所有 API 的访问量
SELECT
api_name,
YEAR(access_time) AS year,
COUNT(*) AS visit_count
FROM
api_logs
GROUP BY
api_name,
YEAR(access_time)
ORDER BY
api_name,
year;
6. 统计一年中访问量最高的月份
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m') AS month,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
YEAR(access_time) = 2024
GROUP BY
api_name,
DATE_FORMAT(access_time, '%Y-%m')
ORDER BY
visit_count DESC
LIMIT 1;
7. 统计一年中访问量最高的一天
SELECT
api_name,
DATE(access_time) AS day,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
YEAR(access_time) = 2024
GROUP BY
api_name,
DATE(access_time)
ORDER BY
visit_count DESC
LIMIT 1;
8. 统计一年中访问量最高的一个小时
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m-%d %H:00:00') AS hour,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
YEAR(access_time) = 2024
GROUP BY
api_name,
DATE_FORMAT(access_time, '%Y-%m-%d %H:00:00')
ORDER BY
visit_count DESC
LIMIT 1;
DATE()
:提取日期部分,用于按天统计。YEARWEEK()
:按 ISO 标准将日期转化为年份和周数,用于按周统计。DATE_FORMAT()
:格式化时间戳,用于按月、小时等粒度进行分组。QUARTER()
:提取季度信息,用于按季度统计。LIMIT 1
:用于获取排序后结果的第一行(即访问量最高的时间段)。
如需要统计指定的时间范围,只需加入where条件限制
- 按天统计
SELECT
api_name,
DATE(access_time) AS day,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
DATE(access_time) = '2024-08-17' -- 指定的日期
GROUP BY
api_name,
day
ORDER BY
api_name,
day;
- 按周统计
SELECT
api_name,
YEARWEEK(access_time, 1) AS week,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
YEARWEEK(access_time, 1) = YEARWEEK('2024-08-17', 1) -- 指定的周
GROUP BY
api_name,
week
ORDER BY
api_name,
week;
- 按月统计
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m') AS month,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
DATE_FORMAT(access_time, '%Y-%m') = '2024-08' -- 指定的月份
GROUP BY
api_name,
month
ORDER BY
api_name,
month;
- 访问量最高的月份
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m') AS month,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
access_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59' -- 指定的时间范围
GROUP BY
api_name,
month
ORDER BY
visit_count DESC
LIMIT 1;
- 访问量最高的一天
SELECT
api_name,
DATE(access_time) AS day,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
access_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59' -- 指定的时间范围
GROUP BY
api_name,
day
ORDER BY
visit_count DESC
LIMIT 1;
- 访问量最高的一个小时
SELECT
api_name,
DATE_FORMAT(access_time, '%Y-%m-%d %H:00:00') AS hour,
COUNT(*) AS visit_count
FROM
api_logs
WHERE
access_time BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59' -- 指定的时间范围
GROUP BY
api_name,
hour
ORDER BY
visit_count DESC
LIMIT 1;