HiveSQL求用户连续到店购买天数以及与上次到店购买间隔天数

HSQL-需求：求用户连续到店购买天数以及与上次到店购买间隔天数
Step 1:对原始数据进行聚合/去重
Step 2:对用户进行分组排序
Step 3:日期与序号进行减法运算
Step 4:获取开始连续的日期以及连续天数
Step 5:使用Hive中lead函数
Step 1:对数据进行聚合/去重

重点是理解解题思路

1.对数据按天进行初步聚合或去重(因为一个用户可能在某天有多次购买行为)

select id,order_date from TABLE_NAME where d='10' group by id,order_date;

2.使用row_number()over（）函数开窗，按照用户和时间分组排序

select
*,row_number() over(partition by id order by order_date) rm
from (select id,order_date from TABLE_NAME where d='10' group by id,order_date) business

3.使用date_sub函数 登录时间减去分组序号

select
*,date_sub(orderdate,rm) diff
from
(select
*,row_number() over(partition by id order by order_date) rm
from (select id,order_date from TABLE_NAME where d='10' group by id,order_date) a ) b

4.求出连续到店客户连续的日期以及连续天数

select
id,continuous_start_date,count(continuous_start_date) as continuous_date
from(
select
id,min(order_date)over(partition by id,diff) as continuous_start_date
from
(select
*,date_sub(order_date,rm) diff
from
(select
*,row_number() over(partition by id order by order_date) rm
from (select id,order_date from TABLE_NAME where d='10' group by id,order_date) a ) b )c )d
group by name,continuous_start_date

5:使用Hive中lead函数求出客户中断的天数

lead() over()   将下一行提到当前行来另起字段 
	lead(需要调整的字段名,下几行,空余部分用什么补充)


lag() over()     将上一行提到当前行来另起字段
	lag(需要调整的字段名,上几行,空余部分用什么补充)

select
id,
continuous_start_date,
continuous_next_date,
continuous_date,
datediff(a.continuous_next_date,date_add(a.continuous_start_date,a.continuous_date)) as discontinuity_date
from
(
select
id,
continuous_start_date,
lead(continuous_start_date,1,0)over(partition by id,continuous_start_date) as continuous_next_date,
continuous_date
from(
select
id,continuous_start_date,count(continuous_start_date) as continuous_date
from(
select
id,min(order_date)over(partition by id,diff) as continuous_start_date
from
(select
*,date_sub(order_date,rm) diff
from
(select
*,row_number() over(partition by id order by order_date) rm
from (select id,order_date from TABLE_NAME where d='10' group by id,order_date) a ) b )c )d
group by name,continuous_start_date)e)f