Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

Home


XLeratorDB/statistics

Use XLeratorDB/statistics for a wide variety of statistical calculations. The feature-rich XLeratorDB function library lets you include calculations in any T-SQL statement including SELECT, INSERT, UPDATE, DELETE, CREATE VIEW as well as in CTEs, stored procedures, user-defined functions, and computed columns.

XLeratorDB/statistics is available in two packages - XLeratorDB/statistics which requires SQL Server 2005 or later, and XLeratorDB/statistics 2008 which requires SQL Server 2008 or later. 

Functions denoted with 'Not available for SQL2005' are only available in the XLeratorDB/statistics 2008 package and will not install or run  in SQL Server 2005. XLeratorDB 2008 packages contain functions which leverage SQL CLR features that are only available in SQL Server 2008 or later, namely 'multi-input aggregates' which provide a much easier calling syntax and typically run much faster than their scalar counterparts.

FUNCTION REFERENCE - STATISTICAL FUNCTIONS
CONTINUOUS DATA
LOCATION
AVERAGE
 
Average (arithmetic mean)  
 
AVERAGE_q
 
Average (arithmetic mean)  
 
COUNT
 
Number of rows in a table that contain numbers  
 
COUNT_q
 
Number of rows in a table that contain numbers  
 
GEOMEAN
 
Geometric mean for a dataset containing positive numbers
 
GEOMEAN_q
 
Geometric mean for a dataset containing positive numbers  
 
HARMEAN
 
Harmonic mean of a dataset containing positive numbers
 
HARMEAN_q
 
Harmonic mean of a dataset containing positive numbers  
 
MEDIAN
 
Calculate the median value in a dataset
 
MEDIAN_q
 
Calculate the median value in a dataset  
 
MODE
 
Calculate the most common value in a dataset
 
MODE_q
 
Calculate the most common value in a dataset  
 
TRIMMEAN
 
Calculate the mean of the interior of a dataset
 
TRIMMEAN_q
 
Calculate the mean of the interior of a dataset  
 
 
DISPERSION
AVEDEV
 
Average of the absolute deviations
 
AVEDEV_q
 
Average of the absolute deviations  
 
COVAR
 
Covariance
 
COVAR_q
 
Covariance  
 
COVARIANCE_P
 
Calculate the covariance for a set of x- and y-values for an entire population
 
COVARIANCE_S
 
Calculate the covariance for a set of x- and y-values based on a sample
 
DEVSQ
 
Sum of the squares of deviations
 
DEVSQ_q
 
Sum of the squares of deviations  
 
IPR
 
Calculate the inter-percentile range of a dataset
Not available for SQL2005
IQR
 
Calculate the inter-quartile range of a dataset
Not available for SQL2005
KURTOSIS_P
 
Calculate the kurtosis for an entire population
 
KURTOSIS_S
 
Calculate the kurtosis for a sample
 
LARGE
 
Calculate the kth largest value in a dataset
 
LARGE_q
 
Calculate the kth largest value in a dataset  
 
PERCENTILE
 
Calculate the kth percentile of value in a dataset
 
PERCENTILE_q
 
Calculate the kth percentile of value in a dataset  
 
PERCENTILE_EXC
 
Calculate the kth percentile of value in a dataset
Not available for SQL2005
PERCENTILES
new!
Calculate any number of percentiles from a set of values  
 (table-valued function)
PERCENTRANK
 
Calculate rank of a value in a dataset as a percentage of the dataset
 
PERCENTRANK_q
 
Calculate rank of a value in a dataset as a percentage of the dataset  
 
PERCENTRANKS
new!
Calculate relative standing of values within a dataset  
 (table-valued function)
PERCENTRANK_EXC
 
Calculate rank of a value in a dataset as a percentage of the dataset
Not available for SQL2005
QUARTILE
 
Calculate the quartile of a dataset
 
QUARTILE_q
 
Calculate the quartile of a dataset  
 
QUARTILE_EXC
 
Calculate the quartile of a dataset
Not available for SQL2005
RANGE
 
