透视表并显示 n 个有序时间序列

2022-01-11 00:00:00 time-series pivot-table mysql

我有一个名为读数"的表，其中包含来自传感器的数据，其中包含以下列:

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)

相关文章