Calculating quantiles in SQL

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

SQL doesn't natively support the calculation of quantiles , so here is my attempt. Suppose there is an array of numbers x from 0 to 100 in my database. Quantile Q is calculated by dividing the following two outcomes :

  • Select count(x) from data where x<Q
  • Select count(x) from data
    How do I combine the two queries into one outcome ?

Select count(D1.x)/count(D2.x) from data AS D1, data AS D2 where D1.x<Q and D1.x = D2.x
(assuming no duplicates)
does not yield the desired answer.

2

Hi Figaro,

the condition "D1.x<Q" of your query will prevent all the lines to be counted, that's why you don't get what you expect.

I suppose the SQL engine you dispose of is very basic, because this query is very easy to write in a Oracle database for instance. I guess it's basic standard SQL.

So I thought in such circumstances that the CASE instructions (which is part of standard SQL) can help you. Here would be the query:

SELECT SUM(1) / SUM(CASE WHEN x<Q THEN 1 ELSE 0 END)
FROM data

Is it what you expected or didn't I understand the question properly?

3

I think I inverted the sums. It should be like this:

SELECT SUM(CASE WHEN x<Q THEN 1 ELSE 0 END) / SUM(1)
FROM data

4

That seems to work, thanks