DSL练习、array函数、map函数、sum() 在 over(窗口函数) 中的两种用法、expr()--转换成列的对象、when(表达式,为true返回的值).otherwise(为false返回的值)--用法
DSL练习
统计每科都及格的学生 需要显示[学号，姓名，班级，性别，科目名，科目分数]
package com.shujia.sql
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SaveMode, SparkSession}
object Demo6Student {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession
.builder()
.master("local")
.appName("student")
.config("spark.sql.shuffle.partitions", 1)
.getOrCreate()
import spark.implicits._
import org.apache.spark.sql.functions._
//读取学生表
val student: DataFrame = spark
.read
.format("csv")
.option("sep", ",")
.schema("id STRING,name STRING,age INT,gender STRING, clazz STRING")
.load("data/students.txt")
//读取分数表
val score: DataFrame = spark.read
.format("csv")
.option("sep", ",")
.schema("sId STRING,cId STRING ,sco DOUBLE")
.load("data/score.txt")
//读取科目表
val subject: DataFrame = spark.read
.format("csv")
.option("sep", ",")
.schema("cId STRING,cName STRING ,sumSco DOUBLE")
.load("data/subject.txt")
/**
* 统计每科都及格的学生 需要显示[学号，姓名，班级，性别，科目名，科目分数]
*/
//1、关联分数表和科目表判断学生科目是否及格
val resultDF: DataFrame = score
//关联科目表
.join(subject, "cId")
//保留及格的分数
.where($"sco" >= $"sumSco" * 0.6)
//计算学生及格的科目数 -- 使用 withColumn 在后面再加一列
.withColumn("c", count($"sId") over Window.partitionBy($"sId"))
//取出都及格的学生
.where($"c" === 6)
//关联学生表获取学生信息
.join(student, $"sId" === $"id")
//整理数据
.select($"id", $"name", $"clazz", $"gender", $"cName", $"sco")
//保存数据
resultDF
.write
.format("csv")
.mode(SaveMode.Overwrite)
.option("sep", "\t")
.save("data/temp1")
}
}
公司代码,年度,1月 -------------------------> 12月的收入金额
burk,year,tsl01,tsl02,tsl03,tsl04,tsl05,tsl06,tsl07,tsl08,tsl09,tsl10,tsl11,tsl12
853101,2010,100200,25002,19440,20550,14990,17227,40990,28778,19088,29889,10990,20990
853101,2011,19446,20556,14996,17233,40996,28784,19094,28779,19089,29890,10991,20991
853101,2012,19447,20557,14997,17234,20560,15000,17237,28780,19090,29891,10992,20992
853101,2013,20560,15000,17237,41000,17234,20560,15000,17237,41000,29892,10993,20993
853101,2014,19449,20559,14999,17236,41000,28788,28786,19096,29897,41000,28788,20994
853101,2015,100205,25007,19445,20555,17236,40999,28787,19097,29898,29894,10995,20995
853101,2016,100206,25008,19446,20556,17237,41000,28788,19098,29899,29895,10996,20996
853101,2017,100207,25009,17234,20560,15000,17237,41000,15000,17237,41000,28788,20997
853101,2018,100208,25010,41000,28788,28786,19096,29897,28786,19096,29897,10998,20998
853101,2019,100209,25011,17236,40999,28787,19097,29898,28787,19097,29898,10999,20999
846271,2010,100210,25012,17237,41000,28788,19098,29899,28788,19098,29899,11000,21000
846271,2011,100211,25013,19451,20561,15001,17238,41001,28789,19099,29900,11001,21001
846271,2012,100212,100213,20190,6484,46495,86506,126518,166529,206540,246551,286562,326573
846271,2013,100213,100214,21297,5008,44466,83924,123382,162839,202297,241755,281213,320671
846271,2014,100214,100215,22405,3531,42436,81341,120245,159150,198055,236959,275864,314769
846271,2015,100215,100216,23512,2055,19096,29897,28786,19096,29897,41000,29892,308866
846271,2016,100216,100217,24620,579,38377,76175,28788,28786,19096,29897,41000,302964
846271,2017,100217,100218,25727,898,36347,73592,40999,28787,19097,29898,29894,297062
846271,2018,100218,100219,26835,2374,34318,71009,41000,28788,19098,29899,29895,291159
846271,2019,100219,100220,27942,3850,32288,68427,17237,41000,15000,17237,41000,2852571、统计每个公司每年按月累计收入 行转列 --> sum窗口函数
输出结果
公司代码,年度,月份,当月收入,累计收入
2、统计每个公司当月比上年同期增长率 行转列 --> lag窗口函数
输出结果
公司代码,年度,月度,增长率（当月收入/上年当月收入 - 1）
1、行列转换
表1
姓名,科目,分数
name,item,score
张三,数学,33
张三,英语,77
李四,数学,66
李四,英语,78表2
姓名,数学,英语
name,math,english
张三,33,77
李四,66,781、将表1转化成表2
2、将表2转化成表1
要想完成这个需求，则需要了解以下知识点
/**
* array -- 构建一个数组
*
*/
spark.sql(
"""
|select explode(array(1,2,3,4,5,6,7,8))
|
""".stripMargin).show()
/**
* map -- 构建一个 map 集合
*/
spark.sql(
"""
|
|select explode(map('001','张三','002','李四','003','王五'))
|
""".stripMargin).show()
/**
*
* sum() 在 over(窗口函数) 有两种情况
* 1、只分区不排序，全局求和
* 2，分区且排序，累加求和
*
*/
// 将map拿到外面定义
// map中要的是 字段(列)的对象的可变参数
// 但是这里 1 -- 基本数据类型
// 所以这边需要用 expr("1") 去转换一下
// 将 1 转成列的对象
val m: Column = map(
expr("1"), $"tsl01",
expr("2"), $"tsl02",
expr("3"), $"tsl03",
expr("4"), $"tsl04",
expr("5"), $"tsl05",
expr("6"), $"tsl06",
expr("7"), $"tsl07",
expr("8"), $"tsl08",
expr("9"), $"tsl09",
expr("10"), $"tsl10",
expr("11"), $"tsl11",
expr("12"), $"tsl12"
)
这里只有代码中的if，没有if函数，所以使用case……when……[在DSL中是when(表达式,为true返回的值).otherwise(为false返回的值)]
package com.shujia.sql
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{Column, DataFrame, SparkSession}
import org.apache.spark.storage.StorageLevel
object Demo7Burk {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession
.builder()
.master("local")
.appName("student")
.config("spark.sql.shuffle.partitions", 1)
.getOrCreate()
import spark.implicits._
import org.apache.spark.sql.functions._
//1、读取数据
//技巧: Ctrl + F -- VSCode 查找替换的快捷键 -- 快捷处理 schema 中的字段数据
val burk: DataFrame = spark.read
.format("csv")
.option("sep", ",")
.schema("burk STRING,year STRING,tsl01 DOUBLE,tsl02 DOUBLE,tsl03 DOUBLE,tsl04 DOUBLE,tsl05 DOUBLE,tsl06 DOUBLE,tsl07 DOUBLE,tsl08 DOUBLE,tsl09 DOUBLE,tsl10 DOUBLE,tsl11 DOUBLE,tsl12 DOUBLE")
.load("data/burk.txt")
//对多次使用的 DF 进行缓存 -- 优化
burk.persist(StorageLevel.MEMORY_ONLY_SER)
/**
* array -- 构建一个数组
*
*/
spark.sql(
"""
|select explode(array(1,2,3,4,5,6,7,8))
|
""".stripMargin).show()
/**
* map -- 构建一个 map 集合
*/
spark.sql(
"""
|
|select explode(map('001','张三','002','李四','003','王五'))
|
""".stripMargin).show()
/**
* 先以SQL的方式实现
*
*/
//将 DF 注册成一个临时的视图
burk.createOrReplaceTempView("burk")
/**
*
* sum() 在 over(窗口函数) 有两种情况
* 1、只分区不排序，全局求和
* 2，分区且排序，累加求和
*
*/
/**
*
* 1、统计每个公司每年按月累计收入 行转列 --> sum窗口函数
* 输出结果
* 公司代码,年度,月份,当月收入,累计收入
*
*/
spark.sql(
"""
|
|select
|burk,year,month,pic,sum(pic) over(partition by burk,year order by month) as sumPic
|from (
|select
|burk,year,month,pic
|from
|burk lateral view explode(map(1,tsl01,2,tsl02,3,tsl03,4,tsl04,5,tsl05,6,tsl06,7,tsl07,8,tsl08,9,tsl09,10,tsl10,11,tsl11,12,tsl12)) t1 as month,pic
|) as a
|
""".stripMargin)
//.show()
/**
* 再以DSL实现
*
*/
// 将map拿到外面定义
// map中要的是 字段(列)的对象的可变参数
// 但是这里 1 -- 基本数据类型
// 所以这边需要用 expr("1") 去转换一下
// 将 1 转成列的对象
val m: Column = map(
expr("1"), $"tsl01",
expr("2"), $"tsl02",
expr("3"), $"tsl03",
expr("4"), $"tsl04",
expr("5"), $"tsl05",
expr("6"), $"tsl06",
expr("7"), $"tsl07",
expr("8"), $"tsl08",
expr("9"), $"tsl09",
expr("10"), $"tsl10",
expr("11"), $"tsl11",
expr("12"), $"tsl12"
)
burk
//将多列转换多行
.select($"burk", $"year", explode(m) as Array("month", "pic"))
//计算累计金额
.withColumn("sumPic", sum($"pic") over Window.partitionBy($"burk", $"year").orderBy($"month"))
//.show(100)
/**
* 2、统计每个公司当月比上年同期增长率 行转列 --> lag窗口函数
* 公司代码,年度,月度,增长率（当月收入/上年当月收入 - 1）
*/
burk
//将多列转换多行
.select($"burk", $"year", explode(m) as Array("month", "pic"))
//湖区上年同期的收入金额
.withColumn("lastPic", lag($"pic", 1, 0) over Window.partitionBy($"burk", $"month").orderBy($"year"))
//计算增长率
.withColumn("p", $"pic" / $"lastPic" - 1)
//整理数据 -- 这里只有代码中的if，没有if函数，所以使用case……when……[在DSL中是when(表达式,为true返回的值).otherwise(为false返回的值)]
.select($"burk", $"year", $"month", when($"p".isNull, 1.0).otherwise(round($"p", 5)) as "p")
.show(1000)
}
}
