SLS mathematical and statistical functions: syntax and examples-Simple Log Service(SLS)-阿里云帮助中心

Syntax and examples for mathematical and statistical functions in Simple Log Service.

SLS supports the following mathematical and statistical functions.

Important

If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.

Category	Function	Syntax	Description	SQL	SPL
Correlation functions	corr function	corr(x, y)	Returns the coefficient of correlation between x and y. The returned value is in the range [-1, 1].	√	×
Variance and standard deviation functions	covar_pop function	covar_pop(x, y)	Returns the population covariance of x and y.	√	×
	covar_samp function	covar_samp(x, y)	Returns the sample covariance of x and y.	√	×
	stddev function	stddev(x)	Returns the sample standard deviation of x. This function is an alias for the `stddev_samp` function.	√	×
	stddev_samp function	stddev_samp(x)	Returns the sample standard deviation of x.	√	×
	stddev_pop function	stddev_pop(x)	Returns the population standard deviation of x.	√	×
	variance function	variance(x)	Returns the sample variance of x. Alias for the `var_samp` function.	√	×
	var_samp function	var_samp(x)	Returns the sample variance of x.	√	×
	var_pop function	var_pop(x)	Returns the population variance of x.	√	×
Linear regression functions	regr_intercept function	regr_intercept(y, x)	Fit a linear equation to the input points `(x,y)`, and then calculate the y-intercept of the line.	√	×
Linear regression functions	regr_slope function	regr_slope(y, x)	Fit a linear equation to the input point `(x,y)`, and then calculate the slope of the line.	√	×
Cumulative distribution functions (CDF)	beta_cdf function	beta_cdf(α, β, v)	Calculates the cumulative distribution function (CDF) of the beta distribution, P(N <= v; α, β), where α and β are the shape parameters.	√	×
	binomial_cdf function	binomial_cdf(x, y, v)	Calculates the cumulative distribution function (CDF) of the binomial distribution, P(N <= v), where x is the number of trials and y is the probability of success for each trial.	√	×
	cauchy_cdf function	cauchy_cdf(x, y, v)	Calculates the cumulative distribution function (CDF) of the Cauchy distribution, P(N <= v; x, y), where x is the location parameter (peak position) and y is the scale parameter.	√	×
	chi_squared_cdf function	chi_squared_cdf(k, v)	Calculates the cumulative distribution function (CDF) of the chi-square distribution, P(N <= v; k), where k is the degree of freedom.	√	×
	inverse_beta_cdf function	inverse_beta_cdf(α, β, p)	For a given probability p, calculates the value v such that P(N <= v; α, β) = p.	√	×
	inverse_binomial_cdf function	inverse_binomial_cdf(x, y, p)	For a given probability p, calculates the value v such that P(N <= v) = p.	√	×
	inverse_cauchy_cdf function	inverse_cauchy_cdf(x, y, p)	For a given probability p, calculates the value v such that P(N <= v; x, y) = p.	√	×
	inverse_chi_squared_cdf function	inverse_chi_squared_cdf(k, p)	For a given probability p, calculates the value v such that P(N <= v; k) = p.	√	×
	inverse_laplace_cdf function	inverse_laplace_cdf(μ, b, p)	For a given probability p, calculates the value v such that P(N <= v; μ, b) = p.	√	×
	inverse_normal_cdf function	inverse_normal_cdf(x, y, p)	For a given probability p, calculates the value v such that P(N <= v; x, y) = p.	√	×
	inverse_poisson_cdf function	inverse_poisson_cdf(x, y, p)	For a given probability p, calculates the value v such that P(N <= v; λ) = p.	√	×
	inverse_weibull_cdf function	inverse_weibull_cdf(x, y, p)	For a given probability p, calculates the value v such that P(N <= v; x, y) = p.	√	×
	laplace_cdf function	laplace_cdf(μ, b, v)	Calculates the cumulative distribution function (CDF) of the Laplace distribution, P(N <= v; μ, b), where μ is the location parameter and b is the scale parameter.	√	×
	normal_cdf function	normal_cdf(x, y, v)	Calculates the cumulative distribution function (CDF) of the normal distribution, P(N <= v; x, y), where x is the mean value and y is the standard deviation.	√	×
	poisson_cdf function	poisson_cdf(λ, v)	Calculates the cumulative distribution function (CDF) of the Poisson distribution, P(N <= v; λ), where λ is the average event rate.	√	×
	weibull_cdf function	weibull_cdf(x, y, v)	Calculates the cumulative distribution function (CDF) of the Weibull distribution, P(N <= v; x, y), where x is the scale parameter and y is the shape parameter.	√	×

