In this article
Table of Contents | ||||
---|---|---|---|---|
|
Overview
You can use this Snap to add window functions such as rank-relation functions, aggregate functions, and bitwise functions to the table's data. See Snowflake Window Functions, Redshift Window Functions, Azure Synapse Window Functions, and Azure Databricks Window Functions for details on these functions.
Prerequisites
None.
Limitations
None.
Known Issues
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Snap Input and Output
...
Document
...
- Min: 1
- Max: 1
...
- ELT Select
- ELT Copy
...
Document
...
- Min: 1
- Max: 1
...
- ELT Transform
- ELT Insert-Select
...
The input data modified with the window functions. If the Pass through check box is selected, the output also includes the original input data.
Snap Settings
...
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
...
Rank Related Functions List
...
In this article
Table of Contents | ||||
---|---|---|---|---|
|
Overview
You can use this Snap to add window functions such as rank-relation functions, aggregate functions, and bitwise functions to the table's data. See Snowflake Window Functions, Redshift Window Functions, Azure Synapse Window Functions, Azure Databricks Window Functions, or BigQuery Expressions, functions, and operators for details on these functions.
Prerequisites
None.
Limitation
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Known Issues
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Snap Input and Output
Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| The data on which to add the window functions. |
Output | Document |
|
| The input data modified with the window functions. If the Pass through check box is selected, the output also includes the original input data. |
Snap Settings
Info | ||
---|---|---|
| ||
You can use the SQL Expressions and Functions supported for ELT to define your Snap or Account settings with the Expression symbol = enabled, where available. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports. |
Parameter Name | Data Type | Description | Default Value | Example | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Label | String |
| ELT Window Functions | Aggregate Salary Data | ||||||||||||||
Get preview data | Check box |
| Not selected | Selected | ||||||||||||||
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, Azure Synapse and Databricks Lakehouse Platform:
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/Expression | 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 rank-related bitwise aggregate function. Specify each rank-related bitwise aggregate function as a separate row. Click Click to add a new row.
This field set contains the following fields:
| |||||||||||||||||
Rank Bitwise Aggregate Function | String/Suggestion | Enter the rank-relatedthe name of the bitwise aggregate function to apply to the table data. Alternatively, clickclick the tobutton to view and select a rankbitwise aggregate function from the suggestion list. The list displays the functions supported by the database that you selectThe following functions are available for Snowflake, Redshift, Azure Synapse and Databricks Lakehouse Platform:
list of supported bitwise aggregate functions. Available options are:
For more information on the ranking functions, refer to the following: | BITOR_AGG | |||||||||||||||
Field | String/Expression | Enter the name of the field in the input table upon which to apply the bitwise aggregate function. | N/A | CUME_DISTRANGE | ||||||||||||||
Alias Name | String | Enter the alias name to associate with the specified rank bitwise aggregate function. | N/A | TOTAL | General WindowBITOR_RANGE | |||||||||||||
Boolean Aggregate Functions List | Use this field set to apply the aggregate function on(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 field set contains the following fields:
| ||||||||||||||||
Boolean Aggregate Function | String/Suggestion | Enter the name of the boolean aggregate function to apply to the table data. Alternatively, click the button to view and select ana boolean 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: | ||||||||||||||||
Snowflake | Redshift | Azure Synapse | ||||||||||||||||
See Snowflake Window Functions for more details. |
See Redshift Window Functions for more details. |
See Synapse Window Functions for more details. | Databricks Lakehouse Platform |
See Azure Databricks Window Functions for more details. |
Note |
---|
The Snap fails during Pipeline validation/execution if you specify an incorrect function name. |
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.
Note |
---|
Applicable only to the Snowflake database. |
This field set contains the following fields:
- Bitwise Aggregate Function
- Field
- Alias Name
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:
Note |
---|
The Snap fails during Pipeline validation/execution if you specify an incorrect function name. |
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.
Note |
---|
Applicable only to the Snowflake database. |
This field set contains the following fields:
- Boolean Aggregate Function
- Field
- Alias Name
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:
Note |
---|
The Snap fails during Pipeline validation/execution if you specify an incorrect function name. |
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.
Click to add a row.
This field set contains the following fields:
- Value Window Function
- Field
- nth Value
- Alias Name
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:
- FIRST_VALUE_IGNORE_NULLS
- FIRST_VALUE_RESPECT_NULLS
- LAST_VALUE_IGNORE_NULLS
- LAST_VALUE_RESPECT_NULLS
- NTH_VALUE_FROM_FIRST_IGNORE_NULLS
- NTH_VALUE_FROM_FIRST_RESPECT_NULLS
- NTH_VALUE_FROM_LAST_IGNORE_NULLS
- NTH_VALUE_FROM_LAST_RESPECT_NULLS
See Snowflake Window functions for more information.
- FIRST_VALUE_IGNORE_NULLS
- FIRST_VALUE_RESPECT_NULLS
- LAST_VALUE_IGNORE_NULLS
- LAST_VALUE_RESPECT_NULLS
- NTH_VALUE_FROM_FIRST_IGNORE_NULLS
- NTH_VALUE_FROM_FIRST_RESPECT_NULLS
- NTH_VALUE_FROM_LAST_IGNORE_NULLS
- NTH_VALUE_FROM_LAST_RESPECT_NULLS
See Redshift Window functions for more information.
- FIRST_VALUE_RESPECT_NULLS
- LAST_VALUE_RESPECT_NULLS
See Azure Synapse Window functions for more information.
- FIRST_VALUE_IGNORE_NULLS
- FIRST_VALUE_RESPECT_NULLS
- LAST_VALUE_IGNORE_NULLS
- LAST_VALUE_RESPECT_NULLS
See Azure Databricks Window Functions for more details.
Info |
---|
Redshift does not natively support the NTH_VALUE_FROM_LAST_RESPECT_NULL and NTH_VALUE_FROM_LAST_IGNORE_NULL functions. But, the ELT Window Functions Snap supports them through special rewrites (using internal SQL rewrites) on Redshift. |
FIRST_VALUE_IGNORE_NULLS
nth Value
Enter a number for the nth value function.
This field is ignored by the FIRST_VALUE and the LAST_VALUE functions during the execution.
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
- Field
- Offset Value
- Default Value
- Alias Name
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.
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:
- LEAD_IGNORE_NULLS
- LEAD_RESPECT_NULLS
- LAG_IGNORE_NULLS
- LAG_RESPECT_NULLS
See Snowflake Lead and Lag Window Functions for more information.
- LEAD_IGNORE_NULLS
- LEAD_RESPECT_NULLS
- LAG_IGNORE_NULLS
- LAG_RESPECT_NULLS
See Redshift Lead and Lag Window Functions for more information.
- LEAD_RESPECT_NULLS
- LAG_RESPECT_NULLS
See Azure Synapse Lead and Lag Window Functions for more information.
- LEAD_RESPECT_NULLS
- LAG_RESPECT_NULLS
See Azure Databricks Lead and Lag window functions for more information.
Info |
---|
Redshift does not natively support the following default value expressions in the LEAD and LAG syntax which is supported by Snowflake and Azure Synapse. But, the ELT Window Functions Snap supports these functions through special rewrites:
|
LEAD_IGNORE_NULLS
list of supported bitwise aggregate functions.
Available options are:
Note |
---|
The Snap fails during Pipeline validation/execution if you specify an incorrect function name. |
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.
Click to add a row.
This field set contains the following fields:
- Value Window Function
- Field
- nth Value
- Alias Name
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:
Snowflake | Redshift | Azure Synapse |
---|---|---|
See Snowflake Window functions for more information. |
See Redshift Window functions for more information. |
See Azure Synapse Window functions for more information. |
Databricks Lakehouse Platform | BigQuery | |
See Azure Databricks Window Functions for more information. |
See BigQuery Navigation Functions for more information. |
Info |
---|
Redshift does not natively support the NTH_VALUE_FROM_LAST_RESPECT_NULL and NTH_VALUE_FROM_LAST_IGNORE_NULL functions. But, the ELT Window Functions Snap supports them through special rewrites (using internal SQL rewrites) on Redshift. |
FIRST_VALUE_IGNORE_NULLS
nth Value
Enter a number for the nth value function.
This field is ignored by the FIRST_VALUE and the LAST_VALUE functions during the execution.
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
- Field
- Offset Value
- Default Value
- Alias Name
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.
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:
Snowflake | Redshift | Azure Synapse |
---|---|---|
See Snowflake Lead and Lag Window Functions for more information. |
See Redshift Lead and Lag Window Functions for more information. |
See Azure Synapse Lead and Lag Window Functions for more information. |
Databricks Lakehouse Platform | BigQuery | |
See Azure Databricks Lead and Lag window functions for more information. |
See BigQuery Lead and Lag Window Functions for more information. |
Info |
---|
Redshift does not natively support the following default value expressions in the LEAD and LAG syntax which is supported by Snowflake and Azure Synapse. But, the ELT Window Functions Snap supports these functions through special rewrites:
|
LEAD_IGNORE_NULLS
Use this field set to add Linear Regression Related Window functions. Specify each function as a new row.
Click to add a row.
This field set contains the following fields:
- Window Function
- First Field
- Second Field
- Alias Name
Required. Enter the name of the Linear Regression function to use. You must ensure that it 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:
- CORR
- COVAR_POP
- COVAR_SAMP
- REGR_AVGX
- REGR_AVGY
- REGR_COUNT
- REGR_INTERCEPT
- REGR_R2
- REGR_SLOPE
- REGR_SXX
- REGR_SXY
- REGR_SYY
See Snowflake Window Functions for more information.
Info | ||
---|---|---|
| ||
These databases do not natively support the window functions listed above. But, the ELT Window Functions Snap supports all of these functions on Redshift, Azure Synapse, Databricks Lakehouse Platform, and BigQuery databases through special rewrites. |
Note | ||
---|---|---|
| ||
The Scale and Precision that the different databases (Snowflake/Redshift/Azure Synapse) allow for calculation of the values has a direct bearing on the accuracy of the Window Function values that the Snap returns in the output. |
This field set enables you to specify the list of aggregate concatenation functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click to add a row.
This field set contains the following fields:
- Aggregate Concatenation Function
- Field Name
- Alias Name
- Delimiter
Aggregate Concatenation Function
Required. Select the aggregate concatenation function to use. Click to retrieve the list of the supported functions. 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.
Alternatively, you can also enter the name of the aggregate concatenation function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.
You can use the following aggregate functions:
Snowflake | Redshift | BigQuery |
---|---|---|
|
|
|
See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.
Field Name
Alias Name
Delimiter
Use this field set to add Linear Regression Related Window functions. Specify each function as a new row.
Click toThis field set enables you to specify the list of percentile distribution functions to be used for aggregation. The list displays the functions supported by the database that you select in the Account settings. Each function must be specified as a new row. Click to add a row.
This field set contains the following fields:
- Percentile Distribution Function
- Percentile
- Alias Name
Percentile Distribution Function
Required.
Alternatively, click to view and select a function from the list of window functions. The
Select the percentile distribution function to use. Click to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings.
- CORR
- COVAR_POP
- COVAR_SAMP
- REGR_AVGX
- REGR_AVGY
- REGR_COUNT
- REGR_INTERCEPT
- REGR_R2
- REGR_SLOPE
- REGR_SXX
- REGR_SXY
- REGR_SYY
See Snowflake Window Functions for more information.
Info | ||
---|---|---|
| ||
These databases do not natively support the window functions listed above. But, the ELT Window Functions Snap supports all of these functions on Redshift, Azure Synapse, and Databricks Lakehouse Platform databases through special rewrites. |
title | Accuracy in Output Values |
---|
An error is displayed if an account is not configured. See the Troubleshooting section for details.
Alternatively, you can also enter the name of the percentile distribution function to use; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error.
You can use the following aggregate functions:
Snowflake | Redshift |
---|---|
|
|
BigQuery | |
|
PERCENTILE_
DISC
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
Note |
---|
You cannot apply the window framing clause, window ordering clause, or window partitioning clause to different functions in the same Snap. You must add another ELT Window Functions Snap and configure it with the target functions to do so. |
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
- Sort Order
- Null Value Sort Preference
Note |
---|
You cannot apply the window framing clause, window ordering clause, or window partitioning clause to different functions in the same Snap. You must add another ELT Window Functions Snap and configure it with the target functions to do so. |
Select the order in which to sort the rows.
Available options are:
- ASC
- DESC
Select whether to keep rows with null values first or last after the sort operation.
Available options are:
- NULLS FIRST
- NULLS LAST
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
- End of Window Frame
Note |
---|
|
Select the policy/condition to indicate the first row in the window frame.
Available options are:
- UNBOUNDED PRECEDING
- BOUNDED VALUE PRECEDING. Activates the Bounded Start Value field.
- CURRENT ROW
- BOUNDED VALUE FOLLOWING. Activates the Bounded Start Value field.
- UNBOUNDED VALUE FOLLOWING
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.
Select the policy/condition to indicate the last row in the window frame.
Available options are:
- UNBOUNDED PRECEDING
- BOUNDED VALUE PRECEDING. Activates the Bounded End Value field.
- CURRENT ROW
- BOUNDED VALUE FOLLOWING. Activates the Bounded End Value field.
- UNBOUNDED VALUE FOLLOWING
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.
...
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 | ||
---|---|---|
| ||
|
...