Calculate average per day based on the difference of the values-CodePudding

I have a table:

value	updated_at	ID
5	2022-1-1 12:00:00	1
10	2022-1-1 12:00:30	2
20	2022-1-1 12:02:30	3

What I want to do is to get an average based on the updated_at column difference, and the values of course.

So, I guess the formula should be:

(sumof((value2 - value1) * (date2 - date1))) / (dateLast - dateFirst) where 1 and 2 means for each two rows when we traverse from the first to the last item. eg for this table we'll have:

First and second row: (value2 - value1) * (date2 - date1) = (10 - 5) * (30 (seconds)) = 150

for second and third row: (20 - 10) * 120 = 1200

So the result is:

(1200 150) / (2022-1-1 12:02:30 - 2022-1-1 12:00:00) = 9

I probably can get this working with a self JOIN on ID and ID 1 and I also can do the diff of last and first date, but I can't do them both in the same query! I have no idea how to do that, is this even possible to be done in a single query?

Update

My MySql version is 5.6

CodePudding user response：

For MySql 8.0 you can use LAG() window function to get each row's previous values and then aggregate:

WITH cte AS (
  SELECT *,
         value - LAG(value) OVER (ORDER BY updated_at) dif_value,
         UNIX_TIMESTAMP(updated_at) - UNIX_TIMESTAMP(LAG(updated_at) OVER (ORDER BY updated_at)) dif_time
  FROM tablename
)
SELECT SUM(dif_value * dif_time) / 
       (UNIX_TIMESTAMP(MAX(updated_at)) - UNIX_TIMESTAMP(MIN(updated_at))) result 
FROM cte;

For previous versions and if there are no gaps between the ids, use a self join:

SELECT SUM(dif_value * dif_time) / 
       (UNIX_TIMESTAMP(MAX(updated_at)) - UNIX_TIMESTAMP(MIN(updated_at))) result 
FROM (
  SELECT t1.*,
         t1.value - t2.value dif_value,
         UNIX_TIMESTAMP(t1.updated_at) - UNIX_TIMESTAMP(t2.updated_at) dif_time
  FROM tablename t1 LEFT JOIN tablename t2
  ON t1.ID = t2.ID   1
) t;

See the demo.