如何仅对SQL中的连续行取平均值

Question

如何仅对SQL Server中的连续行进行平均。例如，我有下面的表:

<表类> ID 价值 tbody><<tr>1212231414

我想我已经用CTE解决了LAG和LEAD的问题。但是，我不知道查询是否有效。我认为它可以升级。但就目前而言，它是服务于目的，并显示给定输入的期望输出=>

DECLARE @MYTable Table(ID INT,[VALUE] INT);
INSERT INTO @MYTable VALUES (1,2);
INSERT INTO @MYTable VALUES (1,2);
INSERT INTO @MYTable VALUES (2,3);
INSERT INTO @MYTable VALUES (1,4);
INSERT INTO @MYTable VALUES (1,4);
WITH CTE AS
(
SELECT *, 
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM @MYTable)
SELECT ID,  Average FROM
(SELECT *,
LEAD(ID, 1) OVER(ORDER BY rownum ASC) AS NEXTID,
LEAD([Value], 1) OVER(ORDER BY rownum ASC) AS NEXTValue,
CASE WHEN ID=LAG(ID, 1) OVER(ORDER BY rownum ASC) THEN -1
ELSE 1 END IsSelect,
CASE WHEN ID=LEAD(ID, 1) OVER(ORDER BY rownum ASC)
THEN ([Value]+LEAD([Value], 1) OVER(ORDER BY rownum ASC))/2
WHEN LEAD(ID, 1) OVER(ORDER BY rownum ASC) IS NULL THEN NULL
WHEN LEAD(ID, 1) OVER(ORDER BY rownum ASC) IS NOT NULL  AND 
ID<>LEAD(ID, 1) OVER(ORDER BY rownum ASC) THEN [Value]
ELSE NULL END Average
FROM CTE) T1
WHERE IsSelect=1

新更新:我认为我以前的代码现在可以在某些情况下工作。所以，我已经更新了我的查询。请检查一下这个。

DECLARE @MYTable Table(ID INT,[VALUE] INT);
INSERT INTO @MYTable VALUES (1,2);
INSERT INTO @MYTable VALUES (1,2);
--INSERT INTO @MYTable VALUES (1,2);
INSERT INTO @MYTable VALUES (2,3);
INSERT INTO @MYTable VALUES (1,4);
INSERT INTO @MYTable VALUES (1,4);
--INSERT INTO @MYTable VALUES (2,3);
--INSERT INTO @MYTable VALUES (1,2);
WITH CTE AS
(
SELECT *, 
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM @MYTable)
,MyY AS
(SELECT
rownum, ID,[VALUE],
CASE WHEN rownum = 1 OR ID <> LAG(ID, 1) OVER (ORDER BY rownum)
THEN 'New'
ELSE 'Continuation'
END ISCONTI
FROM
CTE), Z AS
(SELECT *,
CASE WHEN ID=LAG(ID, 1) OVER(ORDER BY rownum ASC) THEN (Select top 1 rownum FROM MyY Where MyY.ID=y.ID and MyY.ISCONTI='New' AND y.rownum>MyY.rownum ORDER BY rownum DESC) 
WHEN LAG(ID, 1) OVER(ORDER BY rownum ASC) IS NULL THEN rownum
WHEN ID<>LAG(ID, 1) OVER(ORDER BY rownum ASC) THEN rownum
ELSE 0 END MyGroup
FROM MyY y)
, W AS
(SELECT ID,MyGroup,
CASE WHEN ID=LAG(ID, 1) OVER(ORDER BY rownum ASC) THEN -1
ELSE 1 END IsSelect
,SUM(Z.[Value]) OVER(PARTITION BY Z.MyGroup,Z.ID)/COUNT(Z.[Value]) OVER(PARTITION BY Z.MyGroup,Z.ID) MyAVG
FROM Z) SELECT ID,MyAVG FROM W WHERE IsSelect=1;