SQLite Forum

Get column rank from values in selected row
Login

Get column rank from values in selected row

(1) By anonymous on 2021-05-06 20:06:02 [link] [source]

I have table with age values in column 1 and weight values in the remaining columns indicating the weight centiles for a given age, simplified:

age,c10,c25,c50,c75,c90

1,5,6,7,8,9

2,6,7,8,9,10

3,7,8,9,10,11

So, for example age 3, weight 9 then that would give c50 (50th centile, that is average weight for that age).

How would I do that in SQL, so giving me c50 for values age 3 and weight 9?

RBS

(2.1) By Gunter Hick (gunter_hick) on 2021-05-07 06:04:19 edited from 2.0 in reply to 1 [link] [source]

The reason for your problem is that you are using column names as data.

Your centiles need to go into a separate table.

CREATE TABLE centiles (ID INTEGER PRIMARY KEY, cent TEXT);
CREATE TABLE weights  (ID INTEGER PRIMARY KEY, age INTEGER, WEIGHT INTEGER, CENT_ID INTEGER REFERENCES centiles(ID), UNIQUE(age,weight));

SELECT c.cent FROM weights w JOIN centiles c ON (c.ID = w.cent_id) WHERE w.age = 3 and w.weight = 9;

(3) By anonymous on 2021-05-07 06:37:50 in reply to 2.1 [link] [source]

Yes, I can see that would be a solution, but that would mean I have to make those 2 tables in code. Data is always supplied in the format of the posted simplified table. I was hoping I could get the column rank with a window function or a CTE.

RBS

(4) By Harald Hanche-Olsen (hanche) on 2021-05-07 08:43:32 in reply to 3 [link] [source]

You can do all kinds of neat things with triggers. For example, an INSERT trigger on the original table to populate the other tables. I am not sure if this is useful in your case, but it's a thought anyway.

Myself, I have only recently discovered this use of triggers, but now I use them quite a bit. (Perhaps I am overly enthusiastic, but …) A typical pattern for me is a VIEW named incoming with a trigger INSTEAD OF INSERT ON incoming allowing me to directly import data from a csv file (or even json, in one case) and have the values inserted in tables that are better suited to the problem at hand.

(5) By anonymous on 2021-05-07 08:55:11 in reply to 4 [link] [source]

I think the easiest solution is to "tranpose" the table: Centiles in column 1, field name Centile. Ages as further field names. Weights in table columns 2 till last. I can then do: Select centile from T where A3 = 9

RBS

(8) By Gunter Hick (gunter_hick) on 2021-05-07 11:55:43 in reply to 5 [link] [source]

That would be "spreadsheet thinking". Doable but arduous. Try some "database thinking" instead. There are good reasons for normalizing tables in databases. They just work so much better when used as designed.

(7) By Gunter Hick (gunter_hick) on 2021-05-07 11:51:20 in reply to 3 [link] [source]

What is preventing you from loading the non-normalized data into normalized tables? That would be the classic job of the "input" phase: convert data to the form best suited to the actual processing.

What if some day the input format is changed to a different number of precentiles or the borders change? You would have to rewrite all of the queries, as opposed to adjusting only the input conversion.

(10) By anonymous on 2021-05-07 15:02:45 in reply to 7 [link] [source]

Thanks, will have a look at all the mentioned options.

RBS

(6.1) By Ryan Smith (cuz) on 2021-05-07 11:44:19 edited from 6.0 in reply to 1 [link] [source]

Well, that's not how SQL works, but, in the interest of fun....

Are the tables ALWAYS the same layout? (i.e. have the exact same columns?)

If that is the case, then one can do something like this:

WITH normalized(age, weight, percentile) AS (
    SELECT age, c10, 10 FROM centiles UNION ALL
    SELECT age, c25, 25 FROM centiles UNION ALL
    SELECT age, c50, 50 FROM centiles UNION ALL
    SELECT age, c75, 75 FROM centiles UNION ALL
    SELECT age, c90, 90 FROM centiles 
)
SELECT *
  FROM normalized
;


