数据表统计值变化时间

Question

CREATE TABLE [History](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[TagKey] [nvarchar](100) NOT NULL,
[Value] [varchar](100) NOT NULL,
[TagStatus] [tinyint] NULL,
[Timestamp] [datetime] NOT NULL
}
每隔5~20秒向表中插入一次记录（每次有2000条记录，就是说每隔5~20秒向表中插入2000条记录），目前表中有6000万条数据。tagstatus的值有0和1两种
怎么统计tagstatus=1的持续时间
ID TagKey Value TimeStamp TagStatus
804985 YK0611701102 7.93 2014-3-17 22:28:31 0
803520 YK0611701102 7.93 2014-3-17 22:29:39 1
805278 YK0611701102 7.93 2014-3-17 22:30:41 1
805864 YK0611701102 7.93 2014-3-17 22:31:01 1
805571 YK0611701102 7.93 2014-3-17 22:31:51 0
805288 YK0611701102 7.93 2014-3-17 22:32:11 1
805856 YK0611701102 7.93 2014-3-17 22:32:31 1
805978 YK0611701102 7.93 2014-3-17 22:32:51 0统计为
ID TagKey Value startTime EndTime
803520 YK0611701102 7.93 2014-3-17 22:29:39 2014-3-17 22:31:01
803521 YK0611701102 7.93 2014-3-17 22:32:11 2014-3-17 22:32:31

Answer 1

WITH a1 (ID,TagKey,[Value],[TimeStamp],TagStatus) AS
(
SELECT 804985,'YK0611701102',7.93,'2014-3-17 22:28:31',0 UNION ALL
SELECT 803520,'YK0611701102',7.93,'2014-3-17 22:29:39',1 UNION ALL 
SELECT 805278,'YK0611701102',7.93,'2014-3-17 22:30:41',1 UNION ALL
SELECT 805864,'YK0611701102',7.93,'2014-3-17 22:31:01',1 UNION ALL
SELECT 805571,'YK0611701102',7.93,'2014-3-17 22:31:51',0 UNION ALL
SELECT 805288,'YK0611701102',7.93,'2014-3-17 22:32:11',1 UNION ALL
SELECT 805856,'YK0611701102',7.93,'2014-3-17 22:32:31',1 UNION ALL
SELECT 805978,'YK0611701102',7.93,'2014-3-17 22:32:51',0
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY [TimeStamp]) re
FROM a1
)
,a3 AS
(
SELECT *,re-ROW_NUMBER() OVER(ORDER BY [re]) re2
FROM a2
WHERE TagStatus=1
)
,a4 AS
(
SELECT re2,MIN([TimeStamp]) startTime,MAX([TimeStamp]) EndTime,MIN(re) re
FROM a3
GROUP BY re2
HAVING COUNT(*)>1
)
SELECT b.ID,b.TagKey,b.[Value],a.startTime,a.EndTime 
FROM a4 a
JOIN a3 b ON a.re=b.re

Answer 2

Quote: 引用 1 楼 lzw_0736 的回复:

with as 和 row_number() over (order by )使用的真是太厉害了