Westclintech

Getting started

XLeratorDB/Statistical Functions Documentation

FUNCTION REFERENCE - Statistical Functions FUNCTIONS

Continuous Data

AVEDEV

Calculate average deviation.

AVEDEV_q

Calculate average deviation.

AVERAGE

Calculate the average (arithmetic mean) of a dataset.

AVERAGE_q

Calculate the average (arithmetic mean) of a dataset.

COUNT

Calculate the count of numeric rows in a dataset.

COUNT_q

Calculate the count of numeric rows in a dataset.

COVAR

the average of the products of the deviations in known x- and y-values

COVAR_q

Calculate covariance.

COVARIANCE_P

Aggregate function to calculate the population covariance

COVARIANCE_S

Aggregate function to calculate the sample covariance

DEVSQ

Calculate deviation squared.

DEVSQ_q

Calculate deviation squared.

GEOMEAN

Calculate the geometric mean of a dataset.

GEOMEAN_q

Calculate the geometric mean of a dataset.

HARMEAN

Calculate the harmonic mean of a dataset.

HARMEAN_q

Calculate the harmonic mean of a dataset.

IPR

Calculate inter-percentile range.

IQR

Calculate inter-quartile range.

KURT

Calculate the kurtosis of a dataset.

KURT_q

Calculate the kurtosis of a dataset.

KURTOSIS_P

Calculate population kurtosis.

KURTOSIS_S

Calculate sample kurtosis.

LARGE

calculate the kth largest value in a dataset

LARGE_q

Calculate the kthlargest value in a dataset.

MEDIAN

calculate the median of a dataset

MEDIAN_q

Calculate the median value in a dataset.

MODE

Calculate the mode (most frequent value) of a dataset.

MODE_q

Calculate the mode (most frequent value) of a dataset.

PERCENTILE

Aggregate function to calculate a percentile using the 'INC' method

PERCENTILE_EXC

Calculate percentile.

PERCENTILE_q

Calculate percentile.

PERCENTILES

table-valued function to calculate one or many percentile values from a single dataset

PERCENTRANK

Aggregate function to calculate a percent rank using the 'INC' method

PERCENTRANK_EXC

Calculate the rank of a value in dataset

PERCENTRANK_q

Calculate the rank of a value in a dataset as a percentage of the dataset.

PERCENTRANKS

Calculate the relative standing of values within a dataset.

QUARTILE

Calculate the specified quartile of a dataset.

QUARTILE_EXC

Return the specified quartile of a dataset

QUARTILE_q

Calculate the specified quartile of a dataset.

RANGE

Calculate the difference between the minimum and maximum values in a dataset.

RANK

Return the rank of a number in a list of numbers.

RANK_q

Return the rank of a number in a list of numbers.

RANKAVG

Return the average rank of a number in a list of numbers.

SKEW

Return the degree of asymmetry of a distribution.

SKEW_q

Return the degree of asymmetry of a distribution.

SKEWNESS_P

Calculate the skewness for an entire population.

SKEWNESS_S

Calculate the sample skewness.

SMALL

Calculate the kth smallest value in a dataset.

SMALL_q

Calculate the kth smallest value in a dataset.

STDEV

Return the estimated standard deviation based on a sample.

STDEV_P

Calculate the standard deviation for an entire population.

STDEV_q

Return the estimated standard deviation based on a sample.

STDEV_S

Return the estimated standard deviation based on a sample.

STDEVP

Return the standard deviation for an entire population.

STDEVP_q

Return the standard deviation for an entire population.

TRIMMEAN

Calculate the mean of the interior of a dataset.

TRIMMEAN_q

Calculate the mean of the interior of a dataset.

VAR

Estimate variance based on a sample, or use VAR to estimate the population variance from a sample of N elements with an unknown mean (the mean being calculated from the sample itself).

VAR_P

Calculate the variance for an entire population.

VAR_q

Estimate variance based on a sample.

VAR_S

Estimate variance based on a sample, or use VAR_S to estimate the population variance from a sample of N elements with an unknown mean (the mean being calculated from the sample itself).

