DSL实现union、join、case when

小胖超凶哦！

已于 2022-05-17 15:47:58 修改

阅读量702

点赞数

分类专栏：初学大数据 Spark基础文章标签： Spark

于 2022-05-17 15:46:38 首次发布

本文链接：https://blog.csdn.net/ZZJXP/article/details/124816241

版权

初学大数据同时被 2 个专栏收录

158 篇文章 1 订阅

订阅专栏

Spark基础

27 篇文章 0 订阅

订阅专栏

package com.shujia.sql

import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object Demo04DSL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .appName("Demo04DSL")
      .master("local")
      .config("spark.sql.shuffle.partitions",2)//默认200 避免shuffle过后产生过多的分区 导致生成的task数量过多
      .getOrCreate()

    //导入function以及隐式转换
    import org.apache.spark.sql.functions._
    import spark.implicits._

    //DSL的常用方法
    /**
     * SQL中常用的一些关键字：
     * select 查询
     * from tb
     * where 过滤
     * group by 分组 --> max、min、avg、sum、count 聚合函数
     * having 分组聚合后的过滤
     * order by 排序
     * limit 限制返回的数据条数
     * 关联：
     * 内连接：inner join、join
     * 外连接：left join、right join
     * 全外连接：full outer join（很少使用）
     * 连接：
     * union、union all
     * 去重：distinct
     * 常用的函数：
     * 字符串函数、数值函数、日期函数、窗口函数、条件函数
     */

    //构建DataFrame
    val stuDF: DataFrame = spark
      .read
      .format("csv") //文本文件统一用csv读取
      .option("sep", ",") //最好在读取文本类文件的时候加上sep分隔符
      .schema("id String,name String,age Int,gender String,clazz String") //给数据附上结构（列名及列的类型）
      .load("Spark/data/students.txt") //指定读取文件的路径

    //对多次使用的DataFrame进行cache
    stuDF.cache()

    //打印DataFrame（表）结构
//    stuDF.printSchema()

    //select
    stuDF.select("id","name","age")//字符串表达式
//      .show()

    stuDF.select($"id",$"name",$"age")//使用 $ 取列 ==> 列表达式(推荐）
//      .show()

    //where、filter
    //过滤 文科班 的学生
    stuDF.where("clazz like '文科%'")//字符串表达式
//      .show()

    stuDF.where($"clazz" like "文科%")//列表达式
//      .show()

    //使用filter 即支持 字符串表达式 也支持 列表达式 还支持 函数式编程的方式
    //DataFrame 跟 DataSet[Row] 没有区别
    //DataSet 相比 RDD 会有更多好用的API可以快速完成数据分析
    //DataFrame中的数据实际上都是一个个的Row的对象 是有结构的
    stuDF.filter(row=>row.getAs[String]("clazz").contains("文科"))
//      .show()

    //用完记得释放缓存
    stuDF.unpersist()

    //统计班级人数
    /**
     * groupBy会产生shuffle
     * 在spark SQL中默认shuffle过后的分区数为 200 那么会产生200个任务
     * 可以通过 spark.sql.shuffle.partitions 进行设置
     */
    stuDF
      .groupBy($"clazz")
      .agg(count($"id"))
//      .show()
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")//注意数据有重复 需要使用countDistinct
//      .show()

    //having 分组聚合后的过滤
    //统计班级人数 并过滤出 人数大于90的班级
    /**
     * 为什么在DSL中聚合后可以使用where过滤，在SQL中只能用having？
     * 在Spark SQL的DSL中会按照从头往后的顺序执行
     */
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
//      .show()

    //orderBy limit
    //统计班级人数 并过滤出 人数大于90的班级 并按照人数从大到小进行排列
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
      //在Spark SQL中 orderBy跟sort是一样的 理论上都是做全局排序
      //如果不是可以通过改变分区数做到全局排序
//      .coalesce(1)
      .orderBy($"cnt".desc)
      .limit(3)
//      .show()

    //联接 相当于union
    val stuSampleDF1: Dataset[Row] = stuDF.sample(0.01,1)
    val stuSampleDF2: Dataset[Row] = stuDF.sample(0.01,1)

    stuSampleDF1.show()
    stuSampleDF2.show()
    //只有格式一致的两份DataFrame才可以进行联接
    /**
     * SQL中的union会进行去重，union all不会
     * Spark SQL中的union实际上是RDD中的union方法 不会进行去重
     * 如果需要去重 可以接distinct
     */
    stuSampleDF1
      .union(stuSampleDF2)
      .show()

    stuSampleDF1
      .union(stuSampleDF2)
      .distinct()
      .show()

    //关联

  }
}