corr function

Returns the correlation coefficient between x and y. Higher values indicate stronger correlation.

Syntax
```
corr(x, y)
```

Parameters

Parameter	Description
x	A double value.
y	A double value.

Return value type

A double value in the range [0, 1].
Examples

Calculates the correlation coefficient between the request_length and request_time fields.
- Query statement
```
* | SELECT corr(request_length,request_time)
```
- The correlation coefficient between request_length and request_time is 0.0008096234574114261.

covar_pop function

Calculates the population covariance of x and y.

Syntax
```
covar_pop(x, y)
```

Parameters

Parameter	Description
x	A double value.
y	A double value.

Return value type

A double value.
Examples

Calculates the population covariance of pretax profit and pretax turnover per minute.
- Query statement
```
*|
SELECT
  covar_pop(PretaxGrossAmount, PretaxAmount) AS "population covariance",
  time_series(__time__, '1m', '%H:%i:%s', '0') AS time
GROUP BY
  time
```
- The query returns two rows. The population covariance values are 4.402252063414699 and 2.2477524300733857, and the time values are 04:24:00 and 04:47:00, respectively.

covar_samp function

Calculates the sample covariance of x and y.

Syntax
```
covar_samp(x, y)
```

Parameters

Parameter	Description
x	A double value.
y	A double value.

Return value type

A double value.
Examples

Calculates the sample covariance of pretax profit and pretax turnover per minute.
- Query statement
```
*|
SELECT
  covar_samp(PretaxGrossAmount, PretaxAmount) AS "Sample covariance",
  time_series(__time__, '1m', '%H:%i:%s', '0') AS time
GROUP BY
  time
```
- The query returns two rows. The Sample covariance is 2.2910940581194376 when time is 15:50:00, and 4.721554417070316 when time is 15:49:00.

stddev function

Returns the sample standard deviation of x. Alias for the stddev_samp function.

