How to get percentiles instead of rank
I have a SELECT where one of the rows I am using for the percentile is:
NTILE(100) OVER(PARTITION BY ItemID ORDER BY SUM(quantity)) as Percentile
The data looks like this:
ItemID | Quantity | Price | TotalQ
ABC 10 14.50 38
ABC 4 14.25 38
DEF 32 22.41 34
ABC 24 14.10 38
GHI 8 8.50 20
GHI 12 8.60 20
DEF 2 22.30 34
However, the Percentile field becomes just a ranking of each value for each ItemID. I would like each row to measure the cumulative percentage of volume for each transaction price (each row is a separate transaction), starting at the top and going down.
0
source to share
2 answers
When you do percentiles, I prefer to do the calculations myself - it's easy enough with window functions. If you are using SQL Server 2012, you have the sum total and you can do what you want:
select itemId, quantity, price, TotalQ, cast(cumQ as float) / TotQ as Percentile
from (select t.*,
sum(TotalQ) over (partition by ItemId) as TotQ,
sum(TotalQ) over (partition by itemId order by TotalQ desc) as cumQ
from t
) t
You can multiply Percentile by 100 and truncate or round if you like.
If you don't have SQL Server 2012, you can do the same with a correlated subquery:
select itemId, quantity, price, TotalQ, cast(cumQ as float) / TotQ as Percentile
from (select t.*,
sum(TotalQ) over (partition by ItemId) as TotQ,
(select sum(TotalQ)
from t t2
where t2.itemId = t.ItemId and t2.TotalQ >= t.TotalQ
) as cumQ
from t
) t
+2
source to share