如何实现求连续3小时最大累计值的记录

--如何实现求连续3小时最大累计值的记录：
GO
CREATE TABLE [dbo].[TB_RUNDATA](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [STCD] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [TM] [datetime] NOT NULL,
    [Z] [decimal](9, 3) NOT NULL,
 CONSTRAINT [PK_TB_RUNDATA] PRIMARY KEY CLUSTERED
(
    [STCD] ASC,
    [TM] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[TB_RUNDATA] on

Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(7, 'ST101', '2009-10-24 00:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(8, 'ST101', '2009-10-24 01:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(9, 'ST101', '2009-10-24 02:00:00', 2.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(10, 'ST101', '2009-10-24 03:00:00', 3.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(11, 'ST101', '2009-10-24 04:00:00', 5.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(12, 'ST101', '2009-10-24 05:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(13, 'ST101', '2009-10-24 06:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(14, 'ST101', '2009-10-24 07:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(15, 'ST101', '2009-10-24 08:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(16, 'ST101', '2009-10-24 09:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(17, 'ST101', '2009-10-24 10:00:00', 34.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(18, 'ST101', '2009-10-24 11:00:00', 5.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(19, 'ST101', '2009-10-24 12:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(1, 'ST101', '2009-10-24 13:00:00', 11.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(5, 'ST101', '2009-10-24 14:00:00', 31.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(3, 'ST101', '2009-10-24 15:00:00', 30.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(20, 'ST101', '2009-10-24 16:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(21, 'ST101', '2009-10-24 17:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(22, 'ST101', '2009-10-24 18:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(23, 'ST101', '2009-10-24 19:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(24, 'ST101', '2009-10-24 20:00:00', 12.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(25, 'ST101', '2009-10-24 21:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(26, 'ST101', '2009-10-24 22:00:00', 0.000);
Insert TB_RUNDATA ([ID], [STCD], [TM], [Z]) Values(27, 'ST101', '2009-10-24 23:00:00', 0.000);

select row_number()over(order by TM asc) as num,* from [dbo].[TB_RUNDATA]



 select *from [dbo].[TB_RUNDATA]

drop table TB_RUNDATA


----求连续3小时最大累计值的记录
declare @mintime int
select @mintime=min(datepart(hh,TM)) from [TB_RUNDATA]
print @mintime





--方法一：

;with T
as
(
    select *,num=row_number() over (partition by [STCD] order by [TM])
    from [TB_RUNDATA]
),
M as
(
    select a.*
    from T a join T b on a.STCD = b.STCD and b.num = a.num + 1 and datediff(hh,a.TM,b.TM) = 1
    union
    select a.*
    from T a join T b on a.STCD = b.STCD and a.num = b.num + 1 and datediff(hh,b.TM,a.TM) = 1
),
N as
(
    select a.STCD,a.num,a.Z+b.Z+c.Z Z
    from M a join M b on a.STCD = b.STCD and a.num + 1 = b.num
        join M c on a.STCD = c.STCD and a.num + 2 = c.num
)

select a.*
from M a join N b on a.STCD = b.STCD and a.num between b.num and b.num + 2
where not exists (select 1 from N where STCD = b.STCD and Z > b.Z)


--方法二：

SELECT * FROM TB_RUNDATA a
    INNER JOIN (
    SELECT * from
      (
        SELECT *,(SELECT SUM(z) FROM tb_rundata b WHERE b.Tm BETWEEN a.tm
        AND dateadd(hh,2,a.tm)) sumb
        from TB_RUNDATA a
        )b
     WHERE NOT EXISTS (
                SELECT *
                from TB_RUNDATA a WHERE b.sumb < (SELECT SUM(z) FROM tb_rundata b
                WHERE b.Tm BETWEEN a.tm AND dateadd(hh,2,a.tm))
        )) b ON a.Tm BETWEEN b.tm AND dateadd(hh,2,b.tm)
        
/*
ID    STCD    TM    Z    num
5    ST101    2009-10-24 14:00:00.000    31.000    15
3    ST101    2009-10-24 15:00:00.000    30.000    16
1    ST101    2009-10-24 13:00:00.000    11.000    14

*/

自己写的一种方法

if OBJECT_ID('test')is not null
drop table test
go
create table test(
dates date,
value int
)
go
insert test
select '2012-01-01',2 union all
select '2012-01-02',3 union all
select '2012-01-03',2 union all
select '2012-01-04',7 union all
select '2012-01-05',6 union all
select '2012-01-07',3 union all
select '2012-01-09',5 union all
select '2012-01-10',6 union all
select '2012-01-11',8 union all
select '2012-01-13',4 union all
select '2012-01-14',9 union all
select '2012-01-15',4 union all
select '2012-01-16',1 union all
select '2012-01-18',2
go 


;with t
as(
select 
dateadd(dd,-ROW_NUMBER()over(order by dates asc),dates) as px,
* from test
),m
as(
select id=ROW_NUMBER()over(partition by px order by dates asc),
px,dates,value from t where px in(
select px from(
select px,
min(dates) as startdate,MAX(dates) as enddate
from t a group by px)a where datediff(dd,a.startdate,a.enddate)>=2)
),
g as(
select px,dates,
(select SUM(value) from m s 
where s.id between a.id-2 and a.id and a.px=s.px) as totals from m a
where exists(select 1 from m b where a.px=b.px and b.dates=DATEADD(DD,-2,a.dates))
)
select top 3 dates,totals from g
order by totals desc

/*
dates totals
---------------------------
2012-01-11 19
2012-01-15 17
2012-01-05 15
*/