案例 求连续登录三天以上的用户--SparkSQL实现(SQL风格)

部分数据展示
uid,dt
guid01,2018-03-01
guid01,2018-02-28
guid01,2018-03-01
guid01,2018-03-04
guid01,2018-03-02
guid01,2018-03-05
guid01,2018-03-06
guid01,2018-03-07
guid02,2018-03-01
guid02,2018-03-02
guid02,2018-03-03
guid02,2018-03-06
...

import org.apache.spark.sql.SparkSession

object SQLContinuedLogin {
  def main(args: Array[String]): Unit = {
    val session = SparkSession.builder()
      .appName(this.getClass.getSimpleName)
      .master("local[*]")
      .getOrCreate() //一个进程里只有一个

    val df = session.read.option("header",true).csv("data/data1.txt")
    df.createTempView("tb_user_login")
    session.sql(
      s"""
         |select
         |  uid,
         |  min(dt) start_date,
         |  max(dt) end_date,
         |  count(1) times
         |from
         |  (
         |    select
         |      uid,
         |      dt,
         |      date_sub(dt,rn) date_dif -- 日期减行号
         |    from
         |      (
         |        select
         |          uid,
         |          dt,
         |          row_number() over(partition by uid order by dt asc) rn
         |        from
         |          (
         |            select
         |              distinct(uid,dt),uid,dt  -- 去重用户和登录日期相同的数据
         |            from
         |              tb_user_login
         |          )
         |      )
         |  )
         |group by uid,date_dif
         |having times >= 3
        |""".stripMargin).show()

    session.stop()


  }

}

过程解析

1、
select 
  distinct(uid,dt),uid,dt  -- 去重用户和登录日期相同的数据
from
  tb_user_login
  
--执行结果
+------------------------------+------+----------+
|named_struct(uid, uid, dt, dt)|   uid|        dt|
+------------------------------+------+----------+
|          [guid04, 2018-03-04]|guid04|2018-03-04|
|          [guid03, 2018-03-08]|guid03|2018-03-08|
|          [guid03, 2018-03-07]|guid03|2018-03-07|
|          [guid02, 2018-03-02]|guid02|2018-03-02|
|          [guid01, 2018-03-06]|guid01|2018-03-06|
|          [guid03, 2018-03-06]|guid03|2018-03-06|
|          [guid01, 2018-03-01]|guid01|2018-03-01|
|          [guid04, 2018-03-08]|guid04|2018-03-08|
...

2、
select
  uid,
  dt,
  row_number() over(partition by uid order by dt asc) rn
from
  (
    select
      distinct(uid,dt),uid,dt  -- 去重用户和登录日期相同的数据
    from
      tb_user_login
  )
--执行结果
+------+----------+---+
|   uid|        dt| rn|
+------+----------+---+
|guid02|2018-03-01|  1|
|guid02|2018-03-02|  2|
|guid02|2018-03-03|  3|
|guid02|2018-03-06|  4|
|guid03|2018-03-05|  1|
|guid03|2018-03-06|  2|
|guid03|2018-03-07|  3|
|guid03|2018-03-08|  4|
|guid03|2018-03-09|  5|
|guid03|2018-03-10|  6|
...

3、
select
  uid,
  dt,
  date_sub(dt,rn) date_dif -- 日期减行号
from
  (
   select
     uid,
     dt,
     row_number() over(partition by uid order by dt asc) rn
   from
     (
      select
        distinct(uid,dt),uid,dt  -- 去重用户和登录日期相同的数据
      from
        tb_user_login
     )
  )
--执行结果
+------+----------+----------+
|   uid|        dt|  date_dif|
+------+----------+----------+
|guid02|2018-03-01|2018-02-28|
|guid02|2018-03-02|2018-02-28|
|guid02|2018-03-03|2018-02-28|
|guid02|2018-03-06|2018-03-02|
|guid03|2018-03-05|2018-03-04|
|guid03|2018-03-06|2018-03-04|
|guid03|2018-03-07|2018-03-04|
|guid03|2018-03-08|2018-03-04|
|guid03|2018-03-09|2018-03-04|
|guid03|2018-03-10|2018-03-04|
...

4、
select
  uid,
  min(dt) start_date,
  max(dt) end_date,
  count(1) times
from
  (
    select
      uid,
      dt,
      date_sub(dt,rn) date_dif -- 日期减行号
    from
      (
        select
          uid,
          dt,
          row_number() over(partition by uid order by dt asc) rn
        from
          (
            select
              distinct(uid,dt),uid,dt  -- 去重用户和登录日期相同的数据
            from
              tb_user_login
          )
      ) 
  ) 
group by uid,date_dif
having times >= 3


--执行结果
+------+----------+----------+-----+
|   uid|start_date|  end_date|times|
+------+----------+----------+-----+
|guid02|2018-03-01|2018-03-03|    3|
|guid03|2018-03-05|2018-03-10|    6|
|guid04|2018-03-08|2018-03-10|    3|
|guid01|2018-02-28|2018-03-02|    3|
|guid01|2018-03-04|2018-03-07|    4|
+------+----------+----------+-----+