SQL 查询本月无数据用上个月的数据

SQL 查询本月无数据用上个月的数据

前言

因为标题有长度限制,先简要说明一下应用场景。比如我们要查一段时间范围内(2013-08-01至2013-12-31)每个月每个运营商的用户总量,每个运营商用户量每个月更新的时间可能不同也可能该月没有数据,如果某个运营商本月用户量没有值,则用上个月的用户量。

下面就给大家一步一步分析,解决该问题。

1.  创建基础的表和示例数据

首先我们需要创建一个用户量表,具体的表结构和表内容如下:

 

2.  分析题目要求

通过对前言中描述的应用场景分析,我们可以得出以下几点讯息:

l  获取一个时间范围内的各个运营商的各个月的数据

l  个别运营商本月无数据的用距离本月日期最近的数据

结合上边的示例数据分析,也就是如果我们要求2013-09-01到2013-12-31之间各个运营商这几个月的数据得到的结果应该是如下图所示:

 

上图中红框标注的部分在数据库中是不存在的,也就是说12月份数据库中没有数据,我们就使用距离12月最近的数据。

3.  逐步解决问题

题目要求的是求一个时间段内的几个月的各运营商的用户总量,这样我们先把问题分解,先求出一个月的各运营商的用户总量。

3.1要求一个月的各运营商的用户总量,我们先求出该月或者据该月最近的用户总量更新的日期。

为了更好的做演示示例,12月份没有数据,我们就用12月份来做演示。

具体代码如下:

select SP_Name as name,MAX(Create_Date) as "group" from UserCount
where Create_Date<'2013-12-31'
group by SP_Name

  

执行结果如下:

 

3.2求出该月的最近的更新日期后,求出相对应的值.

具体代码如下:

with sub as
(
select SP_Name as name,MAX(Create_Date) as "group" from UserCount
where Create_Date<'2013-12-31'
group by SP_Name
)
  select us.SP_Name as "name",'201312' as "date" ,SUM(us.User_Count) as "value"
  from UserCount us
  right join sub s on s.name=us.SP_Name
  where us.Create_Date=s."group"
 group by us.SP_Name,s."group"

  

执行结果如下:

 

这样我们就完成的题目所需的求出某个月的用户量,且该月没有值,采用距离该月日期最近的更新日期的值。

3.3 循环求出每个月的用户量,最后将几个月的用户量合并

但是,问题又来了,既然我们求出了某一个月的值,那么如何求出一个时间范围内也就是几个月的用户量呢?

很明显,这需要通过循环分别查出时间范围内每一个月的值,然后将这些值Union就可以了。为了考虑到实用性,我们将这一部分的操作封装在一个函数里。

具体代码如下:

create procedure getMonthData(@str1 varchar(200),@str2 varchar(200))--传入两个日期范围的字符串,形如:20130901,
as
declare @date1 date;--定义两个日期变量,用来将传入的字符串转换为时间,并作为循环的控制条件
declare @date2 date;
begin
set @date1=DATEADD("mm",1,cast(@str1+'01' as date));--处理传入的字符串,转换为时间默认为,
set @date2=DATEADD("mm",1,cast(@str2+'01' as date));
create table #temp1 (name varchar(200),"group" varchar(200),"value" int);--创建一个临时表用来存储每次循环产生的数据,相当于将每个月查询出的数据union
while @date1<=@date2
    begin
    with sub as --求出当前月,用户量最新的更新日期
    (
    select SP_NAME as "name",MAX(Create_Date) as "value"
    from UserCount
    where Create_Date<@date1
    group by SP_NAME
    ),
    sub1 as --求出当前月各运营商的用户总量
    (
    select SP_NAME as "name",@str1 as "group",MAX(User_Count) as "value"
    from UserCount ue
    right join sub s on s."name"=ue.SP_NAME
    group by ue.SP_NAME
    )
    insert into  #temp1 select * from sub1 --将当前月的用户总量插入的临时表保存
    set @date1=dateadd("mm",1,@date1)--更改循环条件
    set @str1=cast(CAST(@str1 as int )+1 as varchar(200))--更改月份
    end
select * from #temp1;--查询临时表,展现所有的数据
end

  

具体结果如下:

 

这样就基本上上完成的题目的要求了,但是,这还不是最完美的,请注意上边黄色高亮部分,如果日期为参数为201309,201401时,查询出来的结果就是这样的。

 

也就是说在日前跨年的情况下,月份的展示是乱的,这样我们可以再创建一个函数处理这样的问题。

具体代码如下:

create  function modifydate(@date varchar(200))
returns varchar(200)
as
begin
declare @month int;
set @month=cast(SUBSTRING(@date,5,2) as int)
if @month>12
begin
set @date=cast(cast(SUBSTRING(@date,1,4) as int)+@month/12 as varchar) + (case when @month%12=0 then '01' when @month%12<10 then '0'+CAST(@month%12 as varchar)   else CAST(@month%12 as varchar) end)
end
return @date;
end

  

这样就很好的解决了跨年时显示的日期格式不正确的问题,同时还需要将上边高亮的那一行代码改为如下代码即可:

set @str1=dbo.modifydate(cast(CAST(@str1 as int )+1 as varchar(200)))--更改月份

 

   

    好了,今天就总结到这里了,希望能给大家一些帮助。主要是一个思路吧,之前写过一个同样需求的sql,当时就只是用一个sql语句就实现了,但是时间长了,忘了怎么做的,回头找找,再跟大家分享。

没有更多推荐了,返回首页