RobM RobM - 1 year ago 57
MySQL Question

Select right percentile from joined table

How to select right value from joined table based on the value in more than one column.
I started sqlfiddle: http://sqlfiddle.com/#!9/f92daa/2
Main table:

id val
1 1
2 4
3 67
4 78
5 22


Joined table:

obs_id perc1 perc2 perc3 perc4 perc5 perc6 perc7 perc8 perc9 perc10
1 1 2 3 4 5 6 7 8 9 10
2 1 2 3 4 5 6 7 8 9 10
3 10 20 30 40 50 60 70 80 90 100
4 10 20 30 40 50 60 70 80 90 100
5 10 20 30 40 50 60 70 80 90 100


Expected result should be:

id val perc
1 1 1
2 4 4
3 67 70
4 78 80
5 22 30


perc - should be number from perc table (joined by ids) not greater than highest possible value form columns perc1-10

Table perc stores percentiles of observations. The goal is to find which interval hit the value from main table and take the upper band.

vkp vkp
Answer Source

You can use a case expression to do this. This assumes perc1 < perc2 < perc3 < perc4 < ... and so on.

SELECT obs.id, obs.val, 
case when val <= perc1 then perc1 
     when val <= perc2 then perc2 
     when val <= perc3 then perc3
     when val <= perc4 then perc4 
     when val <= perc5 then perc5 
     when val <= perc6 then perc6 
     when val <= perc7 then perc7 
     when val <= perc8 then perc8 
     when val <= perc9 then perc9
     when val <= perc10 then perc10 
     end
perc
FROM obs
INNER JOIN perc
ON obs.id = perc.obs_id

SQL Fiddle