首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何在单个groupBy中计算sum和count?

问如何在单个groupBy中计算sum和count?
EN

Stack Overflow用户
提问于 2016-11-06 20:05:17
回答 3查看 71.8K关注 0票数 26

基于以下DataFrame

代码语言:javascript
复制
val client = Seq((1,"A",10),(2,"A",5),(3,"B",56)).toDF("ID","Categ","Amnt")
+---+-----+----+
| ID|Categ|Amnt|
+---+-----+----+
|  1|    A|  10|
|  2|    A|   5|
|  3|    B|  56|
+---+-----+----+

我想按类别获取ID号和总金额:

代码语言:javascript
复制
+-----+-----+---------+
|Categ|count|sum(Amnt)|
+-----+-----+---------+
|    B|    1|       56|
|    A|    2|       15|
+-----+-----+---------+

有没有可能在不做连接的情况下进行计数和求和?

代码语言:javascript
复制
client.groupBy("Categ").count
      .join(client.withColumnRenamed("Categ","cat")
           .groupBy("cat")
           .sum("Amnt"), 'Categ === 'cat)
      .drop("cat")

也许是这样的:

代码语言:javascript
复制
client.createOrReplaceTempView("client")
spark.sql("SELECT Categ count(Categ) sum(Amnt) FROM client GROUP BY Categ").show()
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2016-11-06 23:43:28

我举的例子和你的不同

multiple group functions are possible like this. try it accordingly

代码语言:javascript
复制
  // In 1.3.x, in order for the grouping column "department" to show up,
// it must be included explicitly as part of the agg function call.
df.groupBy("department").agg($"department", max("age"), sum("expense"))

// In 1.4+, grouping column "department" is included automatically.
df.groupBy("department").agg(max("age"), sum("expense"))

代码语言:javascript
复制
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.sql.functions._

val spark: SparkSession = SparkSession
      .builder.master("local")
      .appName("MyGroup")
      .getOrCreate()
import spark.implicits._
    val client: DataFrame = spark.sparkContext.parallelize(
Seq((1,"A",10),(2,"A",5),(3,"B",56))
).toDF("ID","Categ","Amnt")

client.groupBy("Categ").agg(sum("Amnt"),count("ID")).show()

代码语言:javascript
复制
+-----+---------+---------+
|Categ|sum(Amnt)|count(ID)|
+-----+---------+---------+
|    B|       56|        1|
|    A|       15|        2|
+-----+---------+---------+
票数 32
EN

Stack Overflow用户

发布于 2017-04-20 15:00:38

您可以像下面这样对给定表进行聚合:

代码语言:javascript
复制
client.groupBy("Categ").agg(sum("Amnt"),count("ID")).show()

+-----+---------+---------+
|Categ|sum(Amnt)|count(ID)|
+-----+---------+---------+
|    A|       15|        2|
|    B|       56|        1|
+-----+---------+---------+
票数 11
EN

Stack Overflow用户

发布于 2018-12-21 20:01:02

在spark中有多种方法来实现聚合函数,

代码语言:javascript
复制
val client = Seq((1,"A",10),(2,"A",5),(3,"B",56)).toDF("ID","Categ","Amnt")

1.

代码语言:javascript
复制
val aggdf = client.groupBy('Categ).agg(Map("ID"->"count","Amnt"->"sum"))

+-----+---------+---------+
|Categ|count(ID)|sum(Amnt)|
+-----+---------+---------+
|B    |1        |56       |
|A    |2        |15       |
+-----+---------+---------+

//Rename and sort as needed.
aggdf.sort('Categ).withColumnRenamed("count(ID)","Count").withColumnRenamed("sum(Amnt)","sum")
+-----+-----+---+
|Categ|Count|sum|
+-----+-----+---+
|A    |2    |15 |
|B    |1    |56 |
+-----+-----+---+

2.

代码语言:javascript
复制
import org.apache.spark.sql.functions._
client.groupBy('Categ).agg(count("ID").as("count"),sum("Amnt").as("sum"))
+-----+-----+---+
|Categ|count|sum|
+-----+-----+---+
|B    |1    |56 |
|A    |2    |15 |
+-----+-----+---+

3.

代码语言:javascript
复制
import com.google.common.collect.ImmutableMap;
client.groupBy('Categ).agg(ImmutableMap.of("ID", "count", "Amnt", "sum"))
+-----+---------+---------+
|Categ|count(ID)|sum(Amnt)|
+-----+---------+---------+
|B    |1        |56       |
|A    |2        |15       |
+-----+---------+---------+
//Use column rename is required. 

  1. 如果您是SQL专家,也可以使用

执行此操作。

代码语言:javascript
复制
client.createOrReplaceTempView("df")

 val aggdf = spark.sql("select Categ, count(ID),sum(Amnt) from df group by Categ")
 aggdf.show()

    +-----+---------+---------+
    |Categ|count(ID)|sum(Amnt)|
    +-----+---------+---------+
    |    B|        1|       56|
    |    A|        2|       15|
    +-----+---------+---------+
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/40449139

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档