Calculate the difference between the min and max of a dataset
Not available for SQL2005
RANK
 
Calculate the rank of a number in a list of numbers
 
RANK_q
 
Calculate the rank of a number in a list of numbers  
 
RANKAVG
 
Calculate the average rank of a number in a list of numbers
Not available for SQL2005
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
 
Standard deviation
 
STDEV_q
 
Standard deviation  
 
STDEVP
 
Standard deviation for an entire population
 
STDEVP_q
 
Standard deviation for an entire population  
 
STDEV_P
 
Calculate the standard deviation for an entire population
 
STDEV_S
 
Estimate the standard deviation based on a sample
 
VAR_P
 
Calculate the variance for an entire population
 
VAR_S
 
Estimate variance based on a sample
 
 
SHAPE
KURT
 
Calculate the kurtosis of a dataset
 
KURT_q
 
Calculate the kurtosis of a dataset  
 
SKEW
 
The degree of asymmetry of a distribution
 
SKEW_q
 
The degree of asymmetry of a distribution  
 
VAR
 
Variance
 
VAR_q
 
Variance  
 
VARP
 
Variance for an entire population
 
VARP_q
 
Variance for an entire population  
 
 
STATISTICAL INFERENCE
SPECIFIC TESTS
CHISQ
 
Chi-square statistic  
 
CHISQ_q
 
Chi-square statistic  
 
CHISQ2
 
Chi-square statistic (user-specified expected range)  
 
CHISQ2_q
 
Chi-square statistic (user-specified expected range)  
 
CHISQN
 
Chi-square statistic on normalized tables
 
CHISQN_q
 
Chi-square statistic on normalized tables  
 
CHISQN2
 
Chi-square statistic on normalized tables (user-specified expected range)
 
CHISQN2_q
 
Chi-square statistic on normalized tables (user-specified expected range)  
 
CHITEST
 
Pearson chi-square test for independence  
 
CHITEST_q
 
Pearson chi-square test for independence  
 
CHITEST2
 
Pearson chi-square test for independence (user-specified expected range)  
 
CHITEST2_q
 
Pearson chi-square test for independence (user-specified expected range)  
 
CHITESTN
 
Pearson chi-square test for independence on normalized tables
 
CHITESTN_q
 
Pearson chi-square test for independence on normalized tables  
 
CHITESTN2
 
Pearson chi-square test for independence on normalized tables (user-specified expected range)
 
CHITESTN2_q
 
Pearson chi-square test for independence on normalized tables (user-specified expected range)  
 
FTEST
 
Determine whether two samples have different variances
 
FTEST_q
 
Determine whether two samples have different variances  
 
KSTEST
 
Compare a sample with a reference probability distribution
 
KSTEST_q
 
Compare a sample with a reference probability distribution  
 
KSTEST2
 
Compare the distributions of the values in two samples
 
KSTEST2_q
 
Compare the distributions of the values in two samples  
 
SFTEST
 
Shapiro-Francia test for the composite hypothesis of normality
 
SFTEST_q
 
Shapiro-Francia test for the composite hypothesis of normality  
 
SWTEST
 
Shapiro and Wilk’s W statistic and its p-value
 
SWTEST_q
 
Shapiro and Wilk’s W statistic and its p-value  
 
TTEST
 
Calculate the probability associated with Student’s t-test
 
TTEST_q
 
Calculate the probability associated with Student’s t-test  
 
TTEST_INDEP
 
T-Test on data in two samples assuming equal variance
 
TTEST_INDEPU
 
T-Test on data in two samples assuming unequal variance
 
TTEST_PAIRED
 
Two-sample, paired t-test
 
ZTEST
 
Calculate the one-tailed probability of a Z-test
 
ZTEST_q
 
Calculate the one-tailed probability of a Z-test  
 
 
CORRELATION AND REGRESSION ANALYSIS
CORRELATIONS
CORREL
 
Correlation coefficient
 
CORREL_q
 
Correlation coefficient  
 
PEARSON
 
Pearson correlation coefficient
 
