例如目前有一张表dlzz:
TG_ID表示变压器台区ID,DATA_DATE表示时间,I1、I2和I3分别表示三个不同时间点的电流值
SparkSQL实现列转行
val frame: DataFrame = spark.read.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test11")
.option("dbtable", "dlzz")
.option("user", "root")
.option("password", "123")
.load()
frame.createTempView("frame")
val result = spark.sql(
"""
|select TG_ID,DATA_DATE,'I1' as DL,I1 as num from frame union all
|select TG_ID,DATA_DATE,'I2' as DL,I2 as num from frame union all
|select TG_ID,DATA_DATE,'I3' as DL,I3 as num from frame
""".stripMargin)
result.show()
结果展示:
SparkSQL实现行转列
现有表dlzz_copy如下:
TG_ID代表的是变压器的台区ID,DATA_DATE表示日期,DY表示时间点,num代表电压值
val frame: DataFrame = spark.read.format("jdbc")
.option("url", "jdbc:mysql://localhost:3306/test11")
.option("dbtable", "dlzz_copy")
.option("user", "root")
.option("password", "123")
.load()
frame.createTempView("frame")
frame.groupBy("TG_ID","DATA_DATE").pivot("DY").sum("num").show()
结果展示: