SparkSQL处理时序数据, 实践case

By 远方时光原创，可转载，open

合作微信公众号：大数据左右手

背景：

1.数据按照时间排序

2.业务逻辑跟数据时序强相关 (不能时间乱序)

3.目标结果与分组统计相关

业务：

物联网场景经常需要处理时序逻辑，数据按照createAt时序，

T15_st代表上下电，下图中黄色1代表上电阶段，0代表下电阶段

常见的业务比如：每一个上电阶段某个列max值，min值, avg值, count....

思考：T15_st只有0或者1，怎么把T15_st列转为每一个上电阶段，也就是想要得到一个flag列，用于分组(第一次上电, 第二次上电，第三次上电)，这样我就可以通过spark groupby(''group_flag'')处理大量的上电阶段，而不需要使用遍历去处理逻辑

备注：SparkSQL优势在于处理分组聚合业务，不擅长处理需要遍历数据的业务，所以能否用SparkSQL在于能否把数据转成分组业务

 

操作方式

1. 添加第一个辅助列 lag_tag，根据T15_st向下错位

// 创建窗口
val windowSpec = Window.orderBy("timestamp")

// 添加 lag 列来标记 tag 列的变化
val dfWithLag = salesDF.withColumn("lag_tag", lag("T15_st", 1, 0).over(windowSpec))

 2. 添加第二个辅助列 group_flag， 得到分组（第几次上下电）

// 根据 tag 列的变化情况创建分组标志列
val dfWithGroup = dfWithLag.withColumn("group_flag",
   sum(when((col("T15_st") === 1) && (col("lag_tag") === 0),  1).otherwise(0))
   .over(Window.orderBy("timestamp")))

group_flag根据上一行的group_flag值累计，只有满足T15_st=1而且lag_tag=0 时累加1（通过错位辅助列lag_tag，得到T15_st从0变为1对应那一行），否则累加0，得到group_flag列

3.最后，上电阶段的数据是对我有用的，最后.where("T15_st == 1").drop("lag_tag")

==> dataframe只要有group_flag, 那么就可以对数据，各种基于分组的业务处理 

完整代码

package sparksqlTest

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{Dataset, Row, SparkSession}
import org.apache.spark.sql.functions._

object sparkSqlWindow {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().master("local").appName("SNF-Income").getOrCreate()

    val salesDF = spark.read
      .option("header", "true")
      .format("csv")
      .load("D:\\BaiduNetdiskDownload\\test.csv")
      .select("name", "createAt", "T15_st")
      .withColumn("timestamp", unix_timestamp(col("createAt"), "yyyy-MM-dd HH:mm:ss"))

    // 创建窗口
    val windowSpec = Window.orderBy("timestamp")

    // 添加 lag 列来标记 tag 列的变化
    val dfWithLag = salesDF.withColumn("lag_tag", lag("T15_st", 1, 0).over(windowSpec))

    // 根据 tag 列的变化情况创建分组标志列
    val dfWithGroup = dfWithLag.withColumn("group_flag",
      sum(when((col("T15_st") === 1) && (col("lag_tag") === 0),  1).otherwise(0))
        .over(Window.orderBy("timestamp")))

    // 保留上电阶段
    val outDF: Dataset[Row] = dfWithGroup.where("T15_st == 1").drop("lag_tag")

    outDF.show(130000,false)
//    outDF.write.option("header", "true").csv("D:\\BaiduNetdiskDownload\\out4.csv")
  }
}

输入csv

name	createAt	T15_st
QING****0*****	2023-10-09 9:48:17	0
QING****0*****	2023-10-09 9:48:18	1
QING****0*****	2023-10-09 9:48:19	1
QING****0*****	2023-10-09 9:48:20	1
QING****0*****	2023-10-09 9:48:21	1
QING****0*****	2023-10-09 9:48:22	1
QING****0*****	2023-10-09 9:48:23	0
QING****0*****	2023-10-09 9:48:24	0
QING****0*****	2023-10-09 9:48:25	1
QING****0*****	2023-10-09 9:48:26	1
QING****0*****	2023-10-09 9:48:27	1
QING****0*****	2023-10-09 9:48:28	1
QING****0*****	2023-10-09 9:48:29	0
QING****0*****	2023-10-09 9:48:30	0
QING****0*****	2023-10-09 9:48:31	1
QING****0*****	2023-10-09 9:48:32	1
QING****0*****	2023-10-09 9:48:33	1
QING****0*****	2023-10-09 9:48:34	1
QING****0*****	2023-10-09 9:48:35	1
QING****0*****	2023-10-09 9:48:36	1
QING****0*****	2023-10-09 9:48:37	1
QING****0*****	2023-10-09 9:48:38	0
QING****0*****	2023-10-09 9:48:39	0
QING****0*****	2023-10-09 9:48:40	0
QING****0*****	2023-10-09 9:48:41	0