rolling averages for a part of the table

[ajoo]

Hi all !

i have the following table in my DB

Quote

+----------+-------------------+-------------+-------------+
| RecNo  |     User           | V_Score  | Average   |
+----------+-------------------+-------------+-------------+
|     1       |     tina1234     |    NULL    |    NULL   |
|     2       |     dinesh11    |    NULL    |    NULL   |
|     3       |     mina1111    |       2       |  2.0000    |
|     4       |     nina12345  |       3       |  2.5000    |
|     5       |     rina1234     |       4       |  3.0000    |
|     6       |     mina1111    |       4       |  3.2500    |
|     7       |     mina1111    |       3       |  3.2000    |
|     8       |     mina1111    |       2       |  3.2000    |
|     9       |     mina1111    |       4       |  3.4000    |
|    10      |     mina1111    |       5       |  3.6000    |
|    11      |     mina1111    |       0       |  2.8000    |
|    13      |     mina1111    |       1       |  2.5000    |
|    14      |     mina1111    |       1       |  1.7500    |
|    15      |     mina1111    |       1       |  0.7500    |
+----------+-------------------+-------------+-------------+
 

The averages are the rolling averages for 5 records at a time. If I try and get the same for only the user mina1111 using the query

SELECT user.RecNo as RecNo, user.User as User, user.V_Score as V_Score, avg(user_past.V_Score) as Average 
FROM user  
JOIN user as user_past on user_past.RecNo between user.RecNo-4 AND user.RecNo WHERE user.User = 'mina1111'
GROUP by RecNo, V_Score;

I get the following :-

Quote

+----------+----------------+---------------+---------------+
| RecNo  | User            | V_Score    |     Average |
+----------+----------------+---------------+---------------+
|     3       | mina1111     |       2         |      2.0000   |
|     6       | mina1111     |       4         |      3.2500   |
|     7       | mina1111     |       3         |      3.2000   |
|     8       | mina1111     |       2         |      3.2000   |
|     9       | mina1111     |       4         |      3.4000   |
|    10      | mina1111     |       5         |      3.6000   |
|    11      | mina1111     |       0         |      2.8000   |
|    13      | mina1111     |       1         |      2.5000   |
|    14      | mina1111     |       1         |      1.7500   |
|    15      | mina1111     |       1         |      0.7500   |
+----------+-----------------+--------------+----------------+

The averages go all haywire. It uses the values in the original table to calculate these, hence the funny values. So how can I get the correct values like this was the table with original values.

Also how can I get the values of say only the last 5 values, considering those were the only values / rows in the table. i.e. the rolling avgs of 

 

Quote

+----------+----------------+---------------+---------------+
| RecNo  | User            | V_Score    |     Average |
+----------+----------------+---------------+---------------+
|    10      | mina1111     |       5         |     5.0000   |
|    11      | mina1111     |       0         |     2.5000   |
|    13      | mina1111     |       1         |     2.0000   |
|    14      | mina1111     |       1         |     1.7500   |
|    15      | mina1111     |       1         |     1.6000   |
+----------+-----------------+--------------+--------------+

Thanks all !

 

 

[Barand]

My solution requires just the records for mina1111 with consecutive record numbers (to facilitate finding the last 5 records) so I created a temporary table from your data. Also because I needed to reference the table a second time in a subquery (you can;t do this with temp tables) I had to create a second temporary table.

I'm afraid it isn't the most efficient query ever written as it uses a dependent subquery.

    --
    -- create temp table a
    --
create temporary table mina1111_a
select a.recno
     , a.v_score
     , @count := @count+1 as reccount
from ajoo a
     JOIN (select @count:=0) as init
where user = 'mina1111';

    --
    -- create temp table b
    --
create temporary table mina1111_b
select a.recno
     , a.v_score
     , @count := @count+1 as reccount
from ajoo a
     JOIN (select @count:=0) as init
where user = 'mina1111';

    --
    -- calculate rolling 5-record averages
    --
