mysql 将数据从多路复用的列值重新排序到行

我有一个如下的表组织，其中温度测量值都存储在一列中，每个时间戳在不同深度有14个不同的温度样本。

select * from water_temp order by unixtimestamp desc limit 28;

| id| unixtimestamp|深度名|深度|温度|
| - -----|- -----|- -----|- -----|- -----|
| 481042727209|电话：1685770037| 600英尺|六百|38.591万|
| 电话：481042727208|电话：1685770037| 500英尺|五百|38.696万|
| 481042727207|电话：1685770037| 400英尺|四百|38.657万|
| 电话：481042727206|电话：1685770037| 300英尺|三百|38.904万|
| 电话：481042727205|电话：1685770037| 200英尺|两百|39.340万|
| 481042727204|电话：1685770037| 150英尺|一百五十|39.5030000|
| 电话：481042727203|电话：1685770037| 100英尺|一百|40.214万|
| 481042727202|电话：1685770037| 80英尺|八十|41.385万|
| 电话：481042727201|电话：1685770037| 60英尺|六十|44.153万|
| 481042727200|电话：1685770037| 50英尺|五十|46.259万|
| 电话：481042727199|电话：1685770037| 40英尺|四十|47.242万|
| 481042727198|电话：1685770037| 30英尺|三十|49.739万|
| 481042727197|电话：1685770037| 20英尺|二十个|55.3010000|
| 电话：481042727196|电话：1685770037| 10英尺|十个|61.884万|
| 电话：481041534602|电话：1685769380| 600英尺|六百|38.591万|
| 481041534601|电话：1685769380| 500英尺|五百|38.640万|
| 481041534600|电话：1685769380| 400英尺|四百|38.713万|
| 481041534599|电话：1685769380| 300英尺|三百|38.904万|
| 481041534598|电话：1685769380| 200英尺|两百|39.284万|
| 481041534597|电话：1685769380| 150英尺|一百五十|39.560万|
| 481041534596|电话：1685769380| 100英尺|一百|40.270万|
| 电话：481041534595|电话：1685769380| 80英尺|八十|41.441万|
| 481041534594|电话：1685769380| 60英尺|六十|44.322万|
| 481041534593|电话：1685769380| 50英尺|五十|46.315万|
| 481041534592|电话：1685769380| 40英尺|四十|47.354万|
| 481041534591|电话：1685769380| 30英尺|三十|49.907万|
| 电话：481041534590|电话：1685769380| 20英尺|二十个|56.145万|
| 电话：481041534589|电话：1685769380| 10英尺|十个|62.503万|
希望获得以下格式的输出：
| unixtimestamp|深度10|深度20|深度30|......这是什么？|
| - -----|- -----|- -----|- -----|- -----|
| 电话：1685769380|六十二点五|五十六点一四|四十九点九一|......这是什么？|
| 电话：1685770037|六十一点八|五十五点三|四十九点七四|......这是什么？|
| ......这是什么？|......这是什么？|......这是什么？|......这是什么？|......这是什么？|
我继承了这个表格格式。我试过使用distinct，但找不到一种方法来拥有一个独特的时间戳和深度。我想我可能不得不转储数据，并使用Python来读取和重组每个时间戳的一行。
是否有一个简单的SQL查询来获取数据，以每个时间戳的一行列出，每个深度温度作为列？我想以csv格式输出数据，其中每行是一个时间戳。
另外，是否有一些理由以这种方式在DB中组织数据？我试着去理解。在我看来，更直观/有用的方法是每个温度深度有一列，每个时间戳有一行。
先谢谢你了。

是否有理由以这种方式组织数据库中的数据？我试着去理解。在我看来，更直观/有用的方法是每个温度深度有一列，每个时间戳有一行。
您组织数据的方式可以使查询更快，或者使数据更易于使用。如果拥有透视数据使它们更能满足您的需求，那么您应该这样做。
给予个例子，如果你必须在温度高于40时获得深度值，试图用你所需要的输出来做，可能会迫使你经历非旋转，而如果你使用原始表，它会非常简单。
相反，如果您需要进行某种报告，并且只观察特定深度（例如深度10和深度100）的温度，则使用输入表将需要进行过滤，而使用输出表可以立即给予过滤。
你所寻找的是所谓的“pivot”，它允许你将行变成列。由于在输出中有14列专用于深度（从数据中我假设每个时间戳都是14列），因此应该在SELECT语句中放入14个计算字段，如：

SELECT unixtimestamp,
       ... AS depth10,
       ... AS depth20,
       ... AS depth30,
       ... AS depth40,
       ...
       ... AS depth600
FROM water_temp

然后你意识到你实际上需要选择你的温度，但只有当深度对应于你选择的深度。你可以用一个CASE表达式（或者一个IF，如果我们愿意的话）来做一些事情：

SELECT unixtimestamp,
       CASE WHEN depth =  10 THEN temperature END AS depth10,
       CASE WHEN depth =  20 THEN temperature END AS depth20,
       CASE WHEN depth =  30 THEN temperature END AS depth30,
       CASE WHEN depth =  40 THEN temperature END AS depth40,
       ...
       CASE WHEN depth = 600 THEN temperature END AS depth600
FROM water_temp

当你尝试这样做时，你会意识到你将有一种平方矩阵，对于这个矩阵，唯一的时间戳现在有14条记录，每条记录都有一个非空值（在相应的深度列上）。
由于您只希望每个时间戳有一条记录，因此减少表大小的最佳选择之一是聚合（只要它“聚合”数据）。您需要通过对时间戳进行分组来进行聚合（因为您希望每个时间戳有一条记录），并使用一个聚合函数，该函数可以将一个非空值与其他空值压缩，即MIN或MAX。然后，您的代码变为：

SELECT unixtimestamp,
       MAX(CASE WHEN depth =  10 THEN temperature END) AS depth10,
       MAX(CASE WHEN depth =  20 THEN temperature END) AS depth20,
       MAX(CASE WHEN depth =  30 THEN temperature END) AS depth30,
       MAX(CASE WHEN depth =  40 THEN temperature END) AS depth40,
       ...
       MAX(CASE WHEN depth = 600 THEN temperature END) AS depth600
FROM water_temp 
GROUP BY unixtimestamp

这正是你要找的

mysql 将数据从多路复用的列值重新排序到行

1条答案

相关问题

热门标签

最新问答