+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100016|潘访烟| 23| 女|文科一班|
|1500100028|幸浩邈| 24| 男|理科五班|
|1500100045|仇运晟| 21| 男|理科一班|
|1500100058|薄谷蕊| 23| 女|理科三班|
|1500100196|汤浩博| 21| 男|文科三班|
|1500100229|逯冬萱| 22| 女|文科三班|
|1500100361|童以山| 21| 女|文科五班|
|1500100484|蒲惜玉| 24| 女|理科六班|
|1500100501|松迎梅| 22| 女|理科四班|
|1500100528|边彦昌| 22| 男|文科六班|
|1500100781|孟昊然| 22| 男|理科四班|
|1500100905|昌醉柳| 23| 女|理科一班|
+----------+------+---+------+--------+

+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100016|潘访烟| 23| 女|文科一班|
|1500100028|幸浩邈| 24| 男|理科五班|
|1500100045|仇运晟| 21| 男|理科一班|
|1500100058|薄谷蕊| 23| 女|理科三班|
|1500100196|汤浩博| 21| 男|文科三班|
|1500100229|逯冬萱| 22| 女|文科三班|
|1500100361|童以山| 21| 女|文科五班|
|1500100484|蒲惜玉| 24| 女|理科六班|
|1500100501|松迎梅| 22| 女|理科四班|
|1500100528|边彦昌| 22| 男|文科六班|
|1500100781|孟昊然| 22| 男|理科四班|
|1500100905|昌醉柳| 23| 女|理科一班|
+----------+------+---+------+--------+

+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100016|潘访烟| 23| 女|文科一班|
|1500100028|幸浩邈| 24| 男|理科五班|
|1500100045|仇运晟| 21| 男|理科一班|
|1500100058|薄谷蕊| 23| 女|理科三班|
|1500100196|汤浩博| 21| 男|文科三班|
|1500100229|逯冬萱| 22| 女|文科三班|
|1500100361|童以山| 21| 女|文科五班|
|1500100484|蒲惜玉| 24| 女|理科六班|
|1500100501|松迎梅| 22| 女|理科四班|
|1500100528|边彦昌| 22| 男|文科六班|
|1500100781|孟昊然| 22| 男|理科四班|
|1500100905|昌醉柳| 23| 女|理科一班|
|1500100016|潘访烟| 23| 女|文科一班|
|1500100028|幸浩邈| 24| 男|理科五班|
|1500100045|仇运晟| 21| 男|理科一班|
|1500100058|薄谷蕊| 23| 女|理科三班|
|1500100196|汤浩博| 21| 男|文科三班|
|1500100229|逯冬萱| 22| 女|文科三班|
|1500100361|童以山| 21| 女|文科五班|
|1500100484|蒲惜玉| 24| 女|理科六班|
+----------+------+---+------+--------+
only showing top 20 rows

+----------+------+---+------+--------+
| id| name|age|gender| clazz|
+----------+------+---+------+--------+
|1500100045|仇运晟| 21| 男|理科一班|
|1500100196|汤浩博| 21| 男|文科三班|
|1500100229|逯冬萱| 22| 女|文科三班|
|1500100361|童以山| 21| 女|文科五班|
|1500100501|松迎梅| 22| 女|理科四班|
|1500100781|孟昊然| 22| 男|理科四班|
|1500100905|昌醉柳| 23| 女|理科一班|
|1500100016|潘访烟| 23| 女|文科一班|
|1500100028|幸浩邈| 24| 男|理科五班|
|1500100058|薄谷蕊| 23| 女|理科三班|
|1500100484|蒲惜玉| 24| 女|理科六班|
|1500100528|边彦昌| 22| 男|文科六班|
+----------+------+---+------+--------+

package com.shujia.sql