SELECT a.recno
     , j.user
     , a.v_score
     , a.reccount
     , ( SELECT AVG(b.v_score) as avscor 
         FROM 
             mina1111_b b
         WHERE reccount BETWEEN a.reccount-4 and a.reccount
       ) as av5
FROM mina1111_a a
JOIN ajoo j using (recno) 
WHERE a.reccount > 4;

    --
    -- remove the temporary tables
    --
drop temporary table mina1111_a;
drop temporary table mina1111_b;

results...

+-------+----------+---------+----------+--------+
| recno | user     | v_score | reccount | av5    |
+-------+----------+---------+----------+--------+
|     9 | mina1111 |       4 |        5 | 3.0000 |
|    10 | mina1111 |       5 |        6 | 3.6000 |
|    11 | mina1111 |       0 |        7 | 2.8000 |
|    12 | mina1111 |       1 |        8 | 2.4000 |
|    13 | mina1111 |       1 |        9 | 2.2000 |
+-------+----------+---------+----------+--------+

Temporary table...

+-------+---------+----------+
| recno | v_score | reccount |
+-------+---------+----------+
|     3 |       2 |        1 |
|     6 |       4 |        2 |
|     7 |       3 |        3 |
|     8 |       2 |        4 |
|     9 |       4 |        5 |
|    10 |       5 |        6 |
|    11 |       0 |        7 |
|    12 |       1 |        8 |
|    13 |       1 |        9 |
+-------+---------+----------+

 

[ajoo]

Hello Guru Barand !

While the result table is great it does not produce the values I want.

I wanted that this table be considered as the complete and only table for which the rolling averages needed to be found. So for  your result table the values that I was looking for in the av5 columns should be as below. (I have changed the av5 values that I am looking for)

 

+-------+----------+---------+----------+--------+
| recno | user     | v_score | reccount | av5    |
+-------+----------+---------+----------+--------+
|     9 | mina1111 |       4 |        5 | 4.0000 |
|    10 | mina1111 |       5 |        6 | 4.5000 |
|    11 | mina1111 |       0 |        7 | 3.0000 |
|    12 | mina1111 |       1 |        8 | 2.5000 |
|    13 | mina1111 |       1 |        9 | 2.2000 |
+-------+----------+---------+----------+--------+

 

The following query gives me the table I want but since it is a temp table, I am unable to open it again for creating the averages.

create temporary table mina1111_a
 Select * from (
	select a.RecNo
	, a.User
	, a.V_Score
	from ajoo as a
	where User = 'mina1111'
	ORDER BY RecNo DESC LIMIT 5)sub
 Order by RecNo ASC;

It gives on my table the following values:-

+-------+--------------+------------+
| RecNo | User         | V_Score    |
+-------+--------------+------------+
|    10 | mina1111     |          5 |
|    11 | mina1111     |          0 |
|    13 | mina1111     |          1 |
|    14 | mina1111     |          1 |
|    15 | mina1111     |          1 |
+-------+--------------+------------+

But I am unable to use the table to get the averages. It gives the can'r reopen table error.

Thanks !

 

 

 

 

[Barand]

I think you should recheck your calculations

+-------+----------+---------+----------+-------------------+-----------------+
| recno | user     | v_score | reccount | last5             |  av5 = last5/5  |   my results      your results
+-------+----------+---------+----------+-------------------+-----------------+
|     3 | mina1111 |       2 |        1 | 2                 |                 |
|     6 | mina1111 |       4 |        2 | 2 + 4             |                 |
|     7 | mina1111 |       3 |        3 | 2 + 4 + 3         |                 |
|     8 | mina1111 |       2 |        4 | 2 + 4 + 3 + 2     |                 |
|     9 | mina1111 |       4 |        5 | 2 + 4 + 3 + 2 + 4 | 15/5 = 3.0      |    3.0000            4.0000
|    10 | mina1111 |       5 |        6 | 4 + 3 + 2 + 4 + 5 | 18/5 = 3.6      |    3.6000            4.5000
|    11 | mina1111 |       0 |        7 | 3 + 2 + 4 + 5 + 0 | 14/5 = 2.8      |    2.8000            3.0000
|    12 | mina1111 |       1 |        8 | 2 + 4 + 5 + 0 + 1 | 12/5 = 2.4      |    2.4000            2.5000
|    13 | mina1111 |       1 |        9 | 4 + 5 + 0 + 1 + 1 | 11/5 = 2.2      |    2.2000            2.2000
+-------+----------+---------+----------+-------------------+-----------------+

 

