package com.shujia.spark.sql

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

object Demo8Stu {
  def main(args: Array[String]): Unit = {

    val spark: SparkSession =SparkSession.builder()
      .appName("stu")
      .master("local")
      .getOrCreate()
    val stu: DataFrame =spark.read
      .format("csv")
      .option("sep",",")
      .schema("name String,item String,score Double")
      .load("data/stu.txt")

    stu.createOrReplaceTempView("stu")

    /**
      *
      * 1、行列转换
      * *
      * 表1
      * 姓名,科目,分数
      * name,item,score
      * 张三,数学,33
      * 张三,英语,77
      * 李四,数学,66
      * 李四,英语,78
      *
      * 表2
      * 姓名,数学,英语
      * name,math,english
      * 张三,33,77
      * 李四,66,78
      * *
      * 1、将表1转化成表2
      * 2、将表2转化成表1
      *
      */

    val stu1: DataFrame =spark.sql(
      """
        |
        |select
        |name,
        |sum(case when item = '数学' then score else 0 end) as math ,
        |sum(case when item = '英语' then score else 0 end) as english
        |from
        |stu
        |group by name
        |
        |
      """.stripMargin)
    stu1.show()

    stu1.createOrReplaceTempView("stu2")
    spark.sql(
      """
        |
        |select name,explode(map('数学',math,'英语',english)) as (item,score)  from stu1
        |
      """.stripMargin).show()



  }
}