In this article
...
Use this Snap to add aggregate functions such as COUNT, SUM, MIN, and MAX along with the GROUP BY clause in the incoming SQL query. GROUP BY clauses are used to group table records based on columns that contain classifying data and are optional in this Snap. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality. The supported aggregate functions vary based on the account configuration. See the description of the Aggregate Function field in the Snap Settings section for details.
Prerequisites
None.
Limitations
...
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
Snap Input and Output
...
Document
...
- Min: 1
- Max: 1
...
- ELT Select
- ELT Transform
...
When running without Sub-Query Pushdown Optimization (SPDO), ELT Pipelines that contain an ELT Aggregate Snap and configured with one or more GROUP BY ROLLUP fields, do not verify the column data types while inserting the Snap output values in the target table. This may lead to incorrect data written to the target table. However, as long as SPDO is on, the same Pipeline runs without this issue.
Snap Input and Output
Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| The modified SQL query with in which to add the aggregate functions and the optional GROUP BY clause. |
Output | Document |
|
| The modified SQL query with the aggregate functions and GROUP BY clause. |
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 Aggregate | Aggregate Revenue | ||||||||||||||||||||||
Get preview data | Checkbox |
| Not selected | Selected | ||||||||||||||||||||||
General Aggregate Functions List | This field set enables you to specify the columns for which to add the aggregate functions. Each aggregate function must be specified as a new row. Click to add a row. This field set contains the following fields:
| |||||||||||||||||||||||||
Aggregate Function | String/Expression/Suggestion | Required. Select the aggregate function to use. Click to retrieve the list of the supported aggregate 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 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:
Additionally, the following aggregate functions are available based on the database type:
See Snowflake Aggregate Functions, Redshift SQL Functions Reference, Aggregate Functions (Transact-SQL), Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function. | N/A | AVG | ||||||||||||||||||||||
Field Name | String/Expression | Required. Select the column on which to apply the aggregate function. | N/A | REVENUE | ||||||||||||||||||||||
Eliminate duplicates | Check box | Select to apply DISTINCT to the column specified in the Field field. This means that the aggregate function is applied only to the unique values in the column.
| Not selected | Selected | ||||||||||||||||||||||
Alias Name | String | Required. Specify the column 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 | TOTAL_REVENUE | ||||||||||||||||||||||
Linear Regression Aggregate Functions List | This field set enables you to specify the columns for which to apply the linear regression aggregate functions. Each function must be specified as a new row. Click to add a row. This field set contains the following fields:
| |||||||||||||||||||||||||
Aggregate Function | String/Expression/Suggestion | Required. Select the aggregate function to use. Click to retrieve the list of the supported linear regression aggregate functions. The list displays the functions supported by the database. An error is displayed if an account is not configured. See the Troubleshooting section for details. Alternatively, you can also enter the name of an aggregate function; you must, however, ensure that it is spelled correctly. Otherwise, the Snap displays an error. You can use the following aggregate functions:
See Snowflake Aggregate Functions, Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL forfor more information on the respective aggregate function.
| N/A | AVGCORR | ||||||||||||||||||||||
First Field Name | String/Expression | Required. Select Specify the first column on which to apply the aggregate function. | N/A | REVENUE | Eliminate duplicates | Check box |
Note | ||
---|---|---|
| ||
Selecting this checkbox while using COUNT_IF aggregate function does not eliminate duplicate records in case of Snowflake and BigQuery databases, as there is no native support. However, it eliminates the duplicates from the list of records when used with a Redshift, Azure Synapse, or Databricks Lakehouse Platform (DLP) instance. |
Second Field
This field set enables you to specify the
columns for which to apply the linear regression aggregate functions.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 linear regression aggregate functions. The list displays the functions supported by the databasedatabase 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 an 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:
CORRSnowflake |
---|
See Snowflake Aggregate Functions, Databricks Built-in Functions Reference, or BigQuery Aggregate Functions in Standard SQL for more information on the respective aggregate function.
Info | ||
---|---|---|
| ||
BigQuery does not natively support the above list of Linear Regression Aggregate Functions. However, SnapLogic provides you with the ability to use these functions with BigQuery through a series of internal query rewrites. |
Redshift | Azure Synapse | |
---|---|---|
|
|
|
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.
Second Field Name
Alias Name
Delimiter
This field set enables you to specify the list of aggregate concatenation 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:
- Aggregate Concatenation Percentile Distribution Function
- Field NamePercentile
- Alias Name
- Delimiter
Aggregate Concatenation Percentile Distribution Function
Required. Select the aggregate concatenation function 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. 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 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 |
---|
- LISTAGG
- LISTAGG_DISTINCT
- ARRAY_AGG
- ARRAY_AGG_DISTINCT
- LISTAGG
- DISTINCT_LISTAGG
- STRING_AGG
- ARRAY_AGG
- ARRAY_AGG_DISTINCT
- ARRAY_CONCAT_AGG
- STRING_AGG
- STRING_AGG_DISTINCT
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
This 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. 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. 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:
- PERCENTILE_DISC
- PERCENTILE_CONT
- APPROXIMATE_PERCENTILE_DISC
- PERCENTILE_CONT
See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.
PERCENTILE_DISC
|
|
See Snowflake Aggregate Functions or Redshift SQL Functions Reference for more information on the respective aggregate function.
PERCENTILE_DISC
This fieldset enables you to specify the columns for which to use the GROUP BY clause. Each column must be specified in a new row. Click to add a row.
This fieldset contains the following field:
- Argument
GRADE
GENDER
Select this checkbox to group the output data using the GROUP BY CUBE clause. This activates the GROUP BY CUBE Arguments List fieldset.
This fieldset enables you to specify the arguments for the GROUP BY CUBE clause. In addition to the GROUP BY ROLLUP, GROUP BY CUBE adds all the “cross-tabulations” rows and is equivalent to a series of grouping sets. Each argument must be specified in a new row. Click to add a row.
This fieldset contains the following field:
- Argument
CUST_ID
ORDERS
Select this checkbox to group the output data using the GROUP BY ROLLUP clause. This activates the GROUP BY ROLLUP Arguments List fieldset.
This fieldset enables you to specify the arguments for the GROUP BY ROLLUP clause that produces sub-total rows (in addition to the grouped rows). Each argument must be specified in a new row. Click to add a row.
This fieldset contains the following field:
- Argument
CATEGORY
REGION
Select this checkbox to group the output data using the GROUP BY GROUPING SETS clause. This activates the GROUP BY GROUPING SETS Arguments List fieldset.
This fieldset enables you to specify the arguments for the GROUP BY GROUPING SETS clause that that allows computing multiple GROUP BY clauses in a single statement. The group set is a set of dimension columns. Each argument must be specified in a new row. Click to add a row.
This fieldset contains the following field:
- Argument
CATEGORY
REGION
This fieldset enables you to specify the arguments for the HAVING predicates (conditions) over the result of applying a GROUP BY clause. Each predicate must be specified in a new row. Click + to add a row.
This field set fieldset contains the following field:
- GROUP BY Field
- Predicate
- Boolean Operator
GRADE
GENDERThis field set enables you to specify the columns by which to sort the output data set. Each column must be specified in a new row. Click + to add a row.
This field set contains the following fields:
- ORDER BY Field
- Sort Order
- Null Value Sort Preference
GRADE
GENDER
...