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

数据库类 专栏收录该内容
2 篇文章 0 订阅

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

前言

这是我第一篇在CSDN的博客,一直想写文章好久了,可是总是迟迟不下笔,今天逼着自己写,希望记录下自己的学习过程。我个人是IT行业从业者,岗位和数据相关,技术薄弱,文中要是有哪里不足的地方,还请各位朋友斧正,共同进步,万分感谢!我个人的邮箱: 286620584@qq.com。

问题的开始是朋友那天问了我一道SQL题目,大意是想要取出连续n天销售额都满足>m的销售人员。我想了想,这道问题蛮有意思的,核心问题是对"连续"这一事实的界定。

问题的求解

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

首先新建实验要用到的数据表,这张表有三个字段。销售人员的id,即cust_id(习惯这么取名字了,工作中全是cust_XXX);销售日期,date_col。销售金额,sell_amount。

建表语句如下:

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');

数据插入后查看表数据如下:

在这里插入图片描述

新建一张表,为每个客户及其销售记录编号,往往这类问题还会涉及到记录的筛选,所以我加了个销售额要大于50的筛选条件。

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
;

查看consecutive_task_tmp2表结果如下:
在这里插入图片描述
此时,你根据cust_id和subtraction2进行分组统计,就能知道哪个销售人员在哪几天连续满足要求了。

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
;

在这里插入图片描述
若是有进有一步提取明细的需求,在这里可以将上述这个统计结果保存成一张临时表,再和原有的销售数据明细表consecutive_task关联,即可。

数学原理

这么求解,是可以得出我们想要的结果了,可是要想记得牢,可以试试研究为什么这么求解会得出正确结果,这里面可以抽象成一个数学问题:
假定销售日期从远及近排列是a,a+1,a+2,…;同时他们的列序号是1,2,3,4,5,6,…日期列减去一个天数b,得到的结果是a-b,a-b+1,a-b+2,…序号列减去这个结果则是a-b+1,a-b+1,a-b+1,…其结果总是固定的一个值a-b+1,结合数据库中的客户号(销售人员的编号)分组,即可得出该人员有哪些连续天数满足要求了。
在这里插入图片描述

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

©️2021 CSDN 皮肤主题: 1024 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值