时间戳

首页 > 解决方案 > 查找按时间间隔分组的响应时间

mysql - 查找按时间间隔分组的响应时间

问题描述

我有一张桌子，上面有某些日志及其响应时间。我可以通过执行以下操作查询表以获取最近 1000 条记录的平均响应时间：

SELECT timestamp, count(*), avg(response_time) FROM table ORDER BY timestamp DESC LIMIT 1000

-- timestamp            count(*)      avg(response_time)
-- 2020-03-17 11:58:37  1000          0.27

但是，我想在过去每千条记录中得到 N 条记录（以查看响应时间随时间的进展，以 1000 个请求为单位），即：

SELECT timestamp, count(*), avg(response_time) FROM table ORDER BY timestamp DESC LIMIT 1000
  UNION
SELECT timestamp, count(*), avg(response_time) FROM table ORDER BY timestamp DESC LIMIT 1000, 1000
  UNION ...

-- timestamp            count(*)      avg(response_time)
-- 2020-03-17 11:58:37  1000          0.27
-- 2020-03-17 11:38:09  1000          0.52
-- 2020-03-17 11:04:11  1000          1.04
-- and keep going, in groups of 1000 records...

是否有一种更清洁的方法可以做到这一点，我可以按 1000 个块对事物进行分组？

标签： mysqlsqldatedatetimegroup-by

解决方案

你似乎想要：

SELECT count(*) no_records, avg(response_time) avg_response_time
FROM table 
WHERE timestamp >= now() - interval 1 day

如果您希望按小时计算：

SELECT 
    date_format(timestamp, '%Y-%m-%d %h:00:00') hr, 
    count(*) no_records, 
    avg(response_time) avg_response_time
FROM table 
WHERE timestamp >= now() - interval 1 day
GROUP BY date_format(timestamp, '%Y-%m-%d %h:00:00)
ORDER BY hr

或者，如果您想按 1000 条记录的块分组，那么（假设 MySQL 8.0），您可以使用row_number()：

SELECT 
    min(timestamp) first_timestamp,
    last(timestamp) first_timestamp,
    count(*) no_records, 
    avg(response_time) avg_response_time
FROM table 
WHERE timestamp >= now() - interval 1 day
GROUP BY floor((row_number() over(order by timestamp) - 1) / 1000)
ORDER BY first_timestamp

推荐阅读