按月分组获取数据集的百分位数

我有一个 SQL 表，其中包含大量记录，如下所示：

| Date       | Score |
+ -----------+-------+
| 01/01/2010 |     4 |
| 02/01/2010 |     6 |
| 03/01/2010 |    10 |
  ...
| 16/03/2010 |     2 |

我将其绘制在图表上，因此我在图表上画了一条漂亮的线，表示随时间变化的得分。迷人的。

现在，我需要做的是将平均分数包含在图表上，这样我们就可以看到它随着时间的推移如何变化，所以我可以简单地将其添加到组合中：

SELECT 
    YEAR(SCOREDATE) 'Year', MONTH(SCOREDATE) 'Month',
    MIN(SCORE) MinScore, 
    AVG(SCORE) AverageScore, 
    MAX(SCORE) MaxScore
FROM SCORES
GROUP BY YEAR(SCOREDATE), MONTH(SCOREDATE) 
ORDER BY YEAR(SCOREDATE), MONTH(SCOREDATE) 

到目前为止这还没有问题。

问题是，如何轻松计算每个时间段的百分位数？我不确定这是否是正确的短语。我总共需要的是：

• 图表上的分数线（简单）
• 图表上的平均值线（简单）
• 图表上的一条线显示 95% 的分数占据的区间（被难住了）

这是第三个，我没看懂。我需要计算 5% 的百分位数字，我可以单独完成：

SELECT MAX(SubQ.SCORE) FROM
    (SELECT TOP 45 PERCENT SCORE 
    FROM SCORES
    WHERE YEAR(SCOREDATE) = 2010 AND MONTH(SCOREDATE) = 1
    ORDER BY SCORE ASC) AS SubQ

SELECT MIN(SubQ.SCORE) FROM
    (SELECT TOP 45 PERCENT SCORE 
    FROM SCORES
    WHERE YEAR(SCOREDATE) = 2010 AND MONTH(SCOREDATE) = 1
    ORDER BY SCORE DESC) AS SubQ

但我不知道如何获得所有月份的表格。

| Date       | Average | 45% | 55% |
+ -----------+---------+-----+-----+
| 01/01/2010 |      13 |  11 |  15 |
| 02/01/2010 |      10 |   8 |  12 |
| 03/01/2010 |       5 |   4 |  10 |
  ...
| 16/03/2010 |       7 |   7 |   9 |

目前，我必须将这些数据加载到我的应用程序中，并自己计算数字。或者运行大量单独的查询并整理结果。

呼。这真是一个脑筋急转弯。首先，我用于测试的表架构是：

Create Table Scores 
( 
    Id int not null identity(1,1) primary key clustered
    , [Date] datetime not null
    , Score int not null
)

现在，首先，我使用 SQL 2008 中的 CTE 计算了这些值，以便检查我的答案，然后构建了一个应该在 SQL 2000 中工作的解决方案。因此，在 SQL 2008 中，我们执行以下操作：

;With 
    SummaryStatistics As
    (
        Select Year([Date]) As YearNum
            , Month([Date]) As MonthNum
            , Min(Score) As MinScore
            , Max(Score) As MaxScore
            , Avg(Score) As AvgScore
        From Scores
        Group By Month([Date]), Year([Date])
    )
    , Percentiles As
    (
        Select Year([Date]) As YearNum
            , Month([Date]) As MonthNum
            , Score
            , NTile( 100 ) Over ( Partition By Month([Date]), Year([Date]) Order By Score ) As Percentile
        From Scores
    )
    , ReportedPercentiles As
    (
        Select YearNum, MonthNum
            , Min(Case When Percentile = 45 Then Score End) As Percentile45
            , Min(Case When Percentile = 55 Then Score End) As Percentile55
        From Percentiles
        Where Percentile In(45,55)
        Group By YearNum, MonthNum
    )
Select SS.YearNum, SS.MonthNum
    , SS.MinScore, SS.MaxScore, SS.AvgScore
    , RP.Percentile45, RP.Percentile55
From SummaryStatistics As SS
    Join ReportedPercentiles As RP
        On  RP.YearNum = SS.YearNum
            And RP.MonthNum = SS.MonthNum
Order By SS.YearNum, SS.MonthNum

现在介绍 SQL 2000 解决方案。本质上，诀窍是使用几个临时表来统计分数的出现情况。

If object_id('tempdb..#Working') is not null
    DROP TABLE #Working
GO
Create Table #Working 
    (
    YearNum int not null
    , MonthNum int not null
    , Score int not null
    , Occurances int not null
    , Constraint PK_#Working Primary Key Clustered ( MonthNum, YearNum, Score )
    )
GO
Insert #Working(MonthNum, YearNum, Score, Occurances)
Select Month([Date]), Year([Date]), Score, Count(*)
From Scores
Group By Month([Date]), Year([Date]), Score
GO
If object_id('tempdb..#SummaryStatistics') is not null
    DROP TABLE #SummaryStatistics
GO
Create Table #SummaryStatistics
    (
    MonthNum int not null
    , YearNum int not null
    , Score int not null
    , Occurances int not null
    , CumulativeTotal int not null
    , Percentile float null
    , Constraint PK_#SummaryStatistics Primary Key Clustered ( MonthNum, YearNum, Score )
    )
GO
Insert #SummaryStatistics(YearNum, MonthNum, Score, Occurances, CumulativeTotal)
Select W2.YearNum, W2.MonthNum, W2.Score, W2.Occurances, Sum(W1.Occurances)-W2.Occurances
From #Working As W1
    Join #Working As W2 
        On W2.YearNum = W1.YearNum
            And W2.MonthNum = W1.MonthNum
Where W1.Score <= W2.Score
Group By W2.YearNum, W2.MonthNum, W2.Score, W2.Occurances

Update #SummaryStatistics
Set Percentile = SS.CumulativeTotal * 100.0 / MonthTotal.Total
From #SummaryStatistics As SS
    Join    (
            Select SS1.YearNum, SS1.MonthNum, Max(SS1.CumulativeTotal) As Total
            From #SummaryStatistics As SS1
            Group By SS1.YearNum, SS1.MonthNum
            ) As MonthTotal
        On MonthTotal.YearNum = SS.YearNum
            And MonthTotal.MonthNum = SS.MonthNum

Select GeneralStats.*, Percentiles.Percentile45, Percentiles.Percentile55
From    (
        Select  Year(S1.[Date]) As YearNum
            , Month(S1.[Date]) As MonthNum
            , Min(S1.Score) As MinScore
            , Max(S1.Score) As MaxScore
            , Avg(S1.Score) As AvgScore
        From Scores As S1
        Group By Month(S1.[Date]), Year(S1.[Date])
        ) As GeneralStats
    Join    (
            Select SS1.YearNum, SS1.MonthNum
                , Min(Case When SS1.Percentile >= 45 Then Score End) As Percentile45
                , Min(Case When SS1.Percentile >= 55 Then Score End) As Percentile55
            From #SummaryStatistics As SS1
            Group By SS1.YearNum, SS1.MonthNum 
            ) As Percentiles
        On Percentiles.YearNum = GeneralStats.YearNum
            And Percentiles.MonthNum = GeneralStats.MonthNum