SparkSQL统计——连续3天或以上

 前言：本章用到了开窗函数，不了解的可以去看看。

SQL开窗函数（窗口函数）详解_流水随清风的博客-CSDN博客_sql开窗函数

 

数据源下载：

链接: https://pan.baidu.com/s/1WMz8B-xJZjOoMmlaLurjZg

提取码: eetv

 

数据源展示：

 

案例实现要求：统计每月连续3天或以上的用户个数

思路讲解：

 

1.以当前时间为基准值，获取到前面两天的时间以及后面两天的时间

通过lag和lead获取，注意它们的默认值，以及over中的排序为升序。

 

2.计算连续三天：

情况1——前两天和当前时间连续（当前时间减前第一天为1，当前时间减前第二天为2）

情况2——前一天与当前时间和后一天连续（当前时间减前第一天为1，后第一天减当前时间为1） 

情况3——后两天和当前时间连续（后第一天减当前时间为1，后第2天减当前时间为2）

满足这三种情况其中之一即实现3天或以上连续

 

4.注意，我们要统计的是人数，所以要根据用户id去重

 

实现源码：

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

    val spark = SparkSession.builder().appName("lianxu").master("local[*]").enableHiveSupport().getOrCreate()

    spark.read.option("header","true").csv("/home/桌面/lianxu.csv").createOrReplaceTempView("data")


    //TODO 统计每月连续购买三天或以上的人数
    spark.sql(
      """
         select
             month,
             count(distinct(user_id)) count
          from
             (select
               month(event_time) month,
               user_id,
               to_date(event_time) event_date,
               lag(to_date(event_time),2,"1970-01-01") over(distribute by user_id sort by to_date(event_time)) lag_2,
               lag(to_date(event_time),1,"1970-01-01") over(distribute by user_id sort by to_date(event_time)) lag_1,
               lead(to_date(event_time),1,"9999-99-99") over(distribute by user_id sort by to_date(event_time)) lead_1,
               lead(to_date(event_time),2,"9999-99-99") over(distribute by user_id sort by to_date(event_time)) lead_2
             from
                data)t1
          where
             (datediff(event_date,lag_1)=1 and datediff(event_date,lag_2)=2)
             or
             (datediff(event_date,lag_1)=1 and datediff(lead_1,event_date)=1)
             or
             (datediff(lead_1,event_date)=1 and datediff(lead_2,event_date)=2)
          group by
             month
          order by
             month
       """.stripMargin).show(2000)



    spark.stop()

  }