mysql - 更改数据类型的带时间戳的 SQL 存储

Question

我需要在 SQL 数据库中存储各种带有时间戳的数据参数。请参阅下面的典型非标准化数据，其中我显示了五个模拟值(室温、房间湿度、游泳池 pH 值、AHU 供应温度和房间压力)和一个二进制值(灯光状态)。

不同的“客户”将拥有我在下面用“地址”字段指示的数据。通常会添加或删除要存储的给定参数。而且，常常会添加一个以前没有存档的新变量(即冷冻水供水温度)，并且无法事先知道将来可能需要存储哪些参数。

典型的查询是返回给定时间跨度内 123 Main Street Room 102 的温度。另一个典型的查询是返回 123 Main Street 中所有房间的温度、湿度和光照水平。

为每个所需参数添加列到表中显然没有意义。但我也很难在同一列中存储不同类型的数据。我还很纠结如何在许多参数中复制房间号，并觉得应该将其标准化，但是，其他参数没有关联的房间号，所以我不知道这将如何工作。我还质疑是否应该创建一个 PK 是建筑物和时间戳的组合的表，并加入另一个包含参数和值(可能还有类型)列的表。

应该如何对这些数据建模？

+------------------+-----------------+------------------------+-------+
|     Address      |    Timestamp    |       Parameter        | Value |
+------------------+-----------------+------------------------+-------+
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Temperature   | 70.99 |
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Humidity      | 50%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 101 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Temperature   | 70.90 |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Humidity      | 50%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 102 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Temperature   | 69.95 |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:00 | Room 103 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Temperature   | 69.65 |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Humidity      | 47%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 101 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Temperature   | 69.18 |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Humidity      | 46%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 102 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Temperature   | 68.49 |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Humidity      | 48%   |
| 123 Main Street  | 7/13/2015 16:15 | Room 103 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Temperature   | 68.93 |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 101 Light Status  | On    |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Temperature   | 69.44 |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Humidity      | 49%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 102 Light Status  | Off   |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Temperature   | 69.63 |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Humidity      | 48%   |
| 123 Main Street  | 7/13/2015 16:30 | Room 103 Light Status  | Off   |
| 321 Front Street | 7/14/2015 14:00 | AHU Supply Temperature | 69.96 |
| 321 Front Street | 7/14/2015 14:00 | Swimming Pool PH       | 7.19  |
| 321 Front Street | 7/14/2015 14:00 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 14:15 | AHU Supply Temperature | 69.92 |
| 321 Front Street | 7/14/2015 14:15 | Swimming Pool PH       | 6.97  |
| 321 Front Street | 7/14/2015 14:15 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 14:30 | AHU Supply Temperature | 70.37 |
| 321 Front Street | 7/14/2015 14:30 | Swimming Pool PH       | 6.84  |
| 321 Front Street | 7/14/2015 14:30 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 14:45 | AHU Supply Temperature | 70.80 |
| 321 Front Street | 7/14/2015 14:45 | Swimming Pool PH       | 6.70  |
| 321 Front Street | 7/14/2015 14:45 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 15:00 | AHU Supply Temperature | 71.29 |
| 321 Front Street | 7/14/2015 15:00 | Swimming Pool PH       | 6.90  |
| 321 Front Street | 7/14/2015 15:00 | Room 101 Pressure      | 0.12  |
| 321 Front Street | 7/14/2015 15:15 | AHU Supply Temperature | 72.13 |
| 321 Front Street | 7/14/2015 15:15 | Swimming Pool PH       | 7.13  |
| 321 Front Street | 7/14/2015 15:15 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 15:30 | AHU Supply Temperature | 72.84 |
| 321 Front Street | 7/14/2015 15:30 | Swimming Pool PH       | 7.01  |
| 321 Front Street | 7/14/2015 15:30 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 15:45 | AHU Supply Temperature | 72.82 |
| 321 Front Street | 7/14/2015 15:45 | Swimming Pool PH       | 7.22  |
| 321 Front Street | 7/14/2015 15:45 | Room 101 Pressure      | 0.11  |
| 321 Front Street | 7/14/2015 16:00 | AHU Supply Temperature | 72.23 |
| 321 Front Street | 7/14/2015 16:00 | Swimming Pool PH       | 7.40  |
| 321 Front Street | 7/14/2015 16:00 | Room 101 Pressure      | 0.11  |
+------------------+-----------------+------------------------+-------+

Answer 1

我会选择 3 张 table :

Address
-------
address_id
address_name

Location
--------
location_id
location_name
address_id

Measurement
-----------
meauserment_id
location_id
type
timestamp
value

这里所有的房间、游泳池和用品都被建模为不同的位置，因此类型只是“温度”、“湿度”等。

对于您的查询，您需要在(location_id、type、timestamp)上有一个复合二级索引。

如果同一地址可以有很多位置，并且您确实关心第二种类型查询的读取性能，那么(假设您可以允许自己忽略每个时间戳相同 location_id-type 的多个数据点)组织测量的最佳方法是:

Measurement
-----------
address_id
location_id
type
timestamp
value

您的 PK 为(address_id、location_id、type、timestamp) + 二级索引(location_id、type、timestamp)。