mysql - AVG 限制为每组最后 3 个值

Question

基本上我有两个表:

如果这可以帮助愿意帮助我的人，这里是创建两个表的代码:

CREATE TABLE IF NOT EXISTS `coefficients` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `datetime` datetime NOT NULL,
    `campaign_id` int(11) NOT NULL,  
    `score` decimal(10,2) NOT NULL,  
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

INSERT INTO `coefficients` (`id`, `datetime`, `campaign_id`, `score`) VALUES    
    (1, '2017-01-29 22:32:13', 1, 20.00),   
    (2, '2017-01-29 22:36:22', 1, 34.00),   
    (3, '2017-01-29 22:36:30', 1, 30.00),   
    (4, '2017-01-29 22:36:43', 1, 1000.00), 
    (5, '2017-01-29 22:37:13', 2, 10.00),   
    (6, '2017-01-29 22:37:26', 2, 15.00),   
    (7, '2017-01-29 22:37:43', 2, 20.00),   
    (8, '2017-01-29 22:30:51', 2, 1000.00);

CREATE TABLE IF NOT EXISTS `statistics` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT,  
    `campaign_id` int(11) NOT NULL,  
    `stats1` int(11) NOT NULL,  
    `stats2` int(11) NOT NULL,  
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `statistics` (`id`, `campaign_id`, `stats1`, `stats2`) VALUES   
    (1, 1, 34, 38),
    (2, 2, 23, 45);

我想根据每个 campaign_id 的最新 3 个记录系数计算每个 campaign_id 的平均系数。

这是我需要获取的两个表和结果的屏幕截图:

data + result (visual representation)

主要问题是，如果我只需要基于 3 个最新记录的 nu,bers 的每个 campaign_id 的平均系数，我不知道如何连接这两个表:(

我将不胜感激

Answer 1

以下查询将为您提供 coefficients 表中每个 campaign_id 的前 3 条记录:

SET @currcount = NULL, @currvalue = NULL;
SELECT id, campaign_id, score, c_index FROM (
  SELECT
  id, campaign_id, score, 
  @currcount := IF(@currvalue = campaign_id, @currcount + 1, 1) AS c_index,
  @currvalue := campaign_id AS s
  FROM coefficients
  order by id
) AS a where c_index <= 3

现在，您所要做的就是，向此查询添加一个GROUP BY，计算average 分数并将其与statistics 表连接，例如:

SET @currcount = NULL, @currvalue = NULL;
SELECT a.id, a.campaign_id, avg(score), c_index, s.stats1, s.stats2 FROM (
  SELECT
  id, campaign_id, score, 
  @currcount := IF(@currvalue = campaign_id, @currcount + 1, 1) AS c_index,
  @currvalue := campaign_id AS s
  FROM coefficients
  order by id
) AS a join statistics s on a.campaign_id = s.campaign_id
where c_index <= 3
group by campaign_id

这是 SQL Fiddle .