-- And to get the specific percentile for Age 3 at weight 9 - simply chenge the end query to 

SELECT percentile
  FROM normalized
 WHERE age = 3 AND weight = 9
;


EDIT: I should add that the table "centiles" being used is constructed from the OP's given layout - resulting in:

CREATE TABLE centiles(age, c10, c25, c50, c75, c90);
INSERT INTO centiles VALUES 
 ( 1, 5, 6, 7,  8,  9)
,( 2, 6, 7, 8,  9, 10)
,( 3, 7, 8, 9, 10, 11)
;

This will work for any size table and can be adjusted to suit more columns.

(9) By Ryan Smith (cuz) on 2021-05-07 12:06:06 in reply to 6.1 [link] [source]

I agree with Gunter's post - this should really be fixed in the INPUT phase, which will future proof your queries.

If you still only want to fix it as it is now, I suggest perhaps changing my suggestion, considering Gunter's comments, to be a view that can be queried in lots of places, but fixed in one place, should the need arise:

CREATE TABLE centiles(age, c10, c25, c50, c75, c90);
INSERT INTO centiles VALUES 
 ( 1, 5, 6, 7,  8,  9)
,( 2, 6, 7, 8,  9, 10)
,( 3, 7, 8, 9, 10, 11)
;


CREATE VIEW centiles_normed AS
  SELECT 0 AS age, 0 AS weight, 0 AS percentile UNION ALL
  SELECT age, c10, 10 FROM centiles UNION ALL
  SELECT age, c25, 25 FROM centiles UNION ALL
  SELECT age, c50, 50 FROM centiles UNION ALL
  SELECT age, c75, 75 FROM centiles UNION ALL
  SELECT age, c90, 90 FROM centiles 
;

(Note: The first select simply adds the column names, but you can do that with an actual row too if you wish not to have a Zero age row)


To later get the specific percentiles, you can have queries like this:

SELECT percentile
  FROM centiles_normed
 WHERE age = 3 AND weight = 9
;

(11) By anonymous on 2021-05-07 15:03:06 in reply to 9 [link] [source]

Thanks, will have a look at all the mentioned options.

RBS

(12) By anonymous on 2021-05-08 05:43:07 in reply to 9 [link] [source]

Thanks, this works nicely indeed and moved the data to a table, holding all possible combinations of sex, months and weight. I am trying to work out what the best way is to get the centile from the input data as the provided months and weight won't be in the table. So for example months 6, weight 8 how would I get the centile. One option is to take the nearest months and weight and I can do that, but ideally I would make it proportional to get the more accurate centile as if it was taken from a chart. I can do this in code, but I would like to do it in SQL and not sure how to do that.

RBS

(13) By anonymous on 2021-05-08 11:49:34 in reply to 12 [link] [source]

I think I have this worked out now:

DDL of table: CREATE TABLE WEIGHT_CENT_M36(MALE INTEGER, MONTHS REAL, WEIGHT REAL, CENTILE INTEGER)

Sample data (up to 2.5 months):

MALE MONTHS WEIGHT CENTILE

