HiveSQL小练习--求连续消费的天数

数据格式：
1 2019-02-08 6214.23
1 2019-02-08 6247.32
1 2019-02-09 85.63
1 2019-02-09 967.36
1 2019-02-10 85.69
1 2019-02-12 769.85
1 2019-02-13 943.86
1 2019-02-14 538.42
1 2019-02-15 369.76
1 2019-02-16 369.76
1 2019-02-18 795.15
1 2019-02-19 715.65
1 2019-02-21 537.71
2 2019-02-08 6214.23
2 2019-02-08 6247.32
2 2019-02-09 85.63
2 2019-02-09 967.36
2 2019-02-10 85.69
2 2019-02-12 769.85
2 2019-02-13 943.86
2 2019-02-14 943.18
2 2019-02-15 369.76
2 2019-02-18 795.15
2 2019-02-19 715.65
2 2019-02-21 537.71
3 2019-02-08 6214.23
3 2019-02-08 6247.32
3 2019-02-09 85.63
3 2019-02-09 967.36
3 2019-02-10 85.69
3 2019-02-12 769.85
3 2019-02-13 943.86
3 2019-02-14 276.81
3 2019-02-15 369.76
3 2019-02-16 369.76
3 2019-02-18 795.15
3 2019-02-19 715.65
3 2019-02-21 537.71

建表语句：
create table deal_tb(
id string
,datestr string
,amount string
)row format delimited fields terminated by ‘,’;

需求以及结果样式：
需求：比如原数据中8号、9号、10号都进行了消费，就算做是连续消费，算是一组数据，但11号没有消费，12号进行了消费，那十二号之后的连续消费算作下一组

答案：
1.先是按照id号和日期进行分组，在对消费做一个求和
select id,datestr,sum(amount) as sum_amount
from deal_tb
group by id,datestr

2.按照id号分组，日期排序打上一个标签
select t1.id
,t1.datestr
,t1.sum_amount
,row_number() over(partition by id order by datestr) as rn
from(
select id
,datestr
,sum(amount) as sum_amount
from deal_tb
group by id,datestr
) t1

3.拿前面的时间减去后面的标签，连续消费的记录就会被标记到一起，都是一样的，就是一个区
select tt1.id
,tt1.datestr
,tt1.sum_amount
,date_sub(tt1.datestr,rn) as grp
from(
select t1.id
,t1.datestr
,t1.sum_amount
,row_number() over(partition by id order by datestr) as rn
from(
select id
,datestr
,sum(amount) as sum_amount
from deal_tb
group by id,datestr
) t1
) tt1

4.加上连续消费的天数，以及连续消费了哪几天
select ttt1.id
,ttt1.grp
,round(sum(ttt1.sum_amount),2) as sc_sum_amount
,count(1) as sc_days
,min(ttt1.datestr) as sc_start_date
,max(ttt1.datestr) as sc_end_date
from(
select tt1.id
,tt1.datestr
,tt1.sum_amount
,date_sub(tt1.datestr,rn) as grp
from(
select t1.id
,t1.datestr
,t1.sum_amount
,row_number() over(partition by id order by datestr) as rn
from(
select id
,datestr
,sum(amount) as sum_amount
from deal_tb
group by id,datestr
) t1
) tt1
) ttt1
group by ttt1.id,ttt1.grp;

感谢阅读，我是啊帅和和，一位大数据专业大四学生，祝你快乐。