[ajoo]

Hi Guru Barand !

Sir, You are correct but so am I because I said I want the derived table to be considered as the complete and only table itself thereby ignoring all the rows / values before the last 5 rows. 

In which case this below is the only table. All other values are discarded and averages are calculated by taking RecNo 9 as the first record and so on. In that case  "your result" values would fit.

+-------+----------+---------+----------+-------------------+-----------------+
| recno | user     | v_score | reccount | last5             |  av5 = last5/5  |   my results      your results
+-------+----------+---------+----------+-------------------+-----------------+
|     9 | mina1111 |       4 |        5 | 2 + 4 + 3 + 2 + 4 | 15/5 = 3.0      |    3.0000            4.0000
|    10 | mina1111 |       5 |        6 | 4 + 3 + 2 + 4 + 5 | 18/5 = 3.6      |    3.6000            4.5000
|    11 | mina1111 |       0 |        7 | 3 + 2 + 4 + 5 + 0 | 14/5 = 2.8      |    2.8000            3.0000
|    12 | mina1111 |       1 |        8 | 2 + 4 + 5 + 0 + 1 | 12/5 = 2.4      |    2.4000            2.5000
|    13 | mina1111 |       1 |        9 | 4 + 5 + 0 + 1 + 1 | 11/5 = 2.2      |    2.2000            2.2000
+-------+----------+---------+----------+-------------------+-----------------+
 

Thank you sir !

Edited August 24, 2019 by ajoo

[Barand]

In your results, the only one that is a correct rolling average of the last 5 records is the last one.

In my results, they all are.

But if that is what you want, carry on (and note that, as a rule, you should not store derived data in a database)

[ajoo]

Hi Guru Barand, 

Sir then how do I go about getting the average values of "your results" ? Using just the 5 rows.

Thank you !

[Barand]

To get a five record rolling average you need to calculate it from the current record and the four records immediately prior to the current one.

[ajoo]

Sir I understand that but I am unable to get those averages from the table that I created. As mentioned in #3, even though I get the 5 rows, I keep getting the Can't reopen error when I try and use it for getting the averages. 

For your convenience here it is again.

create temporary table mina1111_a
 Select * from (
	select a.RecNo
	, a.User
	, a.V_Score
	from ajoo as a
	where User = 'mina1111'
	ORDER BY RecNo DESC LIMIT 5)sub
 Order by RecNo ASC;

which gives the table  

+-------+--------------+------------+
| RecNo | User         | V_Score    |
+-------+--------------+------------+
|    10 | mina1111     |          5 |
|    11 | mina1111     |          0 |
|    13 | mina1111     |          1 |
|    14 | mina1111     |          1 |
|    15 | mina1111     |          1 |
+-------+--------------+------------+

However when i use this table to calculate the averages, it gives an error and I think that's because I cannot use the temp tables like this. It says cannot reopen the table.

Kindly help resolve. 

Thanks 

  

Edited August 24, 2019 by ajoo

[Barand]

15 hours ago, Barand said:

Also because I needed to reference the table a second time in a subquery (you can;t do this with temp tables) I had to create a second temporary table.

 

[ajoo]

yes Guru Barand !

I have been trying without success. I'll try some more. 

Thanks !

 

[ajoo]

Hi Guru Barand, 

I am able to get the results I want  by modifying your query slightly as below: ( Last line  "AND RecNo > 9 " is only added).

create temporary table mina1111_a
select a.recno
     , a.v_score
     , @count := @count+1 as reccount
from ajoo a
     JOIN (select @count:=0) as init
where user = 'mina1111 
AND RecNo > 9';

same for table mini1111_b. 

