Windowing agg

Definition

Window aggregate functions (aka window functions or windowed aggregates) are functions that perform a calculation over a group of records called window that are in some relation to the current record (i.e. can be in the same partition or frame as the current row).

Parameters

1. Partition by (+add):

   • Def: Specify the column for grouping.

2. Order by (+add):

   • Def: Specify the column for ordering.

3. Window size with:

   • Def: Specify the minimum and maximum for the windowing.
   • Range:
     • The Range parameter puts a number of constraints on the ORDER BY expressions: there can be only one expression and this expression must have a numerical data type (integer/float/double).
   • Rows:
     • Start
     • End

4. Function:

   • Def: Specify the function to use.

5. On column:

   • Def: Specify the column to use for function.

6. Column output:

   • Def: Specify the name of the column.

Additionnal Comments

Functions:

1. count:

   • Def: Count the element in the Column input for each group.
     Link to the Count page.

2. sum:

   • Def: Sum all the element of the Column input for each group.
     Link to the Sum page.

3. avg:

   • Def: Returns the average of the Column input for each group.
     Link to the Average page.

4. mean:

   • Def: Returns the mean of the Column input for each group.

5. var:

   • Def: Returns the variance of the Column input for each group.
     Link to the Var page.

6. std:

   • Def: Returns the standart deviation of the Column input for each group.

7. max:

   • Def: Returns the maximum value of the Column input for each group.
     Link to the Max page.

8. min:

   • Def: Returns the maximum value of the Column input for each group.
     Link to the Min page.