Spark sql groupby 和 concat [英] Spark sql groupby and concat

问题描述

我有下表

REF 序列数据---------------------1 5 一个1 1 b1 9 厘米2 12 天2 3 e2 8 英尺

我想得到以下结果.

<预><代码>参考数据---------------------1 巴2 EF

我怎样才能触发 sql?

解决方案

use groupBy, collect_list &concat_ws 函数.

检查下面的代码.

df.orderBy($SEQ".asc).groupBy($REF").agg(collect_list($"DATA").as("DATA")).select($"REF",concat_ws("",$"DATA").as("DATA")).show(假)+---+----+|参考|数据|+---+----+|1 |后 ||2 |efd |+---+----+

SQL

scala>spark.sql("with data as (select * from tbl order by seq asc) select REF,concat_ws('',collect_list(DATA)) AS DATA from data group by REF").show(假)+---+----+|参考|数据|+---+----+|1 |后 ||2 |efd |+---+----+

I have following table

REF    SEQ   DATA
---------------------
1       5      a
1       1      b
1       9      c

2       12      d
2       3      e
2       8      f

And i want to get following result.

REF     DATA
---------------------
1        bac
2        efd 

How can i do it spark sql?

解决方案

use groupBy, collect_list & concat_ws function.

Check below code.

df
.orderBy($"SEQ".asc)
.groupBy($"REF")
.agg(collect_list($"DATA").as("DATA"))
.select($"REF",concat_ws("",$"DATA").as("DATA"))
.show(false)

+---+----+
|REF|DATA|
+---+----+
|1  |bac |
|2  |efd |
+---+----+

SQL

scala> spark.sql("with data as (select * from tbl order by seq asc) select REF,concat_ws('',collect_list(DATA)) AS DATA from data group by REF")
.show(false)

+---+----+
|REF|DATA|
+---+----+
|1  |bac |
|2  |efd |
+---+----+

这篇关于Spark sql groupby 和 concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！