distinct
distinct, aggregator
def process distinct(value: boolean): number
def process distinct(value: date ): number
def process distinct(value: number ): number
def process distinct(value: text ): number
def process distinct(value: enum ): number
The aggregator returns the number of distinct elements in a group. Two elements a and b are considered identical iff a == b holds true.
If the group is empty, this aggregator returns 0.
Examples
table T = with
[| as A, as B |]
[| 1, "a" |]
[| 1, "a" |]
[| 2, "b" |]
[| 3, "b" |]
[| 4, "c" |]
table G[gdim] = by T.B
where T.B != "c"
show table "" a1b4 with
gdim
distinct(T.A)
group by gdim
The above code results in the following table:
| gdim | distinct(T.A) |
|---|---|
| a | 1 |
| b | 2 |
| c | 0 |
Since "c" is filtered out by where T.B != "c", distinct(T.A) returns 0 for this group.
Recipes and best practices
The use of distinct is not recommended for large datasets consisting of number and text values, as this may incur performance problems. If you only need an approximation of the number of distinct values, use distinctapprox instead. If you only need to check whether the group contains at least two distinct elements, use the much faster areSame aggregator instead.