1 0.0 2.36 3 1 0.0 2.53 5 1 0.0 2.77 10 1 0.0 3.15 25 1 0.0 3.53 50 1 0.0 3.88 75 1 0.0 4.17 90 1 0.0 4.34 95 1 0.0 4.45 97 1 0.5 2.80 3 1 0.5 2.96 5 1 0.5 3.21 10 1 0.5 3.60 25 1 0.5 4.00 50 1 0.5 4.39 75 1 0.5 4.72 90 1 0.5 4.91 95 1 0.5 5.03 97 1 1.5 3.61 3 1 1.5 3.77 5 1 1.5 4.02 10 1 1.5 4.43 25 1 1.5 4.88 50 1 1.5 5.33 75 1 1.5 5.73 90 1 1.5 5.97 95 1 1.5 6.12 97 1 2.5 4.34 3 1 2.5 4.50 5 1 2.5 4.75 10 1 2.5 5.18 25 1 2.5 5.67 50 1 2.5 6.18 75 1 2.5 6.64 90 1 2.5 6.92 95 1 2.5 7.11 97 2 0.0 2.41 3 2 0.0 2.55 5 2 0.0 2.75 10 2 0.0 3.06 25 2 0.0 3.40 50 2 0.0 3.72 75 2 0.0 3.99 90 2 0.0 4.15 95 2 0.0 4.25 97 2 0.5 2.76 3 2 0.5 2.89 5 2 0.5 3.10 10 2 0.5 3.44 25 2 0.5 3.80 50 2 0.5 4.15 75 2 0.5 4.45 90 2 0.5 4.63 95 2 0.5 4.74 97 2 1.5 3.40 3 2 1.5 3.55 5 2 1.5 3.77 10 2 1.5 4.14 25 2 1.5 4.54 50 2 1.5 4.95 75 2 1.5 5.31 90 2 1.5 5.52 95 2 1.5 5.66 97 2 2.5 4.00 3 2 2.5 4.15 5 2 2.5 4.39 10 2 2.5 4.78 25 2 2.5 5.23 50 2 2.5 5.68 75 2 2.5 6.09 90 2 2.5 6.33 95 2 2.5 6.49 97

SQL:

select round(max(min(c * (m / ?), 97), 3), 0) as cent from (select months as m, weight as w, abs(1 - weight / ?) as p, centile as c from WEIGHT_CENT_M36 where male = 1 and months > (select max(months) from WEIGHT_CENT_M36 where months < ?) and months <= (select min(months) from WEIGHT_CENT_M36 where months >= ?) order by p asc limit 1)

where the first place holder is months and the second one weight and the 2 remaining ones months again. This seems to be working OK in some light testing.

RBS

(14) By anonymous on 2021-05-08 15:36:51 in reply to 13 [link] [source]

Had left the weight out of the correction and SQL should be:

select round(max(min(c * (m / ?) * (? / w), 97), 3), 0) as cent from (select months as m, weight as w, abs(1 - weight / ?) as p, centile as c from WEIGHT_CENT_M36 where male = 1 and months > (select max(months) from WEIGHT_CENT_M36 where months < ?) and months <= (select min(months) from WEIGHT_CENT_M36 where months >= ?) order by p asc limit 1)

where the second placeholder is the supplied weight.

Still, not quite right as I can see still relatively big centile differences with small provided values differences.

RBS

(15) By anonymous on 2021-05-08 16:50:14 in reply to 14 [link] [source]

This seems a bit better:

select coalesce(round(max(min(c * (m / ?) * (? / w), 97), 3), 0), "") as cent from (select age_months as m, weight as w, abs(1 - age_months / ?) as p1, abs(1 - weight / ?) as p2, centile as c from weight_centiles_m36 where male = ? order by p1 asc, p2 asc limit 1)

where parameters are: months, weight, months, weight

Still this doesn't give smooth chart-like values and I think I may need to interpolate month and weight values in the source table.

RBS

(16) By Ryan Smith (cuz) on 2021-05-08 20:52:06 in reply to 14 [link] [source]

I'd like to help more, but honestly I'm at a loss what you are trying to do or achieve with the math. Taking your word the math is correct, I'm still unsure of what the problem or intended use/result is.

I think it's a typical type of problem where the result/answer is to clear to you because your mind has been involved with it intimately for a while now and the problem definition is second nature to you by now, but none of us know what you really need or why you need it, so it's all guessing.

If you give an example table with clear columns, and a definition of what you need from it and an example result that would be correct (with enough example rows to ensure only one interpretation of the query can work), we can help make that Query happen - but I'm afraid the current definition is hard to follow.

One example, quoting you:

Still, not quite right as I can see still relatively big centile differences with small provided values differences.

Why are big centile differences wrong? Different to what? Should it not be? Why not? Are the calcs working correctly when not in a query? What small value differences? Provided how and why??
You may as well have told us that you are on duty because the train station near you blew its left drum again. It raises very many more questions than it answers.

(17) By anonymous on 2021-05-08 21:21:53 in reply to 16 [link] [source]

Why are big centile differences wrong?

