I need to calculate percentiles, but my data is not in the format typically shown in examples and tutorials. Database is Sybase IQ. My data is in ranges with number of samples in each range. I need to calculate the percentile of the range number, continuous distribution. Any suggestion on how to do this, other than stepping through the ranges until the desired percentile is found and interpolating within that range? Thanks
That's for the PERCENTILE_CONT() function. From what you've described, you'd need to generate a number of rows equaling the Samples column value for each Range value. In SQL Server, that's typically done using a Common Table Expression that generates numeric values:
;WITH n1(n1) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0)
,n2(n2) AS (SELECT 0 FROM n1 a CROSS JOIN n1 b CROSS JOIN n1 c)
,n3(n3) AS (SELECT 0 FROM n2 x CROSS JOIN n2 y CROSS JOIN n2 z)
,numbers(num) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM n3)
SELECT Range, PERCENTILE_CONT(0.1)
WITHIN GROUP ( ORDER BY Range)
FROM numbers n INNER JOIN myTable m ON n.num<=m.Samples
GROUP BY Range;
I don't know if that will work in Sybase IQ or if I've interpreted your question correctly, please elaborate if I did not.
There may be a different way to generate a series of numbers in Sybase, I couldn't find it though in the documentation.
Thank you. Sybase IQ supports what you suggest. The syntax is not the same for the first four rows, so I need to figure out how to change that part, but you pointed me in the right direction, so I should be able to sort it out.