SparkSQL实战_用户7日留存情况

需求：

根据用户的注册信息表及用户每日登录平台的数据信息来分析用户7日留存情况。

注册信息数据data/demo/registuser.csv

xxxxxxxxxx
uid,regist_day,regist_os
u1,20300401,android
u2,20300401,iphone
u3,20300401,android
u4,20300402,iphone
u5,20300402,android
u6,20300403,iphone
u7,20300403,iphone
u8,20300404,android
u9,20300404,android

登录信息部分数据data/demo/loginInfos.csv

xxxxxxxxxx
uid,login_date
u1,20300401
u1,20300401
u2,20300401
u2,20300401
u3,20300401
u1,20300402
u2,20300402
u3,20300402
u4,20300402
u5,20300402
u1,20300403

SQL函数：

1. datediff(日期1，日期2) : 计算日期1与日期2的相差天数，日期格式必须是 “yyyy-MM-dd”格式。
2. unix_timestamp(日期,"yyyyMMdd") : 按照指定的格式将日期数据转换成时间戳。
3. from_unixtime(timestamp,"yyyy-MM-dd") : 将时间戳转换成指定日期格式。

xxxxxxxxxx
package com.itbaizhan.sql.examples
import org.apache.spark.sql.{DataFrame, SparkSession}
object UserKeep7Day {
  def main(args: Array[String]): Unit = {
    //1.创建SparkSession对象
    val spark: SparkSession = SparkSession.builder()
      .master("local[*]")
      .appName("UserKeep7Day")
      .getOrCreate()
    //设置日志的级别
    spark.sparkContext.setLogLevel("Error")
    //3.读取csv文件
    val registInfos: DataFrame = spark.read.option("header",true)
      .csv("data/demo/registuser.csv")
    val loginInfos: DataFrame = spark.read.option("header",true)
      .csv("data/demo/loginInfos.csv")
    //4.注册临时表
    registInfos.createTempView("regist")
    loginInfos.createTempView("login_info")
    //5.去重 distinct uid,login_date   将同一个用户的同一天登录的数据去重保留一条即可
    spark.sql(
      """
        |select distinct uid,login_date
        |from login_info
        |""".stripMargin).createTempView("login")
    //使用login和regist两张表进行关联查询
    //spark.sql("select datediff('2030-04-04','2030-04-03')").show()
    //20300404->时间戳 ->2030-04-04
    spark.sql(
      """
        |select b.uid,b.regist_day,a.login_date,
        |datediff(from_unixtime(unix_timestamp(a.login_date,'yyyyMMdd'),'yyyy-MM-dd'),
        | from_unixtime(unix_timestamp(b.regist_day,'yyyyMMdd'),'yyyy-MM-dd')) as diff
        |from login a join regist b
        |on a.uid = b.uid
        |""".stripMargin).createTempView("temp")
    //统计注册日期 7日留存情况(不包含注册当天登录的)
    spark.sql(
      """
        |select regist_day,diff,count(uid) usercount
        |from temp
        |where diff > 0
        |and diff <= 7
        |group by regist_day,diff
        |order by regist_day,diff
        |""".stripMargin).show()
    //以上两个sql和合并为一个
    spark.sql(
      """
        |select regist_day,diff,count(uid) usercount
        |from (select b.uid,b.regist_day,a.login_date,
        |       datediff(from_unixtime(unix_timestamp(a.login_date,'yyyyMMdd'),'yyyy-MM-dd'),
        |       from_unixtime(unix_timestamp(b.regist_day,'yyyyMMdd'),'yyyy-MM-dd')) as diff
        |     from login a join regist b
        |     on a.uid = b.uid)
        |where diff > 0
        |and diff <= 7
        |group by regist_day,diff
        |order by regist_day,diff
        |""".stripMargin).show()
    spark.close()
  }
}