Statistical aggregate functions

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Statistical aggregate functions

AVG()

STDEV()

STDEVP()

VAR()

VARP()

Mean

Standard Deviation

Population Standard Deviation

Variance

Population Variance

Time Series Analysis in SQL Server

What about median?

SELECT TOP(1)
    PERCENTILE_CONT(0.5)
        WITHIN GROUP (ORDER BY l.SomeVal DESC)
        OVER () AS MedianIncidents
FROM dbo.LargeTable l;
Time Series Analysis in SQL Server

But how bad is it?

AVG execution plan

Time Series Analysis in SQL Server

This bad

Median execution plan

Time Series Analysis in SQL Server

The cost of median

Median Mean
Est. Cost 95.7% 4.3%
Duration 68.5s 0.37s
CPU 68.5s 8.1s
Reads 72,560,946 39,468
Writes 87,982 0
Time Series Analysis in SQL Server

Let's practice!

Time Series Analysis in SQL Server

Preparing Video For Download...