Filter, Group, and Aggregate Operators in Visual SQL
When you change the grouping or aggregation of a column via its aggregation menu or add a filter in your Query, you’re presented with different operator options depending on the column’s data type. Here are quick explanations for each operator you can use:
Filter operators
Text data types
| Operator | Description |
|---|---|
| = | equals |
| != | does not equal |
| is one of | equals one of the values in the specified list |
| is not one of | does not equal one of the values in the specified list |
| is null | does not contain a value |
| is not null | contains a value |
| like |
|
| is not like |
|
| matches regex | matches the specified regular expression |
| like (case insensitive) |
|
| not like (case insensitive) |
|
Number data types
| Operator | Description |
|---|---|
| = | equals |
| != | does not equal |
| is one of | equals one of the values in the specified list |
| is not one of | does not equal one of the values in the specified list |
| is null | does not contain a value |
| is not null | contains a value |
| > | greater than |
| >= | greater than or equal to |
| < | less than |
| <= | less than or equal to |
| between and including | between the specified range, including the end value e.g., “between and including 1 and 5” returns “1, 2, 3, 4, 5” |
Datetime data types
| Operator | Description |
|---|---|
| between and including | between the specified date range, including the end date e.g., “between 2020-06-30 and 2020-07-02” returns dates from 2020-06-30, 2020-07-01, and 2020-07-02 |
| is | the exact specified date |
| earlier than | before and not including the specified date |
| later than | after and not including midnight on the specified date |
| between N and M interval before now | N is the start; M is the end; does not include M Interval options: minutes, hours, days, weeks, months, years Examples for "between N and M days": If today is 2020-06-04...
|
| current interval | Interval options: day, week, month, quarter, year Note: "week" is the current ISO week |
| last N interval | last specified number of chosen interval; inclusive Interval options: minutes, hours, days, weeks, months, years For timestamps, subtracts N * 24 hours from the current timestamp |
| is null | does not contain a value |
| is not null | contains a value |
Boolean data types
| Operator | Description |
|---|---|
| is true | A boolean can have either a TRUE or FALSE value, where TRUE = 1 and FALSE = 0. |
| is false | |
| is not true | |
| is not false | |
| = | equals |
| != | does not equal |
| is one of | equals one of the values in the specified list |
| is not one of | does not equal one of the values in the specified list |
| is null | does not contain a value |
| is not null | contains a value |
| The operators below are only available when using the Filter Action (see note below table) | |
| > | greater than; only shown when using a Filter Action (see note below table) |
| >= | greater than or equal to; only shown when using a Filter Action (see note below table) |
| < | less than; only shown when using a Filter Action (see note below table) |
| <= | less than or equal to; only shown when using a Filter Action (see note below table) |
Note: Boolean columns become Number columns post-Query because all TRUE values are shown as 1s and all FALSE values are shown as 0s.
Group operators
Text data types
| Operator | Description |
|---|---|
| Group | Lists all distinct values |
| Count of distinct | Number of distinct values |
| Count of all | Number of all values, including duplicates |
| Minimum | Sorts the values in alphabetical order and returns the first value |
| Maximum | Sorts the values in alphabetical order and returns the last value |
| Unaggregated | Lists all values, including duplicates |
Datetime data types
| Operator | Description |
|---|---|
| Second | Buckets the time by seconds; only available for datetimes Format: YYYY-mm-ddTHH:MM:SS e.g., 2020-06-30T03:20:59 |
| Minute | Buckets the time by minutes; only available for datetimes Format: YYYY-mm-ddTHH:MM e.g., 2020-06-30T03:20 |
| Hour | Buckets the time by hours; only available for datetimes Format: YYYY-mm-ddTHH e.g., 2020-06-30T03 |
| Day | Buckets the time by days Format: YYYY-mm-dd e.g., 2020-06-30T03:20 |
| Week | Buckets the time by weeks Format: YYYY-W(01-52) e.g., 2020-W22 |
| Month | Buckets the time by months Format: YYYY-mm e.g., 2020-06 |
| Quarter | Buckets the time by quarters Format: YYYY-Q(1-4) e.g., 2020-Q2 |
| Year | Buckets the time by years Format: YYYY e.g., 2020 |
| Hour of Day | 00 to 24; only available for datetimes |
| Day of Week | Sunday, Monday, Tuesday, …, or Saturday |
| Day of Month | 01 to 31 |
| Day of Year | 001 to 366 |
| Month of Year | January, February, March, …, December |
| Count of distinct | Number of distinct datetime values |
| Count of all | Number of all datetime values, including duplicates |
| Minimum | The earliest datetime value |
| Maximum | The latest datetime value |
| Group | Lists all distinct datetime values |
| Unaggregated | Lists all datetime values, including duplicates |
Boolean data types
| Operator | Description |
|---|---|
| Group | Lists all distinct values |
| Count of distinct | Number of distinct values |
| Count of all | Number of all values, including duplicates |
| Unaggregated | Lists all values, including duplicates |
Aggregate operators
Number data types
| Operator | Description |
|---|---|
| Count of distinct | Number of distinct values |
| Approx count distinct | An approximation for the number of distinct values; less memory-intensive than "Count of distinct" so it's better for very large data sets |
| Count of all | Number of all values, including duplicates |
| Total sum | Sums all values |
| Average | Takes average of all values |
| Median | The middle number in a sorted list of numbers |
| Minimum | The smallest value |
| Maximum | The largest value |
| Standard Deviation | How close the numbers are to the mean; the square root of the variance Note: Snowflake uses a sample population to calculate the standard deviation |
| Variance | The average of the squared differences of the mean |
| Group | Lists all distinct values |
| Unaggregated | Lists all values, including duplicates |