"T-SQL Series" Common functions-aggregate functions

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.