CODEWITHSUNDEEP
sqlsql-server
jy06115197
jy06115197

Reputation: 17

How to average only for the consecutive rows in SQL

How to average only for the consecutive rows in SQL Server. For instance, I have the following table:

ID Value
1 2
1 2
2 3
1 4
1 4

I want the output:

ID Average
1 2
2 3
1 4

Thank you

Upvotes: 0

Views: 210

Answers (1)

Srijon Chakraborty
Srijon Chakraborty

Reputation: 2154

I think I have solved the problem using LAG and LEAD with CTE. However, I don't know is the query is efficient or not. It can be upgrade I think. But for the moment It is serving the purpose and displaying the desire output for the given input=>

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

New Update: I think my previous code might now work in some scenarios. So, I have updated my query. Please check this one. I think this one will work for every single scenario.

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;

Upvotes: 3

Related Questions