scala - 在spark.sql中使用group by选择多个元素

Question

有没有办法在sql Spark中按表分组来选择多个元素我正在使用的代码:

val df = spark.read.json("//path")
df.createOrReplaceTempView("GETBYID")

现在正在做分组:

val sqlDF = spark.sql(
  "SELECT count(customerId) FROM GETBYID group by customerId");

但是当我尝试时:

val sqlDF = spark.sql(
  "SELECT count(customerId),customerId,userId FROM GETBYID group by customerId");

Spark 给出错误:

org.apache.spark.sql.AnalysisException: expression 'getbyid.userId' is neither present in the group by, nor is it an aggregate function. Add to group by or wrap in first() (or first_value) if you don't care which value you get.;

有什么办法可以做到这一点

Answer 1

是的，有可能，您所附的错误消息描述了所有可能性。您可以将 userId 添加到 groupBy:

val sqlDF = spark.sql("SELECT count(customerId),customerId,userId FROM GETBYID group by customerId, userId");

或使用first():

val sqlDF = spark.sql("SELECT count(customerId),customerId,first(userId) FROM GETBYID group by customerId");