sql 统计算法，求给个方法或者思路
ID Speed Time
1 10 2013/1/23 10:00:00
2 20 2013/1/23 10:20:00
3 50 2013/1/23 10:50:00
4 60 2013/1/23 11:20:00
5 29 2013/1/23 12:20:00
6 45 2013/1/24 10:20:00
7 48 2013/1/24 10:49:00
8 39 2013/1/24 11:00:00
9 41 2013/1/24 12:20:00
10 29 2013/1/24 12:25:00
11 31 2013/1/24 13:20:00
12 60 2013/1/24 15:20:00
13 10 2013/1/25 1:00:00
大概数据如上，客户可根据自己输入的速度和时间来定义超速，并查询统计结果，如当输入速度30，时间为30分钟：则表示速度大于等于30时，且持续时间在30分钟以上为超速，大概统计结果
Num Time
1 2013/1/23
2 2013/1/24
0 2013/1/25
23号ID为3、4时满足要求，总共有1条记录；24号ID为6、7、8、9和11、12时满足要求，总共2条记录；25号时没有满足数据，则记0
该表数据量很大，有没有好的思路和解决方案 算法 sql 优化查询
[解决办法]
----------------------------
-- Author :磊仔
-- Date :2013-01-24 22:38:56
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
--Sep 16 2010 20:09:22
--Copyright (c) 1988-2008 Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：#TA
if object_id('tempdb.dbo.#TA') is not null drop table #TA
go
create table #TA([ID] int,[Speed] int,[Time] datetime)
insert #TA
select 1,10,'2013/1/23 10:00:00' union all
select 2,20,'2013/1/23 10:20:00' union all
select 3,50,'2013/1/23 10:50:00' union all
select 4,60,'2013/1/23 11:20:00' union all
select 5,29,'2013/1/23 12:20:00' union all
select 6,45,'2013/1/24 10:20:00' union all
select 7,48,'2013/1/24 10:49:00' union all
select 8,39,'2013/1/24 11:00:00' union all
select 9,41,'2013/1/24 12:20:00' union all
select 10,29,'2013/1/24 12:25:00' union all
select 11,31,'2013/1/24 13:20:00' union all
select 12,60,'2013/1/24 15:20:00' union all
select 13,10,'2013/1/25 1:00:00'
--------------开始查询--------------------------
;with cet as(
select convert(varchar(10),time,120) as time,
ID - ROW_NUMBER()OVER(partition by convert(varchar(10),time,120) ORDER BY Time)RN
from #TA WHERE Speed > 30)
,cet2 as(
select COUNT(distinct rn)Num,time
from cet
group by time)
select ISNULL(b.Num,0)Num, a.Time from (select distinct convert(varchar(10),time,120) as Time from #TA) a
left join cet2 b on a.Time = b.time
----------------结果----------------------------
/*
Num Time
----------- ----------
1 2013-01-23
2 2013-01-24
0 2013-01-25
(3 行受影响)*/
