mysql - 将 moving average select 语句重构为 update 语句

Question

我有一个有效的选择语句，它选择了 1day_rank 的移动平均值

SELECT
 *, 
 (SELECT AVG(1day_rank)
  FROM keyword_rank T2
  WHERE (
           SELECT COUNT(*)
           FROM keyword_rank T3
           WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=t2.keyword_id 
      ) BETWEEN 1 AND 7 and T2.keyword_id=T1.keyword_id and (T2.timestamp > T1.timestamp - interval 7 day)
 ) average
FROM keyword_rank T1 where T1.keyword_id=86;

我想将此语句重构为更新语句，将结果更新为 7day_rank。但是 MY_SQL 不允许这样做，导致:“错误代码:1093:您不能在 FROM 子句中指定目标表 'T1' 进行更新”

update keyword_rank T1 set T1.7day_rank=
 (
 SELECT
      AVG(1day_rank)
 FROM
      (select * from keyword_rank sub where sub.keyword_id=86) as T2
 WHERE
      (
           SELECT
                COUNT(*)
           FROM
                keyword_rank T3
           WHERE
                timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=86 
      ) BETWEEN 1 AND 7 and (T2.timestamp > T1.timestamp - interval 7 day)
 )
  where T1.keyword_id=86;

所以我尝试使用内部联接重构此语句，但这会产生另一个错误“错误代码:1054，‘where 子句’中的未知列‘T1.timestamp’”

update keyword_rank T1 inner join (
  select AVG(1day_rank) average, timestamp from keyword_rank T2
  where (
           SELECT COUNT(*)
           FROM keyword_rank T3
           WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=86 
      ) BETWEEN 1 AND 7 and T2.keyword_id=86 and (T2.timestamp >   T1.timestamp - interval 7 day)
 ) as TX set T1.7day_rank=TX.average;

Answer 1

我能够通过获取整个工作选择并将其放入内部联接来修复查询。

UPDATE keyword_rank as U
        INNER JOIN
    (
        SELECT
             T1.keyword_id, T1.timestamp, 
             (SELECT AVG(1day_rank)
             FROM keyword_rank T2
             WHERE (
                       SELECT COUNT(*)
                       FROM keyword_rank T3
                       WHERE timestamp BETWEEN T2.timestamp AND T1.timestamp and t3.keyword_id=T2.keyword_id 
                  ) BETWEEN 1 AND 7 and T2.keyword_id=t1.keyword_id and (T2.timestamp > T1.timestamp - interval 7 day) 
             ) as average
        FROM keyword_rank T1 where T1.keyword_id=86
    ) T on U.keyword_id=T.keyword_id and U.timestamp=T.timestamp
SET 
    U.7day_rank = average;