How to calculate 90th Percentile, SD, Average value for data in SQL

Question

Hi, I have a table. Which keeps the score for each day (Multiple points can be reported every day, and both of them will be valid)

I need to calculate the 90th percentile, SD and the average value for a monthly estimate.

An object:

Id   Month Date  score
1    Jan     1    5
1    Jan     1    5
1    Jan     2    3
1    Jan     3    4
1    Jan     4    4
1    Jan     5    4
1    Feb     1    5
1    Feb     1    5
1    Feb     2    3
1    Feb     3    4
1    Feb     4    4
1    Feb     5    4

Is there any way?

Thank you for your help.

Answer 1

You can use the new set of analytic features introduced in SQL Server 2012:

SELECT DISTINCT
            [Month],
            Mean   = AVG(Score) OVER (PARTITION BY [Month]),
            StdDev = STDEV(Score) OVER (PARTITION BY [Month]),
            P90    = PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Score) OVER (PARTITION BY [Month])
FROM        my_table

There are two percentile functions: PERCENTILE_CONTfor continuous distribution and PERCENTILE_DISCfor discrete distribution. Chooses the one that suits your needs.

Answer 2

...

CREATE TABLE Facility (Id INT NOT NULL, Month nvarchar(3) NOT NULL, Date INT NOT NULL, score INT NOT NULL)

INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 1, 5)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 1, 5)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 2, 3)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 3, 4)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 4, 4)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Jan', 5, 4)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 1, 5)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 1, 5)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 2, 3)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 3, 4)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 4, 4)
INSERT INTO Facility (Id, Month, Date, score) VALUES (1, 'Feb', 5, 4)

- ...

SELECT
    [Month],
    AVG(CONVERT(real, score)) AS [Mean],
    STDEV(score) AS [Standard Deviation]
FROM
    Facility
GROUP BY
    [Month]

90- ...

CREATE FUNCTION NintythPercentile(@Month nvarchar(3)) RETURNS INT AS
BEGIN
    DECLARE @ReturnValue INT

    SELECT 
        @ReturnValue = MIN(DerivedTopTenPercent.score) --AS [90th Percentile]
    FROM
        (
        SELECT TOP 10 PERCENT
            score
        FROM
            Facility
        WHERE
            [Month] = @Month
        ORDER BY
            score DESC
        ) DerivedTopTenPercent

    RETURN @ReturnValue
END

:

SELECT
    [Month],
    AVG(CONVERT(real, score)) AS [Mean],
    STDEV(score) AS [Standard Deviation],
    dbo.NintythPercentile([Month]) AS [90th Percentile]
FROM
    Facility
GROUP BY
    [Month]