Aggregation functions
#
avgavg(value)
calculates simple average of values ignoring missing data (e.g
null
values).
Arguments:
value
is any numeric value.
Return value:
Return value type is double
.
Examples:
avg |
---|
22.4 |
cash_or_card | avg |
---|---|
cash | 22.1 |
card | 27.4 |
null | 18.02 |
#
countcount()
or count(*)
- counts rows irrespective of underlying data.
Arguments:
count
does not require arguments.
Return value:
Return value type is long
.
Examples:
- Count of rows in the transactions table.
count |
---|
100 |
- Count of rows in the transactions table aggregated by
payment_type
value.
cash_or_card | count |
---|---|
cash | 25 |
card | 70 |
null | 5 |
note
null
values are aggregated with count()
.
#
haversine_dist_deghaversine_dist_deg(lat, lon, ts)
- calculates the traveled distance for a
series of latitude and longitude points.
Arguments:
lat
is the latitude expressed as degrees in decimal format (double
)lon
is the longitude expressed as degrees in decimal format (double
)ts
is thetimestamp
for the data point
Return value:
Return value type is double
.
Examples:
#
ksumksum(value)
- adds values ignoring missing data (e.g null
values). Values
are added using the
Kahan compensated sum algorithm.
This is only beneficial for floating-point values such as float
or double
.
Arguments:
value
is any numeric value.
Return value:
Return value type is the same as the type of the argument.
Examples:
ksum |
---|
52.79143968514029 |
#
maxmax(value)
- returns the highest value ignoring missing data (e.g null
values).
Arguments:
value
is any numeric value
Return value:
Return value type is the same as the type of the argument.
Examples:
min |
---|
55.3 |
cash_or_card | amount |
---|---|
cash | 31.5 |
card | 55.3 |
null | 29.2 |
#
minmin(value)
- returns the lowest value ignoring missing data (e.g null
values).
Arguments:
value
is any numeric value
Return value:
Return value type is the same as the type of the argument.
Examples:
min |
---|
12.5 |
cash_or_card | min |
---|---|
cash | 12.5 |
card | 15.3 |
null | 22.2 |
#
nsumnsum(value)
- adds values ignoring missing data (e.g null
values). Values
are added using the
Neumaier sum algorithm.
This is only beneficial for floating-point values such as float
or double
.
Arguments:
value
is any numeric value.
Return value:
Return value type is the same as the type of the argument.
Examples:
nsum |
---|
49.5442334742831 |
#
sumsum(value)
- adds values ignoring missing data (e.g null
values).
Arguments:
value
is any numeric value.
Return value:
Return value type is the same as the type of the argument.
Examples:
sum |
---|
100 |
item | count |
---|---|
apple | 53 |
orange | 47 |
#
Overflowsum
does not perform overflow check. To avoid overflow, you can cast the
argument to wider type.