Syntax
```
stddev(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

Returns a double value.

Examples

Calculates the sample and population standard deviations of pretax income and displays the results in a line chart.

Query statement

* |
SELECT
  stddev(PretaxGrossAmount) as "Sample standard deviation",
  stddev_pop(PretaxGrossAmount) as "Population standard deviation",
  time_series(__time__, '1m', '%H:%i:%s', '0') AS time
GROUP BY
  time

Query and analysis results

stddev_samp function

Calculates the sample standard deviation of x.

Syntax
```
stddev_samp(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

A double value.

Examples

Calculates the sample and population standard deviations of pretax income and displays the results as a line chart.

Query statement

* |
SELECT
  stddev_samp(PretaxGrossAmount) as "Sample standard deviation",
  stddev_pop(PretaxGrossAmount) as "Population standard deviation",
  time_series(__time__, '1m', '%H:%i:%s', '0') AS time
GROUP BY
  time

Query and analysis results

stddev_pop function

Calculates the population standard deviation of x.

Syntax
```
stddev_pop(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

A double value.

Examples

Calculates the sample and population standard deviations of pretax income and displays the results as a line chart.

Query statement

* |
SELECT
  stddev(PretaxGrossAmount) as "Sample standard deviation",
  stddev_pop(PretaxGrossAmount) as "Population standard deviation",
  time_series(__time__, '1m', '%H:%i:%s', '0') AS time
GROUP BY
  time

Query results

variance function

Returns the sample variance of x. This function is an alias for the var_samp function.

Syntax
```
variance(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

A double value.

Examples

Calculates the sample and population variance of pretax income and displays the results as a line chart.

Query statement

* |
SELECT
  variance(PretaxGrossAmount) as "Sample variance",
  var_pop(PretaxGrossAmount) as "Population variance",
  time_series(__time__, '1m', '%H:%i:%s', '0') as time
GROUP BY
  time

Query and analysis results

var_samp function

Calculates the sample variance of x.

Syntax
```
var_samp(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

Returns a double value.

Examples

Calculates the sample and population variance of pretax income and displays the results in a line chart.

Query and analysis statement

* |
SELECT
  var_samp(PretaxGrossAmount) as "Sample variance",
  var_pop(PretaxGrossAmount) as "Population variance",
  time_series(__time__, '1m', '%H:%i:%s', '0') as time
GROUP BY
  time

Query and analysis results

var_pop function

Returns the population variance of x.

Syntax
```
var_pop(x)
```

Parameters

Parameter	Description
x	A double or bigint value.

Return value type

A double value.

Examples

Calculates the sample and population variance of pretax income and displays the results in a line chart.

Query statement

* |
SELECT
  variance(PretaxGrossAmount) as "Sample variance",
  var_pop(PretaxGrossAmount) as "Population variance",
  time_series(__time__, '1m', '%H:%i:%s', '0') as time
GROUP BY
  time

Query and analysis results

regr_intercept function

Fits a linear equation to a set of input (x,y) pairs and returns the y-intercept of the resulting regression line. y is the dependent value, and x is the independent value.

Syntax
```
regr_intercept(y, x)
```

Parameters

Parameter	Description
y	A double value.
x	A double value.

Return value type

Returns a double value.
Examples

Calculates the y-intercept of the regression line from the request_time and request_length fields.
- Query statement
```
* | SELECT regr_intercept(request_length,request_time)
```
- Query results: The value in the _col0 column is 4128.22910642988.

regr_slope function

Fits a linear equation to the input (x,y) pairs and returns the slope of the regression line. y is the dependent value, and x is the independent value.

Syntax
```
regr_slope(y, x)
```

Parameters

Parameter	Description
y	The dependent value. A double.
x	The independent value. A double.

Return value type

Returns a double value.
Examples

Calculates the slope of the regression line from the request_time and request_length fields.
- Query statement
```
* | SELECT regr_slope(request_length,request_time)
```
- The query result is 1.9022724330993215.

beta_cdf function

Calculates the CDF of the beta distribution.

Syntax
```
beta_cdf(α, β, v)
```

Parameters

Parameter	Description
α	A parameter of the `beta distribution`. The value must be a `double` and greater than 0.
β	A parameter of the `beta distribution`. The value must be a `double` and greater than 0.
v	The value at which to evaluate the `cumulative distribution function`. The value must be a `double` in the range [0, 1].

Return value type

double
Examples
- Query statement
```
* | SELECT beta_cdf(0.1, 0.5, 0.7) 
```
- Query and analysis results: 0.8926585878364057

binomial_cdf function

Calculates the cumulative distribution function of the binomial distribution.

Syntax
```
binomial_cdf(x, y, v)
```

Parameters

Parameter	Description
x	The number of trials. Must be a positive integer.
y	The probability of success for each trial. Must be a double in the range [0, 1].
v	The number of successes. Must be an integer.

Return value type

Returns a double.
Examples
- Query statement
```
* | select binomial_cdf(10, 0.1, 1)
```
- The query returns 0.7360989291000001.

cauchy_cdf function

Calculates the CDF of the Cauchy distribution.

Syntax
```
cauchy_cdf(x, y, v)
```

Parameters

Parameter	Description
x	The location parameter, a double value that defines the peak of the distribution.
y	The scale parameter, which must be a double value greater than 0.
v	A double value at which to evaluate the cumulative distribution function.

Return value type

Returns a double value.
Examples
- Query statement
```
* | select cauchy_cdf(-10, 5, -12) 
```
- The query returns 0.3788810584091566.

chi_squared_cdf function

Calculates the CDF of the chi-square distribution.

Syntax
```
chi_squared_cdf(k, v)
```

Parameters

Parameter	Description
k	The degrees of freedom for the chi-square distribution. Must be a double value greater than 0.
v	The value at which to evaluate the cumulative distribution function. Must be a double value greater than or equal to 0.

Return value type

A double value.
Examples
- Query statement
```
* | select chi_squared_cdf(3, 10) 
```
- Result: 0.9814338645369567.

inverse_beta_cdf function

Calculates the inverse CDF of the beta distribution.

Syntax
```
inverse_beta_cdf(α, β, p)
```

Parameters

Parameter	Description
α	A shape parameter for the beta distribution. The value must be a double and greater than 0.
β	A shape parameter for the beta distribution. The value must be a double and greater than 0.
p	The input probability for the inverse cumulative distribution function. The value must be a double in the range [0, 1].

Return value type

Returns a double value.
Examples
- Query statement
```
* | select inverse_beta_cdf(0.1, 0.5, 0.8926585878364057)
```
- Query and analysis result: The value of _col0 is 0.699999999279748.

inverse_binomial_cdf function

Calculates the inverse CDF of the binomial distribution.

Syntax
```
inverse_binomial_cdf(x, y, p)
```

Parameters

Parameter	Description
x	The number of trials, a positive integer.
y	The probability of success for each trial, a double in the range [0, 1].
p	The target probability, a double in the range [0, 1].

Return value type

Returns an integer.

Examples

Query statement

* | select inverse_binomial_cdf(10, 0.1, 0.7360989291000001)

The query and analysis result is 1.

inverse_cauchy_cdf function

Calculates the inverse CDF of the Cauchy distribution.

Syntax
```
inverse_cauchy_cdf(x, y, p)
```

Parameters

Parameter	Description
x	The location parameter, a `double` that specifies the distribution's peak.
y	The scale parameter, a `double` greater than 0.
p	The input probability, a `double` in the range [0, 1].

Return value type

Returns a double value.
Examples
- Query statement
```
* | select inverse_cauchy_cdf(-10, 5, 0.3788810584091566)  
```
- Query and analysis results: The value of the _col0 column is -12.0.

inverse_chi_squared_cdf function

Calculates the inverse CDF of the chi-square distribution.

Syntax
```
inverse_chi_squared_cdf(k, p)
```

Parameters

Parameter	Description
k	The degree of freedom for the chi-square distribution. Must be a positive double.
p	The probability used to calculate the inverse value. Must be a double in the range [0, 1].

Return value type

Returns a double value.
Examples
- Query statement
```
* | select inverse_chi_squared_cdf(3, 0.9814338645369567) 
```
- The query and analysis result is 9.999999999835746.

inverse_laplace_cdf function

Returns the inverse CDF of the Laplace distribution.

Syntax
```
inverse_laplace_cdf(μ, b, p)
```

Parameters

Parameter	Description
μ	The location parameter for the Laplace distribution. Must be a double.
b	The scale parameter for the Laplace distribution. Must be a positive double.
p	The cumulative probability. Must be a double in the range [0, 1].

Return value type

Returns a double.
Examples
- Query statement
```
* | select inverse_laplace_cdf(11, 0.5, 0.18393972058572118)
```
- Query and Analysis Results: The _col0 column shows the returned value 10.5.

inverse_normal_cdf function

Calculates the inverse CDF of the normal distribution.

Syntax
```
inverse_normal_cdf(x, y, p)
```

Parameters

Parameter	Description
x	The mean of the normal distribution. The value must be a double.
y	The standard deviation of the normal distribution. The value must be a positive double.
p	The cumulative probability. The value must be a double in the range (0, 1).

Return value type

Returns a double value.

Examples

Query statement

* | select inverse_normal_cdf(85, 10, 0.06680720126885803)

Query result: 70.0.

inverse_poisson_cdf function

Calculates the inverse CDF of the Poisson distribution.

Syntax
```
inverse_poisson_cdf(λ, p)
```

Parameters

Parameter	Description
λ	The average rate at which a random event occurs.
p	The cumulative probability, a double in the range [0, 1].

Return value type

An integer.

Examples

Query statement

* | select inverse_poisson_cdf(0.1, 0.9953211598395556)

The query and analysis result is 1.

inverse_weibull_cdf function

Calculates the inverse CDF of the Weibull distribution.

Syntax
```
inverse_weibull_cdf(x, y, p)
```

Parameters

Parameter	Description
x	The scale parameter of the Weibull distribution. The value must be of type double and greater than 0.
y	The shape parameter of the Weibull distribution. The value must be of type double and greater than 0.
p	The input probability. The value must be of type double and in the range [0, 1].

Return value type

A double value.
Examples
- Query statement
```
* | select inverse_weibull_cdf(1, 5, 0.3296799539643607) 
```
- The result of the query and analysis is 1.9999999999999998.

laplace_cdf function

Calculates the CDF of the Laplace distribution.

Syntax
```
laplace_cdf(μ, b, v)
```

Parameters

Parameter	Description
μ	The location parameter of the Laplace distribution. Must be a double.
b	The scale parameter of the Laplace distribution. Must be a positive double.
v	The value at which to evaluate the cumulative distribution function. Must be a double.

Return value type

A double value.
Examples
- Query statement
```
* | select laplace_cdf(11, 0.5, 10.5) 
```
- The query and analysis result is 0.18393972058572118.

normal_cdf function

Calculates the CDF of the normal distribution.

Syntax
```
normal_cdf(x, y, v)
```

Parameters

Parameter	Description
x	The mean of the normal distribution, a double.
y	The standard deviation of the normal distribution, a positive double.
v	The value at which to evaluate the function, a double.

Return value type

Returns a double value.
Examples
- Query statement
```
* | select normal_cdf(85, 10, 70) 
```
- The query and analysis result is 0.06680720126885803.

poisson_cdf function

Calculates the CDF of the Poisson distribution.

Syntax
```
poisson_cdf(λ, v)
```

Parameters

Parameter	Description
λ	The average event rate.
v	The value to evaluate. Must be an integer greater than or equal to 0.

Return value type

A double value.
Examples
- Query statement
```
* | select poisson_cdf(0.1, 1) 
```
- Query result: 0.99532115983955556.

weibull_cdf function

Calculates the CDF of the Weibull distribution.

Syntax
```
weibull_cdf(x, y, v)
```

Parameters

Parameter	Description
x	The scale parameter. This value must be a double greater than 0.
y	The shape parameter. This value must be a double greater than 0.
v	The input value for the function. This value must be a double.

Return value type

Returns a double value.
Examples
- Query statement
```
* | select weibull_cdf(1, 5, 2) 
```
- The result is 0.3296799539643607.

corr function

Syntax

Parameters

Return value type

Examples

covar_pop function

Syntax

Parameters

Return value type

Examples

covar_samp function

Syntax

Parameters

Return value type

Examples

stddev function

Syntax

Parameters

Return value type

Examples

stddev_samp function

Syntax

Parameters

Return value type

Examples

stddev_pop function

Syntax

Parameters

Return value type

Examples

variance function

Syntax

Parameters

Return value type

Examples

var_samp function

Syntax

Parameters

Return value type

Examples

var_pop function

Syntax

Parameters

Return value type

Examples

regr_intercept function

Syntax

Parameters

Return value type

Examples

regr_slope function

Syntax

Parameters

Return value type

Examples

beta_cdf function

Syntax

Parameters

Return value type

Examples

binomial_cdf function

Syntax

Parameters

Return value type

Examples

cauchy_cdf function

Syntax

Parameters

Return value type

Examples

chi_squared_cdf function

Syntax

Parameters

Return value type

Examples

inverse_beta_cdf function

Syntax

Parameters

Return value type

Examples