186、Spark 2.0之Dataset开发详解-初步体验untypd操作案例：计算部门平均年龄与薪资...-优快云博客

本文通过使用Spark SQL进行数据处理，展示了如何计算不同部门的平均薪资与平均年龄，并进一步分析了超过20岁员工按性别分组的平均薪资与年龄。通过对department.json和employee.json两个文件的数据进行join操作，实现了对特定业务需求的精准统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求

计算部门平均年龄与薪资
计算部门性别平均年龄与薪资

数据

department.json

{"id": 1, "name": "Technical Department"}
{"id": 2, "name": "Financial Department"}
{"id": 3, "name": "HR Department"}

employee.json

{"name": "Leo", "age": 25, "depId": 1, "gender": "male", "salary": 20000}
{"name": "Marry", "age": 30, "depId": 2, "gender": "female", "salary": 25000}
{"name": "Jack", "age": 35, "depId": 1, "gender": "male", "salary": 15000}
{"name": "Tom", "age": 42, "depId": 3, "gender": "male", "salary": 18000}
{"name": "Kattie", "age": 21, "depId": 3, "gender": "female", "salary": 21000}
{"name": "Jen", "age": 30, "depId": 2, "gender": "female", "salary": 28000}
{"name": "Jen", "age": 19, "depId": 2, "gender": "female", "salary": 8000}

代码

object DepartmentAvgSalaryAndAgeStat {

  def main(args: Array[String]): Unit = {
    val sparkSession= SparkSession
      .builder()
      .master("local")
      .appName("DepartmentAvgSalaryAndAgeStatScala")
      .getOrCreate()

    import sparkSession.implicits._
    import org.apache.spark.sql.functions._

    val deptmentPath = this.getClass.getClassLoader.getResource("department.json").getPath
    val employeePath = this.getClass.getClassLoader.getResource("employee.json").getPath

    val deptmentDF = sparkSession.read.json(deptmentPath)
    val employeeDF = sparkSession.read.json(employeePath)


    deptmentDF.show()
    deptmentDF.printSchema()
    employeeDF.show()
    employeeDF.printSchema()
    // 需求1
    deptmentDF.join(employeeDF, $"id" === $"depId")
        .groupBy(deptmentDF("id"))
        .agg(avg(employeeDF("age")), avg(employeeDF("salary")))
        .show()
    // 需求2
    employeeDF
      .filter("age > 20")
      .join(deptmentDF, $"depId"===$"id")
      .groupBy(deptmentDF("name"), employeeDF("gender"))
      .agg(avg(employeeDF("salary")), avg(employeeDF("age")))
      .show()
  }
}