Aggregate & Window Functions

Advertisements

Previous Page
Next Page  

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.
Advertisements