---
title: Data Preparation
slug: vRWc-data
description: Discover Ocient® SQL functions for time series data preparation. Use lag generators and vector assemblers to simplify feature engineering, reduce boilerplate code, and create inputs for regression, VAR, and neural network models.
docTags: 
createdAt: Mon Aug 25 2025 18:35:38 GMT+0000 (Coordinated Universal Time)
updatedAt: Fri Apr 10 2026 21:24:35 GMT+0000 (Coordinated Universal Time)
---

Lag generator and vector assembler SQL functions simplify generating and organizing lagged features for time series data. Each function expands into standard SQL during query processing, so developers can reduce boilerplate and create feature sets with minimal code.

These functions fall into two main types:&#x20;

- [Lag Generator Functions](./#lag-generator-functions) — Create individual lag feature columns.&#x20;
- [Vector Assembler Functions](./#vector-assembler-function) — Group generated lag columns into vectors.&#x20;

Developers can apply lag features across many Ocient®-supported models, including:

- Simple Linear Regression (univariate forecasting with lag inputs)
- Multiple Linear Regression (multivariate regression with lagged predictors)
- Polynomial Regression (regression with higher-order lagged terms)
- Linear Combination Regression (custom functions of lagged inputs)
- Nonlinear Regression (arbitrary functions that include lag variables)
- Feedforward Neural Networks (lagged features as input vectors)
- Vector Autoregression (VAR) (multivariate time series with structured lag vectors)
- Autoregression (univariate time series modeled through lagged values)
- Vector Valued Regression (multivariate dependent variables with lagged inputs)

The functions provide general-purpose utilities for time series feature engineering. In particular, VAR models benefit the most from lag functions because the models require large numbers of structured lag vectors.&#x20;

## Lag Generator Functions

Lag generator functions provide options for creating lagged columns for one or more variables in a single statement.&#x20;

These functions follow the syntax rules of window aggregate functions as they define the window for the lag computation with an `OVER` and `ORDER BY` clause. For details, see [Window Aggregate Functions](<././Window Aggregate Functions.md>). &#x20;

The window parameters of lag generator functions are represented in these examples by `<window_specification>`.

### LAGS

Generates a series of lagged columns for a single variable in one statement. This function simplifies univariate time-series feature creation.

**Syntax**

```sql
LAGS(expression, start_lag, end_lag [, step]) 
    OVER (<window_specification>)
```

| **Argument**                      |  **Type**                            |  **Description**                                                                                                                         |
| --------------------------------- | ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------- |
|  `expression`                     | ANY                                  | The column or expression to lag.                                                                                                         |
|  `start_lag`                      |  INTEGER                             | First lag to generate. This value must be less than the `end_lag` value.                                                                 |
|  `end_lag`                        |  INTEGER                             | Last lag to generate. This value must be greater than the `start_lag` value.                                                             |
|  `step`                           |  INTEGER                             | Optional. <br />Defines the gap between successive lags.  Must be a positive integer. <br />If unspecified, this value defaults to `1`.  |

**Example**

This query produces three lagged columns (`sales_lag1`, `sales_lag2`, `sales_lag3`) from the sales column.

```sql
SELECT
    LAGS(sales, 1, 3) OVER (ORDER BY day)
FROM store_sales;
```

*Output*

```sql
sales_lag1 | sales_lag2 | sales_lag3
-----------+------------+------------
NULL       | NULL       | NULL
100        | NULL       | NULL
120        | 100        | NULL
130        | 120        | 100
```

### LAGS\_ZEROFILL

Generates lagged columns for a single variable and replaces NULL values with `0`.

**Syntax**

```sql
LAGS_ZEROFILL(expression, start_lag, end_lag [, step]) 
    OVER (<window_specification>)
```

| **Argument**                      |  **Type**                            |  **Description**                                                                                                                        |
| --------------------------------- | ------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------- |
|  `expression`                     | ANY                                  | The column or expression to lag.                                                                                                        |
|  `start_lag`                      |  INTEGER                             | First lag to generate. This value must be less than the `end_lag` value.                                                                |
|  `end_lag`                        |  INTEGER                             | Last lag to generate. This value must be greater than the `start_lag` value.                                                            |
|  `step`                           |  INTEGER                             | Optional. <br />Defines the gap between successive lags. Must be a positive integer. <br />If unspecified, this value defaults to `1`.  |

**Example**

This query produces three lagged sales columns and replaces NULL values with 0.

```sql
SELECT
    LAGS_ZEROFILL(sales, 1, 3) OVER (ORDER BY day)
FROM store_sales;
```

*Output*

```sql
sales_lag1 | sales_lag2 | sales_lag3
-----------+------------+------------
0          | 0          | 0
100        | 0          | 0
120        | 100        | 0
130        | 120        | 100
```

### MULTI\_LAGS

Generates lagged columns for multiple variables at once. This function is intended primarily for creating features for multivariate time-series models.

**Syntax**

```sql
MULTI_LAGS(expr1, expr2, [ ,... ] start_lag, end_lag [, step]) 
    OVER (<window_specification>)
```

|  **Argument**                              |  **Type**                            |  **Description**                                                                                                                       |
| ------------------------------------------ | ------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------- |
|  `expr1, expr2, [ ,... ] `                 | ANY                                  | One or more columns or expressions to lag. Applies the same lag range and step to each input.                                          |
|  `start_lag`                               |  INTEGER                             | First lag to generate. This value must be less than the `end_lag` value.                                                               |
|  `end_lag`                                 |  INTEGER                             | Last lag to generate. This value must be greater than the `start_lag` value.                                                           |
|  `step`                                    |  INTEGER                             | Optional. <br />Defines the gap between successive lags. Must be a positive integer.<br />If unspecified, this value defaults to `1`.  |

**Example**
This query creates four lagged columns for both `interest_rate` and `gdp_growth`.

```sql
SELECT
    MULTI_LAGS(interest_rate, gdp_growth, 1, 4) OVER (ORDER BY quarter)
FROM economic_data;
```

*Output*

```sql
interest_rate_lag1 | interest_rate_lag2 | interest_rate_lag3 | interest_rate_lag4 | gdp_growth_lag1 | gdp_growth_lag2 | gdp_growth_lag3 | gdp_growth_lag4
-------------------+--------------------+--------------------+--------------------+-----------------+-----------------+-----------------+-----------------
NULL               | NULL               | NULL               | NULL               | NULL            | NULL            | NULL            | NULL
2.5                | NULL               | NULL               | NULL               | 1.1             | NULL            | NULL            | NULL
2.7                | 2.5                | NULL               | NULL               | 1.2             | 1.1             | NULL            | NULL
2.8                | 2.7                | 2.5                | NULL               | 1.3             | 1.2             | 1.1             | NULL
```

### MULTI\_LAGS\_ZEROFILL

Generates lagged columns for multiple variables and replaces NULL values with `0`.

**Syntax**

```sql
MULTI_LAGS_ZEROFILL(expr1, expr2, [ ,... ] start_lag, end_lag [, step]) 
    OVER (<window_specification>)
```

|  **Argument**                              |  **Type**                            |  **Description**                                                                                                                       |
| ------------------------------------------ | ------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------- |
|  `expr1, expr2, [ ,... ] `                 | ANY                                  | One or more columns or expressions to lag. Applies the same lag range and step to each input.                                          |
|  `start_lag`                               |  INTEGER                             | First lag to generate. This value must be less than the `end_lag` value.                                                               |
|  `end_lag`                                 |  INTEGER                             | Last lag to generate. This value must be greater than the `start_lag` value.                                                           |
|  `step`                                    |  INTEGER                             | Optional. <br />Defines the gap between successive lags. Must be a positive integer.<br />If unspecified, this value defaults to `1`.  |

**Example**
This query generates four lagged columns each for `x1`, `x2`, and `x3`, replacing missing values with 0.

```sql
SELECT
    MULTI_LAGS_ZEROFILL(x1, x2, x3, 1, 4) OVER (ORDER BY time_col)
FROM public.my_time_series_table;
```

*Output*

```sql
x1_lag1 | x1_lag2 | x1_lag3 | x1_lag4 | x2_lag1 | x2_lag2 | x2_lag3 | x2_lag4 | x3_lag1 | x3_lag2 | x3_lag3 | x3_lag4
--------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------
0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0       | 0
10      | 0       | 0       | 0       | 20      | 0       | 0       | 0       | 30      | 0       | 0       | 0
11      | 10      | 0       | 0       | 21      | 20      | 0       | 0       | 31      | 30      | 0       | 0
12      | 11      | 10      | 0       | 22      | 21      | 20      | 0       | 32      | 31      | 30      | 0

```

## Vector Assembler Function

A vector assembler function provides options for grouping multiple lagged columns into structured vectors in a single statement. This function makes preparing input data for time series models easier, especially those that require organized lag structures, such as VAR.

### LAG\_VECTORS

Groups lagged columns generated by the `MULTI_LAGS` or `MULTI_LAGS_ZEROFILL` functions into vector columns. Models such as VAR require these structured lag vectors.

The resulting vectors are named `lag_vector_`.

**Syntax**

```sql
LAG_VECTORS(expr1, expr2, [ ..., ] start_lag, end_lag [, step])
```

|  **Argument**                              |  **Type**                            |  **Description**                                                                                                                       |
| ------------------------------------------ | ------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------- |
|  `expr1, expr2, [ ,... ] `                 | NUMERIC                              | Two or more lagged columns to group. Applies the same lag range and step to each input.                                                |
|  `start_lag`                               |  INTEGER                             | First lag to assemble. This value must be less than the `end_lag` value.                                                               |
|  `end_lag`                                 |  INTEGER                             | Last lag to assemble. This value must be greater than the `start_lag` value.                                                           |
|  `step`                                    |  INTEGER                             | Optional. <br />Defines the gap between successive lags. Must be a positive integer.<br />If unspecified, this value defaults to `1`.  |

**Examples**

**Assemble Lagged Columns into Vectors**

This query uses `MULTI_LAGS_ZEROFILL` to generate lagged columns for `x1`, `x2`, and `x3`, then uses `LAG_VECTORS` to group them into `lag_vector1` through `lag_vector4`.

```sql
SELECT
    {x1, x2, x3} AS current_vector,
    LAG_VECTORS(x1, x2, x3, 1, 4)
FROM (
    SELECT
        x1, x2, x3,
        MULTI_LAGS_ZEROFILL(x1, x2, x3, 1, 4) OVER (ORDER BY time_col)
    FROM public.my_time_series_table
) s;
e;
```

*Output*

```sql
current_vector | lag_vector1 | lag_vector2 | lag_vector3 | lag_vector4
---------------+-------------+-------------+-------------+-------------
{10, 20, 30}  | {0, 0, 0}   | {0, 0, 0}   | {0, 0, 0}   | {0, 0, 0}
{11, 21, 31}  | {10, 20, 30}| {0, 0, 0}   | {0, 0, 0}   | {0, 0, 0}
{12, 22, 32}  | {11, 21, 31}| {10, 20, 30}| {0, 0, 0}   | {0, 0, 0}
{13, 23, 33}  | {12, 22, 32}| {11, 21, 31}| {10, 20, 30}| {0, 0, 0}

```

**Prepare Data for a VAR Model**

This example shows how to prepare training data for a VAR model with three variables (`x1`, `x2`, `x3`) and four lags. The example demonstrates how the lag generator functions and vector assembler can simplify feature creation compared to writing out every LAG expression manually.

- `MULTI_LAGS_ZEROFILL` generates all individual lagged columns (`x1_lag1`…`x3_lag4`) in a single call, replacing NULL values with zero.
- `LAG_VECTORS` automatically groups these lagged columns into vector inputs (`lag_vector1` ... `lag_vector4`), which the model requires.

```sql
CREATE MLMODEL my_var_model
TYPE VECTOR_AUTOREGRESSION ON (
    SELECT
        {x1, x2, x3},  
        LAG_VECTORS(x1, x2, x3, 1, 4) 
    FROM (
        SELECT
            x1, x2, x3,
            MULTI_LAGS_ZEROFILL(x1, x2, x3, 1, 4) OVER (ORDER BY t)
        FROM public.my_time_series_table
    )
)
OPTIONS (
    'numVariables' -> '3',
    'numLags'      -> '4'
);
```

**Prepare Data for a VAR Model with Manual Vector Assembly**

This version of the univariate regression example uses repeated `LAG` invocations instead of the `LAGS` function. Although it produces the exact same set of lagged features (`sales_lag1`, `sales_lag2`, `sales_lag3`), the query requires writing each `LAG` expression manually. This makes the SQL longer, more repetitive, and harder to maintain compared to the concise single-line `LAGS` version.

```sql
CREATE MLMODEL my_var_model_manual
TYPE VECTOR_AUTOREGRESSION ON (
    SELECT
        {x1, x2, x3},
        {x1_lag1, x2_lag1, x3_lag1},
        {x1_lag2, x2_lag2, x3_lag2},
        {x1_lag3, x2_lag3, x3_lag3},
        {x1_lag4, x2_lag4, x3_lag4}
    FROM (
        SELECT
            x1, x2, x3,
            COALESCE(LAG(x1, 1) OVER (ORDER BY t), 0) AS x1_lag1,
            COALESCE(LAG(x1, 2) OVER (ORDER BY t), 0) AS x1_lag2,
            COALESCE(LAG(x1, 3) OVER (ORDER BY t), 0) AS x1_lag3,
            COALESCE(LAG(x1, 4) OVER (ORDER BY t), 0) AS x1_lag4,
            COALESCE(LAG(x2, 1) OVER (ORDER BY t), 0) AS x2_lag1,
            COALESCE(LAG(x2, 2) OVER (ORDER BY t), 0) AS x2_lag2,
            COALESCE(LAG(x2, 3) OVER (ORDER BY t), 0) AS x2_lag3,
            COALESCE(LAG(x2, 4) OVER (ORDER BY t), 0) AS x2_lag4,
            COALESCE(LAG(x3, 1) OVER (ORDER BY t), 0) AS x3_lag1,
            COALESCE(LAG(x3, 2) OVER (ORDER BY t), 0) AS x3_lag2,
            COALESCE(LAG(x3, 3) OVER (ORDER BY t), 0) AS x3_lag3,
            COALESCE(LAG(x3, 4) OVER (ORDER BY t), 0) AS x3_lag4
        FROM public.my_time_series_table
    )
)
OPTIONS (
    'numVariables' -> '3',
    'numLags'      -> '4'
);
```

## Related Links

[Window Aggregate Functions](<././Window Aggregate Functions.md>)&#x20;

[Machine Learning in Ocient](<./././Analysis in Ocient/Machine Learning in Ocient.md>)&#x20;

[Machine Learning Models](<././Data Definition Language _DDL_ Statement Reference/Machine Learning Models.md>)&#x20;



