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
Popular Topics
Popular Topics
Showing posts with label Oracle rank. Show all posts
Showing posts with label Oracle rank. Show all posts
Friday, December 14, 2012
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
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
Subscribe to:
Posts (Atom)