`g_twindow_nearest(G;S;T;TS;N)`

Returns the number of rows to the nearest row that is at least a specified time period before (or after) the current row within a given group.

Function type

Vector only

Syntax

g_twindow_nearest(G;S;T;TS;N)
t_twindow_nearest(N)

Input

Argument	Type	Description
`G`	any	A space- or comma-separated list of column names Rows are in the same group if their values for all of the columns listed in `G` are the same. If `G` is omitted, all rows are considered to be in the same group. If any of the columns listed in `G` contain N/A, the N/A value is considered a valid grouping value.
`S`	integer	The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation If `S` is omitted, all rows will be considered by the function (subject to any prior row selections). If any of the values in `S` are neither 1 nor 0, an error is returned.
`T`	integer or decimal	The name of a column whose values are used as the basis of the time series Row order is determined by `T`. Note: `T` may not be omitted.
`TS`	text	A string representing the type of the time `T` The choice of `TS` must match the type of values in `T`, or the results will be meaningless. Valid types are: `'D'` for the `date` (`YYYYMMDD`) type (i.e., one period = one day) `'Q'` for the `quarter` (`YYYYQ`) type (i.e., one period = one quarter) `'M'` for the `month` (`YYYYMM`) type (i.e., one period = one month) `'T'` for the `time` (`HHMMSS`) type (i.e., one period = one second) `'TS'` for the `date+time` type (i.e., one period = one second) `'TSn'` for the `date+time` type construed with `n` decimal places of precision in the seconds place (i.e., one period = 1/10^`n` seconds) If `TS` is omitted, then `T` is assumed to be an integer (e.g., a year or a period number, such that one period=1).
`N`	integer big integer	The number of rows to shift within the group

Note: Support for columns of type big integer available as of version 11.26.

Return Value

For every row in each group defined by G and ordered by T (and for those rows where S=1, if specified):

If N >= 0:
- g_twindow_nearest returns the number of rows from the current row to the row that is exactly N time periods after this row in the same group as this row.
  
  If there are multiple rows that are exactly N time periods after this row, g_twindow_nearest returns the number of rows to the first row within that set of rows.
  Note: This behavior is identical to g_twindow.
- If there are no rows that are exactly N time periods after this row, g_twindow_nearest returns the number of rows to the nearest row that is at least N time periods after this row.
If N < 0:
- g_twindow_nearest returns the number of rows from the current row to the row that is exactly |N| time periods before this row in the same group as this row.
  
  If there are multiple rows that are exactly |N| time periods before this row, g_twindow_nearest returns the number of rows to the first row within that set of rows.
  Note: This behavior is identical to g_twindow.
- If there are no rows that are exactly |N| time periods before this row, g_twindow_nearest returns the number of rows to the nearest row that is at least |N| time periods before this row.

Time periods are determined by TS with respect to the values in T.

The result is the same data type as X.

Note: N/As in X are treated like any other value; i.e. they are shifted.

If no rows in a group have valid (non-N/A) values for X, the result for every row of the group is N/A.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/>
<willbe name="g_twindow_nearest_1" value="g_twindow_nearest(state;include;order;;-1)"/>
<willbe name="g_twindow_nearest_2" value="g_twindow_nearest(state city;include;order;;-1)"/>

Example

For this example, we'll use the sales data available in pub.demo.retail.item, which contains sales transactions from three stores.

Let's say we want to find out the number of rows from the current row to the previous day (or the row nearest to that if there's no data for the previous day). To do this, we can use g_twindow_nearest.

For simplicity, let's say we just want to look at the transactions from store 1. We'll do a simple <sel> operation to pare down the data:

<sel value="(store=1)"/>

(Obviously, this is not too critical for our sample table, which only contains 35 rows, but it will make our example a little easier to understand).

Now our table just shows the transactions from store 1:

The g_twindow_nearest function has the form:

g_twindow_nearest(G;S;T;TS;N)

Since we already selected the rows for store 1, which is the group we're interested in, we can omit the G parameter. (If we hadn't done the <sel> operation earlier, we could have set G to store here in order to group the results by store.)

We can also omit the S parameter, since we want to consider all rows when applying the function.

We'll use the date column for our T parameter and set the TS parameter to 'D' (since the data in the date column is of the date type), and we'll set the N parameter to -1, which will allow us to shift back to the previous day in our table.

So, our g_twindow_nearest function should look like:

g_twindow_nearest(;;date;'D';-1)

Note: Even though we're omitting the G and S parameters, we still need to account for them in the function call; hence, the ";;" before date.

Let's add a computed column to our table with this g_twindow_nearest function using <willbe>:


<willbe name="rows_to_nearest_day" label="# Rows to`Nearest Day" value="g_twindow_nearest(;;date;'D';-1)"/>

The results of this operation will look like:

Because there is an exact match with respect to the time period specified for the rows with the date 05/16/12 , g_twindow_nearest returns the number of rows to the previous day, 05/15/12.

Note: Although there are multiple rows with the date 05/15/12, g_twindow_nearest returns the number of rows to the first row with that date.

So, in our example, for the first transaction with the date 05/16/12, g_twindow_nearest returns a -3, meaning that you need to shift back 3 rows in the table to get to the previous day's transaction. For the second transaction with the date 05/16/12, g_twindow_nearest returns a -4, meaning that you need to shift back 4 rows in the table to get to the previous day's transaction.

Note: The results from g_twindow_nearest for the rows with the date 05/16/12 are the same as the results from g_twindow.

However, for those rows where there is no exact match with respect to the time period specified, g_twindow_nearest will return the number of rows to the nearest row that is at least the specified time period away.

For instance, since we have no transactional data for 05/17/12, g_twindow_nearest for the rows with the date 05/18/12 will return the number of rows to the nearest row with the date 05/16/12 (the closest previous day).

Since there is no transactional data for 06/02/12, the results from g_twindow_nearest for the rows with the date 06/03/12 show the number of rows to the nearest row with the date 05/18/12.

Notice that for the rows with the date 05/15/12, which are the first rows in the table and have no rows before them, g_twindow_nearest returns the number of rows to the top of the table.

Additional Information

The t_ version of this function defaults the G and T/TS arguments, and omits the S argument. The defaults for G and T/TS are set at table load time based on the organization of the table.