Hql查询案例三 ： 连续销售记录查询案例

数据

店铺 销售日期 销售额
A,2017-10-11,300
A,2017-10-12,200
B,2017-10-11,400
B,2017-10-12,200
A,2017-10-13,100
A,2017-10-15,100
C,2017-10-11,350
C,2017-10-15,400
C,2017-10-16,200
D,2017-10-13,500
E,2017-10-14,600
E,2017-10-15,500
D,2017-10-14,600…

需求

求出连续三天有销售记录的店铺

实现

1.建表，加载数据

create table t_jd(shopid string,dt string,sale int)
row format delimited fields terminated by ',';

load data local inpath '/root/sale.dat' into table t_jd;

2.使用窗口函数给每条数据打上编号

select shopid,dt,sale,
row_number() over(partition by shopid order by dt) as rn 
from t_jd;

结果：
+———+————-+——-+—–+–+
| shopid | dt | sale | rn |
+———+————-+——-+—–+–+
| A | 2017-10-11 | 300 | 1 |
| A | 2017-10-12 | 200 | 2 |
| A | 2017-10-13 | 100 | 3 |
| A | 2017-10-15 | 100 | 4 |
| A | 2017-10-16 | 300 | 5 |
| A | 2017-10-17 | 150 | 6 |
| A | 2017-10-18 | 340 | 7 |
| A | 2017-10-19 | 360 | 8 |
| B | 2017-10-11 | 400 | 1 |
| B | 2017-10-12 | 200 | 2 |
| B | 2017-10-15 | 600 | 3 |
| C | 2017-10-11 | 350 | 1 |
| C | 2017-10-13 | 250 | 2 |
| C | 2017-10-14 | 300 | 3 |
| C | 2017-10-15 | 400 | 4 |
| C | 2017-10-16 | 200 | 5 |
| D | 2017-10-13 | 500 | 1 |
| D | 2017-10-14 | 600 | 2 |
| E | 2017-10-14 | 600 | 1 |
| E | 2017-10-15 | 500 | 2 |
+———+————-+——-+—–+–+
3.使用销售日期减去编号，生成连续的日期，日期相同为连续，不同为不连续

select shopid,dt,sale,rn,
date_sub(to_date(dt),rn) 
from
(select shopid,dt,sale,
row_number() over(partition by shopid order by dt) as rn 
from t_jd) tmp;

结果：
+———+————-+——-+—–+————-+–+
| shopid | dt | sale | rn | _c4 |
+———+————-+——-+—–+————-+–+
| A | 2017-10-11 | 300 | 1 | 2017-10-10 |
| A | 2017-10-12 | 200 | 2 | 2017-10-10 |
| A | 2017-10-13 | 100 | 3 | 2017-10-10 |
| A | 2017-10-15 | 100 | 4 | 2017-10-11 |
| A | 2017-10-16 | 300 | 5 | 2017-10-11 |
| A | 2017-10-17 | 150 | 6 | 2017-10-11 |
| A | 2017-10-18 | 340 | 7 | 2017-10-11 |
| A | 2017-10-19 | 360 | 8 | 2017-10-11 |
| B | 2017-10-11 | 400 | 1 | 2017-10-10 |
| B | 2017-10-12 | 200 | 2 | 2017-10-10 |
| B | 2017-10-15 | 600 | 3 | 2017-10-12 |
| C | 2017-10-11 | 350 | 1 | 2017-10-10 |
| C | 2017-10-13 | 250 | 2 | 2017-10-11 |
| C | 2017-10-14 | 300 | 3 | 2017-10-11 |
| C | 2017-10-15 | 400 | 4 | 2017-10-11 |
| C | 2017-10-16 | 200 | 5 | 2017-10-11 |
| D | 2017-10-13 | 500 | 1 | 2017-10-12 |
| D | 2017-10-14 | 600 | 2 | 2017-10-12 |
| E | 2017-10-14 | 600 | 1 | 2017-10-13 |
| E | 2017-10-15 | 500 | 2 | 2017-10-13 |
+———+————-+——-+—–+————-+–+
4.按照生成的连续日期对数据进行分组聚合，计算相同日期个数

select shopid,count(1) as cnt 
from
(select shopid,dt,sale,rn,
date_sub(to_date(dt),rn) as flag
from
(select shopid,dt,sale,
row_number() over(partition by shopid order by dt) as rn 
from t_jd) tmp) tmp2
group by shopid,flag
;

结果：
+———+——+–+
| shopid | cnt |
+———+——+–+
| A | 3 |
| A | 5 |
| B | 2 |
| B | 1 |
| C | 1 |
| C | 4 |
| D | 2 |
| E | 2 |
+———+——+–+
5.筛选出连续日期个数大于等于3的数据

select shopid from
(select shopid,count(1) as cnt 
from
(select shopid,dt,sale,rn,
date_sub(to_date(dt),rn) as flag
from
(select shopid,dt,sale,
row_number() over(partition by shopid order by dt) as rn 
from t_jd) tmp) tmp2
group by shopid,flag) tmp3
where tmp3.cnt>=3
;

结果：
+———+–+
| shopid |
+———+–+
| A |
| A |
| C |
+———+–+
6.对上一步骤的数据进行去重操作

select distinct shopid from
(select shopid,count(1) as cnt 
from
(select shopid,dt,sale,rn,
date_sub(to_date(dt),rn) as flag
from
(select shopid,dt,sale,
row_number() over(partition by shopid order by dt) as rn 
from t_jd) tmp) tmp2
group by shopid,flag) tmp3
where tmp3.cnt>=3;

最终结果：
+———+–+
| shopid |
+———+–+
| A |