In this article
...
Parameter Name | Data Type | Description | Default Value | Example | ||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String |
| ELT Window Functions | Aggregate Salary Data | ||||||||
Pass through | Check box | Select to include the original data along with the aggregated data in the output. Otherwise, the output has only aggregated data. | Not selected | Selected | ||||||||
Rank Related Functions List | Use this field set to enter the rank-related function to apply to the table's data. You must also specify an alias for each rank-related function. Specify each rank-related function as a separate row. Click to add a new row. This field set contains the following fields:
| |||||||||||
Rank Function | String/Suggestion | Enter the rank-related function to apply to the table data. Alternatively, click to view and select a rank function from the suggestion list. The list displays the functions supported by the database that you select The following functions are available for Snowflake, Redshift and Synapse databases:
For more information on the ranking functions, refer to the following: | N/A | CUME_DIST | ||||||||
Alias Name | String | Enter the alias name to associate with the specified rank function. | N/A | TOTAL | ||||||||
General Window Functions List | Use this field set to apply the aggregate function on the specified field in the table's data. You must also specify an alias for each aggregate function. Specify each aggregate function as a separate row. Click to add a new row. This field set contains the following fields:
| |||||||||||
Aggregate Function | String/Suggestion | Enter the name of the aggregate function to apply to the table data. Alternatively, click to view and select an aggregate function from the suggestion list. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured. See the Troubleshooting section for details. The available functions are:
| N/A | COUNT | ||||||||
Field | String | Enter the name of the field in the input table on which to apply the aggregate function. | N/A | SALARY | ||||||||
Alias Name | String | Enter the alias name to associate with the specified aggregate function. | N/A | TOTAL_SALARY | ||||||||
Bitwise Aggregate Functions List (Snowflake Only) | Specify the bitwise aggregate function to apply to the specified field in the table's data. You must also specify an alias for each bitwise aggregate function. Specify each bitwise aggregate function as a separate row. Click to add a new row.
This field set contains the following fields:
| |||||||||||
Bitwise Aggregate Function | String/Suggestion | Enter the name of the bitwise aggregate function to apply to the table data. Alternatively, click the button to view and select a bitwise aggregate function from the list of supported bitwise aggregate functions. Available options are:
| N/A | BITOR_AGG | ||||||||
Field | String | Enter the name of the field in the input table upon which to apply the bitwise aggregate function. | N/A | RANGE | ||||||||
Alias Name | String | Enter the alias name to associate with the specified bitwise aggregate function. | N/A | BITOR_RANGE | ||||||||
Boolean Aggregate Functions List (Snowflake Only) | Specify the boolean aggregate function to apply to the specified field in the table's data. You must also specify an alias for each boolean aggregate function. Specify each boolean aggregate function as a separate row. Click to add a new row.
This fieldset consists of the following fields:
| |||||||||||
Boolean Aggregate Function | String/Suggestible | Enter the name of the boolean aggregate function to apply to the table data. Alternatively, click the button to view and select a boolean aggregate function from the list of supported bitwise aggregate functions. Available options are:
| N/A | BOOLOR_AGG | ||||||||
Field | String | Enter the name of the field in the input table upon which to apply the boolean aggregate function. | N/A | RANGE | ||||||||
Alias Name | String | Enter the alias name to associate with the specified boolean aggregate function. | N/A | BOOLOR_RANGE | ||||||||
Value Based Window Functions List | Use this field set to specify the columns for which to apply value-based Window functions. Each Window function must be specified as a new row. This field set contains the following fields:
| |||||||||||
Value Window Function | String/Suggestion | Required. Enter the name of the window function to apply to the table data. You must ensure that the function is spelled correctly, else the Snap displays an error. See the Troubleshooting section for details. Alternatively, click to view and select a function from the list of window functions. The list displays the functions supported by the database that you select in the Account settings. The available functions are:
| N/A | FIRST_VALUE_IGNORE_NULLS | ||||||||
Field | String/Expression | Required. Apply the window function to the target column. | N/A | QTY | ||||||||
nth Value | String/Expression | Enter a number for the nth value function. This field is ignored by the FIRST_VALUE and the LAST_VALUE functions during the execution. | N/A | 3 | ||||||||
Alias Name | String | Required. Specify the column alias name in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further. | N/A | |||||||||
LEAD and LAG Window Functions List | Use this field set to add LEAD and LAG Window functions. Specify each function as a new row. Click to add a row. This field set contains the following fields:
| |||||||||||
Lead Lag Window Function | String/Suggestion | Required. Enter the name of the Lead Lag function to use. You must ensure that it is spelled correctly, else the Snap displays an error. See the Troubleshooting section for details.
| N/A | LEAD_IGNORE_NULLS | ||||||||
Field | String/Expression | Required. Apply the Lead Lag Window function to the target column. | N/A | QTY | ||||||||
Offset Value | String/Expression | Enter the number the LEAD or LAG function uses to fetch the leading or lagging value from the input table partition for the given input field. | N/A | 2 | ||||||||
Default Value | String/Expression | Enter a SQL expression the LEAD or LAG functions use when the offset goes out of the bounds of the window. | N/A | 5 | ||||||||
Alias Name | String | Specify the column alias name in which to display the result of the aggregate function. You can also reference this name in downstream Snaps to process the data further. | N/A | lead_rn | ||||||||
PARTITION BY Fields List | Enter the fields in the table to use to partition the output data. Specify each field in a separate row. Click to add a new row. This field set contains the following fields:
| |||||||||||
Partition By Field | String | Enter the name of the field to partition the data with. | N/A | TOTAL_SALARY | ||||||||
Order By Fields List | Specify the field to use to sort the output data along with the sort order preference and the placement for rows with null values in the specified field. Specify each field in a separate row. Click to add a new row. This field set contains the following fields:
| |||||||||||
Order By Field | String | Enter the name of the field/column in the table based on which to sort the rows. | N/A | TOTAL_SALARY | ||||||||
Sort Order | String | Select the order in which to sort the rows. Available options are:
| ASC | DESC | ||||||||
Null Value Sort Preference | String | Select whether to keep rows with null values first or last after the sort operation. Available options are:
| NULLS FIRST | NULLS LAST | ||||||||
Window Frame | Use this field set to specify the range of the rows in the input table upon which to apply the window functions. This field set contains the following fields:
| |||||||||||
Start of Window Frame | Drop-down list | Select the policy/condition to indicate the first row in the window frame. Available options are:
| UNBOUNDED PRECEDING | CURRENT ROW | ||||||||
Bounded Start Value | Integer | Activates when you select the BOUNDED VALUE PRECEDING or BOUNDED VALUE FOLLOWING option in the Start of Window Frame field. Enter the row value from which to start the window frame. | N/A | 2 | ||||||||
End of Window Frame | Drop-down list | Select the policy/condition to indicate the last row in the window frame. Available options are:
| UNBOUNDED FOLLOWING | BOUNDED VALUE FOLLOWING | ||||||||
Bounded End Value | Integer | Activates when you select the BOUNDED VALUE PRECEDING or BOUNDED VALUE FOLLOWING option in the End of Window Frame field. Enter the row value at which to end the window frame. | N/A | 8 | ||||||||
Get preview data | Check box |
| Not selected | Selected |
...
Error | Reason | Resolution |
---|---|---|
Set an account for the Snap in the Accounts tab. | Account configuration is mandatory in the ELT Window Function Snap. An account has not been selected/configured in the Snap. | Select or configure an account for the Snap. See Configuring the ELT Snap Pack Accounts for details. |
Examples
Calculating Aggregate Data for an Organization
...
Upon successful execution, the ELT Window Functions Snap performs the specified calculations upon the input data and shows the following output:
We can add an ELT Insert-Select or ELT Merge Into Snap downstream of the ELT Window Function Snap and write the above output into another table.
Downloads
Note | ||
---|---|---|
| ||
|
...