查询时自动补全空缺数据的问题

chhrsas 2013-03-18 04:00:56
现有数据表如下
CREATE TABLE #GatherRecord(GatherTime Date not null, value int not null)

go

insert into #GatherRecord values ('2013-01-30', 30)

insert into #GatherRecord values ('2013-02-02', 1)

insert into #GatherRecord values ('2013-02-02', 2)

insert into #GatherRecord values ('2013-02-02', 3)

insert into #GatherRecord values ('2013-02-10', 10)

insert into #GatherRecord values ('2013-02-21', 20)

insert into #GatherRecord values ('2013-02-21', 21)

insert into #GatherRecord values ('2013-02-21', 22)

insert into #GatherRecord values ('2013-02-25', 25)

go

--需要列出指定月中每一天的平均值，如果表中没有这一天的值，则取之前最近一天的数据进行补充,没有则为0

--2月份的结果应该是这样

--   Date				value

-----------------------------------

--  2013-02-01			30		***

--  2013-02-02			2       

--  2013-02-03			3		***

--  2013-02-04			3		***

--  2013-02-05			3		***

--  .......						***

--  2013-02-10			10

--  2013-02-11			10		***

--  2013-02-12			10		***

--  .......						***

--  2013-02-21			21		

--  2013-02-22			22		***

--  2013-02-23			22		***

--  2013-02-24			22		***

--  2013-02-25			25

--  2013-02-26			25		***

--  2013-02-27			25		***

--  2013-02-28			25		***



--  ***是补齐的数据

请问如何才能比较快的实现，因为实际表中的数据量比较大，自己试了很多次，没能实现。
先谢谢了。
...全文
465 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
chhrsas 2013-03-19
  • 打赏
  • 举报
 回复
万分感谢。 看来要好好看文档啊。
昵称被占用了 2013-03-19
  • 打赏
  • 举报
 回复 

CREATE TABLE #GatherRecord(id int IDENTITY(1,1),GatherTime Date not null, value int not null)
go
insert into #GatherRecord values ('2013-01-30', 30)
insert into #GatherRecord values ('2013-02-02', 1)
insert into #GatherRecord values ('2013-02-02', 2)
insert into #GatherRecord values ('2013-02-02', 3)
insert into #GatherRecord values ('2013-02-10', 10)
insert into #GatherRecord values ('2013-02-21', 20)
insert into #GatherRecord values ('2013-02-21', 21)
insert into #GatherRecord values ('2013-02-21', 22)
insert into #GatherRecord values ('2013-02-25', 25)
go

;with cte as (
select dateadd(day,-a.number ,'2013-02-28') as GatherTime,
value = ISNULL(t.value ,(select top 1 value from #GatherRecord 
	where GatherTime < dateadd(day,-a.number ,'2013-02-28')
	order by GatherTime desc,id desc
	))
from  master..spt_values a left join #GatherRecord t
on t.GatherTime = dateadd(day,-a.number ,'2013-02-28')
where  a.TYPE ='p'
)
select GatherTime,AVG(value) as value
from cte 
where GatherTime between '2013-02-01' and '2013-02-28'
group by GatherTime
order by GatherTime

--结果
GatherTime	value
2013-02-01 00:00:00.000	30
2013-02-02 00:00:00.000	2
2013-02-03 00:00:00.000	3
2013-02-04 00:00:00.000	3
2013-02-05 00:00:00.000	3
2013-02-06 00:00:00.000	3
2013-02-07 00:00:00.000	3
2013-02-08 00:00:00.000	3
2013-02-09 00:00:00.000	3
2013-02-10 00:00:00.000	10
2013-02-11 00:00:00.000	10
2013-02-12 00:00:00.000	10
2013-02-13 00:00:00.000	10
2013-02-14 00:00:00.000	10
2013-02-15 00:00:00.000	10
2013-02-16 00:00:00.000	10
2013-02-17 00:00:00.000	10
2013-02-18 00:00:00.000	10
2013-02-19 00:00:00.000	10
2013-02-20 00:00:00.000	10
2013-02-21 00:00:00.000	21
2013-02-22 00:00:00.000	22
2013-02-23 00:00:00.000	22
2013-02-24 00:00:00.000	22
2013-02-25 00:00:00.000	25
2013-02-26 00:00:00.000	25
2013-02-27 00:00:00.000	25
2013-02-28 00:00:00.000	25
chhrsas 2013-03-19
  • 打赏
  • 举报
 回复
看来要好好看看With的用法了。
chhrsas 2013-03-18
  • 打赏
  • 举报
 回复