This is data for weight centile charts, given an age (here in months) and a weight and thirdly male or female. They will give centiles, meaning for that give age and weight and sex x % of children will be below that weight for that age and and the remaining % will be above that weight for that age. If for a given age the weight is slowly supplied higher and higher then the centile values should slowly go up. The data provided is only provided step-wise, so I have to work out interpolating values. This is the full original data set, so not yet normalised:

Sex Agemos P3 P5 P10 P25 P50 P75 P90 P95 P97 1 0.0 2.355451 2.526904 2.773802 3.150611 3.530203 3.879077 4.172493 4.340293 4.446488 1 0.5 2.799549 2.964656 3.209510 3.597396 4.003106 4.387423 4.718161 4.910130 5.032625 1 1.5 3.614688 3.774849 4.020561 4.428873 4.879525 5.327328 5.728153 5.967102 6.121929 1 2.5 4.342341 4.503255 4.754479 5.183378 5.672889 6.175598 6.638979 6.921119 7.106250 1 3.5 4.992898 5.157412 5.416803 5.866806 6.391392 6.942217 7.460702 7.781401 7.993878 1 4.5 5.575169 5.744752 6.013716 6.484969 7.041836 7.635323 8.202193 8.556813 8.793444 1 5.5 6.096775 6.272175 6.551379 7.043627 7.630425 8.262033 8.871384 9.255615 9.513307 1 6.5 6.564430 6.745993 7.035656 7.548346 8.162951 8.828786 9.475466 9.885436 10.161350 1 7.5 6.984123 7.171952 7.472021 8.004399 8.644832 9.341490 10.021014 10.453314 10.744924 1 8.5 7.361236 7.555287 7.865533 8.416719 9.081120 9.805593 10.514064 10.965736 11.270838 1 9.5 7.700624 7.900755 8.220839 8.789882 9.476500 10.226124 10.960172 11.428676 11.745385 1 10.5 8.006677 8.212684 8.542195 9.128110 9.835308 10.607722 11.364450 11.847633 12.174357 1 11.5 8.283365 8.495000 8.833486 9.435279 10.161536 10.954660 11.731602 12.227661 12.563083 1 12.5 8.534275 8.751264 9.098246 9.714942 10.458854 11.270871 12.065948 12.573402 12.916450 1 13.5 8.762649 8.984701 9.339688 9.970338 10.730626 11.559963 12.371453 12.889108 13.238933 1 14.5 8.971407 9.198222 9.560722 10.204418 10.979925 11.825241 12.651749 13.178670 13.534622 1 15.5 9.163180 9.394454 9.763982 10.419863 11.209555 12.069725 12.910152 13.445640 13.807244 1 16.5 9.340328 9.575757 9.951840 10.619101 11.422068 12.296170 13.149687 13.693251 14.060194 1 17.5 9.504964 9.744251 10.126434 10.804329 11.619777 12.507080 13.373106 13.924442 14.296548 1 18.5 9.658975 9.901830 10.289680 10.977527 11.804779 12.704728 13.582902 14.141875 14.519093 1 19.5 9.804039 10.050187 10.443295 11.140475 11.978966 12.891168 13.781331 14.347954 14.730343 1 20.5 9.941645 10.190823 10.588812 11.294771 12.144043 13.068254 13.970425 14.544842 14.932559 1 21.5 10.073105 10.325070 10.727592 11.441847 12.301541 13.237653 14.152010 14.734482 15.127765 1 22.5 10.199575 10.454102 10.860844 11.582978 12.452830 13.400856 14.327718 14.918606 15.317770 1 23.5 10.322062 10.578949 10.989635 11.719299 12.599135 13.559197 14.499004 15.098756 15.504178 1 24.5 10.441442 10.700513 11.114904 11.851817 12.741544 13.713860 14.667158 15.276296 15.688406 1 25.5 10.558473 10.819575 11.237473 11.981419 12.881023 13.865896 14.833316 15.452424 15.871699 1 26.5 10.673803 10.936812 11.358059 12.108888 13.018424 14.016230 14.998478 15.628189 16.055142 1 27.5 10.787982 11.052801 11.477280 12.234907 13.154497 14.165673 15.163512 15.804500 16.239671 1 28.5 10.901473 11.168034 11.595670 12.360072 13.289897 14.314932 15.329172 15.982139 16.426090 1 29.5 11.014664 11.282926 11.713683 12.484898 13.425194 14.464622 15.496103 16.161769 16.615076 1 30.5 11.127870 11.397820 11.831705 12.609827 13.560881 14.615270 15.664853 16.343950 16.807196 1 31.5 11.241348 11.513000 11.950054 12.735234 13.697379 14.767324 15.835883 16.529146 17.002914 1 32.5 11.355298 11.628692 12.068997 12.861438 13.835046 14.921166 16.009575 16.717730 17.202598 1 33.5 11.469880 11.745079 12.188748 12.988699 13.974182 15.077109 16.186239 16.910002 17.406538 1 34.5 11.585210 11.862300 12.309477 13.117232 14.115032 15.235412 16.366119 17.106191 17.614947 1 35.5 11.701371 11.980456 12.431315 13.247207 14.257796 15.396279 16.549405 17.306461 17.827972 1 36.0 11.759784 12.039910 12.492682 13.312776 14.329944 15.477724 16.642369 17.408165 17.936247 2 0.0 2.414112 2.547905 2.747222 3.064865 3.399186 3.717519 3.992572 4.152637 4.254922 2 0.5 2.756917 2.894442 3.101767 3.437628 3.797528 4.145594 4.450126 4.628836 4.743582 2 1.5 3.402293 3.547610 3.770157 4.138994 4.544777 4.946766 5.305632 5.519169 5.657379 2 2.5 3.997806 4.150639 4.387042 4.784820 5.230584 5.680083 6.087641 6.332837 6.492574 2 3.5 4.547383 4.707123 4.955926 5.379141 5.859961 6.351512 6.802770 7.076723 7.256166 2 4.5 5.054539 5.220488 5.480295 5.925888 6.437588 6.966524 7.457119 7.757234 7.954730 2 5.5 5.522500 5.693974 5.963510 6.428828 6.967850 7.530180 8.056331 8.380330 8.594413 2 6.5 5.954272 6.130641 6.408775 6.891533 7.454854 8.047178 8.605636 8.951544 9.180938 2 7.5 6.352668 6.533373 6.819122 7.317373 7.902436 8.521877 9.109878 9.476009 9.719621 2 8.5 6.720328 6.904886 7.197414 7.709516 8.314178 8.958324 9.573546 9.958480 10.215388 2 9.5 7.059732 7.247736 7.546342 8.070932 8.693418 9.360271 10.000792 10.403355 10.672801 2 10.5 7.373212 7.564327 7.868436 8.404400 9.043262 9.731193 10.395451 10.814695 11.096070 2 11.5 7.662959 7.856916 8.166069 8.712513 9.366594 10.074306 10.761063 11.196246 11.489076 2 12.5 7.931030 8.127621 8.441460 8.997692 9.666089 10.392576 11.100887 11.551451 11.855391 2 13.5 8.179356 8.378425 8.696684 9.262185 9.944226 10.688742 11.417920 11.883477 12.198293 2 14.5 8.409744 8.611186 8.933680 9.508085 10.203294 10.965321 11.714911 12.195223 12.520785 2 15.5 8.623887 8.827638 9.154251 9.737329 10.445406 11.224627 11.994379 12.489340 12.825610 2 16.5 8.823370 9.029399 9.360079 9.951715 10.672507 11.468779 12.258624 12.768249 13.115272 2 17.5 9.009668 9.217980 9.552723 10.152900 10.886386 11.699718 12.509741 13.034150 13.392045 2 18.5 9.184160 9.394782 9.733630 10.342415 11.088682 11.919211 12.749637 13.289041 13.657990 2 19.5 9.348127 9.561110 9.904140 10.521669 11.280895 12.128870 12.980037 13.534728 13.914971 2 20.5 9.502760 9.718170 10.065489 10.691956 11.464397 12.330156 13.202503 13.772841 14.164667 2 21.5 9.649162 9.867081 10.218820 10.854462 11.640434 12.524391 13.418439 14.004844 14.408584 2 22.5 9.788355 10.008874 10.365180 11.010271 11.810139 12.712769 13.629108 14.232045 14.648068 2 23.5 9.921281 10.144499 10.505533 11.160373 11.974537 12.896363 13.835635 14.455614 14.884316 2 24.5 10.048808 10.274829 10.640760 11.305666 12.134555 13.076132 14.039025 14.676585 15.118389 2 25.5 10.171734 10.400664 10.771667 11.446967 12.291025 13.252935 14.240165 14.895872 15.351221 2 26.5 10.290791 10.522736 10.898985 11.585013 12.444692 13.427531 14.439838 15.114276 15.583627 2 27.5 10.406644 10.641710 11.023380 11.720466 12.596223 13.600594 14.638729 15.332495 15.816318 2 28.5 10.519902 10.758192 11.145454 11.853924 12.746209 13.772713 14.837433 15.551131 16.049903 2 29.5 10.631115 10.872728 11.265746 11.985917 12.895172 13.944404 15.036464 15.770701 16.284906 2 30.5 10.740782 10.985812 11.384743 12.116919 13.043572 14.116113 15.236260 15.991643 16.521765 2 31.5 10.849350 11.097886 11.502878 12.247348 13.191808 14.288225 15.437192 16.214324 16.760846 2 32.5 10.957219 11.209345 11.620537 12.377573 13.340229 14.461065 15.639570 16.439044 17.002447 2 33.5 11.064747 11.320538 11.738058 12.507913 13.489134 14.634908 15.843647 16.666048 17.246809 2 34.5 11.172248 11.431774 11.855740 12.638647 13.638774 14.809981 16.049626 16.895526 17.494115 2 35.5 11.279999 11.543323 11.973842 12.770013 13.789365 14.986470 16.257667 17.127623 17.744504 2 36.0 11.334045 11.599289 12.033121 12.835999 13.865074 15.075293 16.362500 17.244688 17.870885

