从SQL中的直方图类型数据生成百分位数

Question

我有一个直方图类型的表。有2列: Bucket、Count。

bucket是直方图存储桶，Count是该存储桶中的值的数量。

现在，我的存储桶是有序的，例如，假设存储桶表示完成任务所需的分钟数。我们可以使用0-5分钟、5-10分钟、10-15等存储桶。

我试图计算的是哪个桶落在XXth百分位数中。例如，如果90%的任务在12分钟内完成，那么我想知道90%的任务在10-15桶或更少的范围内。

举个例子，假设我有下表：

Bucket | Count
--------------
  0    | 10
  1    | 15
  2    | 5
  3    | 15

如果我想计算60的百分位数。那就是

(10+15+5+15)*.60 = 27，因此结果将是存储桶2，因为60%的条目位于存储桶2或更少

有没有办法在SQL中计算这个值？

谢谢!

Answer 1

注意：COUNT是一个保留的SQL字，所以您可能应该使用valueCount。

应该是这样的(假设您的表名为histogramTable)：

SELECT bucket, 
    (SELECT SUM(valuecount) 
        FROM histogramTable AS in1 
        WHERE in1.bucket <= ot.bucket
    ) * 100 / (
            SELECT SUM(valueCOUNT) 
            FROM histogramTable AS in1
            ) As Percentile
FROM histogramTable AS ot 

当然，我没有转换任何DECIMAL数据类型的值，所以您的Percentile列将失去一些精度。

然后假设您需要表示百分位数80的存储桶：

DECLARE @Percentile AS INT
SET @Percentile = 80

SELECT TOP 1 bucket FROM(
SELECT bucket, 
    (SELECT SUM(valuecount) 
        FROM histogramTable AS in1 
        WHERE in1.bucket <= ot.bucket
    ) * 100 / (
            SELECT SUM(valueCOUNT) 
            FROM histogramTable AS in1
            ) As Percentile
FROM histogramTable AS ot 
) AS h
WHERE h.Percentile > @Percentile
ORDER BY Percentile;

Answer 2

从SQL Server2012开始，现在有了可用于此目的的SQL标准PERCENTILE_DISC和PERCENTILE_CONT反向分布函数。遗憾的是，到目前为止，SQL Server仅将它们实现为窗口函数，而不是聚合函数。

它们在您已经显示的数据集(似乎是预聚合的)上不是很有用，但它们在源数据集上肯定会有帮助，在那里您可以简单地计算：

SELECT DISTINCT percentile_disc(0.6) WITHIN GROUP (ORDER BY bucket) OVER ()
FROM t

I have blogged about percentiles in SQL here, in more detail。