Introduction
This article lists all the Snowflake functions you can use with Astrato data objects, covering both alphanumeric and numeric data fields.
Note: These functions are only available if you are working with a Snowflake data view.
Adding Functions to a Data Object
Once you've added a data object, you'll be able to specify the dimensions and measures you want to use. When you add measures, you'll see a list of functions which you can use with the measure. Each function you select will affect the appearance of the data object in the sheet. Figure 1 shows an example of a chart which uses the COUNT function to show the number of customers for each channel.
Figure 1: Function Example
Numeric Functions
These functions can be used with numeric data fields (both whole number and floating point) and are listed as they appear in Astrato. We've also included the Snowflake syntax and a link to Snowflake's documentation reference.
Note: Null values are discarded for functions like Average, Median and Sum. If the data field holds only null values, a NULL value will be returned.
Any value - ANY_VALUE - Returns an expression value.
Average - AVG - Returns the average of all non-null values.
Count Records - COUNT - Returns the number of non-null values, or the total number of records.
Approx count distinct - APPROX_COUNT_DISTINCT - Returns the number of distinct values held for the data field.
Max - MAX - returns the maximum recorded value.
Median - MEDIAN - returns the median of all non-null values.
Min - MIN - returns the minimum recorded value.
Mode - MODE - returns the most frequent value.
Standard deviation - STDDEV - returns the sample standard deviation (the square root of sample variance) of non-null values.
Sum - SUM - Returns the sum of all non-null values.
Variance - VARIANCE, VARIANCE_SAMP, (VAR_SAMP) - Returns the sample variance of all non-null values.
Alphanumeric Functions
These functions can be used with alphanumeric data fields and are listed as they appear in Astrato. We've also included the Snowflake syntax and a link to the relevant page in the Snowflake documentation reference.
Any value - ANY_VALUE - Returns an expression value.
Count Records - COUNT - Returns the number of non-NULL values, or the total number of records.
Approx count distinct - APPROX_COUNT_DISTINCT - Returns the number of distinct values held for the data field.
Max - MAX - returns the maximum recorded value.
Min - MIN - returns the minimum recorded value.
Mode - MODE - returns the most frequent value.