原始数据表中的是有ID字段的，我本以为省略了能描述的更清楚，结果弄巧成拙，惭愧。 
CREATE TABLE #GatherRecord(dataid int identity , GatherTime Date not null, value int not null)
go
insert into #GatherRecord values ('2013-01-30 00:00:00', 30)
insert into #GatherRecord values ('2013-02-02 00:00:00', 1)
insert into #GatherRecord values ('2013-02-02 11:00:00', 2)
insert into #GatherRecord values ('2013-02-02 22:00:00', 3)
insert into #GatherRecord values ('2013-02-10 00:00:00', 10)
insert into #GatherRecord values ('2013-02-21 13:00:00', 20)
insert into #GatherRecord values ('2013-02-21 15:00:00', 21)
insert into #GatherRecord values ('2013-02-21 18:00:00', 22)
insert into #GatherRecord values ('2013-02-25 00:00:00', 25)
昵称被占用了 2013-03-18
  • 打赏
  • 举报
 回复
如果最近一条，你的原表需要增加一个id字段，否则不能区分哪一条是最后一条
chhrsas 2013-03-18
  • 打赏
  • 举报
 回复
谢谢。 不过表中的数据应该是这样的，怪我没表达清楚。 
CREATE TABLE #GatherRecord(GatherTime Date not null, value int not null)
go
insert into #GatherRecord values ('2013-01-30 00:00:00', 30)
insert into #GatherRecord values ('2013-02-02 00:00:00', 1)
insert into #GatherRecord values ('2013-02-02 11:00:00', 2)
insert into #GatherRecord values ('2013-02-02 22:00:00', 3)
insert into #GatherRecord values ('2013-02-10 00:00:00', 10)
insert into #GatherRecord values ('2013-02-21 13:00:00', 20)
insert into #GatherRecord values ('2013-02-21 15:00:00', 21)
insert into #GatherRecord values ('2013-02-21 18:00:00', 22)
insert into #GatherRecord values ('2013-02-25 00:00:00', 25)
现在关键在于 2013-02-03 到2013-02-09 的值应该是 3 ，也就是用2号最后一条记录来补充，而不是2号的平均值。
昵称被占用了 2013-03-18
  • 打赏
  • 举报
 回复
把 where GatherTime between dateadd(day,-1,'2013-02-01') and '2013-02-28' 修改为 where GatherTime <= '2013-02-28' 就可以了
昵称被占用了 2013-03-18
  • 打赏
  • 举报
 回复
这是由于你的注释，漏了1月31日
chhrsas 2013-03-18
  • 打赏
  • 举报
 回复
引用 1 楼 Haiwer 的回复:
;with cte as ( select GatherTime,avg(value) as value from #GatherRecord where GatherTime between dateadd(day,-1,'2013-02-01') and '2013-02-28' group by GatherTime union all select DATEADD(day,1……
先谢谢了。 不过这个方法取到的数据与实际需求不符，2月1日的数据没有，而后面补全的数据应该是最近一条数据，而不是平均值。
昵称被占用了 2013-03-18
  • 打赏
  • 举报
 回复
;with cte as ( select GatherTime,avg(value) as value from #GatherRecord where GatherTime between dateadd(day,-1,'2013-02-01') and '2013-02-28' group by GatherTime union all select DATEADD(day,1,a.GatherTime) as GatherTime,a.value as value from cte a where not exists ( select 1 from #GatherRecord b where b.GatherTime = DATEADD(day,1,a.GatherTime) ) and DATEADD(day,1,a.GatherTime) <= '2013-02-28' ) select * from cte where GatherTime between '2013-02-01' and '2013-02-28' order by GatherTime
大规模知识图谱补全技术的研究进展
随着谷歌知识图谱、DBpedia、微软 Concept Graph、YAGO 等众多知识图谱的不断出现, 根据 RDF 来构建的知识表达体系越来越为人们所熟知。
第6节：点云补全实战解读.zip
第6节：点云补全实战解读.zip
2017年上半年软件设计师下午真题试卷
2017年上半年软件设计师下午真题试卷 2017年上半年软件设计师下午真题试卷 2017年上半年软件设计师下午真题试卷
字典树即字典树型的关键词搜索
字典树，类似于百度搜索引擎的原理，打进去一个字，直接出来与这个字所有有关的信息，还有，这是我们的数据结构大作业，理解了它对数据结构很有影响。
面向对象程序设计二试卷B.doc
面向对象程序设计二试卷B.doc
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章