Menu
I'm now stuck on calculation median on postgres because there is no function about calculation median in postgres. Following is my query and I want to get median value of that query. Please help me how to do it.
SELECT count, conversion, company_id FROM (SELECT count(ap.id),
(count(ap.id)/cast(NULLIF(SUM(j.views), 0) as float) * 100) AS conversion,
j.company_id FROM applications ap RIGHT JOIN jobs j ON ap.job_id = j.id GROUP BY j.company_id order by conversion) with_avg
807
To compute the median, we will use the PERCENTILE_CONT(order_value, 0.5) function. The percentile function will go over each row of our datasets and return the median value (this is why we use the 0.5 parameters, meaning 50% of the values are above or below this point) of the order_value column.
To find the median: Arrange the data points from smallest to largest. If the number of data points is odd, the median is the middle data point in the list. If the number of data points is even, the median is the average of the two middle data points in the list.
SELECT CEIL(COUNT(*)/2) FROM data; Then take the middle value in a sorted subquery: SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x; I tested this with a 5x10e6 dataset of random numbers and it will find the median in under 10 seconds.
There is a function for calculating the median. The median is the 50% percentile, you could use percentile_disc, I think: https://www.postgresql.org/docs/9.6/static/functions-aggregate.html
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
