旋转表格并显示n个有序的时间序列 [英] Pivot a table and display n ordered time series

问题描述

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

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查询如何?

推荐答案

尝试一下:

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 ',\n') 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个有序的时间序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！