CODEWITHSUNDEEP
mysqlsql
user3828311
user3828311

Reputation: 967

mysql limiting the number of rows in a comparison based on a condition

I have a table "my_table" with 2 columns 

 1. Timestamp (ts)
 2. Value (val)

I need to return for each timestamp, the average of 10 latest values that occur on or before the timestamp.

I am able to do something similar with the following query:

SELECT t2.ts as time, 
       AVG(t1.val) as avg_val 
FROM table_name as t1, table_name as t2 
WHERE t1.ts <= t2.ts

This returns a timestamp and the average of all the values that occur on or before that timestamp. I need to modify this query to return the average of the 10 latest values and not all the values.

PS: Apologies for a bad question title. Was not sure how to frame it.

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

There is not an easy way to express this query in MySQL prior to version 8 -- even using variables. I'm not going to vouch for performance, but this should do what you want:

SELECT t1.ts, t1.val, AVG(t2.val)
FROM t t1 LEFT JOIN
     t t2
     ON t2.ts <= t.ts AND
        t2.ts >= (SELECT t3.ts
                  FROM t t3
                  WHERE t3.ts <= t1.ts
                  ORDER BY t3.ts DESC
                  LIMIT 9, 1
                 )
GROUP BY t1.ts, t1.val;

Of course, in 8+, this is simply:

select t.*,
       avg(t.val) over (order by ts rows between 9 preceding and current row) as avg_10
from t;

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

Ignore previous answer. You can use "poor man's rank" to rank the rows sorted by timestamp. You can join the result by itself for find previous 9 rows for each rank:

SELECT a.ts, AVG(c.val)
FROM (
    SELECT t1.ts, COUNT(t2.ts) AS rank
    FROM t AS t1
    INNER JOIN t AS t2 ON t2.ts <= t1.ts
    GROUP BY t1.ts
) AS a
INNER JOIN (
    SELECT t1.ts, COUNT(t2.ts) AS rank
    FROM t AS t1
    INNER JOIN t AS t2 ON t2.ts <= t1.ts
    GROUP BY t1.ts
) AS b ON b.rank BETWEEN a.rank - 9 AND a.rank
INNER JOIN t AS c ON b.ts = c.ts
GROUP BY a.ts

I understand it is a bit clunky but it could be better than correlated sub queries under certain conditions.

Upvotes: 2

Related Questions