VARP

Return the variance for an entire population.

VARP_q

Calculate population variance.

Combinatorics

BETA

Beta function

BETAI

Incomplete beta function

BICO

Binomial coefficient

FACTLN

natural logarithm of a factorial

FACTORIALPOWER

the step-h factorial power x(n,h)

GAMMA

complete gamma function

GAMMAINC

incomplete gamma function

GAMMALN

natural logarithm of the complete gamma function

LCHOOSE

Natural logarithm of the binomial coefficient

LPERMUT

Calculate the natural logarithm of the PERMUT function.

LPERMUTA

calculate the natural logarithm of the PERMUTATIONA function.

MULTINOM

calculate the probability distribution of the outcomes of a multinomial experiment.

PERMUT

calculate the number of permutations for a given number objects that can be selected from a number of objects

PERMUTATIONA

number of permutations with repetition

STIRLING1

Stirling numbers of the first kind

STIRLING2

Calculate a Stirling Number of the second kind.

Distributions

BETA_DIST

Calculate pdf or cdf of beta distribution

BETADIST

beta distribution

BETAINV

Inverse of the beta distribution

BETAPDF

Probability density function of the beta distribution

BINOM_DIST_RANGE

Calculate the probability of a trial result using a binomial distribution.

BINOMDIST

Binomial distribution

BINOMINV

Inverse (quantile) of the binomial distribution

BIVAR

bi-variate normal distribution

CAUCHY

Calculate the probability density function or the lower cumulative distribution function of the Cauchy distribution.

CAUCHYINV

Calculate the inverse lower cumulative distribution of the Cauchy distribution.

CHI2NC

Calculate the lower cumulative distribution function of the non-central chi-square distribution.

CHI2NCINV

Calculate the inverse of the lower cumulative distribution function of the non-central chi-square distribution.

CHIDIST

chi-square distribution

CHIINV

Calculate the inverse of the one-tailed probability of the chi-square distribution.

CHISQ_DIST

Lower chi-squared distribution

CHISQ_DIST_RT

Upper chi-squared distribution

CHISQ_INV

Inverse of the chi-squared distribution

CHISQ_INV_RT

Calculate the inverse of the right-tailed probability of a chi-squared distribution.

CHISQPDF

Calculate the probability density function of the chi square distribution.

CONFIDENCE

Calculate a value that you can use to construct a confidence interval for a population mean.

CONFIDENCE_NORM

Confidence interval using the standard normal distribution

CONFIDENCE_T

Calculate a confidence interval when you don't know the population standard deviation and are using the sample standard deviation instead.

CRITBINOM

Calculate the smallest value for the cumulative binomial distribution that is greater than or equal to a criterion value.

DIGAMMA

Calculate digamma(z), the logarithmic derivative of the gamma function.

EXPDIST

Calculate the probability density function or the upper cumulative distribution function of the Exponential distribution.

EXPINV

Calculate the inverse of the cumulative distribution function of the exponential distribution.

EXPONDIST

Calculate an exponential distribution that is equivalent to the function used in EXCEL.

F_DIST

Lower F distribution

F_DIST_RT

Upper F distribution

F_INV

Inverse of the lower-tailed F distribution

F_INV_RT

Calculate the inverse of the F upper probability distribution.

FDIST

Calculate the F probability distribution.

FINV

Calculate the inverse of the F probability distribution.

FISHER

the Fisher transformation at x

FISHERINV

the inverse Fisher transformation

FPDF

Calculate the probability density function of the F distribution.

GAMMADIST

gamma distribution

GAMMAINV

inverse gamma function

GAMMAP

lower regularized gamma function

GAMMAQ

upper regularized gamma function

GEOMETRIC

Calculate the probability mass function of the geometric distribution.

GEOMETRICINV

Calculate the inverse of the cumulative distribution function of the geometric distribution.

GEOMETRICP

Calculate the cumulative distribution function of the geometric distribution.

HYPGEOM_DIST

Hypergeometric distribution

HYPGEOM_INV

