如何在没有聚合的情况下透视时间序列表

Question

我正在从Teradata表中提取传感器数据进行分析。下面是表格的样子。

​

我想要旋转它，使传感器名称成为列。

​

有超过一百个传感器，因此在枢轴之后矩阵中有那么多列。最终结果集将非常稀疏，因为并非所有传感器都具有所有日期的值。如何在没有聚合的情况下透视表？

Answer 1

聚合有什么问题？

select timestamp,
       max(case when sensor_id = 'sensor1' then val end) as sensor1,
       max(case when sensor_id = 'sensor2' then val end) as sensor2,
       max(case when sensor_id = 'sensor3' then val end) as sensor3,
       . . .
from t
group by timestamp;

这似乎是表达逻辑的最简单方式。而且它的性能可能比100 join要好一点。

Answer 2

您可以使用LEFT JOIN

SELECT DISTINCT t.timestamp, t1.val AS sensor_1, t2.val AS sensor_2, t3.val as sensor_3
FROM (SELECT DISTINCT timestamp FROM tab) t
LEFT JOIN tab t1
  ON t.timestamp = t1.timestamp
  AND t1.sensor_id = 'sensor1'
LEFT JOIN tab t2
  ON t.timestamp = t2.timestamp
  AND t2.sensor_id = 'sensor2'
LEFT JOIN tab t3
  ON t.timestamp = t3.timestamp
  AND t3.sensor_id = 'sensor3'