Showing posts with label Oracle rank. Show all posts
Showing posts with label Oracle rank. Show all posts

Friday, December 14, 2012

Oracle NTILE function

Oracle Ntile function divides the records into the same number of rows (off at most by 1). Please notice that the records with same variable values (ties) may be placed into different buckets. The following query result shows that the maximum value in a bucket may overlap with the minimum value in the next bucket.

SQL> with tbl as (select ntile(5) over(order by price) nt, price from STOCK_PRICE) select nt, count(1), min(price), max(price) from tbl group by nt
 order by nt;

        NT   COUNT(1) MIN(PRICE) MAX(PRICE)
---------- ---------- ---------- ----------
         1      36387       9.58      13.68
         2      36387      13.68      16.72
         3      36386      16.72      20.87
         4      36386      20.87      26.87
         5      36386      26.87      89.53

We can also use function cume_dist combined with ceil function to divide the records into  similar number of rows. Ties will be placed into the same buckets. The following query result shows that there is no overlap of values between different buckets.

SQL> with tbl as (select ceil(cume_dist() over(order by price nulls first)*5)  nt, price from MV_VIX_15S_AVG) select nt, count(1), min(price), max(price) from tbl group by nt order by nt;

        NT   COUNT(1) MIN(PRICE) MAX(PRICE)
---------- ---------- ---------- ----------
         1      36358       9.58     13.675
         2      36332      13.68      16.71
         3      36413      16.72      20.86
         4      36429      20.87     26.865
         5      36400      26.87      89.53

Friday, August 24, 2012

Calculate Median value in Oracle

There are three functions that calculate median values, median(), percentile_count() and percentile_disc.
1. The data. 
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5

2. Calculate the median or 50th percentile.
select median(num) f_median,
   PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY num)  f_percentile_cont,
   PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY num) f_percentile_disc
from TBL_TEST ;
  F_MEDIAN F_PERCENTILE_CONT F_PERCENTILE_DISC
---------- ----------------- -----------------
         2                 2                 2

Wednesday, August 15, 2012

Rank in Oracle: rank() and dense_rank()


Unlike row_number() function, rank() and dense_rank() may produce duplicate ranks.

1. The data
select num from  TBL_TEST order by num;
       NUM
----------
         1
         1
         2
         2
         2
         3
         4
         4
         5
2.  rank() may have gaps. 
select num, rank() over(order by num) as rnk from  TBL_TEST  order by num;
       NUM        RNK
---------- ----------
         1          1
         1          1
         2          3
         2          3
         2          3
         3          6
         4          7
         4          7
         5          9
3. dense_ran() does not have gap in ranks.
select num, dense_rank() over(order by num) as rnk from  TBL_TEST  order by num;
       NUM        RNK
---------- ----------
         1          1
         1          1
         2          2
         2          2
         2          2
         3          3
         4          4
         4          4
         5          5