Inverse of the hypergeometric distribution

HYPGEOMDIST

Calculate the hypergeometric distribution.

HYPGEOMDISTP

Calculate the lower cumulative distribution of the hypergeometric function.

HYPGEOMDISTPINV

Calculate the inverse of the cumulative distribution function of the hypergeometric distribution.

HYPGEOMDISTQ

Calculate the upper cumulative distribution of the hypergeometric function.

INVGAMMAP

Calculate the inverse of the incomplete gamma functionP(a,x).

KSccdf

Compute the complementary cumulative probability P[Dn>= x] of the Kolmogorov-Smirnov distribution with sample size n at x.

KScdf

Compute the cumulative probability P[Dn>= x] of the Kolmogorov-Smirnov distribution with sample size n at x.

KSP

Calculate the Kolmogorov-Smirnov (KS) cumulative distribution function.

KSPINV

Calculate the inverse of the Kolmogorov-Smirnov (KS) cumulative distribution function.

KSQ

Calculate the complementary Kolmogorov-Smirnov (KS) cumulative distribution function, Qks= 1 - Pks.

KSQINV

Calculate the inverse of the complementary Kolmogorov-Smirnov (KS) cumulative distribution function, Qks= 1 - Pks.

LAPLACE

Calculate the probability density function or the lower cumulative distribution function of the Laplace distribution.

LAPLACEINV

Calculate the inverse lower cumulative distribution of the Laplace distribution.

LOGINV

Calculate the inverse of the lognormal cumulative distribution function of x where ln(x) is normally distributed within the parameters mean and standard deviation.

LOGISTIC

Calculate the probability density function or the lower cumulative distribution function of the logistic distribution.

LOGISTICINV

Calculate the inverse lower cumulative distribution of the logistic distribution.

LOGNORM_DIST

Lognormal distribution

LOGNORM_INV

Calculate the inverse of the lognormal distribution function of x, when ln(x) is normally distributed with parameters µ and s.

LOGNORMDIST

Calculate the lognormal cumulative distribution function of x where ln(x) is normally distributed within the parameters mean and standard deviation.

LOGNORMPDF

Calculate the probability density function of the lognormal distribution.

NCFCDF

Calculate the lower cumulative distribution function of the non-central F distribution.

NCFINV

Calculate the inverse of the lower cumulative distribution function of the non-central F distribution.

NCFPDF

Calculate the probability density function of the non-central F distribution.

NCHISQPDF

Calculate the probability density function of the non-central chi square distribution.

NCTCDF

Calculate the lower cumulative distribution function of the non-central T distribution.

NCTINV

Calculate the inverse of the lower cumulative distribution function of the non-central T distribution.

NCTPDF

Calculate the probability density function of the non-central T distribution.

NEGBINOM_DIST

Negative binomial distribution

NEGBINOMDIST

Negative binomial distribution

NEGBINOMDISTP

Negative binomial distribution (pdf)

NEGBINOMINV

Inverse of the negative binomial distribution

NEGHYPGEOM_DIST

Negative hypergeometric distribution

NEGHYPGEOM_INV

Calculate the quantiles of the negative hypergeometric distribution.

NORM_S_DIST

Calculate the probability density function or the lower cumulative distribution function of the standard normal distribution in SQL Server.

NORMAL

probability density function of the standard normal distribution

NORMDIST

Normal distribution

NORMINV

Inverse of the normal distribution

NORMSDIST

standard normal distribution

NORMSINV

Inverse of the standard normal distribution

POISSON

Calculate the Poisson distribution.

POISSON_DIST

Poisson distribution

POISSONINV

The poisson inverse function

STANDARDIZE

normalized value from a distribution for a specified mean and standard deviation

STUDENTST

Calculate Student’st-distribution.

T_DIST

Calculate the probability density function and the left-tailed cumulative probability distribution of Student's t-distribution.

T_DIST_2T

Calculate the two-tailed cumulative probability distribution of Student's t-distribution.

T_DIST_RT

Calculate the right-tailed cumulative probability distribution of Student's t-distribution.

