CODEWITHSUNDEEP
mysqlsqlpostgresql
Gigi Contra
Gigi Contra

Reputation: 95

SQL selecting average score over range of dates

I have 3 tables:

doctors (id, name) -> has_many:
    patients (id, doctor_id, name) -> has_many:
        health_conditions (id, patient_id, note, created_at)

Every day each patient gets added a health condition with a note from 1 to 10 where 10 is a good health (full recovery if you may).

What I want to extract is the following 3 statistics for the last 30 days (month): - how many patients got better - how many patients got worst - how many patients remained the same

These statistics are global so I don't care right now of statistics per doctor which I could extract given the right query.

The trick is that the query needs to extract the current health_condition note and compare with the average of past days (this month without today) so one needs to extract today's note and an average of the other days excluding this one.

I don't think the query needs to define who went up/down/same since I can loop and decide that. Just today vs. rest of the month will be sufficient I guess.

Here's what I have so far which obv. doesn't work because it only returns one result due to the limit applied:

SELECT
    p.id,
    p.name,
    hc.latest,
    hcc.average
FROM
    pacients p
INNER JOIN (
        SELECT
            id,
            pacient_id,
            note as LATEST
        FROM
            health_conditions
        GROUP BY pacient_id, id
        ORDER BY created_at DESC
        LIMIT 1
    ) hc ON(hc.pacient_id=p.id)
INNER JOIN (
        SELECT
            id,
            pacient_id,
            avg(note) AS average
        FROM
            health_conditions
        GROUP BY pacient_id, id
    ) hcc ON(hcc.pacient_id=p.id AND hcc.id!=hc.id)
WHERE
    date_part('epoch',date_trunc('day', hcc.created_at))
    BETWEEN
        (date_part('epoch',date_trunc('day', hc.created_at)) - (30 * 86400))
    AND
        date_part('epoch',date_trunc('day', hc.created_at))

The query has all the logic it needs to distinguish between what is latest and average but that limit kills everything. I need that limit to extract the latest result which is used to compare with past results.

Upvotes: 1

Views: 1760

Answers (2)

user330315
user330315

Reputation:

Something like this assuming created_at is of type date 

select p.name,
       hc.note as current_note,
       av.avg_note
from patients p
   join health_conditions hc on hc.patient_id = p.id
   join (
      select patient_id, 
             avg(note) as avg_note
      from health_conditions hc2
      where created_at between current_date - 30 and current_date - 1
      group by patient_id
    ) avg on t.patient_id = hc.patient_id
where hc.created_at = current_date;

This is PostgreSQL syntax. I'm not sure if MySQL supports date arithmetics the same way.

Edit:

This should get you the most recent note for each patient, plus the average for the last 30 days:

select p.name,
       hc.created_at as last_note_date
       hc.note as current_note,
       t.avg_note
from patients p
   join health_conditions hc 
     on hc.patient_id = p.id
    and hc.created_at = (select max(created_at) 
                         from health_conditions hc2 
                         where hc2.patient_id = hc.patient_id)
   join (
      select patient_id, 
             avg(note) as avg_note
      from health_conditions hc3
      where created_at between current_date - 30 and current_date - 1
      group by patient_id
    ) t on t.patient_id = hc.patient_id

Upvotes: 1

eggyal
eggyal

Reputation: 125925

SELECT SUM(delta < 0) AS worsened,
       SUM(delta = 0) AS no_change,
       SUM(delta > 0) AS improved
FROM  (
  SELECT   patient_id,
           SUM(IF(DATE(created_at) = CURDATE(),note,NULL))
         - AVG(IF(DATE(created_at) < CURDATE(),note,NULL)) AS delta
  FROM     health_conditions
  WHERE    DATE(created_at) BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE()
  GROUP BY patient_id
) t

Upvotes: 1

Related Questions