Aggregate functions
This page describes the available functions to assist with performing aggregate calculations.
#
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()
.
#
count_distinctcount_distinct(STRING_COL)
or count_distinct(SYMBOL_COL)
- counts distinct
values in STRING
or SYMBOL
columns.
Return value:
Return value type is long
.
Examples:
- Count of distinct sides in the transactions table. Side column can either be
BUY
orSELL
ornull
count_distinct |
---|
2 |
- Count of distinct counterparties in the transactions table aggregated by
payment_type
value.
cash_or_card | count_distinct |
---|---|
cash | 3 |
card | 23 |
null | 5 |
note
null
values are not counted in `count_distinct
functions.
#
firstfirst(SYMBOL)
- returns the first value of a SYMBOL
column.
Return value:
Return value type is string
.
Examples:
Given a table with the following contents:
device_id | temperature | ts |
---|---|---|
arduino-01 | 12 | 2021-06-02T14:33:19.970258Z |
arduino-02 | 10 | 2021-06-02T14:33:21.703934Z |
arduino-03 | 18 | 2021-06-02T14:33:23.707013Z |
The following query returns the first symbol value for the device_id
column
which is of SYMBOL
type:
first |
---|
arduino-01 |
#
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 |
#
lastlast(SYMBOL)
- returns the last value of a SYMBOL
column.
Return value:
Return value type is string
.
Examples:
Given a table with the following contents:
device_id | temperature | ts |
---|---|---|
arduino-01 | 12 | 2021-06-02T14:33:19.970258Z |
arduino-02 | 10 | 2021-06-02T14:33:21.703934Z |
arduino-03 | 18 | 2021-06-02T14:33:23.707013Z |
The following query returns the last symbol value for the device_id
column
which is of SYMBOL
type:
last |
---|
arduino-03 |
#
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:
max |
---|
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 double
.
Examples:
nsum |
---|
49.5442334742831 |
#
stddev_sampstddev_samp(value)
- calculates the sample standard deviation of values ignoring missing data (e.g
null
values).
Arguments:
value
is any numeric value.
Return value:
Return value type is double
.
Examples:
stddev_samp |
---|
29.011491975882 |
#
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.