我正在尝试对如下数据集进行四分位划分：
ID VolumeID9 7500ID8 7000ID2 6400ID3 5800ID6 5800ID5 5200ID4 1400ID10 800ID7 700ID1 600我希望生成的结果如下：
ID Volume % CumSum QuartileID9 7500 0.182038835 18.2% 1ID8 7000 0.169902913 35.2% 2ID2 6400 0.155339806 50.7% 3ID3 5800 0.140776699 64.8% 3ID6 5800 0.140776699 78.9% 4ID5 5200 0.126213592 91.5% 4ID4 1400 0.033980583 94.9% 4ID10 800 0.019417476 96.8% 4ID7 700 0.016990291 98.5% 4ID1 600 0.014563107 100.0% 4我尝试了以下代码，但它只是根据ID和体积创建了等大小的四分位数：
SELECT ID,VOLUME, NTILE (4) OVER (ORDER BY VOLUME DESC) QUARTILE FROM MY_DATA;回答：
类似于Gordon的结果，使用ratio_to_report和case：
SQL> with temp as 2 (select id, 3 volume, 4 round(ratio_to_report (volume) over (), 9) pct 5 from test 6 ) 7 select id, 8 volume, 9 pct, 10 round(sum(pct) over (order by volume desc) * 100, 1) cumsum, 11 -- 12 case when sum(pct) over (order by volume desc) <= 0.25 then 1 13 when sum(pct) over (order by volume desc) <= 0.50 then 2 14 when sum(pct) over (order by volume desc) <= 0.75 then 3 15 else 4 16 end quartile 17 from temp 18 order by volume desc;ID VOLUME PCT CUMSUM QUARTILE---- ---------- ---------- ------ ----------ID9 7500 ,182038835 18,2 1ID8 7000 ,169902913 35,2 2ID2 6400 ,155339806 50,7 3ID3 5800 ,140776699 78,9 4ID6 5800 ,140776699 78,9 4ID5 5200 ,126213592 91,5 4ID4 1400 ,033980583 94,9 4ID10 800 ,019417476 96,8 4ID7 700 ,016990291 98,5 4ID1 600 ,014563107 100,0 410 rows selected.SQL>