This chapter explains the aggregate and window functions in detail.
Aggregation Functions
Aggregate functions produce a single result from a set of input values. The following table describes the list of aggregate functions in detail.
| S.No. | Function & Description |
|---|---|
| 1 | AVG(exp)Averages a column of all records in a data source. |
| 2 | CORR(expression1, expression2)Returns the coefficient of correlation between a set of number pairs. |
| 3 | COUNT()Returns the number rows. |
| 4 | MAX(expression)Returns the largest value of the selected column. |
| 5 | MIN(expression)Returns the smallest value of the selected column. |
| 6 | SUM(expression)Returns the sum of the given column. |
| 7 | LAST_VALUE(expression)Returns the last value of the given column. |
Window Function
The Window functions execute on a set of rows and return a single value for each row from the query. The term window has the meaning of set of row for the function.
The Window function in a query, defines the window using the OVER() clause.
The OVER() clause has the following capabilities −
- Defines window partitions to form groups of rows. (PARTITION BY clause)
- Orders rows within a partition. (ORDER BY clause)
The following table describes the window functions in detail.
| Function | Return type | Description |
|---|---|---|
| rank() | int | Returns rank of the current row with gaps. |
| row_num() | int | Returns the current row within its partition, counting from 1. |
| lead(value[, offset integer[, default any]]) | Same as input type | Returns value evaluated at the row that is offset rows after the current row within the partition. If there is no such row, default value will be returned. |
| lag(value[, offset integer[, default any]]) | Same as input type | Returns value evaluated at the row that is offset rows before the current row within the partition. |
| first_value(value) | Same as input type | Returns the first value of input rows. |
| last_value(value) | Same as input type | Returns the last value of input rows. |