• sparksql使用collect_list自定义排序的实现方式


    原始数据如下:
    +---+-----+----+
    |id |name |type|
    +---+-----+----+
    |1  |name1|p   |
    |2  |name2|p   |
    |3  |name3|p   |
    |1  |x1   |q   |
    |2  |x2   |q   |
    |3  |x3   |q   |
    +---+-----+----+
    目标数据如下:
    +----+---------------------+
    |type|value_list           |
    +----+---------------------+
    |p   |[name3, name2, name1]|
    |q   |[x3, x2, x1]         |
    +----+---------------------+

    spark-shell
    val df=Seq((1,"name1","p"),(2,"name2","p"),(3,"name3","p"),(1,"x1","q"),(2,"x2","q"),(3,"x3","q")).toDF("id","name","type")
    df.show(false)
    1.使用开窗函数

    df.createOrReplaceTempView("test")
    spark.sql("select type,max(c) as c1 from (select type,concat_ws('&',collect_list(trim(name)) over(partition by type order by id desc)) as c  from test) as x group by type ")
    因为使用开窗函数本身会使用比较多的资源,
    这种方式在大数据量下性能会比较慢,所以尝试下面的操作。
    2.使用struct和sort_array(array,asc?)的方式来进行,效率高些:
    val df3=spark.sql("select type, concat_ws('&',sort_array(collect_list(struct(id,name)),false).name) as c from test group by type ")
    df3.show(false)
    3.udf的方式
    import org.apache.spark.sql.functions._
    import org.apache.spark.sql._
    val sortUdf = udf((rows: Seq[Row]) => {
      rows.map { case Row(id:Int, value:String) => (id, value) }
        .sortBy { case (id, value) => -id } //id if asc
        .map { case (id, value) => value }
    })

    val grouped = df.groupBy(col("type")).agg(collect_list(struct("id", "name")) as "id_name")
    val r1 = grouped.select(col("type"), sortUdf(col("id_name")).alias("value_list"))
    r1.show(false)


    Looking for a job working at Home about MSBI
  • 相关阅读:
    FpSpread实现筛选的功能
    比较通用的ID,ParentID结构表,返回树路径。
    如何解决,在安装Sql2000时,出现程序挂起的解决方法。
    FarPoint Spread 控件如何实现剪切,粘贴,复制
    MySql中Group By和Order By使用的注意事项!
    php md5下16位和32位的实现代码
    Shadows在C#代替方法!
    FTP命令使用详解
    数据库中存放目录与文件结构,方案选择
    vs2008 sp1下载
  • 原文地址:https://www.cnblogs.com/huaxiaoyao/p/14792918.html
Copyright © 2020-2023  润新知