PEARSON_q
 
Pearson correlation coefficient  
 
PROB
 
Calculate probability that values in a range are between two limits
 
PROB_q
 
Calculate probability that values in a range are between two limits  
 
RSQ
 
Pearson product moment correlation coefficient
 
RSQ_q
 
Pearson product moment correlation coefficient  
 
 
LINEAR REGRESSIONS
FORECAST
 
Calculate a future value
 
FORECAST_q
 
Calculate a future value  
 
GROWTH
 
Predicted exponential growth
 
GROWTH_q
 
Predicted exponential growth  
 
GROWTHMX
 
Calculate values along an exponential trend for multiple x values  
 
GROWTHMX_q
 
Calculate values along an exponential trend for multiple x values  
 
INTERCEPT
 
Calculate the point at which a line will intersect the y-axis
 
INTERCEPT_q
 
Calculate the point at which a line will intersect the y-axis  
 
LINEST
 
Calculate the straight line that fits a series of X and Y values  
 
LINEST_q
 
Calculate the straight line that fits a series of X and Y values  
 
LOGEST
 
Calculate the exponential curve tha fits a series of X and Y values  
 
LOGEST_q
 
Calculate the exponential curve that fits a series of X and Y values  
 
SLOPE
 
Slope of linear regression
 
SLOPE_q
 
Slope of linear regression  
 
TREND
 
Calculate the values along a linear trend
 
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  
 
 
LOGISTIC REGRESSIONS
LOGIT
new!
Calculate the binary logistic regression coefficients  
 (table-valued function)
LOGITPRED
new!
Calculate the probability that Y = 1 given a set of co-efficients from a logistic regression
 
LOGITPROB
new!
Calculate the probability that Y = 1 given a set of coefficients from a logistic regression
Not available for SQL2005
LOGITSUM
new!
Calculate the binary logistic regression coefficients from a table of independent variables  
 (table-valued function)
ROCTable
new!
Show the calculation of the area under the ROC curve  
 (table-valued function)
VIF
new!
Calculate the R2, tolerance, and the variance inflation factor for a set of independent variables  
 (table-valued function)
 
DATA COLLECTION
SAMPLING
KMEANS
new!
K-means clustering in N-dimensions  
 (table-valued function)
STEYX
 
Standard error
 
STEYX_q
 
Standard error  
 
 
INTEROBSERVER AGREEMENT
ICC
 
Calculate the Intra Class Coefficient  
 
ICC_TV
 
Calculate the Intra Class Coefficient  
 (table-valued function)
KAPPA_COHEN
 
Calculate Cohen's Kappa (?) or weighted kappa  
 
KAPPA_COHEN_TV
 
Calculate Cohen's Kappa (?) or weighted kappa  
 (table-valued function)
KAPPA_FLEISS
 
Calculate Fleiss's Kappa (?)  
 
KAPPA_FLEISS_TV
 
Calculate Fleiss's Kappa (?)  
 (table-valued function)
KENDALLT
 
Calculate Kendall’s coefficient of concordance (t)  
 
KENDALLT_TV
 
Calculate Kendall’s coefficient of concordance (t)  
 (table-valued function)
KENDALLW
 
Calculate Kendall’s coefficient of concordance (w)  
 
KENDALLW_TV
 
Calculate Kendall’s coefficient of concordance (w)  
 (table-valued function)
MCNEMAR
 
Perform McNemar’s chi-squared test for symmetry  
 
MCNEMAR_TV
 
Perform McNemar’s chi-squared test for symmetry  
 (table-valued function)
PSIGNRANK
 
Wilcoxon Signed Rank
 
RANK_AVG
 
Calculate the ranks for a collection of x- and y-values  
 (table-valued function)
SPEARMAN
 
Calculate Spearman’s rank correlation coefficient (?)  
 
SPEARMAN_TV
 
Calculate Spearman’s rank correlation coefficient (?)  
 (table-valued function)
WMPSR
 
Calculate the Wilcoxon matched-pair signed-rank test  
 
