时间戳

首页 > 解决方案 > 四分位数百分比分析

oracle - 四分位数百分比分析

问题描述

我试图获得每个四分位数中 t3 和 t3 之间差异的最大值和最小值。但是当我将百分比 # 更改为 50,75.. 我看到下面列出的输出。我不确定我在这里做错了什么。

SELECT MIN(SPREAD),MAX(SPREAD) FROM
  (SELECT COUNT(*)*1.0 AS TOTALLINES FROM pa_fcs)A,
  (SELECT SPREAD,ROWNUM *1.0 AS LINENUM FROM (
select BOARD_RATE_T3,BOARD_RATE_T1 ,(BOARD_RATE_T3-BOARD_RATE_T1) AS SPREAD 
  from  pa_fcs ))B 
  WHERE LINENUM/TOTALLINES <= .25;

OUTPUT: .25
Min(Spread)      Max(Spread)
7.47             5160.24

Output :.50
Min(Spread)      Max(Spread)
7.47             5160.24

Output: .75
Min(Spread)      Max(Spread)
0              5160.24

标签： oracle

解决方案

使用NTILE解析函数计算四分位数。

然后是MIN，MAX琐碎的练习

create table tab as
select rownum spread from dual connect by level <= 100;

with ntile as (
select 
    spread,
    NTILE(4) OVER (ORDER BY spread DESC)  qtile
from tab)
select qtile, min(spread), max(spread)
from ntile
group by qtile order by 1
;

     QTILE MIN(SPREAD) MAX(SPREAD)
---------- ----------- -----------
         1          76         100
         2          51          75
         3          26          50
         4           1          25

对于您的桌子，它将类似于

with spr as (
  select  (BOARD_RATE_T3-BOARD_RATE_T1) AS SPREAD 
  from  pa_fcs),
ntile as (
  select 
    spread,
    NTILE(4) OVER (ORDER BY spread DESC)  qtile
  from spr)
select qtile, min(spread), max(spread)
from ntile
group by qtile order by 1

推荐阅读