Window function

WINDOW_SUM

Used as a part of a window function to calculate a running sum over a specified window or partition of rows in a result set.

Syntax – WINDOW SUM(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are computing the cumulative sum.

Example Queries:

• We are computing the cumulative sum:
  WINDOW_SUM(SUM([Retail].[Sales]),1,2)
  OVER(PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
• Computes the sum of sales for the last 2 rows, current row, and next row per region:
  WINDOW_SUM(SUM([Retail].[Sales]), 2, 1)
  OVER(PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
• Computes the sum of last 4 rows and current row by region:
  WINDOW_SUM(SUM([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY [Retail].[Order Date])
• Computes the sum of last 6 rows and current row for each category:
  WINDOW_SUM(SUM([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY SUM([Retail].[Order Discount]))
• Computes the sum of last 5 rows and current row per shipping mode:
  WINDOW_SUM(SUM([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY SUM([Retail].[Sales]))

WINDOW_AVG

To calculate a running average over a specified window or partition of rows in a result set. The exact syntax and availability may vary depending on the specific database system.

Syntax – WINDOW_AVG(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are computing the running average.

Example Queries:

• We are computing the running average:
  WINDOW_AVG(AVG([Retail].[Sales]),1,2)
  over(partition by [Retail].[Region] order by MAX([Retail].[Sales]))
• Computes the average sales for the last 2 rows, current row, and next row per region:
  WINDOW_AVG(AVG([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY AVG([Retail].[Sales]))
• Computes the average of last 4 rows and current row by region:
  WINDOW_AVG(AVG([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY AVG([Retail].[Quantity]))
• Computes the Average of last 6 rows and current row for each category:
  WINDOW_AVG(AVG([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY AVG([Retail].[Order Discount]))
• Computes the AVG of last 5 rows and current row per shipping mode:
  WINDOW_AVG(AVG([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY AVG([Retail].[Sales]))

WINDOW_COUNT

To calculate a running count of rows over a specified window or partition in a result set. The exact syntax and availability may vary depending on the specific database system.

Syntax – WINDOW_COUNT(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are calculating the running count.

Example Queries:

• We are computing the running count:
  WINDOW_COUNT(COUNT([Retail].[Sales]),1,2)
  OVER (PARTITION BY [Retail].[Region] order by COUNT([Retail].[Sales]))
• Computes the count of sales for the last 2 rows, current row, and next row per region.:
  WINDOW_COUNT(COUNT([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY COUNT([Retail].[Sales]))
• Computes the Count of last 4 rows and current row by region:
  WINDOW_COUNT(COUNT([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY COUNT([Retail].[Quantity]))
• Computes the Count of last 6 rows and current row for each category:
  WINDOW_COUNT(COUNT([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY COUNT([Retail].[Order Discount]))
• Computes the Count of last 5 rows and current row per shipping mode:
  WINDOW_Count(Count([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY Count([Retail].[Sales]))

WINDOW_LAST_VALUE

To retrieve the value of a specified expression for the last row within a specified window or partition of rows in a result set. The exact syntax and availability may vary depending on the specific database system.

Syntax – WINDOW_LAST_VALUE(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are retrieving the value of a specified expression for the last row within a specified window.

Example Queries:

• Computes the last value of sales for the last 2 rows, current row, and next row per region:
  WINDOW_LAST_VALUE(SUM([Retail].[Sales]),1,2)
  OVER (PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
• Computes the last value of the last 4 rows and current row for each customer:
  WINDOW_LAST_VALUE(SUM([Retail].[Sales]), 2, 1)
  OVER(PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
• Computes the last value of the last 6 rows and current row for each category:
  WINDOW_LAST_VALUE(SUM([Retail].[Quantity]), 4, 0)
  OVER(PARTITION BY [Retail].[Customer id] ORDER BY SUM([Retail].[Quantity]))
• Computes the last value of the last 7 rows and current row per shipping mode:
  WINDOW_LAST_VALUE(SUM([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY SUM([Retail].[Order Discount]))
• Computes the last value of the last 5 rows and current row per shipping mode:
  WINDOW_LAST_VALUE(SUM([Retail].[Sales]), 5, 0) OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY SUM([Retail].[Sales]))

WINDOW_MAX

To calculate the maximum value of a specified expression for a specified window or partition of rows in a result set.

Syntax – WINDOW_LAST_VALUE(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are calculating the maximum value of a specified expression for a specified window.

Example Queries:

• We are computing the Maximum sumL:
  WINDOW_MAX(MAX([Retail].[Sales]),1,2)
  OVER (PARTITION BY [Retail].[Region] order by MAX([Retail].[Sales]))
• Computes the maximum sales for the last 2 rows, current row, and next row per region:
  WINDOW_MAX(MAX([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY MAx([Retail].[Sales]))
• Computes the max of last 4 rows and current row by:
  WINDOW_MAX(MAX([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY [Retail].[Order Date])
• Computes the MAX of last 6 rows and current row for each category:
  WINDOW_MAX(MAX([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY MAX([Retail].[Order Discount]))
• Computes the MAX of last 5 rows and current row per shipping mode:
  WINDOW_MAX(MAX([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY MAX([Retail].[Sales]))

WINDOW_MIN

To calculate the minimum value of a specified expression for a specified window or partition of rows in a result set.

Syntax – WINDOW_MIN(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are calculating the minimum value of a specified expression for a specified window.

Example Queries:

• We are computing the Minimum sum:
  WINDOW_MIN(MIN([Retail].[Sales]),1,2)
  over(partition by [Retail].[Region] order by MIN([Retail].[Sales]))
• Computes the minimum sales for the last 2 rows, current row, and next row per region:
  WINDOW_MIN(MIN([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY MIN([Retail].[Sales]))
• Computes the minimum of last 4 rows and current row for each customer:
  WINDOW_MIN(MIN([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY MIN([Retail].[Quantity]))
• Computes the MIN of last 6 rows and current row for each category:
  WINDOW_MIN(MIN([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY MIN([Retail].[Order Discount]))
• Computes the MIN of last 5 rows and current row per shipping mode:
  WINDOW_MIN(MIN([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY MIN([Retail].[Sales]))

WINDOW_STDDEV

Used to calculate the standard deviation for a specified expression over a window or partition of rows is a common operation.

Syntax – WINDOW_STDDEV(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are calculating the standard deviation.

Example Queries:

• We are computing the running count:
  WINDOW_STDEV(SUM([Retail].[Sales]),1,2)
  over(partition by [Retail].[Region] order by SUM([Retail].[Sales]))
  (note: if data contains null values, use ZEROIFNULL or NULLIFZERO)
• Computes the standard deviation of sales for the last 2 rows, current row, and next row per region:
  WINDOW_STDDEV(SUM([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
  (note: if data contains null values, use ZEROIFNULL or NULLIFZERO)
• Computes the standard deviation of last 4 rows and current row for each customer:
  WINDOW_STDDEV(SUM([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Customer id] ORDER BY SUM([Retail].[Quantity]))
  (note: if data contains null values, use ZEROIFNULL or NULLIFZERO)
• Computes the standard deviation of last 6 rows and current row for each category:
  WINDOW_STDDEV(SUM([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY SUM([Retail].[Order Discount]))
• Computes the standard deviation of last 5 rows and current row per shipping mode:
  WINDOW_STDDEV(SUM([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY SUM([Retail].[Sales]))

WINDOW_VAR

Used to calculate the variance for a specified expression over a window or partition of rows is a common operation.

Syntax – WINDOW_VAR(aggregate column name, preceding integer, forward integer) OVER ([PARTITION BY column name] ORDER BY Aggregate function with column name)

Example: We are calculating the variance over a window.

Example Queries:

• We are computing the running count:
  WINDOW_VAR(SUM([Retail].[Sales]),1,2)
  over(partition by [Retail].[Region] order by SUM([Retail].[Sales]))
• Computes the variance of sales for the last 2 rows, current row, and next row per region:
  WINDOW_VAR(SUM([Retail].[Sales]), 2, 1)
  OVER (PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Sales]))
• Computes the variance of last 4 rows and current row for each customer:
  WINDOW_VAR(SUM([Retail].[Quantity]), 4, 0)
  OVER (PARTITION BY [Retail].[Region] ORDER BY SUM([Retail].[Quantity]))
• Computes the variance of last 6 rows and current row for each category:
  WINDOW_VAR(SUM([Retail].[Order Discount]), 6, 0)
  OVER (PARTITION BY [Retail].[Category] ORDER BY SUM([Retail].[Order Discount]))
• Computes the variance of last 5 rows and current row per shipping mode:
  WINDOW_VAR(SUM([Retail].[Sales]), 5, 0)
  OVER (PARTITION BY [Retail].[Ship Mode] ORDER BY SUM([Retail].[Sales]))