ContextQMD
Back to Influxdb

Aggregate

content/shared/sql-reference/functions/aggregate.md

latest36.6 KB
Original Source

SQL aggregate functions aggregate values in a specified column for each group or SQL partition and return a single row per group containing the aggregate value.

General aggregate functions

array_agg

Returns an array created from the expression elements.

[!Note] array_agg returns a LIST Arrow type. Use bracket notation to reference the index of an element in the returned array. Arrays are 1-indexed.

sql
array_agg(expression)

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View array_agg query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  array_agg(temp)[3] AS '3rd_temp'
FROM home
GROUP BY room
room3rd_temp
Kitchen22.7
Living Room21.8

{{% /expand %}} {{< /expand-wrapper >}}

avg

Returns the average of numeric values in the specified column.

sql
avg(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
Aliases
  • mean

{{< expand-wrapper >}} {{% expand "View avg query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  avg(precip) AS avg_precip
FROM weather
GROUP BY location
locationavg_precip
Concord0.027120658135283374
Hayward0.03708029197080292
San Francisco0.03750912408759125

{{% /expand %}} {{< /expand-wrapper >}}

bit_and

Computes the bitwise AND of all non-null input values.

sql
bit_and(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View bit_and query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  bit_and(precip::BIGINT) AS precip_bit_and
FROM weather
GROUP BY location
locationprecip_bit_and
Concord0
Hayward0
San Francisco0

{{% /expand %}} {{< /expand-wrapper >}}

bit_or

Computes the bitwise OR of all non-null input values.

sql
bit_or(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View bit_or query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  bit_or(precip::BIGINT) AS precip_bit_or
FROM weather
GROUP BY location
locationprecip_bit_or
Concord7
Hayward7
San Francisco7

{{% /expand %}} {{< /expand-wrapper >}}

bit_xor

Computes the bitwise exclusive OR of all non-null input values.

sql
bit_xor(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View bit_xor query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  bit_xor(precip::BIGINT) AS precip_bit_xor
FROM weather
GROUP BY location
locationprecip_bit_xor
Concord4
Hayward6
San Francisco4

{{% /expand %}} {{< /expand-wrapper >}}

bool_and

Returns true if all non-null input values are true, otherwise returns false.

sql
bool_and(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View bool_and query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  bool_and(precip > 0) AS precip_bool_and
FROM weather
GROUP BY location
locationprecip_bool_and
Concordfalse
Haywardfalse
San Franciscofalse

{{% /expand %}} {{< /expand-wrapper >}}

bool_or

Returns true if any non-null input value is true, otherwise returns false.

sql
bool_or(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View bool_or query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  bool_or(precip > 0) AS precip_bool_or
FROM weather
GROUP BY location
locationprecip_bool_or
Concordtrue
Haywardtrue
San Franciscotrue

{{% /expand %}} {{< /expand-wrapper >}}

count

Returns the number of rows in the specified column.

Count includes null values in the total count. To exclude null values from the total count, include <column> IS NOT NULL in the WHERE clause.

sql
count(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View count query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  count(precip) AS precip_count
FROM weather
GROUP BY location
locationprecip_count
Concord1094
Hayward1096
San Francisco1096

{{% /expand %}} {{< /expand-wrapper >}}

first_value

Returns the first element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

sql
first_value(expression [ORDER BY expression])
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View first_value query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  first_value(temp_max ORDER BY time) AS temp_max_first_value
FROM weather
GROUP BY location
locationtemp_max_first_value
Concord59
Hayward57
San Francisco66

{{% /expand %}} {{< /expand-wrapper >}}

grouping

Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set.

sql
grouping(expression)
Arguments
  • expression: Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function.

{{< expand-wrapper >}} {{% expand "View grouping query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  avg(temp_max) AS avg_max_temp,
  grouping(location) AS grouping
FROM weather
GROUP BY GROUPING SETS ((location), ())
locationavg_max_tempgrouping
Concord75.543795620437960
Hayward69.120437956204380
San Francisco67.599452554744530
70.754562043795621

{{% /expand %}} {{< /expand-wrapper >}}

last_value

Returns the last element in an aggregation group according to the specified ordering. If no ordering is specified, returns an arbitrary element from the group.

sql
last_value(expression [ORDER BY expression])
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View last_value query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  last_value(temp_max ORDER BY time) AS temp_max_last_value
FROM weather
GROUP BY location
locationtemp_max_last_value
Concord59
Hayward58
San Francisco62

{{% /expand %}} {{< /expand-wrapper >}}

max

Returns the maximum value in the specified column.

sql
max(expression)

To return both the maximum value and its associated timestamp, use selector_max.

Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View max query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  max(precip) AS max_precip
FROM weather
GROUP BY location
locationmax_precip
Concord4.53
Hayward4.34
San Francisco4.02

{{% /expand %}} {{< /expand-wrapper >}}

mean

Alias of avg.

median

Returns the median value in the specified column.

median(expression)

Arguments

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View median query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  median(temp_avg) AS median_temp_avg
FROM weather
GROUP BY location
locationmedian_temp_avg
Concord61.0
Hayward59.0
San Francisco58.0

{{% /expand %}} {{< /expand-wrapper >}}

min

Returns the minimum value in the specified column.

sql
min(expression)

To return both the minimum value and its associated timestamp, use selector_max.

Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View min query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  min(temp_min) AS min_temp_min
FROM weather
GROUP BY location
locationmin_temp_min
Concord28.0
Hayward32.0
San Francisco35.0

{{% /expand %}} {{< /expand-wrapper >}}

nth_value

Returns the nth value in a group of values.

sql
nth_value(expression, n [ORDER BY order_expression_1, ... order_expression_n])
arguments
  • expression: The column or expression to retrieve the nth value from.
  • n: The position (nth) of the value to retrieve, based on the ordering.
  • order_expression_1, ... order_expression_n: Expressions to order by. Can be a column or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View nth_value query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  nth_value(temp, 3 ORDER BY time) AS "3rd_temp"
FROM
  home
GROUP BY
  room
room3rd_temp
Living Room21.8
Kitchen22.7

{{% /expand %}} {{< /expand-wrapper >}}

string_agg

Concatenates the values of string expressions and places separator values between them.

sql
string_agg(expression, delimiter)
Arguments
  • expression: The string expression to concatenate. Can be a column or any valid string expression.
  • delimiter: A literal string to use as a separator between the concatenated values.

{{< expand-wrapper >}} {{% expand "View string_agg query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT
  location,
  string_agg(temp_avg::STRING, ', ') AS string_agg
FROM 
  weather
WHERE
  time > '2020-01-01T00:00:00Z'
  AND time < '2020-01-05T00:00:00Z'
GROUP BY
  location
locationstring_agg
San Francisco54.0, 52.0, 54.0
Hayward51.0, 50.0, 51.0
Concord53.0, 49.0, 51.0

{{% /expand %}} {{< /expand-wrapper >}}

sum

Returns the sum of all values in the specified column.

sql
sum(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View sum query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  sum(precip) AS total_precip
FROM weather
GROUP BY location
locationtotal_precip
Concord29.670000000000012
Hayward40.64
San Francisco41.110000000000014

{{% /expand %}} {{< /expand-wrapper >}}

Statistical aggregate functions

corr

Returns the coefficient of correlation between two numeric values.

sql
corr(expression1, expression2)
Arguments
  • expression1: First column or literal value to operate on.
  • expression2: Second column or literal value to operate on.

{{< expand-wrapper >}} {{% expand "View corr query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  corr(hum, temp) AS correlation
FROM home
GROUP BY room
roomcorrelation
Living Room0.43665270457835725
Kitchen0.6741766954929539

{{% /expand %}} {{< /expand-wrapper >}}

covar

Returns the covariance of a set of number pairs.

sql
covar(expression1, expression2)
Arguments
  • expression1: First column or literal value to operate on.
  • expression2: Second column or literal value to operate on.

{{< expand-wrapper >}} {{% expand "View covar query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  covar(hum, temp) AS covar
FROM home
GROUP BY room
roomcovar
Living Room0.03346153846153959
Kitchen0.11134615384615432

{{% /expand %}} {{< /expand-wrapper >}}

covar_pop

Returns the population covariance of a set of number pairs.

sql
covar_pop(expression1, expression2)
Arguments
  • expression1: First column or literal value to operate on.
  • expression2: Second column or literal value to operate on.

{{< expand-wrapper >}} {{% expand "View covar_pop query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  covar_pop(hum, temp) AS covar_pop
FROM home
GROUP BY room
roomcovar_pop
Kitchen0.10278106508875783
Living Room0.030887573964498087

{{% /expand %}} {{< /expand-wrapper >}}

covar_samp

Returns the sample covariance of a set of number pairs.

sql
covar_samp(expression1, expression2)
Arguments
  • expression1: First column or literal value to operate on.
  • expression2: Second column or literal value to operate on.

{{< expand-wrapper >}} {{% expand "View covar_samp query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  covar_samp(hum, temp) AS covar_samp
FROM home
GROUP BY room
roomcovar_samp
Kitchen0.11134615384615432
Living Room0.03346153846153959

{{% /expand %}} {{< /expand-wrapper >}}

regr_avgx

Computes the average of the independent variable (input), expression_x, for the non-null dependent variable, expression_y.

sql
regr_avgx(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_avgx query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_avgx(temp_min, temp_max) AS temp_regr_avgx
FROM weather
GROUP BY location
locationtemp_regr_avgx
Concord75.54379562043796
Hayward69.14808043875686
San Francisco67.59945255474454

{{% /expand %}} {{< /expand-wrapper >}}

regr_avgy

Computes the average of the dependent variable (output), expression_y, for the non-null dependent variable, expression_y.

sql
regr_avgy(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_avgy query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_avgy(temp_min, temp_max) AS temp_regr_avgy
FROM weather
GROUP BY location
locationtemp_regr_avgy
Concord50.153284671532845
Hayward50.913162705667276
San Francisco51.52372262773722

{{% /expand %}} {{< /expand-wrapper >}}

regr_count

Counts the number of non-null paired data points.

sql
regr_count(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_count query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_count(temp_min, temp_max) AS temp_regr_count
FROM weather
GROUP BY location
locationtemp_regr_count
Concord1096
Hayward1094
San Francisco1096

{{% /expand %}} {{< /expand-wrapper >}}

regr_intercept

Computes the y-intercept of the linear regression line. For the equation (y = kx + b), this function returns b.

sql
regr_intercept(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_intercept query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_intercept(temp_min, temp_max) AS temp_regr_intercept
FROM weather
GROUP BY location
locationtemp_regr_intercept
Concord11.636281392206769
Hayward12.876956842745152
San Francisco19.125237647086607

{{% /expand %}} {{< /expand-wrapper >}}

regr_r2

Computes the square of the correlation coefficient between the independent and dependent variables.

sql
regr_r2(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_r2 query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_r2(temp_min, temp_max) AS temp_regr_r2
FROM weather
GROUP BY location
locationtemp_regr_r2
Concord0.6474628308450441
Hayward0.5166296626320914
San Francisco0.5032317511200297

{{% /expand %}} {{< /expand-wrapper >}}

regr_slope

Returns the slope of the linear regression line for non-null pairs in aggregate columns. Given input column Y and X: regr_slope(Y, X) returns the slope (k in Y = k*X + b) using minimal RSS fitting.

sql
regr_slope(expression_y, expression_x)
Arguments
  • expression_y: Y expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: X expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_slope query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_slope(temp_min, temp_max) AS temp_regr_slope
FROM weather
GROUP BY location
locationtemp_regr_slope
Concord0.5098632252058237
Hayward0.5500688612261629
San Francisco0.4792714105844738

{{% /expand %}} {{< /expand-wrapper >}}

regr_sxx

Computes the sum of squares of the independent variable.

sql
regr_sxx(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_sxx query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_sxx(temp_min, temp_max) AS temp_regr_sxx
FROM weather
GROUP BY location
locationtemp_regr_sxx
Concord210751.89781021897
Hayward99644.01096892142
San Francisco77413.15967153282

{{% /expand %}} {{< /expand-wrapper >}}

regr_syy

Computes the sum of squares of the dependent variable.

sql
regr_syy(expression_y, expression_x)
Arguments
  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_syy query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_syy(temp_min, temp_max) AS temp_regr_syy
FROM weather
GROUP BY location
locationtemp_regr_syy
Concord84618.24817518248
Hayward58358.750457038404
San Francisco35335.38321167884

{{% /expand %}} {{< /expand-wrapper >}}

regr_sxy

Computes the sum of products of paired data points.

sql
regr_sxy(expression_y, expression_x)

Arguments

  • expression_y: Dependent variable. Can be a constant, column, or function, and any combination of arithmetic operators.
  • expression_x: Independent variable. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View regr_sxy query example" %}}

The following example uses the NOAA Bay Area weather data.

sql
SELECT 
  location,
  regr_sxy(temp_min, temp_max) AS temp_regr_sxy
FROM weather
GROUP BY location
locationtemp_regr_sxy
Concord107454.64233576645
Hayward54811.06764168191
San Francisco37101.914233576645

{{% /expand %}} {{< /expand-wrapper >}}

stddev

Returns the standard deviation of a set of numbers.

sql
stddev(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View stddev query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  stddev(co) AS stddev
FROM home
GROUP BY room
roomstddev
Living Room5.885662718931967
Kitchen9.321879418735037

{{% /expand %}} {{< /expand-wrapper >}}

stddev_pop

Returns the population standard deviation of a set of numbers.

sql
stddev_pop(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View stddev_pop query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  stddev_pop(co) AS stddev_pop
FROM home
GROUP BY room
roomstddev_pop
Kitchen8.956172047894082
Living Room5.654761830612032

{{% /expand %}} {{< /expand-wrapper >}}

stddev_samp

Returns the sample standard deviation of a set of numbers.

sql
stddev_samp(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View stddev_samp query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  stddev_samp(co) AS stddev_samp
FROM home
GROUP BY room
roomstddev_samp
Living Room5.885662718931967
Kitchen9.321879418735037

{{% /expand %}} {{< /expand-wrapper >}}

var

Returns the statistical variance of a set of numbers.

sql
var(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View var query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  var(co) AS var
FROM home
GROUP BY room
roomvar
Living Room34.64102564102564
Kitchen86.89743589743587

{{% /expand %}} {{< /expand-wrapper >}}

var_pop

Returns the statistical population variance of a set of numbers.

sql
var_pop(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
Aliases
  • var_population

{{< expand-wrapper >}} {{% expand "View var_pop query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  var_pop(co) AS var_pop
FROM home
GROUP BY room
roomvar_pop
Living Room31.976331360946745
Kitchen80.21301775147927

{{% /expand %}} {{< /expand-wrapper >}}

var_population

Alias of var_pop.

var_samp

Returns the statistical sample variance of a set of numbers.

sql
var_samp(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
Aliases
  • var_sample

{{< expand-wrapper >}} {{% expand "View var_samp query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  var_samp(co) AS var_samp
FROM home
GROUP BY room
roomvar_samp
Kitchen86.89743589743587
Living Room34.64102564102564

{{% /expand %}} {{< /expand-wrapper >}}

var_sample

Alias of var_samp.

Approximate aggregate functions

approx_distinct

Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm.

sql
approx_distinct(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View approx_distinct query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  approx_distinct(co::string) AS approx_distinct
FROM home
GROUP BY room
roomapprox_distinct
Living Room7
Kitchen8

{{% /expand %}} {{< /expand-wrapper >}}

approx_median

Returns the approximate median (50th percentile) of input values. It is an alias of approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY expression).

sql
approx_median(expression)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

{{< expand-wrapper >}} {{% expand "View approx_median query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  approx_median(temp) AS approx_median
FROM home
GROUP BY room
roomapprox_median
Kitchen22.7
Living Room22.3

{{% /expand %}} {{< /expand-wrapper >}}

approx_percentile_cont

Returns the approximate percentile of input values using the t-digest algorithm.

sql
approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont(expression, percentile, centroids)
Arguments

  • percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).

  • centroids: Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.

  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

    If there are this number or fewer unique values, you can expect an exact result. A higher number of centroids results in a more accurate approximation, but requires more memory to compute.

{{< expand-wrapper >}} {{% expand "View approx_percentile_cont query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  approx_percentile_cont(0.99) WITHIN GROUP (ORDER BY temp) AS "99th_percentile"
FROM home
GROUP BY room
room99th_percentile
Kitchen23.3
Living Room22.8

{{% /expand %}} {{< /expand-wrapper >}}

approx_percentile_cont_with_weight

Returns the weighted approximate percentile of input values using the t-digest algorithm.

sql
approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
-- OR
approx_percentile_cont_with_weight(expression, weight, percentile)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • weight: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.
  • percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
  • centroids: Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.

{{< expand-wrapper >}} {{% expand "View approx_percentile_cont_with_weight query example" %}}

The following example uses the {{< influxdb3/home-sample-link >}}.

sql
SELECT
  room,
  approx_percentile_cont_with_weight(co, 0.99) WITHIN GROUP (ORDER BY temp) AS "co_weighted_99th_percentile"
FROM home
GROUP BY room
roomco_weighted_99th_percentile
Kitchen23.3
Living Room22.8

{{% /expand %}} {{< /expand-wrapper >}}