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月份来做演示。

具体代码如下:

[sql][/sql] view plaincopy

  1. select SP_Name as name,MAX(Create_Date) as “group” from UserCount
  2. where Create_Date<‘2013-12-31’
  3. group by SP_Name

执行结果如下:

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

具体代码如下:

[sql][/sql] view plaincopy

  1. with sub as
  2. (
  3. select SP_Name as name,MAX(Create_Date) as “group” from UserCount
  4. where Create_Date<‘2013-12-31’
  5. group by SP_Name
  6. )
  7.   select us.SP_Name as “name”,’201312′ as “date” ,SUM(us.User_Count) as “value”
  8.   from UserCount us
  9.   right join sub s on s.name=us.SP_Name
  10.   where us.Create_Date=s.”group”
  11.  group by us.SP_Name,s.”group”

执行结果如下:

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

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

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

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

具体代码如下:

[sql][/sql] view plaincopy

  1. create procedure getMonthData(@str1 varchar(200),@str2 varchar(200))–传入两个日期范围的字符串,形如:20130901,
  2. as
  3. declare @date1 date;–定义两个日期变量,用来将传入的字符串转换为时间,并作为循环的控制条件
  4. declare @date2 date;
  5. begin
  6. set @date1=DATEADD(“mm”,1,cast(@str1+’01’ as date));–处理传入的字符串,转换为时间默认为,
  7. set @date2=DATEADD(“mm”,1,cast(@str2+’01’ as date));
  8. create table #temp1 (name varchar(200),”group” varchar(200),”value” int);–创建一个临时表用来存储每次循环产生的数据,相当于将每个月查询出的数据union
  9. while @date1<=@date2
  10.     begin
  11.     with sub as –求出当前月,用户量最新的更新日期
  12.     (
  13.     select SP_NAME as “name”,MAX(Create_Date) as “value”
  14.     from UserCount
  15.     where Create_Date<@date1
  16.     group by SP_NAME
  17.     ),
  18.     sub1 as –求出当前月各运营商的用户总量
  19.     (
  20.     select SP_NAME as “name”,@str1 as “group”,MAX(User_Count) as “value”
  21.     from UserCount ue
  22.     right join sub s on s.”name”=ue.SP_NAME
  23.     group by ue.SP_NAME
  24.     )
  25.     insert into  #temp1 select * from sub1 –将当前月的用户总量插入的临时表保存
  26.     set @date1=dateadd(“mm”,1,@date1)–更改循环条件
  27.     set @str1=cast(CAST(@str1 as int )+1 as varchar(200))–更改月份
  28.     end
  29. select * from #temp1;–查询临时表,展现所有的数据
  30. end

具体结果如下:

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

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

具体代码如下:

[sql][/sql] view plaincopy

  1. create  function modifydate(@date varchar(200))
  2. returns varchar(200)
  3. as
  4. begin
  5. declare @month int;
  6. set @month=cast(SUBSTRING(@date,5,2) as int)
  7. if @month>12
  8. begin
  9. 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)
  10. end
  11. return @date;
  12. end

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

[sql][/sql] view plaincopy

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

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

标签