and i get the result i want. The only problem is that i can use > 9 here because i get 5 rows with that. How can I generalize this to get the last 5 rows each time no matter how many rows there may be in the table ajoo ?

Thanks loads !

 

 

 

[Barand]

You could do it the same way you did it 4 hours ago

 

[ajoo]

Hi Guru Barand !

Sir this finally achieves it. 

create temporary table mina1111_a
 Select * from (
	select a.RecNo
	, a.User
	, a.V_Score
	, @count := @count-1 as reccount
	from ajoo as a
	JOIN (select @count:=6) as init
	where User = 'mina1111'
	ORDER BY RecNo DESC LIMIT 5)sub
 Order by RecNo ASC;

create temporary table mina1111_b
Select * from (
	select a.RecNo
	, a.User
	, a.V_Score
	, @count := @count-1 as reccount
	from ajoo as a
	JOIN (select @count:=6) as init
	where User = 'mina1111'
	ORDER BY RecNo DESC LIMIT 5)sub
 Order by RecNo ASC;

SELECT a.RecNo
     , a.V_Score
     , ( SELECT AVG(b.V_Score) as avscor 
         FROM 
             mina1111_b b
         WHERE reccount BETWEEN a.reccount-4 and a.reccount
       ) as av5
FROM mina1111_a a
JOIN ajoo j using (RecNo); 
WHERE a.reccount > 4;

finally gives

+-------+------------+--------+
| RecNo | Wrt_V_Sums | av5    |
+-------+------------+--------+
|    10 |          5 | 5.0000 |
|    11 |          0 | 2.5000 |
|    13 |          1 | 2.0000 |
|    14 |          1 | 1.7500 |
|    15 |          1 | 1.6000 |
+-------+------------+--------+

Thank you !!🙏 

P.S. I thought if there was a way of generalizing the "RecNo > N " so as to target the last 5-rows or last x-rows ( in general ) , the querys to create the temp tables would have been so much easier. 

 

Edited August 24, 2019 by ajoo

[ajoo]

 

 

Hello all !

I have been trying to ADD another column to the final result of this query which is the TOTAL of the SCORE OF THE 3 most current rows like this:

SELECT a.RecNo
     , a.V_Score
     , ( SELECT AVG(b.V_Score) as avscor 
         FROM 
             mina1111_b b
         WHERE reccount BETWEEN a.reccount-4 and a.reccount
       ) as av5
    , (SELECT SUM(b.V_Score) as sumscore 
         FROM 
             mina1111_b b
         WHERE reccount BETWEEN a.reccount-2 and a.reccount
       ) as summed3
FROM mina1111_a a
JOIN ajoo j using (RecNo); 
WHERE a.reccount > 4;

 

But it does not seem to be working and gives the old familiar "can't reopen table b " error.  Is there any simple way to achieve this ?

The following is the output that I am trying to get.

+-------+------------+--------+--------+
| RecNo | Wrt_V_Sums | av5    | summed3|
+-------+------------+--------+--------+
|    10 |          5 | 5.0000 |    5   |
|    11 |          0 | 2.5000 |    5   |
|    13 |          1 | 2.0000 |    6   |
|    14 |          1 | 1.7500 |    2   |
|    15 |          1 | 1.6000 |    3   |
+-------+------------+--------+--------+

All help appreciated.

Thanks !  

 

Edited June 18, 2021 by ajoo
displaced code

[Barand]

https://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html

[ajoo]

Thank you Guru Barand for the response.

I understand that there are issues with the temporary tables as also mentioned by you in this very thread earlier. So if i must use them then creating a 3rd temporary table would be the only option. I also recall your mentioning that this is not the most efficient query, which would make it even more so if I created a 3rd temporary table. Therefore I ask what is the solution to this, if we are not to use temporary tables? What should be done ?

Thanks loads again !   

[ajoo]

The following with yet another temporary table achieves it but temporary tables is what I wish to avoid. 

