Suppose I have a table named Data:

And I want to find the first 'id' index (nFirst) where the average of the 'value' column is above some pre-given value ('avg'). In other words I want to solve for nFirst. I have this query

SELECT AVG(value)
FROM Data
WHERE id < nFirst AND AVG(value) > avg
GROUP BY id;

The above query has the general idea of what I'd like to calculate but it obviously won't work. Any ideas?

CodePudding user response：

For MySql 8.0 use AVG() window function to get the running average for each row and filter out the rows with less than the average that you want and finally pick the smallest id of the results:

SELECT *
FROM (
  SELECT id, AVG(value) OVER (ORDER BY id) avg_value  
  FROM Data
) d
WHERE avg_value > ?
ORDER BY id LIMIT 1;

For previous versions use a self join and aggregation:

SELECT d1.id, AVG(d2.value) avg_value
FROM Data d1 INNER JOIN Data d2
ON d2.id <= d1.id
GROUP BY d1.id
HAVING avg_value > ?
ORDER BY d1.id LIMIT 1;

Replace ? with the average value that you want.

CodePudding user response：

If you have DB version 8.0 , then use SUM() OVER () window function, presuming all id values are consecutively incrementing as in the shared example data set, in such a way

SELECT MIN(id) AS least_id
  FROM
  (SELECT id, SUM(value) OVER (ORDER BY id)/id AS avg_
     FROM Data) AS d
 WHERE avg_ > avg --> substitute 5 as an example value for the parameter "avg"

Demo