MySQL 5.7计算列avg_month结果写入下一行问题求助

问题出在用户变量的执行顺序不明确上，MySQL 5.7不保证SELECT子句里表达式的执行顺序，加上GROUP BY的分组处理逻辑，导致@avg_month引用的是上一行的@qty_mth和@count_mth值，所以结果错位到下一行。

直接用下面两种方案解决：

方案1：去掉用户变量（推荐） #

不需要用变量存储中间聚合结果，直接基于聚合函数计算avg_month，逻辑更清晰且无顺序问题：

CREATE TABLE tbl_temp_si_trans4 
(temp_si_trans4id INT NOT NULL AUTO_INCREMENT,  
PRIMARY KEY temp_trans4idkey (temp_si_trans4id), 
INDEX index1 (order_month,SKU))
SELECT 
  order_month,  
  SKU, 
  SUM(net_qty_after_refund) AS qty_mth, 
  COUNT(DISTINCT order_year) AS count_mth, 
  SUM(net_qty_after_refund)/COUNT(DISTINCT order_year) AS avg_month
FROM order_trans4
GROUP BY order_month, SKU;

方案2：用子查询先聚合再处理变量 #

如果一定要保留变量逻辑，先通过子查询完成分组聚合，再在外部查询中使用变量计算平均，确保变量引用的是当前行的聚合值：

CREATE TABLE tbl_temp_si_trans4 
(temp_si_trans4id INT NOT NULL AUTO_INCREMENT,  
PRIMARY KEY temp_trans4idkey (temp_si_trans4id), 
INDEX index1 (order_month,SKU))
SELECT 
  order_month,
  SKU,
  @qty_mth := qty_mth AS qty_mth,
  @count_mth := count_mth AS count_mth,
  @qty_mth/@count_mth AS avg_month
FROM (
  SELECT 
    order_month,
    SKU,
    SUM(net_qty_after_refund) AS qty_mth,
    COUNT(DISTINCT order_year) AS count_mth
  FROM order_trans4
  GROUP BY order_month, SKU
) AS agg_data;

内容的提问来源于stack exchange，提问作者Glenn