CODEWITHSUNDEEP
sqlpostgresqlgroup-bysql-order-bylag
The Great
The Great

Reputation: 7723

Handle missing values while multiply the lagged values of each group using postgresql

I have my data like as shown below

id  person_id Measurement   Value   UNIT
1     A1         Height      1.75      m
2     A1         Weight       64     kg
3     A2         Weight       71     kg
4     A2         Height      1.81     m 
5     A3         Height      1.89     m
6     A4         Weight      75      kg
7     A4         Height      1.76     m

I would like to calculate the BMI for each subject using the formula BMI = (Weight) / (height * height)

I was trying the below to get the values in one row so I can apply the formula but was encountering an error

SELECT *
    LAG(value) OVER(PARTITION BY person_id ORDER BY id) val_prev1,
    LAG(value, 2) OVER(PARTITION BY person_id ORDER BY id) val_prev2
  FROM 
(select * from table1)A

In the above code, am not sure how to factor in for missing records. For ex, person_id = A3 doesn't have weight measurement and we need to put in NA and leave the BMI as NA as well

I expect my output to be like as shown below

Person_id  Measurement Value unit
   A1       BMI         20.8
   A2       BMI         XXX    #Multiplied value goes here
   A3       BMI         XXX  
   A4       BMI         XXX

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269983

Use conditional aggregation:

select person_id,
       (power(max(value) filter (where measurement = 'weight'), 2) /
        max(value) filter (where measurement = 'height')
       ) as bmi
from t
group by person_id;

You can also use the infix operator ^ to calculate the square.

In your example, the units are all consistent for the heights and weights. If this is not the case in your actual data, ask a new question with an explanation of how to convert the values to the appropriate units.

Upvotes: 1

Related Questions