import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object Demo04DSL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .appName("Demo04DSL")
      .master("local")
      .config("spark.sql.shuffle.partitions",2)//默认200 避免shuffle过后产生过多的分区 导致生成的task数量过多
      .getOrCreate()

    //导入function以及隐式转换
    import org.apache.spark.sql.functions._
    import spark.implicits._

    //DSL的常用方法
    /**
     * SQL中常用的一些关键字：
     * select 查询
     * from tb
     * where 过滤
     * group by 分组 --> max、min、avg、sum、count 聚合函数
     * having 分组聚合后的过滤
     * order by 排序
     * limit 限制返回的数据条数
     * 关联：
     * 内连接：inner join、join
     * 外连接：left join、right join
     * 全外连接：full outer join（很少使用）
     * 连接：
     * union、union all
     * 去重：distinct
     * 常用的函数：
     * 字符串函数、数值函数、日期函数、窗口函数、条件函数
     */

    //构建DataFrame
    val stuDF: DataFrame = spark
      .read
      .format("csv") //文本文件统一用csv读取
      .option("sep", ",") //最好在读取文本类文件的时候加上sep分隔符
      .schema("id String,name String,age Int,gender String,clazz String") //给数据附上结构（列名及列的类型）
      .load("Spark/data/students.txt") //指定读取文件的路径

    //对多次使用的DataFrame进行cache
    stuDF.cache()

    //打印DataFrame（表）结构
//    stuDF.printSchema()

    //select
    stuDF.select("id","name","age")//字符串表达式
//      .show()

    stuDF.select($"id",$"name",$"age")//使用 $ 取列 ==> 列表达式(推荐）
//      .show()

    //where、filter
    //过滤 文科班 的学生
    stuDF.where("clazz like '文科%'")//字符串表达式
//      .show()

    stuDF.where($"clazz" like "文科%")//列表达式
//      .show()

    //使用filter 即支持 字符串表达式 也支持 列表达式 还支持 函数式编程的方式
    //DataFrame 跟 DataSet[Row] 没有区别
    //DataSet 相比 RDD 会有更多好用的API可以快速完成数据分析
    //DataFrame中的数据实际上都是一个个的Row的对象 是有结构的
    stuDF.filter(row=>row.getAs[String]("clazz").contains("文科"))
//      .show()

    //用完记得释放缓存
    stuDF.unpersist()

    //统计班级人数
    /**
     * groupBy会产生shuffle
     * 在spark SQL中默认shuffle过后的分区数为 200 那么会产生200个任务
     * 可以通过 spark.sql.shuffle.partitions 进行设置
     */
    stuDF
      .groupBy($"clazz")
      .agg(count($"id"))
//      .show()
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")//注意数据有重复 需要使用countDistinct
//      .show()

    //having 分组聚合后的过滤
    //统计班级人数 并过滤出 人数大于90的班级
    /**
     * 为什么在DSL中聚合后可以使用where过滤，在SQL中只能用having？
     * 在Spark SQL的DSL中会按照从头往后的顺序执行
     */
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
//      .show()

    //orderBy limit
    //统计班级人数 并过滤出 人数大于90的班级 并按照人数从大到小进行排列
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
      //在Spark SQL中 orderBy跟sort是一样的 理论上都是做全局排序
      //如果不是可以通过改变分区数做到全局排序
//      .coalesce(1)
      .orderBy($"cnt".desc)
      .limit(3)
//      .show()

    //联接 相当于union
    val stuSampleDF1: Dataset[Row] = stuDF.sample(0.01,1)
    val stuSampleDF2: Dataset[Row] = stuDF.sample(0.01,1)

//    stuSampleDF1.show()
//    stuSampleDF2.show()
    //只有格式一致的两份DataFrame才可以进行联接
    /**
     * SQL中的union会进行去重，union all不会
     * Spark SQL中的union实际上是RDD中的union方法 不会进行去重
     * 如果需要去重 可以接distinct
     */
    stuSampleDF1
      .union(stuSampleDF2)
//      .show()

    //通过union+distinct实现SQL中union可以去重的效果
    stuSampleDF1
      .union(stuSampleDF2)
      .distinct()
//      .show()

    //关联
    val scoDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("id String,sub_id String,score Int")
      .load("Spark/data/score.txt")

    //inner join
    stuDF
      //两个DF在关联时如果关联字段相同 使用列表达式会有问题
      .join(scoDF,"id")
      .show()

    //left join、right join
    //joinType可以指定：
    // inner, cross, outer, full, full_outer, left, left_outer, right, right_outer, left_semi, left_anti
    // 一定要注意 关联的字段名相同时 又需要指定关联的方式 必须将关联的字段放入List中 作为参数传入
    stuDF
      .join(scoDF,List("id"),"left")
      .show()

    //将scoDF中id列改名为sid
    val newScoDF: DataFrame = scoDF.withColumnRenamed("id", "sid")
    newScoDF
      .join(stuDF,$"id"===$"sid","right")
      .show()

  }
}

