首页 新闻 赞助 找找看

sql查一小时内60分钟的数据 把缺少的补全

0
悬赏园豆:10 [已解决问题] 解决于 2016-01-18 15:06

数据表Table 字段有【CreateTime】【CrteaterId】【AvgValue】

在正常的情况下,一分钟向Table 插入一条数据,查询一个小时内的数据应该有60条。

查询结果如下:

【CreateTime】【CrteaterId】【AvgValue】

2015-11-17 09:00:00  01  100.12

2015-11-17 09:01:00  01  100.12

2015-11-17 09:02:00  01  100.12

.....

2015-11-17 09:59:00  01  100.12

 

但是由于特殊原因,会有数据丢失的情况,比如:02分钟的数据丢失了,所以想造一条丢失的记录,只要把值变成0即可,形成完整的60条记录

2015-11-17 09:00:00  01  100.12

2015-11-17 09:01:00  01  100.12

2015-11-17 09:02:00  01  100.12  2015-11-17 09:02:00  01  0.00

.....

2015-11-17 09:59:00  01  100.12

cppwen的主页 cppwen | 初学一级 | 园豆:55
提问于:2015-11-17 13:55
< >
分享
最佳答案
1
DECLARE @t TABLE(createtime DATETIME,v int);

INSERT INTO @T (createtime,v) VALUES
('2015-11-19 09:00:00',1),
('2015-11-19 09:01:00',1),
('2015-11-19 09:02:00',1),
('2015-11-19 09:03:00',1),
('2015-11-19 09:05:00',1),
('2015-11-19 09:06:00',1),
('2015-11-19 09:07:00',1),
('2015-11-19 09:59:00',1)


; with cte as
(
select n
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as t(n)
)
,cte_h as 
(
SELECT a.n+b.n*10 as n
from cte a
cross join cte b
where a.n+b.n*10<60
)

merge @t t
using cte_h s
    on DATEPART(minute,t.createtime)=s.n 

when not MATCHED
    then insert (createtime,v)
    values('2015-11-19 09:'+cast(n as varchar(2))+':00',1);


select *
from @t
order by createtime


你看看这段代码,适当修改一下,能否解决你的问题?

 

收获园豆:10
悦光阴 | 老鸟四级 |园豆:2251 | 2015-11-23 11:07
其他回答(2)
-1

那就造啊,没有人挡住你不让你做吧?找人砍他?

爱编程的大叔 | 园豆:30839 (高人七级) | 2015-11-17 14:27

是要形成完整的60条记录

支持(0) 反对(0) cppwen | 园豆:55 (初学一级) | 2015-11-17 14:30

还是大叔幽默

支持(0) 反对(0) 明Lam | 园豆:615 (小虾三级) | 2015-11-17 14:44
0
DECLARE @t TABLE(createtime DATETIME,v int);

INSERT INTO @T (createtime,v) VALUES
('2015-11-19 09:00:00',1),
('2015-11-19 09:01:00',1),
('2015-11-19 09:02:00',1),
('2015-11-19 09:03:00',1),
('2015-11-19 09:05:00',1),
('2015-11-19 09:06:00',1),
('2015-11-19 09:07:00',1),
('2015-11-19 09:59:00',1),
('2015-11-19 10:10:00',1),
('2015-11-19 10:20:00',1)
;WITH cte AS (
    SELECT r1=DATEDIFF(mi,'2015-11-19 09:00:00',createtime),r2=ROW_NUMBER() OVER(ORDER BY createtime),* FROM @T
), c AS (
SELECT rn=ROW_NUMBER() OVER(ORDER BY r1-r2),r1-r2 AS ct,MAX(createtime) AS createtime FROM cte GROUP BY r1-r2
)
SELECT DATEADD(mi,x.number+1,u.createtime) AS t,0 AS v FROM 
(SELECT b.ct-a.ct AS ti,a.createtime FROM c a JOIN c b ON a.rn=b.rn-1) u
 JOIN master..spt_values x ON x.number<u.ti AND x.TYPE='p'

注意边界的地方,如果时间太长了,spt_values不够可以考虑自己构造一个表来处理。

gw2010 | 园豆:1487 (小虾三级) | 2015-11-19 15:50
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册