查詢以獲取加權百分位數

嘗試生成 SQL 以在給定的一組百分位值（下面使用的 25%、50% 和 75% 級別，但解決方案應允許任意參數級別）處計算加權連續值。換句話說，想要在下面的“源”表中的測試數據的 25%、50% 和 75% 累積百分位數中找到插值的“原始”值，由“cnt”加權。

NB： cnt表示該raw值在採樣期間出現的次數，預期輸出將對該raw值加權cnt以達到百分位數（類似於分位數/中位數和類似統計數據）

**測試數據：（**表：來源）

|  site  |  dateval   |  raw  |   cnt   |
+--------+------------+-------+---------+
|   A    | 2019-01-05 |   45  |      14 |
|   A    | 2019-01-05 |   52  |     178 |
|   A    | 2019-01-05 |   45  |       9 |
|   A    | 2019-01-05 |   37  |      75 |
|   A    | 2019-01-05 |   23  |      98 |
|   A    | 2019-01-05 |   78  |     102 |
|   A    | 2019-01-05 |   56  |       9 |
|   A    | 2019-01-05 |   17  |      54 |
|   A    | 2019-01-05 |   56  |       8 |
|   A    | 2019-01-06 |   33  |      35 |
|   A    | 2019-01-06 |   67  |      45 |
|   A    | 2019-01-06 |   65  |      93 |
|   A    | 2019-01-06 |   89  |     113 |
|   A    | 2019-01-06 |   52  |      64 |
|   A    | 2019-01-06 |  101  |      12 |
|   B    | 2019-01-05 |    5  |      25 |
|   B    | 2019-01-05 |   16  |      48 |
|   B    | 2019-01-05 |   12  |     107 |
|   B    | 2019-01-05 |   25  |      78 |
|   B    | 2019-01-05 |   44  |      53 |
|   B    | 2019-01-05 |    8  |      12 |
|   B    | 2019-01-05 |   31  |      32 |
|   B    | 2019-01-06 |   34  |      87 |
|   B    | 2019-01-06 |   18  |      35 |
|   B    | 2019-01-06 |   51  |      17 |
|   B    | 2019-01-06 |   22  |      23 |
|   B    | 2019-01-06 |   14  |      52 |
|   B    | 2019-01-06 |    6  |      34 |
+--------+------------+-------+---------+

預期輸出（四捨五入到最接近的 1/100）：

|  site  |   dateval  |   p00   |   p25   |   p50   |   p75   |   p100  |
+--------+------------+---------+---------+---------+---------+---------+
|   A    | 2019-01-05 |   17.00 |   22.07 |   45.92 |   51.30 |   78.00 |
|   A    | 2019-01-06 |   33.00 |   49.48 |   63.46 |   73.72 |  101.00 |
|   B    | 2019-01-05 |    5.00 |    9.93 |   14.79 |   24.57 |   44.00 |
|   B    | 2019-01-06 |    6.00 |   10.31 |   18.52 |   27.79 |   51.00 |
+--------+------------+---------+---------+---------+---------+---------+

注意：以上結果假設raw值之間是線性平滑的。例如， 的p25值22.07 = [ (25.00% - 54/547) / ((98+54)/547 - 54/547) ] * (23-17) + 17，其中547 = sum(cnt) | site='A' & dateval='2019-01-05'。

目前 SQL

下面根據表“源”中的“原始”值計算離散點的百分位值。但是，所需的輸出是在連續基礎上對應於給定百分位數的“原始”值（為簡單起見，離散“原始”級別之間的插值是線性的，而不是樣條/其他）。坦率地說，不確定以下方法是最合適的路徑：

WITH raw_lvl AS (
 SELECT "site", "dateval", "raw", sum("cnt") AS "sumcnt"
 FROM   source
 GROUP BY "site", "dateval", "raw"
), cum_raw AS (
 SELECT tlr.*, sum(tlr."sumcnt") OVER "win_cr" AS "cumsumcnt"
 FROM raw_lvl AS "tlr"
 WINDOW "win_cr" AS (PARTITION BY tlr."site", tlr."dateval" ORDER BY tlr."raw" ASC)
)
SELECT cr.*, cr."cumsumcnt"/(sum(cr."sumcnt") OVER "win_pr") AS "percentile" 
FROM cum_raw AS cr
WINDOW "win_pr" AS (PARTITION BY cr."site", cr."dateval");

Postgres 版本 10.3

Postgres 為您的目的提供了有序集聚合函式。

特殊困難：您希望行“加權”為cnt. 如果這應該意味著每一行代表cnt相同的行，您可以通過加入來乘以輸入行generate_series(1, cnt)：

SELECT site, dateval
    , percentile_cont('{0,.25,.5,.75,1}'::float8[]) WITHIN GROUP (ORDER BY raw)
FROM   source s, generate_series(1, s.cnt)
GROUP  BY 1, 2;

db<>在這裡擺弄

但結果與您的預期輸出不同（0 和 100 百分位數除外）。所以你“加權”的方式不同……

此外，您的原始查詢可以簡化為等效的：

SELECT site, dateval, raw, sum(cnt) AS sumcnt
    , sum(sum(cnt)) OVER w AS cumsumcnt
    , sum(sum(cnt)) OVER w / sum(sum(cnt)) OVER (PARTITION BY site, dateval) AS percentile 
FROM   source
GROUP  BY site, dateval, raw
WINDOW w AS (PARTITION BY site, dateval ORDER BY raw);

您可以在同一聚合函式的結果上執行視窗函式SELECT（反之亦然）。看：

• 需要單獨的列來計算百分比

我在上面的小提琴中添加了一個展示。

但兩者都沒有解釋你的“預期結果”中的奇數。無論你如何插值，這些都讓我覺得不正確。範例：22.07在第一行中 forp25似乎沒有意義 -在根據您自己的查詢考慮因素後，該值23佔據所有行直到27.7879百分位……cnt

引用自：https://dba.stackexchange.com/questions/227543