我现在遇到了一个问题,关于在表minutedata几百万条大数据量情况下统计满足A表条件的数据,stationinfo表台站名a,b,c 对应不同的设备ID,每个设备一天24小时,每隔分钟采集一条数据,采集到的数据存放在minutedata中,正常情况下采集到的是1440条,异常情况下可能会超过1440,或少于1440.现在需要根据stationinfo表中的条件在minutedata统计30天内每一天每个设备ID到底采集多少条数据,统计的结果导入到另一张表中。,在存储过程中考虑过采用游标的方法,可速度太慢,统计十天每一天的记录最少竟然需要7秒,现在有没有更好的办法来统计?要求是执行速度快,不采用游标,查询的结果导入到另一表。
表数据如下:
stationinfo表
id station itemid
1 a jw0026
2 b jw0036
3 c jw0010
minutedata表
station itemid jw_date data1 data2 data3 flag
......
......
a jw0026 2008-07-17 01:45:00 33.9 35.84 21.15 1
a jw0026 2008-07-17 01:46:00 32.98 35.84 21.14 1
a jw0026 2008-07-17 01:47:00 33.6 35.84 21.06 1
a jw0026 2008-07-17 01:48:00 32.93 35.84 21.12 1
a jw0026 2008-07-17 01:49:00 32.7 35.84 21.03 1
a jw0026 2008-07-17 01:50:00 32.67 35.84 21.14 1
a jw0026 2008-07-17 01:51:00 33.09 35.85 21.14 1
a jw0026 2008-07-17 01:52:00 31.98 35.82 21.14 1
a jw0026 2008-07-17 01:53:00 32.79 35.84 21.09 1
a jw0026 2008-07-17 01:54:00 32.96 35.84 21.04 1
a jw0026 2008-07-17 01:55:00 32.64 35.84 20.96 1
a jw0026 2008-07-17 01:56:00 32.5 35.84 20.96 1
a jw0026 2008-07-17 01:57:00 32.39 35.85 21.01 1
......
b jw0036 2008-07-17 01:45:00 33.9 35.84 21.15 1
b jw0036 2008-07-17 01:46:00 32.98 35.84 21.14 1
b jw0036 2008-07-17 01:47:00 33.6 35.84 21.06 1
......
c jw0010 2008-07-17 01:45:00 33.9 35.84 21.15 1
c jw0010 2008-07-17 01:46:00 32.98 35.84 21.14 1
c jw0010 2008-07-17 01:47:00 33.6 35.84 21.06 1高手请教
表数据如下:
stationinfo表
id station itemid
1 a jw0026
2 b jw0036
3 c jw0010
minutedata表
station itemid jw_date data1 data2 data3 flag
......
......
a jw0026 2008-07-17 01:45:00 33.9 35.84 21.15 1
a jw0026 2008-07-17 01:46:00 32.98 35.84 21.14 1
a jw0026 2008-07-17 01:47:00 33.6 35.84 21.06 1
a jw0026 2008-07-17 01:48:00 32.93 35.84 21.12 1
a jw0026 2008-07-17 01:49:00 32.7 35.84 21.03 1
a jw0026 2008-07-17 01:50:00 32.67 35.84 21.14 1
a jw0026 2008-07-17 01:51:00 33.09 35.85 21.14 1
a jw0026 2008-07-17 01:52:00 31.98 35.82 21.14 1
a jw0026 2008-07-17 01:53:00 32.79 35.84 21.09 1
a jw0026 2008-07-17 01:54:00 32.96 35.84 21.04 1
a jw0026 2008-07-17 01:55:00 32.64 35.84 20.96 1
a jw0026 2008-07-17 01:56:00 32.5 35.84 20.96 1
a jw0026 2008-07-17 01:57:00 32.39 35.85 21.01 1
......
b jw0036 2008-07-17 01:45:00 33.9 35.84 21.15 1
b jw0036 2008-07-17 01:46:00 32.98 35.84 21.14 1
b jw0036 2008-07-17 01:47:00 33.6 35.84 21.06 1
......
c jw0010 2008-07-17 01:45:00 33.9 35.84 21.15 1
c jw0010 2008-07-17 01:46:00 32.98 35.84 21.14 1
c jw0010 2008-07-17 01:47:00 33.6 35.84 21.06 1高手请教
感谢你的回复.不好意思,我刚才表达可能不太清楚.比方说吧,根据stationinfo表中记录的station,itemid进行统计2009-07-01 到2009-07-31之间 每个itemid每天的数据量有多少条.不使用游标.执行速度要快。
select
convert(varchar(10),jw_date,120) as jw_date,
count(1) as 总数
from
tb
group by
convert(varchar(10),jw_date,120)
convert(varchar(10),jw_date,120) as jw_date,
count(1) as 总数
from
tb
where
jw_date
between '2009-07-01' and '2009-07-31'
group by
convert(varchar(10),jw_date,120)
SELECT
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)AS TIME,
COUNT(B.*)AS 个数 FROM
stationinfo A JOIN minutedata B
ON A.station=B.station
AND A.itemid =B.itemid ]GROUP BY
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)AS TIME
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)AS TIME,
COUNT(B.*)AS 个数 FROM
stationinfo A JOIN minutedata B
ON A.station=B.station
AND A.itemid =B.itemid ]GROUP BY
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)AS TIME,
COUNT(B.*)AS 个数 FROM
stationinfo A JOIN minutedata B
ON A.station=B.station
AND A.itemid =B.itemid GROUP BY
A.station,
A.itemid ,
CONVERT(VARCHAR(10),B.jw_date ,120)老打错,晕,楼主试试