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

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

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

 

数据源下载：

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

提取码: eetv

 

数据源展示：

 

案例实现要求：统计连续3月或以上都有购买记录的用户个数

 

思路讲解：

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

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

 

2.计算连续3月：

情况1——前两月和当前月份连续

（当前日期减前第一个月份的结果再减1)除以2）<=  30

   and

（当前日期减前第二个月份的结果再减1)除以3）<=30 

 

情况2——前一月与当前日期及后一月连续

（当前日期减前第一个月份的结果再减1)除以2）<=  30

   and

（后第一个月减当前日期的结果再减1)除以2）<= 30 

 

情况3——后两月和当前日期连续

（后第一个月减当前日期的结果再减1)除以2）<=30 

   and

（后第二个月减当前日期的结果再减1)除以3）<=30 

 

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

 

3.根据用户id进行去重

 

答疑：

1.为什么判断连续条件要减去1  且  <= 30 ？

答：两个连续月份之间最大的跨度为61天，如：7.1——8.31 ，我们减去1，然后除去月份个数2，刚刚好可以完成这个过滤操作。

       三个连续月份之间最大的跨度为91天，如：7.1——9.30，我们减去1，然后除去月份个数3，也是刚刚好可以完成这个过滤操作。

如图所示

 

 

实现源码：

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


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



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


    //TODO 统计出连续3月都有购买记录的用户个数
    spark.sql(
      """
         select
            count(distinct(user_id)) count
         from
             (select
                user_id,
                event_date,
                lag(event_date,2,"1970-01-01") over(distribute by user_id sort by event_date) lag_2,
                lag(event_date,1,"1970-01-01") over(distribute by user_id sort by event_date) lag_1,
                lead(event_date,1,"9999-99-99") over(distribute by user_id sort by event_date) lead_1,
                lead(event_date,2,"9999-99-99") over(distribute by user_id sort by event_date) lead_2
             from
                (select
                  user_id,
                  to_date(event_time) event_date
                from
                  data)t1 )t2
         where
             (datediff(event_date,lag_1)-1)/2 <= 30 and (datediff(event_date,lag_2)-1)/3 <=30
             or
             (datediff(event_date,lag_1)-1)/2 <= 30 and (datediff(lead_1,event_date)-1)/2 <=30
             or
             (datediff(lead_1,event_date)-1)/2 <= 30 and (datediff(lead_2,event_date)-1)/3 <=30
        """.stripMargin).show()


    spark.stop()


  }

 

其实无论判断连续几个月份都没关系，掌握这个运算规则就行了～