how to divide by subset of table by itself, i.e. normalisation (t!=0 rows by t=0 rows)-CodePudding

I'm essentially looking for a way to normalise (t/t0) a bunch of measurements across timepoints with a specific timepoint, e.g. timepoint=0.

I have a table as following:

coordinate,timepoint,quantity
A1,0,50
B2,0,10
C3,0,60
A2,0,20
F1,0,20
A1,1,100
B2,1,150
C3,1,120
A2,1,140
F1,1,160
A1,4,100
B2,4,80
C3,4,80
A2,4,100
F1,4,120

I want to make a table that divides all the other non-zero timepoint rows by the 0 timepoint rows where the coordinates match, i.e. A1-t1 / A1-t0, A1-t4 / A1-t0, B2-t1 / B2-t0, B2-t0 / B2-t4 etc. etc. for wherever there is a join on coordinate available.

The result would be like:

coordinate,timepoint,quantity
A1,0,1
B2,0,1
C3,0,1
A2,0,1
F1,0,1
A1,1,2
B2,1,15
C3,1,2
A2,1,7
F1,1,8
etc.

Something like this mostly works...

select t0.coordinate,t0.quantity,tother.quantity,tother.quantity/t0.quantity as tnorm
(select * from table
where timepoint != 0) as tother
LEFT JOIN (select * from table
where timepoint = 0) as t0
ON (t1.coordinate = t2.coordinate);

Though I ideally would like to have a pivot of the table could be displayed where each column is each normalisation, e.g. columns as

coordinate, t0/t0, t1/t0, t4/t0 etc.
A1,1,2,value etc.
B2,1,15,value etc.
C3,1,2,value etc.
A2,1,7,value etc.
F1,1,8,value etc.

...though this might not be possible and must be done in postprocessing (e.g. pandas pivot).

I couldn't work out the right syntax for this one - any help is appreciated.

CodePudding user response：

WITH t1 AS (
    SELECT position, quantity
    FROM table
    WHERE timepoint = 0
)
SELECT t2.position, t2.timepoint, (t2.quantity/t1.quantity) quantity
FROM table t2
INNER JOIN t1 ON t2.position=t1.position