"T-SQL Series" Common functions-aggregate functions

Original: "T-SQL Series" Common functions-aggregate functions

Aggregation functions
Average AVG, standard deviation Stdev, variance var, max Max, Min min, total sum, count of times, max-min, coefficient of variation stdev/avg*100

What is statistics
Statistics is the process of inferring the overall characteristics through sample characteristics.
Similar to gambling, there is a certain risk.
Reliability is influenced by sampling method, sample size and other factors.
Statistics are scientific.

Why use standard deviation?
Variance and standard deviation represent the best indicator of the degree of dispersion of a set of data, and are the most commonly used variance quantities.
The features are:
1, the response is sensitive, each data change should be reflected in the variance;
2, the calculation is strict;
3, easy to calculate;
4, suitable for algebraic operation;
5, the influence of sampling changes is small;
6, simple and clear;
7. Easy to be affected by extreme data.

The steps to calculate the variance can be summed up as "first average, after difference, squared, then average".
Variance is used to measure the amount of fluctuation in a batch of data. (That is, the amount of data deviates from the average).
The larger the variance, the greater the fluctuation of the data, the more unstable.

Coefficient:
The coefficient of variation, also known as the "standard deviation rate", is another statistic to measure the variation of the observed values in the data. When comparing two or more data variations, if the unit of measure is the same as the average, you can compare it directly with the standard deviation. If the number of units and/or averages is not the same, the standard deviation should not be used to compare the degree of variation, and the ratio of the standard deviation to the average (relative value) should be compared.

The Extreme Difference value:
Refers to the difference between the largest data and the smallest data in a set of data, which is often used in statistics to characterize the degree of dispersion of a set of data.

Example: Coach's troubles

Next page A, b two shooters are now choosing a shooter to participate in the competition.
If you are a coach, which one do you think is more appropriate to choose?

Player Score

A, b Two shooters test scores are as follows:

9

& nbsp;	First	second time	third time	Fourth time	Fifth time
Armor hit ring	7	8	8	8
B hit ring number	Ten	6	Ten /td>	6	8

1, please calculate the average of two shooters, standard deviation, variance, maximum, minimum, total, frequency, extreme difference, coefficient of variation equivalence ;
2, please according to the results of the two shooters in the drawing of a line chart;
Line:

Set up test data:

DECLARE @t1 TABLE(SequenceINT, NameVARCHAR( -), scoreINT    )INSERT   into @t1        SELECT  1 ,                'Armor' ,                7        UNION  All        SELECT  2 ,                'Armor' ,                8        UNION  All        SELECT  3 ,                'Armor' ,                8        UNION  All        SELECT  4 ,                'Armor' ,                8        UNION  All        SELECT  5 ,                'Armor' ,                9        UNION  All        SELECT  1 ,                'b' ,                Ten        UNION  All        SELECT  2 ,                'b' ,                6        UNION  All        SELECT  3 ,                'b' ,                Ten        UNION  All        SELECT  4 ,                'b' ,                6        UNION  All        SELECT  5 ,                'b' ,                8--SELECT *--From @t1SELECTName as 'Shooting Hand' ,        AVG(score) as 'Average' ,        STDEV(score) as 'Standard deviation' ,        VAR(score) as 'Variance' ,        MAX(score) as 'Maximum Value' ,        MIN(score) as 'Minimum Value' ,        MAX(score)- MIN(score) as 'Extreme Value' ,        STDEV(score)/ AVG(score) as 'coefficient of variation' ,        COUNT(score) as 'number of' from    @t1GROUP  byName

Result set:

T-SQL Series common functions-aggregate functions