Hopefully this makes it clearer.

RBS

(18) By anonymous on 2021-05-09 09:07:09 in reply to 17 [link] [source]

There is something I overlooked in the provided data: These files contain the L, M, and S parameters needed to generate exact percentiles and z-scores. Got these files from here: https://www.cdc.gov/growthcharts/percentile_data_files.htm Not sure how these L, M, and S parameters but it looks they are needed to get the missing values, so will have a look into this.

RBS

(19) By anonymous on 2021-05-09 13:52:46 in reply to 18 [source]

I made things difficult by keeping the months column as real and looking at lower and higher months rows. just providing an integer month, ranging from 0 to 37 makes things a lot easier and this simple SQL:

select centile from weight_centiles_m36 where male = ? and age_months = ? order by abs(? - weight) limit 1

With the supplied parameters sex, months and weight gets the centile nearest to the provided data. This is for now good enough. Will have a look at making the centile more accurate, based on the difference between the provided weight and picked weight from the normalized table (made as suggested by Ryan Smith).

I couldn't make the formula as in the quoted file work to get the outcome it should give:

To obtain the z-score (Z) and corresponding percentile for a given measurement (X), use the following equation:

((X/M)**L) – 1

Z = ————————-, L≠0 LS

or

Z = ln(X/M)/S ,L=0

