子查询中的 MySQL 未知列

Question

我试图在 MySQL 中为只有两列 id 和 ideas_count (int) 的表 contests 实现“滚动平均值” :

对于 contests 中的每一行，返回 id 以及前 10 行(包括当前行)的 ideas_count 的平均值。

这是我卡住的地方:

select 
  id, (
    select avg(ideas_count) from (
      select * from contests c2 where c2.id<=c1.id order by id desc limit 10
    ) prev
  ) 
from 
  contests c1
order by 
  id asc;

MySQL 返回错误:“‘where 子句’中的未知列‘c1.id’”

这是为什么？

我怎样才能让它工作？

更新

select 
  id, (
    select avg(ideas_count) from contests c2 
    where c2.id <= c1.id order by id desc limit 10
  ) 
from 
  contests c1
order by 
  id asc;

将不工作，因为它计算avg首先并在<之后应用limit/strong>，而我需要最后 10 行的平均值。

更新2

添加了一个 SQLFiddle .

Answer 1

使用几个序列号，在子查询中生成。然后根据这些序列号加入子查询。

SELECT sub1.id, 
        AVG(sub2.ideas_count) AS rolling_average
FROM
(
    SELECT id, ideas_count, @seq1:=@seq1 + 1 AS seq
    FROM contests
    CROSS JOIN (SELECT @seq1:=0) sub0
    ORDER BY id
) sub1
INNER JOIN
(
    SELECT id, ideas_count, @seq2:=@seq2 + 1 AS seq
    FROM contests
    CROSS JOIN (SELECT @seq2:=0) sub0
    ORDER BY id
) sub2
ON sub2.seq BETWEEN sub1.seq -9 AND sub1.seq
GROUP BY sub1.id