spark-蚂蚁森林植物申领统计

蚂蚁森林植物申领统计

使用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

posted @ 2022-07-17 22:55 a-tao必须奥利给阅读(155) 评论(0) 收藏举报

刷新页面返回顶部

atao-BigData

人生如逆旅，你我亦行人。

spark-蚂蚁森林植物申领统计

蚂蚁森林植物申领统计

背景说明

数据样例

题目一

题目二

答案：

数据

公告