spark：计算LTV3

需求

某游戏公司的笔试题，上班的时候偷偷做的。。。昨天审题没审清，给做错了，回去重新做了发一下。

 

以下是hive表，请通过Spark Code于每日凌晨计算注册用户的LTV3，需要考虑查看单区服维度的LTV3。最终报表将以BI展示。

LTV3定义：注册角色在注册3天（含当天）的总充值金额除以当天注册用户数。

注册用户表：dwd_reg

server_id	role_id	log_time	log_date
int	str	timestamp	date
区服ID	角色ID	注册时间	分区键、注册日期

用户充值表：dwd_tran

server_id	role_id	amount	log_time	log_date
int	str	float	timestamp	date
区服ID	角色ID	充值金额	注册时间	分区键、注册日期

思路

1. 先求出每天注册总数  
2. 求出用户在注册后第三天时所有充值记录的总值  
3. 用2关联1，在结果中将总值除以注册总数
    

实现

测试数据

dwd_reg:

1 a 2020-09-01 2020-09-01
1 b 2020-09-01 2020-09-01
1 c 2020-09-02 2020-09-02
1 d 2020-09-02 2020-09-02
1 e 2020-09-03 2020-09-03
1 f 2020-09-03 2020-09-03
1 g 2020-09-04 2020-09-04
1 h 2020-09-04 2020-09-04

dwd_tran:

1 a 10.0 2020-09-01 2020-09-01
1 b 19.0 2020-09-01 2020-09-01
1 a 648.0 2020-09-02 2020-09-02
1 c 648.0 2020-09-02 2020-09-02
1 c 648.0 2020-09-02 2020-09-02
1 d 198.0 2020-09-02 2020-09-02
1 d 648.0 2020-09-03 2020-09-03
1 e 648.0 2020-09-03 2020-09-03
1 f 1000.0 2020-09-03 2020-09-03
1 g 128.0 2020-09-04 2020-09-04
1 h 6.0 2020-09-04 2020-09-04

主要逻辑

读取数据

    val dwd_reg =
      context.textFile("C:\\Users\\zixuan.wu\\Desktop\\sparkTest\\DWD_REG.txt")
      .map(s=>s.split(" "))
      .map(strings=>DWD_REG(strings(0).toInt ,strings(1),strings(2),strings(3)))
      .toDF()

    val dwd_tran =
      context.textFile("C:\\Users\\zixuan.wu\\Desktop\\sparkTest\\DWD_TRAN.txt")
        .map(s=>s.split(" "))
        .map(strings=>DWD_TRAN(strings(0).toInt ,strings(1),strings(2).toFloat,strings(3),strings(4)))
        .toDF()

1.统计各区服每天注册的用户数

    var dwd_reg_count = dwd_reg.groupBy("log_date","server_id").count()
    dwd_reg_count.show()

2.统计用户在注册后三天内所有充值记录的总值

    //先用充值表关联注册表,并过滤掉充值记录在注册时间三天后，或注册时间在当前日期前三天内的数据；然后再求每个用户的充值总值
    import org.apache.spark.sql.functions._
    val dwd_tran_sum = dwd_tran.toDF("server_id","role_id","amount","dwd_tran_log_time","dwd_tran_log_date")
      .join(dwd_reg.toDF("server_id","role_id","dwd_reg_log_time","dwd_reg_log_date"),Seq("role_id","server_id"),"left")
      .where("datediff(dwd_tran_log_date,dwd_reg_log_date)<3 and dwd_reg_log_date<=date_sub(current_date(),3)")
      .groupBy("server_id","role_id","dwd_reg_log_date")
      .agg(sum("amount").alias("amount_sum"))
    dwd_tran_sum.show()

3.将每日注册总数和用户三日充值总额表关联

关联后删除重复的server_id 字段

  val dwd_tran_reg = dwd_tran_sum.join(dwd_reg_count,
      date_sub(dwd_reg_count.col("log_date"), 3) === dwd_tran_sum.col("dwd_reg_log_date")
        && dwd_reg_count.col("server_id") === dwd_tran_sum.col("server_id")
      , "left")
        .drop(dwd_reg_count("server_id"))

  dwd_tran_reg.show()

count和log_date为空是因为造的数据不足，最大的注册时间的用户没有关联到三天后的注册人数总额。

4.用充值总额除以注册总人数，即可算出ltv3

    dwd_tran_reg.createOrReplaceTempView("dwd_tran_reg")
    val ltv3_result = session.sql("select server_id,role_id,dwd_reg_log_date,amount_sum/coalesce(count,0) as LTV3 from dwd_tran_reg")
    ltv3_result.show()

 

