SQLStatistics

SQLStatistics includes:

• A portfolio of tests and metrics of correlation, similarity or dependence between datasets, including chi-squared tests of independence and goodness of fit; covariance metrics; F-tests to compare variances; paired and unpaired Student's-T tests to compare means; Pearson's product moment correlation coefficient and its square; the Fisher transformation; and the Z-test.
• A broad range of descriptive statistics including geometric, harmonic and quadratic means; truncated and inter-quartile means and the tri-mean; modes of both continuous and discrete data; medians, quartiles, percentiles, percentile rank and absolute rank; skewness and kurtosis; weighted averages, variances, standard deviations and power means; range, mid-range, inter-quartile range, nth largest, nth smallest and more.
• A comprehensive set of complete and partial beta and gamma functions and their inverses, complements and derivatives.
• Many others including confidence tests using both normal and Student's-T distributions; functions for linear and exponential regression and extrapolation, including forecasting, error estimation, slope and intercept estimation; combinations and permutations; sum of squared deviations; normalization and more.

Beyond Excel
The functions available in SQLStatistics include numerically faithful equivalents of the familiar statistical functions found in Microsoft Excel and other popular packages, but also many others which - combined with the innate power and ability of SQL Server to rapidly crunch through huge volumes of data - gives you an analytical capability that completely beats anything you could do with spreadsheets.

Native Look and Feel
All TotallySQL Transact-SQL extensions are implemented using familiar programming objects within SQL (scalar and table-valued functions, aggregate functions, user-defined types, stored procedures), keeping the syntax clean and making them as fast and easy to use as the built-in native statistics functions such as SUM, AVG etc.

Outstanding Performance and Numerical Accuracy
The algorithms behind the SQLStatistics functions use industry-proven, best-in-class techniques devised by some of the world's leading numerical computing experts to maximise performance and accuracy even for fairly extreme input values. Careful heuristics and edge-case checks through the code ensure that problems caused by the limitations of floating-point arithmetic on digital computers (such as cancellation errors) are kept to an absolute minimum, and a broad range of special-purpose, high-performance numerical computing approaches are used in place of the slow, expensive computations that would result from 'naive' implementations of the underlying mathematics.

Multi-Valued Aggregates - even in SQL2005
Several of the SQLStatistics functions (for example weighted metrics for average, variance, standard deviation etc) are in the form of aggregate functions that take multiple input values. This works fine for SQL2008 but is commonly supposed to be impossible in SQL2005 - since you can supply only one input parameter to an aggregate function. TotallySQL have, however, devised a very simple but smart way of doing this in SQL2005 using SQLTuples: you simply combine the input parameters (of any type) into a SQLTuples object on-the-fly as part of the function call. Only a very minor syntax change is needed and there is no measurable performance penalty even when used with truly enormous data sets.

SQLStatistics Features:

• X2 statistics and tests for goodness of fit to a distribution and for independence of outcomes F-statistic and F-test to compare the variance of two populations
• One & two sample (paired & unpaired) Student's T test statistics & results
• Z-test statistic & result comparing predicted with observed means
• Fisher transformation and its inverse Complete & incomplete beta functions, inverses, complements & beta derivative
• Gamma function, lower & upper incomplete gamma functions & inverses, log-gamma, gamma ratios, digamma, gamma derivative
• Average absolute deviation, sum of squared deviations
• Combinations and permutations  Confidence intervals based on Normal and Student's T distributions
• Pearson product moment correlation coefficient & its square
• Sample and population co-variance of two data sets
• Geometric mean, harmonic mean, quadratic mean, truncated mean & weighted power mean
• Weighted averages, weighted sample & population variances & standard deviations
• Linear & exponential extrapolations for forecasting
• Linear regression to determine slope and intercept
• Median & quartiles (inclusive & exclusive), interquartile range, interquartile mean, tri-mean
• Percentile & percentile rank, inclusive & exclusive
• Average & absolute rank
• Skewness, kurtosis and kurtosis excess
• Range, mid-range, nth largest, nth smallest
• Mode for continuous or discrete data
• Cumulative product Normalization of values based on supplied mean and standard deviation
• Standard error of predicted values based on linear regression
• Riemann zeta function for a complex number All functions implemented as straightforward scalar or aggregate functions that can be used inline in queries
• Multi-input aggregate functions, even in SQL2005