Mysql – Single MySQL query with row averages based on conditions

MySQLsubquery

I have problem, where i know how to proceed with multiple queries, but dont really now

I Have a table with

  BP {
  name: varchar
  systolic : int
  diastolic : int
  timestamp: date
  }

I need to get the Names from BP table that satisfy the following ANY of the following conditions.

  1. If systolic > 180 OR diastolic > 110
  2. If (systolic >= 140 AND systolic < 180), then take the next 2 readings of systolic, get the average and if that average is >= 140. Then Condition Satisfied.
  3. Same diastolic. If (diastolic >= 90 AND systolic < 110), then take the next 2 readings of diastolic, get the average and if that average is >= 90. Then Condition Satisfied.

There will be more many rows with the same unique name. (meaning many BP Recordings per person).

I can certainly get this working if i jus get all the values and parse it myself in PHP.

But Im wondering if theres a better way to do it directly in MySQL.

My goal here was to find the people that have Hypertension. And those conditions above the guidelines in categorising a person as Hypertensive.

In this example, I simplified the table. And I want to get the name(s) of those that meet these hypertensive guidelines.

Condition 1 is very simple to address. Condition 2 & 3 are whats troubling me.

What I do now

I just get all those values using SELECT and ORDER BY timestamp ASC, name. And parse everything in PHP
I simply get the list of people with systolic >=140 AND systolic < 180, Filter them out based on name, order it by timestamps and enumerate through each set, if I find a systolic >= 140 AND systolic < 180, i break enumeration and check the next 2 readings of that name and take the average.
I repeat that with diastolic. and Merge the results with all the three conditions, removing any duplicate entries.

I really want to learn if there was a good way to do this in sql itself. Im assuming using a Stored Procedure would be a must. But I'd be glad to know if there was a better way using some kind of subquerying.

Best Answer

Keep the raw data in MySQL. But plan on coding the logic in PHP (or whatever). You have only begun to touch on how complex the math can get. What about the blood pressure readings, plus whether the patient is/was taking a BP lowering drug?

Related Question