2 篇文章 0 订阅

# 关于SQL中,连续天数问题的求解

## 问题的求解

1 将数据按照天数排序，并利用数据库row_number()函数为销售人员的销售记录编号排序。
2 用当前时间减去随意给定的一个时间，得到一个日期间差值，在这里为了表述方便，我们可以记这个差值为subtraction1。这里减去的这个日期是可以随意指定的，但是个人认为最好取今天now会好一些。
3 利用步骤1中，得到的row_number列，减去2中的差值subtraction1，即可得到另一列的差值subtraction2。
4 根据cust_id，和列subtraction2分组，即可得到每个人员的连续天数。

drop table if exists consecutive_task ;
create table consecutive_task
(
cust_id varchar(10)
,date_col date
,sell_amount int
,primary key(cust_id,date_col)
)
;

insert into consecutive_task values('c001','2019-01-01','100');
insert into consecutive_task values('c001','2019-01-03','20');
insert into consecutive_task values('c001','2019-01-04','76');
insert into consecutive_task values('c001','2019-01-05','80');
insert into consecutive_task values('c001','2019-01-06','75');
insert into consecutive_task values('c002','2019-01-01','58');
insert into consecutive_task values('c002','2019-01-02','60');
insert into consecutive_task values('c003','2019-01-01','49');
insert into consecutive_task values('c003','2019-01-02','51');
insert into consecutive_task values('c003','2019-01-03','60');
insert into consecutive_task values('c004','2019-01-01','100');
insert into consecutive_task values('c005','2019-01-01','30');
insert into consecutive_task values('c006','2019-01-01','51');
insert into consecutive_task values('c006','2019-01-02','52');
insert into consecutive_task values('c006','2019-01-03','50');
insert into consecutive_task values('c007','2019-01-01','78');
insert into consecutive_task values('c007','2019-01-02','80');
insert into consecutive_task values('c007','2019-01-03','69');
insert into consecutive_task values('c007','2019-01-04','66');
insert into consecutive_task values('c007','2019-01-05','30');
insert into consecutive_task values('c008','2019-01-01','20');
insert into consecutive_task values('c008','2019-01-04','50');
insert into consecutive_task values('c008','2019-01-05','52');
insert into consecutive_task values('c008','2019-01-07','53');
insert into consecutive_task values('c008','2019-01-08','55');



drop table if exists consecutive_task_tmp1;
create table consecutive_task_tmp1
(
cust_id varchar(10)
,date_col date
,sell_amount int
,row_num int
)
;

insert into consecutive_task_tmp1
(
cust_id
,date_col
,sell_amount
,row_num
)
select t1.cust_id
,t1.date_col
,t1.sell_amount
,row_number() over(partition by t1.cust_id order by t1.date_col asc)
from consecutive_task as t1
where t1.sell_amount >= 50
;


drop table if exists consecutive_task_tmp2;
create table consecutive_task_tmp2
(
cust_id varchar(10)
,date_col date
,sell_amount int
,row_num int
,subtraction1 int
,subtraction2 int
)
;

insert into consecutive_task_tmp2
(
cust_id
,date_col
,sell_amount
,row_num
,subtraction1
,subtraction2
)
select t1.cust_id
,t1.date_col
,t1.sell_amount
,t1.row_num
,datediff(t1.date_col,now())
,t1.row_num - datediff(t1.date_col,now())
from consecutive_task_tmp1 as t1
-- order by t1.cust_id,t1.date_col
;


select a.cust_id,a.subtraction2,count(1)
from consecutive_task_tmp2 as a
group by a.cust_id,a.subtraction2
having count(1) > 1
;


## 数学原理

• 0
点赞
• 1
评论
• 2
收藏
• 一键三连
• 扫一扫，分享海报

07-21 7493
03-30 1300

08-26 1万+
07-25 2066
09-04 6107
11-20 1279
08-15 1万+
11-14 1577
04-03 3210
05-12 6090
©️2021 CSDN 皮肤主题: 1024 设计师:白松林

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。