Window aggregation family syntax

The syntax for the window aggregation family is as follows:

Func( value_expression) OVER ( <partition_by_clause> <order_by_clause> 
[<frame_spec_clause>] )

or

Func( value_expression) OVER (<order_by_clause> [<frame_spec_clause>])

Where Func is an expression:

<partition_by_clause> = PARTITION BY <value_expression> [, ...]+
<order_by_clause> = ORDER BY <value_expression> [asc | desc] [nulls 
{first|last}] [, ...]+
<frame_spec_clause> = <frame_extent> [<exclusion clause>]
<frame_extent> = 
       ROWS  UNBOUNDED PRECEDING
      |ROWS  <constant> PRECEDING
      |ROWS   CURRENT ROW
      |RANGE  UNBOUNDED PRECEDING
      |RANGE  <constant> PRECEDING
      |RANGE  CURRENT ROW
      |ROWS BETWEEN {UNBOUNDED PRECEDING| <constant> PRECEDING | CURRENT 
ROW } AND { UNBOUNDED FOLLOWING | <constant>  FOLLOWING | CURRENT ROW }
      |RANGE BETWEEN {UNBOUNDED PRECEDING| <constant> PRECEDING | CURRENT 
ROW } AND { UNBOUNDED FOLLOWING | <constant>  FOLLOWING | CURRENT ROW } 
<exclusion_clause> =  EXCLUDE CURRENT ROW | EXCLUDE TIES | EXCLUDE 
GROUP | EXCLUDE  NO OTHERS

avg

Returns the average value of the expression.

count

Returns the number of rows in the query.

• If you specify value expression, count returns the number of rows where the expression is not null.
• If you specify an asterisk, count returns all rows, including duplicates and nulls. Otherwise, count never includes nulls.

max

Returns the maximum value of the expression.

min

Returns the minimum value of the expression.

sum

Returns the sum of the expression.

stddev

Returns the standard deviation of the expression, which is the square root of variance. When variance returns null, this function returns null.

stddev_pop

Computes the population standard deviation. This function is the same as the square root of the var_pop function. When var_pop returns null, this function returns null.

stddev_samp

Computes the sample standard deviation, which is the square root of var_ samp. When var_samp returns null, this function returns null.

variance Variance = (sum(expr**2) - (sum(expr)**2) / count(expr)) / (count(expr) - 1)

Returns the variance of the expression. If you apply this function to an empty set, it returns null. IBM® Netezza® SQL calculates the variance of expression as follows:

• 0 if the number of rows in expression = 1
• var_samp if the number of rows in expression > 1

var_pop Var_pop = (sum(expr**2) - sum(expr)2 / count(expr)) / count(expr)

Returns the population variance of a set of numbers after it discards the nulls in this set. If you apply this function to an empty set, it returns null.

var_ samp var_samp = (sum (expr**2) - ((sum (expr) **2) / (count (*)))) / (count (*) - 1)

Returns the sample variance of a set of numbers after it discards the nulls in this set. If you apply this function to an empty set, it returns null.