create temporary table mina1111_c
Select * from (
	select a.RecNo
	, a.User
	, a.V_Score
	, @count := @count-1 as reccount
	from ajoo as a
	JOIN (select @count:=6) as init
	where User = 'mina1111'
	ORDER BY RecNo DESC LIMIT 5)sub
 Order by RecNo ASC;
 
SELECT a.RecNo
     , a.V_Score
     , ( SELECT AVG(b.V_Score) as avscor 
         FROM 
             mina1111_b b
         WHERE reccount BETWEEN a.reccount-4 and a.reccount
       ) as av5
     , ( SELECT SUM(c.V_Score) as SUM3 
         FROM 
             mina1111_c c
         WHERE reccount BETWEEN a.reccount-2 and a.reccount
       ) as SUMMED3
FROM mina1111_a a
JOIN ajoo j using (RecNo); 
WHERE a.reccount > 4;

Is there a better, more efficient way to achieve this or is this the only way to go? 

Thanks loads !

[Barand]

Here's my attempt

DATA

mysql> select * from ajoo
    -> order by user, recno;
+-------+----------+---------+---------+
| recno | user     | v_score | rollavg |
+-------+----------+---------+---------+
|     6 | mina1111 |       4 |  3.2500 |
|     7 | mina1111 |       3 |  3.2000 |
|     8 | mina1111 |       2 |  3.2000 |
|     9 | mina1111 |       4 |  3.4000 |
|    10 | mina1111 |       5 |  3.6000 |
|    11 | mina1111 |       0 |  2.8000 |
|    12 | mina1111 |       1 |  2.5000 |
|    13 | mina1111 |       1 |  1.7500 |
|    14 | mina1111 |       1 |  0.7500 |
|     1 | nina1234 |       3 |    NULL |
|     4 | nina1234 |       3 |  2.5000 |
|     5 | nina1234 |       4 |  3.0000 |
|    15 | nina1234 |       5 |    NULL |
|    17 | nina1234 |       2 |  2.0000 |
|    22 | nina1234 |       2 |    NULL |
+-------+----------+---------+---------+

QUERIES

    --
    -- create temp table a
    --
CREATE TEMPORARY TABLE temp_a
SELECT a.recno
     , a.v_score
     , @count := CASE WHEN user = @prevu
                      THEN @count+1 
                      ELSE 1
                      END AS reccount
     , @prevu := user AS user
FROM ajoo a
     JOIN (SELECT @count:=0, @prevu:=NULL) AS init
ORDER BY user, recno
;

    --
    -- create temp table b 
    -- (copy of temp_a)
    -- 
CREATE TEMPORARY TABLE temp_b
SELECT * FROM temp_a
;

    -- 
    -- get results
    -- 
SELECT
       av.user 
     , avg5
     , tot3
FROM 
    (
    SELECT user 
         , AVG(v_score) as avg5
    FROM (
            SELECT a.user 
                 , v_score
            FROM temp_a a
                 JOIN (
                        SELECT user 
                             , COUNT(*) AS maxrec
                         FROM ajoo
                         GROUP BY user 
                      ) max ON a.user = max.user
                            AND a.reccount > max.maxrec - 5
          ) tots
    GROUP BY user
    ) av
JOIN
    (
    SELECT user 
         , SUM(v_score) as tot3
    FROM (
            SELECT b.user 
                 , v_score
            FROM temp_b b
                 JOIN (
                        SELECT user 
                             , COUNT(*) AS maxrec
                         FROM ajoo
                         GROUP BY user 
                      ) max ON b.user = max.user
                            AND b.reccount > max.maxrec - 3
          ) tots
    GROUP BY user
    ) tot
    USING (user)
;

RESULTS

+----------+--------+------+
| user     | avg5   | tot3 |
+----------+--------+------+
| mina1111 | 1.6000 |    3 |
| nina1234 | 3.2000 |    9 |
+----------+--------+------+

 

[ajoo]

Guru Barand Magic !

Took me quite some time to figure out what you did. Your attempt is far more efficient as well as compared to the 3 temp tables solution. .0089 seconds vs 0.013 seconds. 

Thanks loads. 🙏