T_INV

Inverse of student's t distribution

T_INV_2T

Calculate the inverse of the two-tailed distribution of Student's t-distribution.

TDIST

Calculate the probability for the Studentt-distribution where a numeric value (x) is a calculated value oftfor which the probability is to be computed.

TINV

The inverse t distribution function

TPDF

Calculate the probability density function of the T distribution.

TRIGAMMA

trigamma function; the second derivative of the gamma function

UNIFORM

Calculate the probability density function or the lower cumulative distribution function of the uniform distribution.

UNIFORMINV

Calculate the inverse of the lower cumulative distribution of the uniform distribution.

WEIBULL

Calculate the Weibull distribution.

WEIBULLINV

The inverse Weibull distribution function

Statistical Inference

CHISQ

Calculate the chi-square statistic.

CHISQ_q

Calculate the chi-square statistic.

CHISQ2

Calculate the chi-square statistic.

CHISQ2_q

Calculate the chi-square statistic.

CHISQN

Calculate the chi-square statistic for data in third-normal form.

CHISQN_q

Calculate the chi-square statistic for data in third-normal form.

CHISQN2

Calculate the chi-square statistic for data in third-normal form.

CHISQN2_q

Calculate the chi-square statistic for data in third-normal form.

CHITEST

Calculate the Pearson chi-square test for independence.

CHITEST_q

Calculate the Pearson chi-square test for independence.

CHITEST2

Calculate the Pearson chi-square test for independence.

CHITEST2_q

Calculate the Pearson chi-square test for independence.

CHITESTN

Calculate the Pearson chi-square test for independence on data in third-normal form.

CHITESTN_q

Calculate the Pearson chi-square test for independence on data in third-normal form.

CHITESTN2

Calculate the Pearson chi-square test for independence on data in third-normal form.

CHITESTN2_q

Calculate the Pearson chi-square test for independence on data in third-normal form.

FTEST

Return the result of an F-test.

FTEST_q

Return the result of an F-test.

KSTEST

Compare a sample with a reference probability distribution.

KSTEST_q

Compare a sample with a reference probability distribution.

KSTEST2

Perform the two-sample Kolmogorov-Smirnov test to compare the distributions of the values in two samples.

KSTEST2_q

Perform the two-sample Kolmogorov-Smirnov test to compare the distributions of the values in two samples.

SFTEST

Perform the Shapiro-Francia test for the composite hypothesis of normality.

SFTEST_q

Perform the Shapiro-Francia test for the composite hypothesis of normality.

SWTEST

Compute Shapiro and Wilk’s W statistic and its p-value.

SWTEST_q

Compute Shapiro and Wilk’s W statistic and its p-value.

TTEST

Calculate the probability associated with Student’st-test.

TTEST_INDEP

Perform a t-test on data in two samples assuming equal variance.

TTEST_INDEPU

Perform a t-test on data in two samples assuming unequal variance.

TTEST_PAIRED

Perform a t-test on data in two samples assuming unequal variance.

TTEST_q

Calculate the probability associated with Student’s t-test.

ZTEST

one-tailed probability-value of a z-test (SQL Server 2008 and 2012 version)

ZTEST_q

Calculate the one-tailed probability of a Z-test.

Correlation And Regression Analysis

CORREL

Aggregate function to calculate the correlation coefficient

CORREL_q

Calculate the correlation coefficient between two datasets.

EXPPRED

Calculate the predicted value for a dependent variable using the coeffiecients from a previously run mutlivariate exponential regression.

FORECAST

The predicted y-value for a given x-value

FORECAST_q

Calculate a future value by using existing values.

GROWTH

calculate predicted exponential growth using existing values

GROWTH_q

Calculate predicted exponential growth by using existing data.

GROWTHMX

Values along an exponential trend

GROWTHMX_q

Calculate values along an exponential trend for multiple x values

INTERCEPT

the point at which a line will intersect the y-axis by using existing x-values and y-values

INTERCEPT_q

Calculate the point at which a line will intersect the y-axis by using existing x-values and y-values.

