蚂蚁森林植物申领统计
使用spark sql DSL完成以下作业
背景说明
下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。
table_name:user_low_carbon
字段名 | 字段描述 |
---|---|
user_id | 用户 |
data_dt | 日期 |
low_carbon | 减少碳排放(g) |
蚂蚁森林植物换购表,用于记录申领环保植物所需要减少的碳排放量
table_name: plant_carbon
字段名 | 字段描述 |
---|---|
plant_id | 植物编号 |
plant_name | 植物名 |
plant_carbon | 换购植物所需要的碳 |
数据样例
user_low_carbon:
user_id | date_dt | low_carbon |
---|---|---|
u_001 | 2017/1/1 | 10 |
u_001 | 2017/1/2 | 150 |
u_001 | 2017/1/2 | 110 |
u_001 | 2017/1/2 | 10 |
u_001 | 2017/1/4 | 50 |
u_001 | 2017/1/4 | 10 |
u_001 | 2017/1/6 | 45 |
u_001 | 2017/1/6 | 90 |
u_002 | 2017/1/1 | 10 |
u_002 | 2017/1/2 | 150 |
u_002 | 2017/1/2 | 70 |
u_002 | 2017/1/3 | 30 |
u_002 | 2017/1/3 | 80 |
u_002 | 2017/1/4 | 150 |
u_002 | 2017/1/5 | 101 |
u_002 | 2017/1/6 | 68 |
plant_carbon:
plant_id | plant_name | plant_carbon |
---|---|---|
p001 | 梭梭树 | 17 |
p002 | 沙柳 | 19 |
p003 | 樟子树 | 146 |
p004 | 胡杨 | 215 |
题目一
蚂蚁森林植物申领统计
问题:假设2017年1月1日开始记录低碳数据(user_low_carbon),假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨,
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息;以及他比后一名多领了几颗沙柳。
得到的统计结果如下表样式:
user_id plant_count less_count(比后一名多领了几颗沙柳)
u_101 1000 100
u_088 900 400
u_103 500 …
题目二
蚂蚁森林低碳用户排名分析,
问题:查询user_low_carbon表中每日流水记录,条件为:
用户在2017年,连续三天(或以上)的天数里,
每天减少碳排放(low_carbon)都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下,因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g:
user_id data_dt low_carbon
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
答案:
package com.sql
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SparkSession}
import scala.language.postfixOps
object HomeWork {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession
.builder
.master("local")
.appName("maYi")
.config("spark.sql.shuffle.partitions", 1)
.getOrCreate
import org.apache.spark.sql.functions._
import spark.implicits._
//读取数据
val userDF: DataFrame = spark
.read
.format("csv")
.option("sep", "\t")
.schema("uid STRING, data STRING, low_c STRING")
.load("data/mayi_anli/user_low_carbon.txt")
val plantDF: DataFrame = spark
.read
.format("csv")
.option("sep", "\t")
.schema("pid STRING, pname STRING, p_c STRING")
.load("data/mayi_anli/plant_carbon.txt")
userDF.show()
plantDF.show()
/**
* 蚂蚁森林植物申领统计
问题:假设2017年1月1日开始记录低碳数据(user_low_carbon),假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨,
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息;以及他比后一名多领了几颗沙柳。
*
*/
userDF
//格式化日期,取出小于10月的月份,
.where((month(from_unixtime(unix_timestamp($"data","yyyy/mm/dd"),"yyyy-mm-dd"))) <10)
//按照人分区,求出总的减少碳排放量
.groupBy($"uid")
.agg(sum($"low_c") as "sum_c")
//求出所有人领了多少颗的p002-沙柳
.withColumn("plant_count",when($"sum_c" > 215,round(($"sum_c"-215) / 19)).otherwise("不满足申领条件"))
//按照p002-沙柳棵树降序排列,求出下一人的领取量
.withColumn("shang_duo",lag($"plant_count",-1) over Window.orderBy($"sum_c".desc))
//将自己的领取量减去下家的领取量,得出比下家多的量
.withColumn("less_count", $"plant_count" -$"shang_duo" )
//取出想要的字段
.select($"uid",$"plant_count",$"less_count")
.limit(10)
//.show()
/**
*蚂蚁森林低碳用户排名分析,
问题:查询user_low_carbon表中每日流水记录,条件为:
用户在2017年,连续三天(或以上)的天数里,
每天减少碳排放(low_carbon)都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下,因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g:
*
*/
userDF
//将日期格式化,并按照uid,data 进行分组求出每个人每天的总排碳量
.groupBy($"uid",(from_unixtime(unix_timestamp($"data","yyyy/mm/dd"),"yyyy-mm-dd")) as "date")
.agg(sum($"low_c") as "sum_c")
//去除每天减碳量没达到 100 的人
.where($"sum_c" >= 100)
//按照每人来分组,在后面打大上标记
.withColumn("rm",row_number() over Window.partitionBy($"uid").orderBy($"date"))
//将date中的 天 取出来 减去 rm 就可以将连续的天数求出
.withColumn("fenZu",dayofyear($"date") - $"rm")
//取出连续3天排碳量都大于100的人
.withColumn("lx>3",count($"fenZu") over Window.partitionBy($"uid",$"fenZu"))
.where($"lx>3" >= 3)
.select($"uid" as "user_id", $"date" as "data_dt",$"sum_c" as "low_carbon")
.show(1000)
}
}
数据
user:
u_001 2017/1/1 10
u_001 2017/1/2 150
u_001 2017/1/2 110
u_001 2017/1/2 10
u_001 2017/1/4 50
u_001 2017/1/4 10
u_001 2017/1/6 45
u_001 2017/1/6 90
u_002 2017/1/1 10
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_002 2017/1/6 68
u_003 2017/1/1 20
u_003 2017/1/2 10
u_003 2017/1/2 150
u_003 2017/1/3 160
u_003 2017/1/4 20
u_003 2017/1/5 120
u_003 2017/1/6 20
u_003 2017/1/7 10
u_003 2017/1/7 110
u_004 2017/1/1 110
u_004 2017/1/2 20
u_004 2017/1/2 50
u_004 2017/1/3 120
u_004 2017/1/4 30
u_004 2017/1/5 60
u_004 2017/1/6 120
u_004 2017/1/7 10
u_004 2017/1/7 120
u_005 2017/1/1 80
u_005 2017/1/2 50
u_005 2017/1/2 80
u_005 2017/1/3 180
u_005 2017/1/4 180
u_005 2017/1/4 10
u_005 2017/1/5 80
u_005 2017/1/6 280
u_005 2017/1/7 80
u_005 2017/1/7 80
u_006 2017/1/1 40
u_006 2017/1/2 40
u_006 2017/1/2 140
u_006 2017/1/3 210
u_006 2017/1/3 10
u_006 2017/1/4 40
u_006 2017/1/5 40
u_006 2017/1/6 20
u_006 2017/1/7 50
u_006 2017/1/7 240
u_007 2017/1/1 130
u_007 2017/1/2 30
u_007 2017/1/2 330
u_007 2017/1/3 30
u_007 2017/1/4 530
u_007 2017/1/5 30
u_007 2017/1/6 230
u_007 2017/1/7 130
u_007 2017/1/7 30
u_008 2017/1/1 160
u_008 2017/1/2 60
u_008 2017/1/2 60
u_008 2017/1/3 60
u_008 2017/1/4 260
u_008 2017/1/5 360
u_008 2017/1/6 160
u_008 2017/1/7 60
u_008 2017/1/7 60
u_009 2017/1/1 70
u_009 2017/1/2 70
u_009 2017/1/2 70
u_009 2017/1/3 170
u_009 2017/1/4 270
u_009 2017/1/5 70
u_009 2017/1/6 70
u_009 2017/1/7 70
u_009 2017/1/7 70
u_010 2017/1/1 90
u_010 2017/1/2 90
u_010 2017/1/2 90
u_010 2017/1/3 90
u_010 2017/1/4 90
u_010 2017/1/4 80
u_010 2017/1/5 90
u_010 2017/1/5 90
u_010 2017/1/6 190
u_010 2017/1/7 90
u_010 2017/1/7 90
u_011 2017/1/1 110
u_011 2017/1/2 100
u_011 2017/1/2 100
u_011 2017/1/3 120
u_011 2017/1/4 100
u_011 2017/1/5 100
u_011 2017/1/6 100
u_011 2017/1/7 130
u_011 2017/1/7 100
u_012 2017/1/1 10
u_012 2017/1/2 120
u_012 2017/1/2 10
u_012 2017/1/3 10
u_012 2017/1/4 50
u_012 2017/1/5 10
u_012 2017/1/6 20
u_012 2017/1/7 10
u_012 2017/1/7 10
u_013 2017/1/1 50
u_013 2017/1/2 150
u_013 2017/1/2 50
u_013 2017/1/3 150
u_013 2017/1/4 550
u_013 2017/1/5 350
u_013 2017/1/6 50
u_013 2017/1/7 20
u_013 2017/1/7 60
u_014 2017/1/1 220
u_014 2017/1/2 120
u_014 2017/1/2 20
u_014 2017/1/3 20
u_014 2017/1/4 20
u_014 2017/1/5 250
u_014 2017/1/6 120
u_014 2017/1/7 270
u_014 2017/1/7 20
u_015 2017/1/1 10
u_015 2017/1/2 20
u_015 2017/1/2 10
u_015 2017/1/3 10
u_015 2017/1/4 20
u_015 2017/1/5 70
u_015 2017/1/6 10
u_015 2017/1/7 80
u_015 2017/1/7 60
植物:
p001 梭梭树 17
p002 沙柳 19
p003 樟子树 146
p004 胡杨 215