RobM RobM - 4 months ago 19
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

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

Comments