+----------+------+---+------+--------+-------+-----+
| id| name|age|gender| clazz| sub_id|score|
+----------+------+---+------+--------+-------+-----+
|1500100001|施笑槐| 22| 女|文科六班|1000001| 98|
|1500100001|施笑槐| 22| 女|文科六班|1000002| 5|
|1500100001|施笑槐| 22| 女|文科六班|1000003| 137|
|1500100001|施笑槐| 22| 女|文科六班|1000004| 29|
|1500100001|施笑槐| 22| 女|文科六班|1000005| 85|
|1500100001|施笑槐| 22| 女|文科六班|1000006| 52|
|1500100002|吕金鹏| 24| 男|文科六班|1000001| 139|
|1500100002|吕金鹏| 24| 男|文科六班|1000002| 102|
|1500100002|吕金鹏| 24| 男|文科六班|1000003| 44|
|1500100002|吕金鹏| 24| 男|文科六班|1000004| 18|
|1500100002|吕金鹏| 24| 男|文科六班|1000005| 46|
|1500100002|吕金鹏| 24| 男|文科六班|1000006| 91|
|1500100003|单乐蕊| 22| 女|理科六班|1000001| 48|
|1500100003|单乐蕊| 22| 女|理科六班|1000002| 132|
|1500100003|单乐蕊| 22| 女|理科六班|1000003| 41|
|1500100003|单乐蕊| 22| 女|理科六班|1000007| 32|
|1500100003|单乐蕊| 22| 女|理科六班|1000008| 7|
|1500100003|单乐蕊| 22| 女|理科六班|1000009| 99|
|1500100004|葛德曜| 24| 男|理科三班|1000001| 147|
|1500100004|葛德曜| 24| 男|理科三班|1000002| 69|
+----------+------+---+------+--------+-------+-----+
only showing top 20 rows

+----------+------+---+------+--------+-------+-----+
| id| name|age|gender| clazz| sub_id|score|
+----------+------+---+------+--------+-------+-----+
|1500100001|施笑槐| 22| 女|文科六班|1000006| 52|
|1500100001|施笑槐| 22| 女|文科六班|1000005| 85|
|1500100001|施笑槐| 22| 女|文科六班|1000004| 29|
|1500100001|施笑槐| 22| 女|文科六班|1000003| 137|
|1500100001|施笑槐| 22| 女|文科六班|1000002| 5|
|1500100001|施笑槐| 22| 女|文科六班|1000001| 98|
|1500100002|吕金鹏| 24| 男|文科六班|1000006| 91|
|1500100002|吕金鹏| 24| 男|文科六班|1000005| 46|
|1500100002|吕金鹏| 24| 男|文科六班|1000004| 18|
|1500100002|吕金鹏| 24| 男|文科六班|1000003| 44|
|1500100002|吕金鹏| 24| 男|文科六班|1000002| 102|
|1500100002|吕金鹏| 24| 男|文科六班|1000001| 139|
|1500100003|单乐蕊| 22| 女|理科六班|1000009| 99|
|1500100003|单乐蕊| 22| 女|理科六班|1000008| 7|
|1500100003|单乐蕊| 22| 女|理科六班|1000007| 32|
|1500100003|单乐蕊| 22| 女|理科六班|1000003| 41|
|1500100003|单乐蕊| 22| 女|理科六班|1000002| 132|
|1500100003|单乐蕊| 22| 女|理科六班|1000001| 48|
|1500100004|葛德曜| 24| 男|理科三班|1000009| 60|
|1500100004|葛德曜| 24| 男|理科三班|1000008| 21|
+----------+------+---+------+--------+-------+-----+
only showing top 20 rows

