I have such table in my MySQL database:
---------------------------
|fid | price | date |
---------------------------
| 1 | 1.23 | 2011-08-11 |
| 1 | 1.43 | 2011-08-12 |
| 1 | 1.54 | 2011-08-13 |
| 1 | 1.29 | 2011-08-14 |
| 1 | 1.60 | 2011-08-15 |
| 1 | 1.80 | 2011-08-16 |
fid
- this is the product idprice
- this is the price of the product in specified day
I want to calculate average price of the product fid=1
. I want to calculate the average price of first n=3
rows ordered by date for specified fid
, and then calculate average price for another 3 rows ordered by date.
How can I group first 3 rows and calculate avg and then group next 3 rows and calculate avg. Before calculation I need to sort the rows by date and then group n
rows.
If n=3
this should return such result:
--------------
|fid | price |
--------------
| 1 | 1.40 | 2011-08-11 -> 2011-08-13 - average price for 3 days
| 1 | 1.56 | 2011-08-14 -> 2011-08-16 - average price for 3 days
How can I create SQL Query to do such calculations?
Thanks in advance.
Unluckily mysql doesn't offer analytic functions like oracle,mssql and postgres do. So you have to play with variables to reach your goal.
create table mytest (
id int not null auto_increment primary key,
fid int,
price decimal(4,2),
fdate date
) engine = myisam;
insert into mytest (fid,price,fdate)
values
(1,1.23,'2011-08-11'),
(1,1.43,'2011-08-12'),
(1,1.54,'2011-08-13'),
(1,1.29,'2011-08-14'),
(1,1.60,'2011-08-15'),
(1,1.80,'2011-08-16');
select
concat_ws('/',min(fdate),max(fdate)) as rng,
format(avg(price),2) as average from (
select *,@riga:=@riga+1 as riga
from mytest,(select @riga:=0) as r order by fdate
) as t
group by ceil(riga/3);
+-----------------------+---------+
| rng | average |
+-----------------------+---------+
| 2011-08-11/2011-08-13 | 1.40 |
| 2011-08-14/2011-08-16 | 1.56 |
+-----------------------+---------+
2 rows in set (0.02 sec)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With