where X is the physical measurement (e.g. weight, length, head circumference, stature or calculated BMI value) and L, M and S are the values from the appropriate table corresponding to the age in months of the child (or length/stature). (X/M)**L means raising the quantity (X/M) to the Lth power. For example, to obtain the weight-for-age z-score of a 9-month-old male who weighs 9.7 kg, we would look up the L, M and S values from the WTAGEINF table, which are L=-0.1600954, M=9.476500305, and S=0.11218624. Using the equation above, we calculate that the z-score for this child is 0.207. This z-score corresponds to the 58th percentile.

Not sure what exactly is meant with LS as in the first formula.

Nearly there though.

RBS

(20) By anonymous on 2021-05-09 22:38:00 in reply to 19 [link] [source]

All sorted now and although this has little to do with SQLite now, I might as well show how this was done. This is coding in B4A, for Android phones. As it turns out I only need this clinical data:

Sex Agemos M S

1 0 3.530203168 0.152385273 1 1 4.003106424 0.146025021 1 2 4.879525083 0.136478767 1 3 5.672888765 0.129677511 1 4 6.391391982 0.124717085 1 5 7.041836432 0.121040119 1 6 7.630425182 0.1182712 1 7 8.162951035 0.116153695 1 8 8.644832479 0.114510349 1 9 9.081119817 0.113217163 1 10 9.476500305 0.11218624 1 11 9.835307701 0.111354536 1 12 10.16153567 0.110676413 1 13 10.45885399 0.110118635 1 14 10.7306256 0.109656941 1 15 10.97992482 0.109273653 1 16 11.20955529 0.10895596 1 17 11.4220677 0.108694678 1 18 11.61977698 0.108483324 1 19 11.80477902 0.108317416 1 20 11.9789663 0.108193944 1 21 12.14404334 0.108110954 1 22 12.30154103 0.108067236 1 23 12.45283028 0.108062078 1 24 12.59913494 0.108095077 1 25 12.74154396 0.108166005 1 26 12.88102276 0.108274705 1 27 13.01842382 0.108421024 1 28 13.1544966 0.108604769 1 29 13.28989667 0.108825681 1 30 13.42519408 0.109083423 1 31 13.56088113 0.109377581 1 32 13.69737858 0.109707646 1 33 13.83504622 0.110073084 1 34 13.97418199 0.110473238 1 35 14.1150324 0.1109074 1 36 14.25779618 0.111374787 1 37 14.32994444 0.111620652 2 0 3.39918645 0.142106724 2 1 3.79752846 0.138075916 2 2 4.544776513 0.131733888 2 3 5.230584214 0.126892697 2 4 5.859960798 0.123025182 2 5 6.437587751 0.119840911 2 6 6.967850457 0.117166868 2 7 7.454854109 0.11489384 2 8 7.902436186 0.112949644 2 9 8.314178377 0.11128469 2 10 8.693418423 0.109863709 2 11 9.043261854 0.10866078 2 12 9.366593571 0.10765621 2 13 9.666089185 0.106834517 2 14 9.944226063 0.106183085 2 15 10.20329397 0.105691242 2 16 10.4454058 0.105349631 2 17 10.67250698 0.105149754 2 18 10.88638558 0.105083666 2 19 11.08868151 0.105143752 2 20 11.28089537 0.105322575 2 21 11.46439708 0.10561278 2 22 11.64043402 0.106007025 2 23 11.81013895 0.106497957 2 24 11.97453748 0.107078197 2 25 12.13455528 0.107740346 2 26 12.2910249 0.108477009 2 27 12.44469237 0.109280822 2 28 12.59622335 0.110144488 2 29 12.74620911 0.111060814 2 30 12.89517218 0.112022758 2 31 13.04357164 0.113023466 2 32 13.19180827 0.114056316 2 33 13.34022934 0.115114952 2 34 13.48913357 0.116193337 2 35 13.63877446 0.11728575 2 36 13.78936547 0.118386847 2 37 13.86507382 0.118939087

