Im trying to write query to select data from table and group it by date and calculate average. It gets a bit complicated because i dont want to calculate average for all the data for certain date but only the 3 entries in descending order and also skip the first one.

I manage to write query for single date

select datum,datum_aladin,veter from napovedt where datum='2023-01-30' and spot='19' order by datum_aladin desc limit 1,3

which gives me returns:

datum     |datum_aladin|veter|
---------- ------------ ----- 
2023-01-30|  2023013000|  4.0|
2023-01-30|  2023012918|  4.0|
2023-01-30|  2023012912|  4.5|

Now how do i get average for "veter" and write query to get this average value for all the dates in database ?

CodePudding user response：

With MySQL 8 you might try:

Schema (MySQL v8.0)

CREATE TABLE napovedt
    (`datum` datetime, `datum_aladin` int, `veter` decimal(8,3))
;
    
INSERT INTO napovedt
    (`datum`, `datum_aladin`, `veter`)
VALUES
    ('2023-01-30 00:00:00', 2023013100, 2.0),
    ('2023-01-30 00:00:00', 2023013000, 4.0),
    ('2023-01-30 00:00:00', 2023012918, 4.0),
    ('2023-01-30 00:00:00', 2023012912, 4.5),
    ('2023-01-30 00:00:00', 1023013000, 3.0),
    
    ('2023-01-31 00:00:00', 2023013100, 5.0),
    ('2023-01-31 00:00:00', 2023013000, 4.0),
    ('2023-01-31 00:00:00', 2023012918, 2.0),
    ('2023-01-31 00:00:00', 2023012912, 1.5),
    ('2023-01-31 00:00:00', 1023013000, 3.0)
;

Query #1

with sq as (
  select n.datum, n.veter, row_number () over (
    partition by datum
        order by datum_aladin desc
    ) rn
  from napovedt n
)


select sq.datum, avg(sq.veter) from sq
where sq.rn between 2 and 4
group by sq.datum
order by sq.datum;

View on DB Fiddle

If you do not have MySQL 8, you can then emulate PARTITION OVER with the following:

select sq.datum, avg(sq.veter) from (
  select *, if(@prev <> datum, @rn:=0, @rn), @prev:=datum, @rn:=@rn 1 as rn
  from napovedt, (select @rn:=0) as rn, (select @prev:='') as prev
  order BY datum, datum_aladin desc
) sq 
where sq.rn between 2 and 4
group by sq.datum
order by sq.datum;

View on DB Fiddle