完整代码

package com.zixuan.spark.test


import com.zixuan.spark.bean.{DWD_REG, DWD_TRAN}
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SparkSession

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

    val conf = new SparkConf().setAppName("test").setMaster("local[1]")
    val context = new SparkContext(conf)
    //屏蔽spark的info warn信息
    context.setLogLevel("ERROR")
    val session = SparkSession.builder().config(conf).getOrCreate()
    //导入转换DF的隐式转换
    import session.implicits._

    val dwd_reg =
      context.textFile("C:\\Users\\zixuan.wu\\Desktop\\sparkTest\\DWD_REG.txt")
      .map(s=>s.split(" "))
      .map(strings=>DWD_REG(strings(0).toInt ,strings(1),strings(2),strings(3)))
      .toDF()
    //val dwd_reg = session.sql("select * from dwd_reg")
    
    val dwd_tran =
      context.textFile("C:\\Users\\zixuan.wu\\Desktop\\sparkTest\\DWD_TRAN.txt")
        .map(s=>s.split(" "))
        .map(strings=>DWD_TRAN(strings(0).toInt ,strings(1),strings(2).toFloat,strings(3),strings(4)))
        .toDF()

    //val dwd_tran = session.sql("select * from dwd_tran")
    
    //思路：1.先求出每天注册总数  2.求出用户在注册后第三天时所有充值记录的总值   3.用2关联1，在结果中将总值除以注册总数


    //1.先计算出各区服每天注册的用户数
    var dwd_reg_count = dwd_reg.groupBy("log_date","server_id").count()
    dwd_reg_count.show()

    //2.求出用户在注册后三天内所有充值记录的总值
    //先用充值表关联注册表,并过滤掉充值记录在注册时间三天后，或注册时间在当前日期前三天内的数据；然后再求每个用户的充值总值
    import org.apache.spark.sql.functions._
    val dwd_tran_sum = dwd_tran.toDF("server_id","role_id","amount","dwd_tran_log_time","dwd_tran_log_date")
      .join(dwd_reg.toDF("server_id","role_id","dwd_reg_log_time","dwd_reg_log_date"),Seq("role_id","server_id"),"left")
      .where("datediff(dwd_tran_log_date,dwd_reg_log_date)<3 and dwd_reg_log_date<=date_sub(current_date(),3)")
      .groupBy("server_id","role_id","dwd_reg_log_date")
      .agg(sum("amount").alias("amount_sum"))
    dwd_tran_sum.show()

    //3.将用户注册后三天内充值总额表 join 每日注册总额表，关联条件为 dwd_reg_log_date+2等于注册总额表的日期
    val dwd_tran_reg = dwd_tran_sum.join(dwd_reg_count,
      date_sub(dwd_reg_count.col("log_date"), 3) === dwd_tran_sum.col("dwd_reg_log_date")
        && dwd_reg_count.col("server_id") === dwd_tran_sum.col("server_id")
      , "left")
        .drop(dwd_reg_count("server_id"))

    dwd_tran_reg.show()

    //4.用充值总额除以注册总人数，即可算出ltv3
    dwd_tran_reg.createOrReplaceTempView("dwd_tran_reg")
    val ltv3_result = session.sql("select server_id,role_id,dwd_reg_log_date,amount_sum/coalesce(count,0) as LTV3 from dwd_tran_reg")
    ltv3_result.show()
  }
}

样例类

package com.zixuan.spark.bean

case class DWD_REG(server_id:Int, role_id:String, log_time:String, log_date:String)
case class DWD_TRAN(server_id:Int,role_id:String,amount:Float,log_time:String,log_date:String)

拓展

如果要计算LTV3、LTV8、LTV10、LTV15、LTV30、LTV60、LTV 180,请设计思路，如何能在凌晨快速计算完。数据量百亿级别。

思考：

1.添加一个每日注册总数表。每日更新。

无论是统计ltv几，都要考虑每日注册的总数，如果每次都重新计算，会浪费大量的时间和计算资源。像这种可复用、且只插入不更新的中间表，直接创建出来，可以节省一部分时间和资源。同理，如果集群的存储资源充足的情况下，也可以在user表中添加多个充值总额的维度，用于记录注册后x天的充值金额，使用缓慢变慢变化维或拉链表的形式更新。

2.基于1的情况下，只计算180天内的数据

ltv n从描述上来看，就是注册后n天内的充值总额除以注册后第n天的注册人数，这两部分的数据是不变的。所以，一旦计算出来，就不必每次计算。

所以，在1的基础上，只需要每次计算180天内的数据即可，180天前的历史数据可不必关心，因为结果已经计算出来并永久保存了。