+----------+-------+-----+----------+------+---+------+--------+
| sid| sub_id|score| id| name|age|gender| clazz|
+----------+-------+-----+----------+------+---+------+--------+
|1500100001|1000006| 52|1500100001|施笑槐| 22| 女|文科六班|
|1500100001|1000005| 85|1500100001|施笑槐| 22| 女|文科六班|
|1500100001|1000004| 29|1500100001|施笑槐| 22| 女|文科六班|
|1500100001|1000003| 137|1500100001|施笑槐| 22| 女|文科六班|
|1500100001|1000002| 5|1500100001|施笑槐| 22| 女|文科六班|
|1500100001|1000001| 98|1500100001|施笑槐| 22| 女|文科六班|
|1500100002|1000006| 91|1500100002|吕金鹏| 24| 男|文科六班|
|1500100002|1000005| 46|1500100002|吕金鹏| 24| 男|文科六班|
|1500100002|1000004| 18|1500100002|吕金鹏| 24| 男|文科六班|
|1500100002|1000003| 44|1500100002|吕金鹏| 24| 男|文科六班|
|1500100002|1000002| 102|1500100002|吕金鹏| 24| 男|文科六班|
|1500100002|1000001| 139|1500100002|吕金鹏| 24| 男|文科六班|
|1500100003|1000009| 99|1500100003|单乐蕊| 22| 女|理科六班|
|1500100003|1000008| 7|1500100003|单乐蕊| 22| 女|理科六班|
|1500100003|1000007| 32|1500100003|单乐蕊| 22| 女|理科六班|
|1500100003|1000003| 41|1500100003|单乐蕊| 22| 女|理科六班|
|1500100003|1000002| 132|1500100003|单乐蕊| 22| 女|理科六班|
|1500100003|1000001| 48|1500100003|单乐蕊| 22| 女|理科六班|
|1500100004|1000009| 60|1500100004|葛德曜| 24| 男|理科三班|
|1500100004|1000008| 21|1500100004|葛德曜| 24| 男|理科三班|
+----------+-------+-----+----------+------+---+------+--------+

package com.shujia.sql

