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