在mysql中存储数据样本 [英] storing samples of data in mysql

问题描述

我想存储从mysql中大约

500个设备收集的数据样本。每个设备都有自己的数据，这些数据会在
时间内发生变化。数据可能是整数或浮点数。


我对以下内容感兴趣的查询：显示来自设备X的

数据大于70且来自设备Y的数据是

大于90。


这样做的简单方法是使用一个大表每个

列代表一个设备加上一个datetime列。在这个表格中，我可以每隔一秒钟收集每个设备的样本并存储在表格中。

这样我就可以轻松查询我需要的数据。


但是，由于每个设备中的数据变化率很低

（平均每20分钟约1个），我想存储每个设备
在它自己的一个表中，只在数据发生变化时添加一条新记录。

那样我将有500个表，但每个表都很小。问题是

，我无法弄清楚如何进行我需要的查询。

做类似的事情：

select x ，y from table_x，table_y where（x> 70& y>>&

table_x.time = table_y.time）;


我不会工作，因为我不会在每张桌子上插入每一秒的记录。


我可能有两张桌子，例如：


XY

时间数据时间数据

03:00:00 69 03:30:00 92

04:00:00 70 04:30:00 93

05:00:00 71 05:30:00 94


有没有办法告诉mysql这些是样品及时，

所以它实际上会扩展查询表，

喜欢：


X

时间数据

03:00:00 69

03:00:01 69

03:00:02 69

..

..

..

03:59:59 69

04:00:00 70

请指教，


谢谢，


Ehud。

Hi,

I would like to store samples of data which are gathered from about
500 devices in mysql. Each device has its own data which changes over
time. The data may be integer or float.

The queries I''m interested in our something like: "show me when the
data from device X was greater than 70 and the data from device Y was
greater than 90".

The trivial way of doing so is to use one large table in which each
column represents a device plus a datetime column. In this table I can
gather each second a sample from each device and store in the table.
This way I can easily query the data I need.

However, as the rate of change of the data in each device is low
(about 1 every 20 minutes average), I would like to store each device
in a table of its own and only add a new record when the data changes.
That way I will have 500 tables but each will be small. The problem is
that I can''t figure out how can I do the queries I need.
Doing something like:
select x,y from table_x,table_y where (x>70 && y>90 &&
table_x.time=table_y.time);

Won''t work as I don''t insert every second a record into every table.

I may have two tables such as:

X Y
time data time data
03:00:00 69 03:30:00 92
04:00:00 70 04:30:00 93
05:00:00 71 05:30:00 94

Is there a way to tell mysql that these are points of samples in time,
and so it will actually expand the tables for the queries, something
like:

X
time data
03:00:00 69
03:00:01 69
03:00:02 69
..
..
..
03:59:59 69
04:00:00 70
Please advise,

Thanks,

Ehud.

推荐答案

Ehud Shabtai写道：

Ehud Shabtai wrote:

请提供建议，

使用一张桌子即可肯定要使用索引。


当你做查询时，不要比较两个不同设备的时间，

只需使用时间限制来获取所有值来自想要的时间段。


然后在你的程序中使用这些数据并填写缺少的部分。


这是正确的方法。这样做恕我直言。因为

1）表结构很简单

2）查询简单快速

3）查询返回的数据很小（这通常是好的。

特别是如果你使用的是网络数据库。）


这可能会有一些问题。您可能需要能够在时间限制之前检索
（额外查询类似于''select

* from table from time time< timelimit limit 1''） ，除非你想获得

设备的所有数据。

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,
just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because
1) Table structure is simple
2) Queries are simple and fast
3) Data which queries return is small ( This is usually a good thing.
Especially if you are using a network database. )

There might be some problems with this. You might need to be able to
retrieve the value before time limit(extra query something like ''select
* from table where time < timelimit limit 1''), unless you want to get
all data for devices.

Ehud Shabtai写道：

Ehud Shabtai wrote:

请指教，

使用一个表并确保使用索引。


当你查询时不要比较两个不同设备的时间，

只需使用时间限制来获取所需时间段内的所有值。


然后在您的程序中使用此数据并填写缺少的部分。


这是正确的方式。这样做恕我直言。因为

1）表结构很简单

2）查询简单快速

3）查询返回的数据很小（这通常是好的。

特别是如果你使用的是网络数据库。）


这可能会有一些问题。您可能需要能够在时间限制之前检索
（额外查询类似于''select

* from table from time time< timelimit limit 1''） ，除非你想获得

设备的所有数据。

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,
just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because
1) Table structure is simple
2) Queries are simple and fast
3) Data which queries return is small ( This is usually a good thing.
Especially if you are using a network database. )

There might be some problems with this. You might need to be able to
retrieve the value before time limit(extra query something like ''select
* from table where time < timelimit limit 1''), unless you want to get
all data for devices.

Ehud Shabtai写道：

Ehud Shabtai wrote:

请指教，

使用一个表并确保使用索引。


当你查询时不要比较两个不同设备的时间，

只需使用时间限制来获取所需时间段内的所有值。


然后在您的程序中使用此数据并填写缺少的部分。


这是正确的方式。这样做恕我直言。因为

1）表结构很简单

2）查询简单快速

3）查询返回的数据很小（这通常是好的。

特别是如果你使用的是网络数据库。）


这可能会有一些问题。您可能需要能够在时间限制之前检索
（额外查询类似于''select

* from table from time time< timelimit limit 1''） ，除非你想获得

设备的所有数据。

Use one table and be sure to use indexes.

And when you do queries don''t compare time of two different devices,
just use a time limit to get all values from wanted timeperiod.

Then in your program use this data and fill in the missing parts.

This is the "correct way" to do this IMHO. Because
1) Table structure is simple
2) Queries are simple and fast
3) Data which queries return is small ( This is usually a good thing.
Especially if you are using a network database. )

There might be some problems with this. You might need to be able to
retrieve the value before time limit(extra query something like ''select
* from table where time < timelimit limit 1''), unless you want to get
all data for devices.

这篇关于在mysql中存储数据样本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！