Counting the average of intervals in mySQL-CodePudding

Say, I have a table that I derived in mySQL:

doctor	patient_of_doctor	patient_bought_item_from_doctor_x_days_ago
Aaron	Jeff	10
Aaron	Jeff	20
Jess	Jason	50
Jess	Jason	20
Jess	Jason	30
Aaron	Stu	90
Aaron	Stu	70
Aaron	Stu	110
Aaron	Stu	105

Now, I would want to make a new table from this one such that for each doctor, the patient's average buy interval is shown.

For this table,

Jeff bought from Aaron 10 and 20 days ago. Jeff's average buy interval is therefore, (20-10)/1 = 10.

Jason bought from Jess 20, 50 and 30 days ago. Jason's average buy interval is ((50-30) (30-20))/2 = 15.

Stu bought from Aaron 90, 70, 110 and 105 days ago. Stu's average buy interval is ((110-105) (105-90) (90-70))/3 = (5 15 20)/3 =40/3 = 13.33

I would want to output a table that looks like this:

doctor	patient_of_doctor	avg_buy_interval
Aaron	Jeff	10
Jess	Jason	15
Aaron	Stu	13.33

I am seriously considering using python to do this but I could not pass up the chance to learn some mySQL from you guys.

Thanks! Umesh

CodePudding user response：

If you're using MySQL v8 (you can run SELECT version(); to check), you may try using either LEAD() or LAG() functions to get the previous or next x_days_ago. Then you can use AVG() on the subtraction result between the current row data with its previous or next x_days_ago.

With LEAD():

SELECT doctor,
       patient,
       AVG(bxd-prev_data)
 FROM
(SELECT doctor,
       patient,
       bought_x_days_ago AS bxd,
       LEAD(bought_x_days_ago) /*using LEAD*/
             OVER (PARTITION BY doctor, patient 
                    ORDER BY bought_x_days_ago DESC) AS prev_data
  FROM                        /*with ORDER BY in descending*/
    mytable) v
 WHERE prev_data IS NOT NULL
 GROUP BY doctor,
          patient;

With LAG():

SELECT doctor,
       patient,
       AVG(bxd-prev_data)
 FROM
(SELECT doctor,
       patient,
       bought_x_days_ago AS bxd,
       LAG(bought_x_days_ago) /*using LAG*/
             OVER (PARTITION BY doctor, patient 
                    ORDER BY bought_x_days_ago) AS prev_data
  FROM                     /*with ORDER BY in ascending (default)*/
    mytable) v
 WHERE prev_data IS NOT NULL
 GROUP BY doctor,
          patient

Demo fiddle

CodePudding user response：

To do That You should use the group by clause in sql which will be somethin like this:

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

which in your case would be this EXACT SQL Code:

SELECT  doctor,
        patient_of_doctor,
        AVG(bought_item) as 'avg_buy_interval'
FROM Patients
GROUP BY doctor, patient_of_doctor

and you will have thi result:

The result of query above