And then a conversion table to convert Z-Score to centile

Z_Score,Centile

-2.326,1 -2.054,2 -1.881,3 -1.751,4 -1.645,5 -1.555,6 -1.476,7 -1.405,8 -1.341,9 -1.282,10 -1.227,11 -1.175,12 -1.126,13 -1.08,14 -1.036,15 -0.994,16 -0.954,17 -0.915,18 -0.878,19 -0.842,20 -0.806,21 -0.772,22 -0.739,23 -0.706,24 -0.674,25 -0.643,26 -0.613,27 -0.583,28 -0.553,29 -0.524,30 -0.496,31 -0.468,32 -0.44,33 -0.412,34 -0.385,35 -0.358,36 -0.332,37 -0.305,38 -0.279,39 -0.253,40 -0.228,41 -0.202,42 -0.176,43 -0.151,44 -0.126,45 -0.1,46 -0.075,47 -0.05,48 -0.025,49 0,50 0.025,51 0.05,52 0.075,53 0.1,54 0.126,55 0.151,56 0.176,57 0.202,58 0.228,59 0.253,60 0.279,61 0.305,62 0.332,63 0.358,64 0.385,65 0.412,66 0.44,67 0.468,68 0.496,69 0.524,70 0.553,71 0.583,72 0.613,73 0.643,74 0.674,75 0.706,76 0.739,77 0.772,78 0.806,79 0.842,80 0.878,81 0.915,82 0.954,83 0.994,84 1.036,85 1.08,86 1.126,87 1.175,88 1.227,89 1.282,90 1.341,91 1.405,92 1.476,93 1.555,94 1.645,95 1.751,96 1.881,97 2.054,98 2.326,99

