工作中经常遇到按天、周、月、季度和年统计所有 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条件限制
- 按天统计
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;
- 按周统计
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;
- 按月统计
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;