问题描述
我有一个名为读数"的表,其中包含来自传感器的数据,其中包含以下列:
I have a table named "readings" which contains data from sensors with the following columns:
id id_device date value
1 1 2015-01-01 00:00:00 0.2
2 2 2015-01-01 00:00:00 0.7
3 1 2015-01-01 00:00:10 0.3
4 2 2015-01-01 00:00:10 0.8
5 1 2015-01-01 00:00:20 0.4
6 2 2015-01-01 00:00:20 0.9
我想在单个查询中将其转换为该表:
And I want to transform it to this table in a single query:
date device_id_1 device_id_2
2015-01-01 00:00:00 0.2 0.7
2015-01-01 00:00:10 0.3 0.8
2015-01-01 00:00:20 0.4 0.9
我发现这样做被称为透视表",但我只发现了如何对值求和,而不是如何按时间序列对它们进行排序
I've found that doing this is called "pivoting a table" but I've only found how to sum values, not how to order them in time series
我试过了
SELECT DISTINCT(date) FROM readings
然后在结果循环中,再次查询每个传感器
and then inside the results loop, querying again for each sensor
SELECT value FROM readings WHERE date=$date AND id_device=$id
但我确信这不是很有效,因为它有很多查询(每个值一个查询)
But I'm sure that this is not very efficient because it is a lot of queries (one query for each value)
sql 查询会是什么样子?
How the sql query would be like?
推荐答案
试试这个:
SELECT r.id,
r.`date`,
GROUP_CONCAT(IF(r.device_id = 1,r.`VALUE`,NULL)) AS device_id_1,
GROUP_CONCAT(IF(r.device_id = 2,r.`VALUE`,NULL)) AS device_id_2
FROM readings r
GROUP BY r.`DATE`
ORDER BY r.`DATE` ASC;
+----+---------------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 |
+----+---------------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 |
+----+---------------------+-------------+-------------+
2 rows in set (0.00 sec)
这里是动态 device_id 的版本
And here a Version for dynamic device_id's
SELECT group_concat(
"GROUP_CONCAT(IF(device_id = ",
device_id,",`value`,NULL)) AS device_id_",
device_id
SEPARATOR ',
') INTO @sql_mid
FROM (SELECT DISTINCT device_id FROM readings ORDER BY device_id) AS r
ORDER BY device_id;
SELECT CONCAT("SELECT id, `date`, ", @sql_mid, " FROM readings GROUP BY `DATE` ORDER BY `DATE` ASC") INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
它生成所有列
+----+---------------------+-------------+-------------+-------------+
| id | date | device_id_1 | device_id_2 | device_id_3 |
+----+---------------------+-------------+-------------+-------------+
| 1 | 2015-10-01 10:00:00 | 2 | 0.5 | NULL |
| 3 | 2015-10-01 10:01:00 | 3.1 | 7.5 | NULL |
| 5 | 2015-10-01 10:11:00 | NULL | NULL | 9.9 |
+----+---------------------+-------------+-------------+-------------+
3 rows in set (0.00 sec)
这篇关于透视表并显示 n 个有序时间序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!