sql - Spark SQL 中的转置

Question

对于以下表结构：

+------------------------------------------------------+
|   timestamp   |   value1   |   value2 | ... value100 |
+------------------------------------------------------+
|1/1/1 00:00:00 |     1      |    2     |    100       |
+------------------------------------------------------+

如何使用 Spark SQL 语法将其转换为这样的结构？

+---------------------------------------+
|   timestamp   |      id       | value |  
+---------------------------------------+
|1/1/1 00:00:00 | value1        |   1   |
|1/1/1 00:00:00 | value2        |   2   |  
|1/1/1 00:00:00 | ... value100  |  100  |  
+---------------------------------------+

在 Python 或 R 中，这相对简单，UNPIVOT在这里似乎并不适用。

标签： sqlapache-spark-sql

Answer 1

您可以使用常规 SQL 执行相同操作，如下所示

select timestamp
       ,'value1' as id
       ,value1 as value
  from table
union all
select timestamp
       ,'value2' as id
       ,value2 as value
  from table
union all
select timestamp
       ,'value3' as id
       ,value3 as value
  from table