WMPSR_TV
 
Calculate the Wilcoxon matched-pair signed-rank test  
 (table-valued function)
 
FUNCTION REFERENCE - PROBABILITY FUNCTIONS
DISTRIBUTIONS
BETA_DIST
new!
Beta distribution
 
BETADIST
 
Beta cumulative probability density
 
BETAINV
 
Inverse of the beta distribution
 
BETAPDF
 
Beta distribution (pdf)
 
BINOM_DIST_RANGE
new!
Trial probability using binomial distribution
 
BINOMDIST
 
Binomial distribution
 
BINOMINV
 
Inverse (quantile) of the binomial distribution
 
BIVAR
 
Bivariate normal probabilities
 
CAUCHY
 
Cauchy distribution (cdf or pdf)
 
CAUCHYINV
 
Inverse (quantile) of the Cauchy distribution (cdf)
 
CHI2NC
 
Non-Central Chi-square distribution
 
CHI2NCINV
 
Inverse of the Non-Central Chi-square distribution
 
CHIDIST
 
Chi-square distribution
 
CHIINV
 
Inverse of the Chi-square distribution
 
CHISQ_DIST
new!
Lower chi-squared distribution
 
CHISQ_DIST_RT
new!
Upper chi-squared distribution
 
CHISQ_INV
new!
Inverse of the chi-squared distribution
 
CHISQ_INV_RT
new!
Inverse of the upper chi-squared distribution
 
CHISQPDF
 
Calculate the probability density function of the Chi-square distribution
 
CONFIDENCE
 
Confidence interval
 
CONFIDENCE_NORM
new!
Confidence interval using the standard normal distribution
 
CONFIDENCE_T
new!
Confidence interval using a Student's t distribution
 
CRITBINOM
 
Criterion value for the cumulative binomial distribution
 
EXPDIST
 
Exponential distribution (cdf or pdf)
 
EXPONDIST
 
Exponential distribution - Excel version
 
EXPINV
 
Inverse (quantile) of the exponential distribution
 
FDIST
 
F probability distribution
 
F_DIST
new!
Lower F distribution
 
F_DIST_RT
new!
Upper F distribution
 
FINV
 
Inverse of the F probability distribution
 
F_INV
new!
Inverse of the lower-tailed F distribution
 
F_INV_RT
new!
Inverse of the upper F distribution
 
FPDF
 
F distribution (pdf)
 
FISHER
 
Fisher transformation
 
FISHERINV
 
Inverse of the Fisher transformation
 
GAMMADIST
 
Gamma distribution
 
GAMMAINV
 
Inverse  of the cumulative gamma distribution
 
GAMMAP
 
Regularized gamma function P(a, x)
 
GAMMAQ
 
Regularized gamma function Q(a, x)
 
GEOMETRIC
 
Geometric distribution (cdf or pdf)
 
GEOMETRICP
 
Cumulative distribution function of the Geometric distribution
 
GEOMETRICINV
 
Inverse (quantile) of the Geometric distribution
 
HYPGEOM_DIST
new!
Hypergeometric distribution
 
HYPGEOM_INV
new!
Inverse of the hypergeometric distribution
 
HYPGEOMDIST
 
Hypergeometric distribution
 
HYPGEOMDISTP
 
Hypergeometric distribution P-tail
 
HYPGEOMDISTPINV
 
Inverse of the pdf of the Hypergeometric distribution
 
HYPGEOMDISTQ
 
Hypergeometric distribution Q-tail
 
INVGAMMAP
 
Inverse of the incomplete gamma function P(a,x)
 
KScdf
 
Cumulative probability of the Kolmogorov-Smirnov distribution
 
KSccdf
 
Complementary cumulative probability of the Kolmogorov-Smirnov distribution
 
KSP
 
Kolmogorov-Smirnov (KS) cumulative distribution function
 
KSPINV
 
Inverse of the Kolmogorov-Smirnov (KS) cumulative distribution function
 
KSQ
 
Complementary Kolmogorov-Smirnov (KS) cumulative distribution function
 
