MySQL按天、周、月、季度和年统计所有 API 的访问量

最新推荐文章于 2025-01-09 10:00:00 发布

原创最新推荐文章于 2025-01-09 10:00:00 发布 · 329 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #经验分享 #数据库

在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;