RANGE BETWEEN、ROWS BETWEEN

DECLARE @Gamer TABLE
(
  Id INT IDENTITY(1, 1) PRIMARY KEY ,
  [Name] NVARCHAR(50) ,
  Gender NVARCHAR(10) ,
  GameScore INT
)

INSERT  INTO @Gamer VALUES  ( 'Name01', 'Male', 1500 );
INSERT  INTO @Gamer VALUES  ( 'Name02', 'Male', 2600 );
INSERT  INTO @Gamer VALUES  ( 'Name03', 'Male', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name04', 'Female', 1500 );
INSERT  INTO @Gamer VALUES  ( 'Name05', 'Female', 3350 );
INSERT  INTO @Gamer VALUES  ( 'Name06', 'Female', 3350 );
INSERT  INTO @Gamer VALUES  ( 'Name07', 'Female', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name08', 'Male', 3500 );
INSERT  INTO @Gamer VALUES  ( 'Name09', 'Male', 3450 );
INSERT  INTO @Gamer VALUES  ( 'Name10', 'Male', 2500 );

SELECT  * ,
        AVG(GameScore) OVER(ORDER BY GameScore) AS AAA,--預設
        AVG(GameScore) OVER(ORDER BY GameScore RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS BBB,--這個跟預設的一樣
        AVG(GameScore) OVER(ORDER BY GameScore ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CCC,--ROWS 是把相同的值當作不同的個體(私心覺得這個邏輯比較正確)
        AVG(GameScore) OVER(ORDER BY GameScore ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DDD
FROM    @Gamer