LINEST

the Ordinary Least Squares (OLS) solution for a series of x-values and y-values

LINEST_q

the Ordinary Least Squares (OLS) solution for a series of x-values and y-values

LMPRED

Calculate the predicted value for a dependent variable using the coefficients from a previously run multivariate linear regression.

LOGEST

Logarithmic regression

LOGEST_q

Exponential curve that best fits a series of x- and y-values

LOGIT

Logit regression

LOGITPRED

Calculate predicted values based on a logit regression

LOGITPROB

Calculate the probability that Y = 1 given a set of coefficients from a logistic regression

LOGITSUM

Logit regression using summary data

PEARSON

Aggregate function to calculate the correlation coefficient

PEARSON_q

Return the Pearson product-moment coefficient between two datasets.

PROB

Calculate the probability that values in a range are between two limits.

PROB_q

Calculate the probability that values in a range are between two limits.

ROCTable

Show the calculation of the area under the ROC curve.

RSQ

the Pearson product moment correlation coefficient through data points in known-y’s and known-x’s

RSQ_q

Return the Pearson product moment correlation coefficient through data points in known-y’s and known-x’s.

SLOPE

slope of the linear regression through the data points in the known x-values and y-values

SLOPE_q

Calculate the slope of the linear regression through the data points in the known x-values and y-values.

TREND

for simpler queries

TREND_q

Calculate the values along a linear trend.

TRENDMX

Calculate the values along a linear trend for multiple x values

TRENDMX_q

Calculate the values along a linear trend for multiple x values

VIF

Variance inflation factors

WLS

Calculate a weighted least squares (WLS) solution for a series of x- and y-values and their associated weights.

WLS_q

Calculate a weighted least squares (WLS) solution for a series of x- and y-values and their associated weights.

LLS_q

the Ordinary Least Squares (OLS) solution for a series of x-values and y-values including a tolerance parameter for collinearity

Interobserver Agreement

ICC

Calculate the intra-class coefficient.

ICC_TV

Calculate the intra-class coefficient.

KAPPA_COHEN

Calculate Cohen’s kappa () or weighted kappa as an index of inter-rater agreement between 2 raters.

KAPPA_COHEN_TV

Calculate Cohen’s kappa () or weighted kappa as an index of inter-rater agreement between 2 raters.

KAPPA_FLEISS

Calculate Fleiss’ kappa () as an index of inter-rater agreement betweenmraters on categorical data.

KAPPA_FLEISS_TV

Calculate Fleiss’ kappa () as an index of inter-rater agreement between m raters on categorical data.

KENDALLT

Calculate Kendall’s tau (t), a non-parametric measure of association based on the number of concordances and discordances in paired observations.

KENDALLT_TV

Calculate Kendall’s tau (t), a non-parametric measure of association based on the number of concordances and discordances in paired observations.

KENDALLW

Calculate Kendall’s coefficient of concordance (w) as an index of inter-rater reliability for ordinal data.

KENDALLW_TV

Calculate Kendall’s coefficient of concordance (w) as an index of inter-rater reliability for ordinal data.

MCNEMAR

Perform McNemar’s chi-squared test for symmetry of rows and columns in a two-dimensional contingency table.

MCNEMAR_TV

Perform McNemar’s chi-squared test for symmetry of rows and columns in a two-dimensional contingency table.

PSIGNRANK

Calculate the distribution function of the Wilcoxon Signed Rank statistic.

RANK_AVG

Calculate the ranks for a collection of x- and y-values.

SPEARMAN

Calculate Spearman’s rank correlation coefficient ().

SPEARMAN_TV

Calculate Spearman’s rank correlation coefficient ().

WMPSR

Calculate the Wilcoxon matched-pair signed-rank test.

WMPSR_TV

Calculate the Wilcoxon matched-pair signed-rank test.

Data Collection

KMEANS

Perform k-means clustering in N-dimensions.

STEYX

the standard error of the predicted y-value for each x in the regression

STEYX_q

Return the standard error of the predicted y-value for each x in a regression.