import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object Demo04DSL {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .appName("Demo04DSL")
      .master("local")
      .config("spark.sql.shuffle.partitions", 2) //默认200 避免shuffle过后产生过多的分区 导致生成的task数量过多
      .getOrCreate()

    //导入function以及隐式转换
    import org.apache.spark.sql.functions._
    import spark.implicits._

    //DSL的常用方法
    /**
     * SQL中常用的一些关键字：
     * select 查询
     * from tb
     * where 过滤
     * group by 分组 --> max、min、avg、sum、count 聚合函数
     * having 分组聚合后的过滤
     * order by 排序
     * limit 限制返回的数据条数
     * 关联：
     * 内连接：inner join、join
     * 外连接：left join、right join
     * 全外连接：full outer join（很少使用）
     * 连接：
     * union、union all
     * 去重：distinct
     * 常用的函数：
     * 字符串函数、数值函数、日期函数、窗口函数、条件函数
     */

    //构建DataFrame
    val stuDF: DataFrame = spark
      .read
      .format("csv") //文本文件统一用csv读取
      .option("sep", ",") //最好在读取文本类文件的时候加上sep分隔符
      .schema("id String,name String,age Int,gender String,clazz String") //给数据附上结构（列名及列的类型）
      .load("Spark/data/students.txt") //指定读取文件的路径

    //对多次使用的DataFrame进行cache
    stuDF.cache()

    //打印DataFrame（表）结构
    //    stuDF.printSchema()

    //select
    stuDF.select("id", "name", "age") //字符串表达式
    //      .show()

    stuDF.select($"id", $"name", $"age") //使用 $ 取列 ==> 列表达式(推荐）
    //      .show()

    //where、filter
    //过滤 文科班 的学生
    stuDF.where("clazz like '文科%'") //字符串表达式
    //      .show()

    stuDF.where($"clazz" like "文科%") //列表达式
    //      .show()

    //使用filter 即支持 字符串表达式 也支持 列表达式 还支持 函数式编程的方式
    //DataFrame 跟 DataSet[Row] 没有区别
    //DataSet 相比 RDD 会有更多好用的API可以快速完成数据分析
    //DataFrame中的数据实际上都是一个个的Row的对象 是有结构的
    stuDF.filter(row => row.getAs[String]("clazz").contains("文科"))
    //      .show()

    //用完记得释放缓存
    stuDF.unpersist()

    //统计班级人数
    /**
     * groupBy会产生shuffle
     * 在spark SQL中默认shuffle过后的分区数为 200 那么会产生200个任务
     * 可以通过 spark.sql.shuffle.partitions 进行设置
     */
    stuDF
      .groupBy($"clazz")
      .agg(count($"id"))
    //      .show()
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt") //注意数据有重复 需要使用countDistinct
    //      .show()

    //having 分组聚合后的过滤
    //统计班级人数 并过滤出 人数大于90的班级
    /**
     * 为什么在DSL中聚合后可以使用where过滤，在SQL中只能用having？
     * 在Spark SQL的DSL中会按照从头往后的顺序执行
     */
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
    //      .show()

    //orderBy limit
    //统计班级人数 并过滤出 人数大于90的班级 并按照人数从大到小进行排列
    stuDF
      .groupBy($"clazz")
      .agg(countDistinct($"id") as "cnt")
      .where($"cnt" > 90)
      //在Spark SQL中 orderBy跟sort是一样的 理论上都是做全局排序
      //如果不是可以通过改变分区数做到全局排序
      //      .coalesce(1)
      .orderBy($"cnt".desc)
      .limit(3)
    //      .show()

    //联接 相当于union
    val stuSampleDF1: Dataset[Row] = stuDF.sample(0.01, 1)
    val stuSampleDF2: Dataset[Row] = stuDF.sample(0.01, 1)

    //    stuSampleDF1.show()
    //    stuSampleDF2.show()
    //只有格式一致的两份DataFrame才可以进行联接
    /**
     * SQL中的union会进行去重，union all不会
     * Spark SQL中的union实际上是RDD中的union方法 不会进行去重
     * 如果需要去重 可以接distinct
     */
    stuSampleDF1
      .union(stuSampleDF2)
    //      .show()

    //通过union+distinct实现SQL中union可以去重的效果
    stuSampleDF1
      .union(stuSampleDF2)
      .distinct()
    //      .show()

    //关联
    val scoDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("id String,sub_id String,score Int")
      .load("Spark/data/score.txt")

    //inner join
    stuDF
      //两个DF在关联时如果关联字段相同 使用列表达式会有问题
      .join(scoDF, "id")
    //      .show()

    //left join、right join
    //joinType可以指定：
    // inner, cross, outer, full, full_outer, left, left_outer, right, right_outer, left_semi, left_anti
    // 一定要注意 关联的字段名相同时 又需要指定关联的方式 必须将关联的字段放入List中 作为参数传入
    stuDF
      .join(scoDF, List("id"), "left")
    //      .show()

    //将scoDF中id列改名为sid
    val newScoDF: DataFrame = scoDF.withColumnRenamed("id", "sid")
    newScoDF
      .join(stuDF, $"id" === $"sid", "right")
    //      .show()

    //DSL中实现case when
    //计算每个学生的平均分 考虑个科目总分不一样的情况（数值归一化）
    //如果平均分低于60 则 输出不及格
    //如果平均分大于等于60 则 输出及格
    //如果平均分大于等于70 则 输出中等
    //如果平均分大于等于80 则 输出良好
    //如果平均分大于等于90 则 输出优秀

    //读取科目表并构建DataFrame
    val subDF: DataFrame = spark
      .read
      .format("csv")
      .option("sep", ",")
      .schema("sub_id String,sub_name String,sub_score String")
      .load("Spark/data/subject.txt")

    //基于科目表 将所有学生的分数转化为百分制
    scoDF
      .join(subDF,"sub_id")
      .select($"id",$"sub_id",round(($"score"*100.toDouble/$"sub_score") ,2)as "new_score")
      .groupBy($"id")
      //计算平均分
      .agg(round(avg($"new_score"),2) as "avg_score")
      //实现case when
      .select($"id",$"avg_score",
        when($"avg_score">=90,"优秀")
          .when($"avg_score">=80,"良好")
          .when($"avg_score">=70,"中等")
          .when($"avg_score">=60,"及格")
          .otherwise("不及格") as "等级"
      )
      .sort($"avg_score" desc)
      .show()
  }
}

+----------+---------+----+
| id|avg_score|等级|
+----------+---------+----+
|1500100929| 84.45|良好|
|1500100308| 83.55|良好|
|1500100080| 81.67|良好|
|1500100418| 80.05|良好|
|1500100943| 79.83|中等|
|1500100136| 79.78|中等|
|1500100873| 79.55|中等|
|1500100930| 79.5|中等|
|1500100258| 78.83|中等|
|1500100875| 78.5|中等|
|1500100904| 77.61|中等|
|1500100596| 77.5|中等|
|1500100184| 77.44|中等|
|1500100834| 77.39|中等|
|1500100823| 75.95|中等|
|1500100871| 75.78|中等|
|1500100547| 75.33|中等|
|1500100949| 75.22|中等|
|1500100839| 74.33|中等|
|1500100716| 74.33|中等|
+----------+---------+----+
only showing top 20 rows