mysql 按时间排序 取每5条数据的平均值、评价数

建表语句：

CREATE TABLE `trade_params1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PERIOD` int(11) DEFAULT NULL,
  `THEDATE` date DEFAULT NULL,
  `DAYCOUNT` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (1,20,'2018-01-02',7);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (2,20,'2018-01-03',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (3,20,'2018-01-04',5);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (4,20,'2018-01-05',7);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (5,20,'2018-01-08',7);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (6,20,'2018-01-09',7);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (7,20,'2018-01-10',7);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (8,20,'2018-01-11',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (9,20,'2018-01-12',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (10,20,'2018-01-15',5);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (11,20,'2018-01-16',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (12,20,'2018-01-17',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (13,20,'2018-01-18',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (14,20,'2018-01-19',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (15,20,'2018-01-22',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (16,20,'2018-01-23',6);
INSERT INTO `trade_params1` (`ID`,`PERIOD`,`THEDATE`,`DAYCOUNT`) VALUES (17,20,'2018-01-24',6);

 

执行sql 先按时间排序 然后查询出每行的行号 按行号分组

SELECT avg(tt.daycount) FROM (
SELECT t.daycount,(@rownum := @rownum + 1) - 1 AS rownum FROM (SELECT @rownum := 0) r,trade_params1 t 
    WHERE t.THEDATE < '2019-01-18' ORDER BY  THEDATE desc
) tt GROUP BY tt.rownum - tt.rownum % 5