Spark SQL批处理窗口函数的使用

前言

Spark SQL 1.4.0引入了窗口化功能，使我们可以更轻松地处理行的范围或窗口。window函数对于DataFrame中的每一行都返回一个计算出的值，而groupBy则是对于一个group的key返回一个值。对于DataFrame中的每一行，WindowSpec指定了这行对应的一个WindowFrame，然后在这个WindowFrame上执行相关统计函数。

还要注意，这里的窗口函数是用于批处理中的，我们常见的窗口函数却是用于流处理的。既然是批处理的窗口，肯定需要有一步排序操作，保证数据有序。
欢迎关注微信公众号：大数据报文

窗口函数说明

def testMyTable(session: SparkSession): Unit = {
    val kTopic = session.read.format("jdbc")
    .option("url", MY_URL)
    .option("dbtable", MY_DBTABLE)
    .option("driver", MYSQL_DRIVER)
    .option("user", MY_USER)
    .option("password", MY_PASSWD)
    val wlTable = kTopic.load()
    val groupedDF = wlTable.groupBy(col("topicpartition"))
    //窗口函数使用测试
    //下面这个函数说明的问题：
    /**
          * 构造一个窗口，这个窗口按照score排序，按照age分区（这样输出的时候相同age的会紧邻，相同score的会递增排序）
          * rangeBetween决定了窗口的大小，这里就是10-0=10分，即分数相差十分作为一个窗口
          * rCol也是一个列，当前列减去平均值之后的的值构成新的列，当前值是说每一行的值，平均值是有条件的，即分数在10分以内的行才会去计算平均值，而不是全部行的平均值
          * 注意到rangeBetween方法中写的start = 0, end = 10，即从当前行开始计算比如我们拿着计算结果来讲：
          * xx的成绩为40,[40,40+10]的范围内只有自己，所以平均值为40，col("score") - avg(col("score")).over(windowSpec2)=0
          * dd的成绩为77,[77,77+10]的范围内还有aa同学，平均值为82，col("score") - avg(col("score")).over(windowSpec2)=-5
          * aa的成绩为87,[87,87+10]的范围内只有自己，因此情况同xx同学
          *
          * +----+---+-----+------+
          * |name|age|score|window|
          * +----+---+-----+------+
          * |  xx| 12|   40|   0.0|
          * |  dd| 12|   77|  -5.0|
          * |  aa| 12|   87|   0.0|
          * +----+---+-----+------+
          * 如果.rangeBetween(start = 0, end = 10)变成了.rangeBetween(start = -10, end = 0)，那么每一行都会跟自己的前面比较
          * 因此有了下面的结果：
          *
          * +----+---+-----+------+
          * |name|age|score|window|
          * +----+---+-----+------+
          * |  xx| 12|   40|   0.0|
          * |  dd| 12|   77|   0.0|
          * |  aa| 12|   87|   5.0|
          * +----+---+-----+------+
          *
          * 现在我们有了第三种情况：.rangeBetween(start = -5, end = 5)，有了前面两个例子，这里理解起来也很简单：
          * 没扫描到一行会找到[x-5, x+5]区间内的所有同学的成绩，再用当前值减去这个区间的平均值
          * +----+---+-----+--------------------+
          * |name|age|score|              window|
          * +----+---+-----+--------------------+
          * |  xx| 12|   40|                 0.0|
          * |  ll| 12|   60|                 0.0|
          * |  ff| 12|   80|                -3.0|
          * |  ss| 12|   85|-0.20000000000000284|
          * |  dd| 12|   84|                 0.0|
          * |  aa| 12|   87| -0.5999999999999943|
          * |  gg| 12|   90|                 1.5|
          * |  hh| 12|   92|  2.3333333333333286|
          * +----+---+-----+--------------------+
          */
    val windowSpec2 = Window.orderBy(col("score"))
    .partitionBy(col("age"))
    .rangeBetween(start = -5, end = 5)
    //avg(col("score")).over(windowSpec2)意思是在构造的窗口windowSpec2之上计算avg
    val rCol = col("score") - avg(col("score")).over(windowSpec2)
    wlTable.filter(col("age")===12).select(col("name"), col("age"), col("score"), rCol.as("window")).show()
}

案例

//再举一个例子：统计前后十分钟内同一网吧，上下网人数
val icDF = icDFR.load()
//选取需要的字段，并进行加工，如把String变成时间戳
val filterDF = icDF.select(col("hdkssj"), unix_timestamp(col("hdkssj")).as("timestamp"), col("hdcsdm"), col("zjhm"))
//构造窗口。这里partitionBy也可以有多个，用于二次分区三次分区...同样orderBy可以有多个用于二次排序、三次排序
val windowSpec = Window.partitionBy("hdcsdm").orderBy(col("timestamp")).rangeBetween(start = -600, end = 600)
//计算列
val rCol = count(col("hdkssj")).over(windowSpec)
//输出结果
filterDF.select(col("hdkssj"),col("timestamp"), col("hdcsdm"), col("zjhm"), rCol.as("num")).show(false)
//本程序的不足：只能计算单个人前后十分钟上下网时间，无法判断人物之间关系

总结

需要注意，窗口函数只能针对某一列进行统计，即它；属于列内算子，不支持列间操作。