MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (
  value INT,
  date_column DATE
);
INSERT INTO test VALUES
  (1, '2019-03-13'),
  (2, '2019-03-12'),
  (3, '2019-03-16'),
  (4, '2019-03-16'),
  (5, '2019-03-16'),
  (6, '2019-03-16');

Records: 6  Duplicates: 0  Warnings: 0


SELECT AVG(value) 
FROM (
   SELECT value, CEIL((@row_number:=@row_number + 1) / 3) AS group_num
   FROM test, (SELECT @row_number:=0) rownumber
) t1
GROUP BY t1.group_num;


AVG(value)


2.0000

5.0000


SELECT AVG(value) 
FROM (
    SELECT value, CEIL(ROW_NUMBER() OVER () / 3) AS group_num
    FROM test
) t1
GROUP BY t1.group_num;


AVG(value)


2.0000

5.0000