B4A code is then like this:

Sub btnCentilesCalculate_Click

Dim iSexType As Int
Dim iMonths As Int
Dim dWeight As Double
Dim dMedian As Double
Dim strSQL As String
Dim dGCV As Double 'Generalized Coefficient of Variation
Dim dZ As Double 'Z-Score
Dim iCentile As Int
Dim RS1 As ResultSet

If chkCentilesMale.Checked Then
	iSexType = 1
Else
	iSexType = 2
End If

iMonths = edtCentilesMonths.Text
dWeight = edtCentilesWeight.Text

strSQL = "select M, S from weight_data_m36 " & _
		 "where sex = ? And agemos = ?"
RS1 = General.cConn.SQL1.ExecQuery2(strSQL, _
Array As String(iSexType, iMonths))
RS1.Position = 0
dMedian = RS1.GetDouble2(0)
dGCV = RS1.GetDouble2(1)

dZ = Logarithm(dWeight / dMedian, cE) / dGCV
strSQL = "select centile from zscore2centile " & _
		 "order by Abs(? - z_score) Asc limit 1"
iCentile = General.cConn.SQL1.ExecQuerySingleResult2(strSQL, _
		   Array As String(dZ))

lblCentile.Text = iCentile

End Sub

RBS

(21) By Ryan Smith (cuz) on 2021-05-10 06:19:09 in reply to 20 [link] [source]

Thank you, these subsequent posting were a lot clearer.

Nice going sorting it out, and thanks for posting, it's always fun to see a mystery solved. :)

(22) By Gunter Hick (gunter_hick) on 2021-05-10 06:56:45 in reply to 19 [link] [source]

The general problem is "interpolation" and "extrapolation".

You need the two data points left and right of the given weight for the specified age. You then assume that the graph is a straight line in between the two data points and compute the percentile coordinate from the weight coordinate.

E.g. for age 3 and weight 8.75 you find (25,8) and (50,9); since 8.75 is 3/4 the way from 8 to 9, the result should be 3/4 of the way from 25 to 50 which is 43.75

In the case of an outlier, you would need to extrapolate using the two closest entries (which both lie on the same side of the given weight) and pretend the graph is a straight line there too.

E.g. for age 3 and weight 11.25 you find (75,10) and (90,11); since 11.25 is 1/4 past 11, the result should be 1/4 the difference between 75 and 90 higher, yielding 93.75.

This would be "linear interpolation" and "linear extrapolation". Adding more data points improves the accuracy of the result. Or you can take advantage of the fact that you are dealing with statistics, which means that the age/weight distribution follows the classical "bell curve". The difference is that it makes the math of interpolation more difficult, but yields better results for a smaller number of input points (median and standard deviation suffice).

(23) By anonymous on 2021-05-10 10:32:28 in reply to 22 [link] [source]

This all sorted now and interpolation is not done linear as you can see from the posted formula: dZ = Logarithm(dWeight / dMedian, cE) / dGCV so this is using natural logarithm (hence cE) and that will give the typical upwards curving graph of these early (first 36 months) weight charts.

RBS