Microsoft SQL Window Function

Microsoft SQL Windowing Function

-- Window function can be used to created accumulated aggregates
-- Window Function can also be used to show aggregate values alongside individual records
-- Window functions can use the following clauses:
-- Partition, Over, Rows Between Preceeding and Following, Order By
-- Window Functions include AVG, SUM, Count, LAG, Lead, ETC

-- Window Accumulated Aggregates
SELECT [Product]
      ,[Low]
      ,avg(cast(low as numeric)) over(order by low) avgLow
      ,[High]
      ,sum(cast(high as numeric)) over(order by high) as sumHigh
      ,[Close]
      ,count([close]) over(order by [close]) as closeCount
      ,[Previous]
      ,avg(cast([previous] as numeric)) over(order by [previous]) avgPrevious
      ,[Direction]
      ,[Date]
  FROM [test].[dbo].[options on fx majorsii]
  -- Window Total Aggregates
SELECT [Product]
      ,[Low]
      ,avg(cast(low as numeric)) over(order by low rows between unbounded preceding and unbounded following) avgLow
      ,[High]
      ,sum(cast(high as numeric)) over(order by high rows between unbounded preceding and unbounded following) as sumHigh
      ,[Close]
      ,count([close]) over(order by [close] rows between unbounded preceding and unbounded following) as closeCount
      ,[Previous]
      ,avg(cast([previous] as numeric)) over(order by [previous] rows between unbounded preceding and unbounded following) avgPrevious
      ,[Direction]
      ,[Date]
  FROM [test].[dbo].[options on fx majorsii]

  -- Window Previous and following row aggregate.  Based on current row and 1 above and 1 below it.
SELECT [Product]
      ,[Low]
      ,avg(cast(low as numeric)) over(order by low rows between 1 preceding and 1 following) avgLow
      ,[High]
      ,sum(cast(high as numeric)) over(order by high rows between 1 preceding and 1 following) as sumHigh
      ,[Close]
      ,count([close]) over(order by [close] rows between 1 preceding and 1 following) as closeCount
      ,[Previous]
      ,avg(cast([previous] as numeric)) over(order by [previous] rows between 1 preceding and 1 following) avgPrevious
      ,[Direction]
      ,[Date]
  FROM [test].[dbo].[options on fx majorsii]