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

Pivot a table and display n ordered time series(透视表并显示 n 个有序时间序列)
本文介绍了透视表并显示 n 个有序时间序列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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 个有序时间序列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)
SQL/MySQL: split a quantity value into multiple rows by date(SQL/MySQL:按日期将数量值拆分为多行)