MySQL：动态创建存储桶

发布于 2024-11-05 12:38:31 字数 584 浏览 1 评论 0原文

我有一个 mysql 表，每五分钟存储一次网络利用率，我现在想使用这些数据进行绘图。有没有一种方法可以让我只指定开始时间和结束时间以及我需要的桶/样本的数量，并且MySQL可以以某种方式强制：？

我的表

+---------------------+-----+
| Tstamp              | QID |
+---------------------+-----+
| 2010-12-10 15:05:39 |  20 |
| 2010-12-10 15:06:09 |  26 |
| 2010-12-10 15:06:14 |  27 |
| 2010-12-10 15:06:18 |  28 |
| 2010-12-10 15:06:23 |  40 |
| 2010-12-10 15:10:38 |  20 |
| 2010-12-10 15:11:12 |  26 |
| 2010-12-10 15:11:17 |  27 |
| 2010-12-10 15:11:21 |  28 | 
------ SNIP ------

那么我可以指定我需要过去 24 小时内的 20 个样本吗？

谢谢！

残酷的

原文

I have a mysql table that stores network utilization for every five minutes, I want to now use this data for graphing. Is there a way where I could just specify the start time and the end time and the number of buckets / samples I need, and MySQL could in someway oblige :?

My table

+---------------------+-----+
| Tstamp              | QID |
+---------------------+-----+
| 2010-12-10 15:05:39 |  20 |
| 2010-12-10 15:06:09 |  26 |
| 2010-12-10 15:06:14 |  27 |
| 2010-12-10 15:06:18 |  28 |
| 2010-12-10 15:06:23 |  40 |
| 2010-12-10 15:10:38 |  20 |
| 2010-12-10 15:11:12 |  26 |
| 2010-12-10 15:11:17 |  27 |
| 2010-12-10 15:11:21 |  28 | 
------ SNIP ------

So can I specify I need 20 samples from the last 24 hours.

Thanks!

Harsh

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风情万种。 2024-11-12 12:38:31

您可以将 DATETIME 转换为 UNIX_TIMESTAMP，并使用除法和模数...

回复收藏 0 原文

∞觅青森が 2024-11-12 12:38:31

这是您可以使用的示例查询。请注意，如果给定时间范围内请求的样本数量超过该范围内可用记录的一半（这意味着存储桶大小为 1），则该方法不起作用。

-- Configuration
SET @samples = 4;
SET @start = '2011-05-06 19:44:00';
SET @end =   '2011-05-06 20:46:50';
--

SET @bucket = (SELECT FLOOR(count(*)/@samples) as bucket_size FROM table1
WHERE Tstamp BETWEEN @start AND @end);

SELECT
      SUM(t.QID), FLOOR((t.ID-1)/@bucket) as bucket
FROM (SELECT QID , @r:=@r+1 as ID
     FROM table1
     JOIN (SELECT @r:=0) r
     WHERE Tstamp BETWEEN @start AND @end
     ORDER BY Tstamp) as t
GROUP BY bucket
HAVING count(t.QID) = @bucket
ORDER BY bucket;

PS 我相信有一种更优雅的方法来做到这一点，但由于没有人提供有效的查询，我希望这会有所帮助。

Here is a sample query you can use. Notice it does not work if the number of requested samples in the given time range is more than half of the available records for that range (which would mean the bucket size is one).

-- Configuration
SET @samples = 4;
SET @start = '2011-05-06 19:44:00';
SET @end =   '2011-05-06 20:46:50';
--

SET @bucket = (SELECT FLOOR(count(*)/@samples) as bucket_size FROM table1
WHERE Tstamp BETWEEN @start AND @end);

SELECT
      SUM(t.QID), FLOOR((t.ID-1)/@bucket) as bucket
FROM (SELECT QID , @r:=@r+1 as ID
     FROM table1
     JOIN (SELECT @r:=0) r
     WHERE Tstamp BETWEEN @start AND @end
     ORDER BY Tstamp) as t
GROUP BY bucket
HAVING count(t.QID) = @bucket
ORDER BY bucket;

P.S. I believe there is a more elegant way to do this, but since no one has provided a working query I hope this helps.

回复收藏 0 原文

~没有更多了~