 |
|
 |
| |
Developer on focus
Anton Zamov is a dipl. engineer with more than 6 years of active professional
experience and many awards ...
read more>>
|
|
 |
 |
 |
|
|
|
|
| |
|
MySQL: Computing running and sliding aggregates
|
Computing running and sliding aggregates. Assume the following table
and data:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);
insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
The following gives a running sum:
mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | sum(b.roll) |
+------+-------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
| 4 | 10 |
| 5 | 15 |
| 6 | 21 |
+------+-------------+
6 rows in set (0.00 sec)
Note, it's also possible to get a running average by changing the
sum to avg as follows:
mysql> select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | avg(b.roll) |
+------+-------------+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |
| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+------+-------------+
6 rows in set (0.00 sec)
|
About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (c) 2004 (GPU Free Documentation License)
Last Updated: Tue Jul 20 12:14:51 EDT 2004
|
|
|
|
|
|
|
|
