mysql - 当日期有差距时，mySQL 更新最近 5 个日期的平均数量

Question

我有如下 mySQL 表，有什么方法可以将 table2 中的值更新到 where ？被放置。需要销售实际登记时的最后 5 天平均金额，从表 1 的最后 5 个日期开始，为（9th, 7th, 3rd, 30th, 28th）

|         | Table1     |        | -------- |         | Table2     |          |
|---------|------------|--------|----------|---------|------------|----------|
| Brand   | Date       | Amount |          | Brand   | As_on      | 5day_Avg |
| Samsung | 2020-07-09 | 2000   |          | Apple   | 2020-07-09 |     ?    |
| Nokia   | 2020-07-09 | 200    |          | Samsung | 2020-07-09 |     ?    |
| Apple   | 2020-07-09 | 600    |          | Nokia   | 2020-07-09 |     ?    |
| Samsung | 2020-07-07 | 450    |          | Others  | 2020-07-09 |     ?    |
| Nokia   | 2020-07-07 | 12     |          |         |            |          |
| Apple   | 2020-07-03 | 450    |          |         |            |          |
| Samsung | 2020-07-03 | 45     |          |         |            |          |
| Nokia   | 2020-07-03 | 350    |          |         |            |          |
| Others  | 2020-06-30 | 450    |          |         |            |          |
| Apple   | 2020-06-30 | 500    |          |         |            |          |
| Samsung | 2020-06-30 | 850    |          |         |            |          |
| Nokia   | 2020-06-28 | 100    |          |         |            |          |
| Others  | 2020-06-27 | 50     |          |         |            |          |
| Samsung | 2020-06-26 | 125    |          |         |            |          |

标签： mysqlsql

Answer 1

这是一种方法：

select brand,avg(amount) last5avg from (
select * , row_number() over (partition by brand order by date desc) rn
from table1) t
where t.rn <= 5
group by brand 

更新 ：

update table2
inner join (
   select brand,avg(amount) last5avg from (
       select * , row_number() over (partition by brand order by date desc) rn
       from table1) t
   where t.rn <= 5
   group by brand) t1
  on t1.brand = t2.brand
set table2.5day_Avg = t1.last5avg;