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    new! Calculate the covariance for a set of x- and y-values for an entire population
COVARIANCE_S    new! 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    new! Calculate the kurtosis for an entire population
KURTOSIS_S    new! 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
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  
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    new! Calculate the skewness for an entire population
SKEWNESS_S    new! 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    new! Calculate the standard deviation for an entire population
STDEV_S    new! Estimate the standard deviation based on a sample
VAR_P    new! Calculate the variance for an entire population
VAR_S    new! 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  
   
DATA COLLECTION
SAMPLING
STEYX Standard error
STEYX_q Standard error  
   
INTEROBSERVER AGREEMENT
ICC    new! Calculate the Intra Class Coefficient  
ICC_TV    new! Calculate the Intra Class Coefficient   (table-valued function)
KAPPA_COHEN    new! Calculate Cohen's Kappa (?) or weighted kappa  
KAPPA_COHEN_TV    new! Calculate Cohen's Kappa (?) or weighted kappa   (table-valued function)
KAPPA_FLEISS    new! Calculate Fleiss's Kappa (?)  
KAPPA_FLEISS_TV    new! Calculate Fleiss's Kappa (?)   (table-valued function)
KENDALLT    new! Calculate Kendall’s coefficient of concordance (t)  
KENDALLT_TV    new! Calculate Kendall’s coefficient of concordance (t)   (table-valued function)
KENDALLW    new! Calculate Kendall’s coefficient of concordance (w)  
KENDALLW_TV    new! Calculate Kendall’s coefficient of concordance (w)   (table-valued function)
MCNEMAR    new! Perform McNemar’s chi-squared test for symmetry  
MCNEMAR_TV    new! Perform McNemar’s chi-squared test for symmetry   (table-valued function)
PSIGNRANK    new! Wilcoxon Signed Rank
RANK_AVG    new! Calculate the ranks for a collection of x- and y-values   (table-valued function)
SPEARMAN    new! Calculate Spearman’s rank correlation coefficient (?)  
SPEARMAN_TV    new! Calculate Spearman’s rank correlation coefficient (?)   (table-valued function)
WMPSR    new! Calculate the Wilcoxon matched-pair signed-rank test  
WMPSR_TV    new! Calculate the Wilcoxon matched-pair signed-rank test   (table-valued function)
   
FUNCTION REFERENCE - PROBABILITY FUNCTIONS
DISTRIBUTIONS
BETADIST Beta cumulative probability density
BETAINV Inverse of the beta cumulative probability density
BETAPDF Beta distribution (pdf)
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
CHISQPDF Calculate the probability density function of the Chi-square distribution
CONFIDENCE Confidence interval
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
FINV Inverse of the F probability 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
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
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)
NEGBINOMDIST Negative binomial distribution
NEGBINOMDISTP Negative binomial distribution (pdf)
NEGBINOMINV Inverse (quantile) of the Negative binomial distribution
NORMAL Probability density function for the 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
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
TINV Value of the Student's 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
PERMUT Permutations
   
FUNCTION REFERENCE - MISCELLANEOUS FUNCTIONS
 XLDB_STATISTICS_VERSION  Version Information
   
 XLDB Statistics Sample Data  

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




 |  View Topic History  |
Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service