The first_value and last_value aggregates return the first and last elements of window. Available only in the Enterprise Edition.

Synopsis

AFL% window(array, dimension1_low, dimension1_high [,dimension2_low, dimension2_high],... first_value(attribute))

AFL% variable_window(array, dimension,left_edge,right_edge, first_value(attribute))

Library

The first_value aggregate resides in the Linear Algebra library. Run the following query to load this library:

AFL% load_library('linear_algebra');

Summary

The first_value aggregate returns the first value from a subarray defined by a window aggregate. Note that if the window goes beyond the end of the array (in any direction), first_value fills-in values within the window that fall outside the array boundary with a constant value taken from the array boundary. This behavior is illustrated in an example below.

Examples

Calculate the First and Last Value from a 2x2 Moving Window on a 3×4 Array

To calculate the first and last value from a 2x2 moving window on a 3×4 array, do the following:

Create a 3x4 array containing the values 0 to 11:

AFL% create array M <val:double>[row=0:2; col=0:3];
AFL% store(build(M,row*4+col),M);

The output is:

{row,col} val
{0,0} 0
{0,1} 1
{0,2} 2
{0,3} 3
{1,0} 4
{1,1} 5
{1,2} 6
{1,3} 7
{2,0} 8
{2,1} 9
{2,2} 10
{2,3} 11

Select the first and last values from a 2x2 window on M:

AFL% window(M, 0,1, 0, 1, first_value(val), last_value(val));