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

工作中经常遇到按天、周、月、季度和年统计所有 API 的访问量，并能够找出一年中访问量最高的月份、天和小时。在 Oracle 数据库中，可以使用 GROUP BY 和日期函数来按天、周、月、季度和年分别统计所有 API 的访问量，并显示每个 API 及其对应的访问次数。

1. 按天统计所有 API 的访问量

SELECT 
    api_name, 
    TRUNC(access_time) AS day, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
GROUP BY 
    api_name, 
    TRUNC(access_time)
ORDER BY 
    api_name, 
    day;

2. 按周统计所有 API 的访问量

SELECT 
    api_name, 
    TRUNC(access_time, 'IW') AS week_start, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
GROUP BY 
    api_name, 
    TRUNC(access_time, 'IW')
ORDER BY 
    api_name, 
    week_start;

3. 按月统计所有 API 的访问量

SELECT 
    api_name, 
    TRUNC(access_time, 'MM') AS month, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
GROUP BY 
    api_name, 
    TRUNC(access_time, 'MM')
ORDER BY 
    api_name, 
    month;

4. 按季度统计所有 API 的访问量

SELECT 
    api_name, 
    TRUNC(access_time, 'Q') AS quarter, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
GROUP BY 
    api_name, 
    TRUNC(access_time, 'Q')
ORDER BY 
    api_name, 
    quarter;

5. 按年统计所有 API 的访问量

SELECT 
    api_name, 
    TRUNC(access_time, 'YYYY') AS year, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
GROUP BY 
    api_name, 
    TRUNC(access_time, 'YYYY')
ORDER BY 
    api_name, 
    year;

6. 统计一年中访问量最高的月份

SELECT 
    api_name, 
    TRUNC(access_time, 'MM') AS month, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    EXTRACT(YEAR FROM access_time) = 2024 
GROUP BY 
    api_name, 
    TRUNC(access_time, 'MM')
ORDER BY 
    visit_count DESC 
FETCH FIRST 1 ROWS ONLY;

7. 统计一年中访问量最高的一天

SELECT 
    api_name, 
    TRUNC(access_time) AS day, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    EXTRACT(YEAR FROM access_time) = 2024  
GROUP BY 
    api_name, 
    TRUNC(access_time)
ORDER BY 
    visit_count DESC 
FETCH FIRST 1 ROWS ONLY;

8. 统计一年中访问量最高的一个小时

SELECT 
    api_name, 
    TO_CHAR(access_time, 'YYYY-MM-DD HH24') AS hour, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    EXTRACT(YEAR FROM access_time) = 2024  
GROUP BY 
    api_name, 
    TO_CHAR(access_time, 'YYYY-MM-DD HH24')
ORDER BY 
    visit_count DESC 
FETCH FIRST 1 ROWS ONLY;

• TRUNC()：用于将时间戳截断到指定的粒度（天、周、月、季度、年）。
• GROUP BY：按 API 名称和指定的日期粒度进行分组。
• EXTRACT(YEAR FROM access_time)：从时间戳中提取年份，用于限定统计范围。
• FETCH FIRST 1 ROWS ONLY：用于获取排序后结果的第一行（即访问量最高的时间段）。

如需要统计指定的时间范围，只需加入where条件限制

1. 按天统计

SELECT 
    api_name, 
    TRUNC(access_time) AS day, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    access_time BETWEEN TO_TIMESTAMP('2024-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND TO_TIMESTAMP('2024-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')  -- 指定的时间范围
GROUP BY 
    api_name, 
    TRUNC(access_time)
ORDER BY 
    api_name, 
    day;

2. 按周统计

SELECT 
    api_name, 
    TRUNC(access_time, 'IW') AS week_start, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    access_time BETWEEN TO_TIMESTAMP('2024-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND TO_TIMESTAMP('2024-08-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')  -- 指定的时间范围
GROUP BY 
    api_name, 
    TRUNC(access_time, 'IW')
ORDER BY 
    api_name, 
    week_start;

3. 按月统计

SELECT 
    api_name, 
    TRUNC(access_time, 'MM') AS month, 
    COUNT(*) AS visit_count 
FROM 
    api_logs 
WHERE 
    access_time BETWEEN TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')  -- 指定的时间范围
GROUP BY 
    api_name, 
    TRUNC(access_time, 'MM')
ORDER BY 
    api_name, 
    month;