Partition data in Percentile range and assign different value for different Range

Question

I have table structure as shown in below

Temp
 Customer_id | sum

Now I have to create view with extra column customer_type and assign value 1 if customer lies in top 10% customer (with descending order of sum,also total number of customer may vary) and 2 if customer lies between 10%-20%, 3 if customer lies between 20%-60% and 4 if customer lies between 60%-100%. How can I do this?

I just able to extract top 10% and between 10% - 20% data but couldn't able to assign value as (source)

SELECT * FROM temp WHERE sum >= (SELECT sum FROM temp t1 
WHERE(SELECT count(*) FROM temp t2 WHERE t2.sum >= t1.sum) <= 
(SELECT 0.1 * count(*) FROM temp));

and (not efficient just enhance above code)

select * from temp t1 
where (select count(*) from temp t2 where t2.sum>=t2.sum)
>= (select 0.1 * count(*) from temp) and (select count(*) from temp t2 where t2.sum>=t1.sum)
<= (select 0.2 * count(*) from temp);

Sample data are available at sqlfiddle.com

Just my opinion but I don't think this is good use of a view. This has the ability to be a rather intensive operation and I wouldn't want it running every time someone accesses the view. Instead I would add a new table with this information, and populate it with a script in the wee hours each morning, so your data may not be real-time, but will be close enough and will be very efficient to grab when you need it. — invertedSpear, May 23 '13 at 18:03
@invertedSpear I agreed with you. But I'm currently doing data analysis, for which I create view(just for temporary)... — nKandel, May 24 '13 at 04:20
Maybe put up some sample data here http://sqlfiddle.com/ (50 records or so) and it might be easier to get help. — invertedSpear, May 24 '13 at 15:58

score 2 · Answer 1 · edited May 23 '17 at 11:50

This should help you. You need to get row number for sum and total number of rows. I'm sure you can figure out the rest easily.

SELECT  
    *,
    @curRow := @curRow + 1 AS row_number,
    (@curRow2 := @curRow2 + 1) / c as pct_row
FROM    
    temp t
    JOIN (SELECT @curRow := 0) r
    JOIN (SELECT @curRow2 := 0) r2
    join (select count(*) c from temp) s
order by 
    sum desc

This is based on this answer

score 2 · Accepted Answer · edited May 23 '17 at 12:18

I had solve this as like this. Thanks for @twn08 for his answer which guide me upto this.

select customer_id,sum,case 
when pct_row<=0.10 then 1
when pct_row>0.10 and pct_row<=0.20 then 2
when pct_row>0.20 and pct_row<=0.60 then 3
when pct_row>0.60 then 4
end as customer_label from (
select customer_id,sum,(@curRow := @curRow+1)/c as pct_row
from temp t 
jOIN (SELECT @curRow := 0) r
JOIN (SELECT @curRow2 := 0) r2 
join (select count(*) c from temp) s
order by sum desc) p;

I don't know whether this is efficient method or not but work fine for small data set.

Partition data in Percentile range and assign different value for different Range

2 Answers2