KSQINV
 
Inverse of the Complementary Kolmogorov-Smirnov (KS) cumulative distribution function
 
LAPLACE
 
Laplace distribution (cdf or pdf)
 
LAPLACEINV
 
Inverse (quantile) of the Laplace distribution
 
LOGINV
 
Inverse of the lognormal cumulative distribution
 
LOGISTIC
 
Logistic distribution (cdf or pdf)
 
LOGISTICINV
 
Inverse (quantile) of the logistic distribution
 
LOGNORM_DIST
new!
Lognormal distribution
 
LOGNORM_INV
new!
Inverse of the lognormal distribution
 
LOGNORMDIST
 
Lognormal cumulative distribution
 
LOGNORMPDF
 
Lognormal distribution (pdf)
 
NCFCDF
 
Non-central F distribution (cdf)
 
NCFINV
 
Inverse (quantile) of the Non-central F distribution
 
NCFPDF
 
Non-central F distribution (pdf)
 
NCHISQPDF
 
Non-central chi square distribution (pdf)
 
NCTCDF
 
Non-central T distribution (cdf)
 
NCTINV
 
Inverse (quantile) of the Non-central T distribution
 
NCTPDF
 
Non-central T distribution (pdf)
 
NEGBINOM_DIST
new!
Negative binomial distribution
 
NEGBINOMDIST
 
Negative binomial distribution
 
NEGBINOMDISTP
 
Negative binomial distribution (pdf)
 
NEGBINOMINV
 
Inverse (quantile) of the Negative binomial distribution
 
NEGHYPGEOM_DIST
new!
Negative hypergeometric distribution
 
NEGHYPGEOM_INV
new!
Inverse of the negative hypergeometric distribution
 
NORMAL
 
Probability density function for the standard normal distribution
 
NORM_S_DIST
new!
Standard normal distribution
 
NORMDIST
 
Normal distribution
 
NORMINV
 
Inverse of the normal cumulative distribution
 
NORMSDIST
 
Standard normal cumulative distribution
 
NORMSINV
 
Inverse of the standard normal cumulative distribution
 
POISSON
 
Poisson distribution
 
POISSON_DIST
new!
Poisson distribution
 
POISSONINV
 
Inverse (quantile) of the Poisson distribution
 
STANDARDIZE
 
Normalized value from a distribution
 
STUDENTST
 
Student's t-distribution
 
TDIST
 
Probability for the Student t-distribution
 
T_DIST
new!
Left-tailed t-distribution
 
T_DIST_2T
new!
Two-tailed t-distribution
 
T_DIST_RT
new!
Right-tailed t-distribution
 
TINV
 
Value of the Student's T distribution
 
T_INV
new!
Inverse of the left-tailed t-distribution
 
T_INV_2T
new!
Inverse of the two-tailed t-distribution
 
TPDF
 
Calculate the probability density function of the T distribution
 
UNIFORM
 
Uniform distribution (cdf or pdf)
 
UNIFORMINV
 
Inverse (quantile) of the Uniform distribution
 
WEIBULL
 
Weibull distribution
 
WEIBULLINV
 
Inverse (quantile) of the Weibull distribution
 
 
COMBINATORICS
BETA
 
Beta
 
BETAI
 
Incomplete beta
 
BICO
 
Binomial coefficient
 
FACTLN
 
Natural logarithm of a factorial
 
GAMMA
 
Complete gamma function
 
GAMMAINC
 
Incomplete gamma
 
GAMMALN
 
Natural logarithm of the complete gamma
 
LCHOOSE
new!
Natural logarithm of the binomial coefficient
 
PERMUT
 
Permutations
 
 
FUNCTION REFERENCE - MISCELLANEOUS FUNCTIONS
RelativeError
 
Calculate the relative error between two values
 
 XLDB_STATISTICS_VERSION
 
 Version Information
 
 
SAMPLE DATA
 XLDB Statistics Sample Data
 
 
 

This function cannot reference data from SQL Server 2014 memory-optimized tables




Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service