Jump to content
ajoo

rolling averages for a part of the table

Recommended Posts

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 !

 

 

Share this post


Link to post
Share on other sites

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 |
+-------+---------+----------+

 

  • Great Answer 1

Share this post


Link to post
Share on other sites

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 !

 

 

 

 

Share this post


Link to post
Share on other sites

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
+-------+----------+---------+----------+-------------------+-----------------+

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ajoo

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Hi Guru Barand, 

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

Thank you !

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ajoo

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

yes Guru Barand !

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

Thanks !

 

Share this post


Link to post
Share on other sites

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 !

 

 

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by ajoo

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.