mysql - 对前 N 行进行分组，但还需要对列值求和

Question

我使用以下查询对数据集中的前 N ​​行进行分组:

SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, 
mgap_growth AS growth,mgap_recovery,
(mgap_growth+mgap_recovery) total
  FROM
  (SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, mgap_growth,
  mgap_recovery,(mgap_growth+mgap_recovery) total,
    @acid_rank := IF(@current_acid = account_manager_id, @acid_rank + 1, 1) 
    AS acid_rank,
       @current_acid := account_manager_id 
       FROM mgap_orders
       ORDER BY account_manager_id, mgap_growth DESC 
     ) ranked
     WHERE acid_rank <= 5 

结果非常接近我所需要的，但我遇到了一个需要帮助的总体问题。我已经 attcached 了查询结果的屏幕截图(出于隐私考虑，我必须屏蔽客户名称和 ID；mgap_ska_id 和 account_manager_id 是 INT 列，mgap_ska_id_name 是 VARCHAR。

理论上，我需要对多个 mgap_growth 值进行求和(我知道它是一个聚合；这就是问题)，同时保持排名不变。

如果我 GROUP BY，那么我就会失去前 5 名的排名。目前，mgap_growth 值只是 mgap_growth 列中每个 mgap_ska_id 的一个值；我需要它是每个 mgap_ska_id 的所有 mgap_growth 值的总和，并保持前五名的排名，如图所示。

谢谢!

Answer 1

您可以在选择的字段中添加以下行:

(SELECT SUM(t.mgap_growth) FROM mgap_orders t WHERE t.mgap_ska_id = ranked.mgap_ska_id ) AS total_mgap_growth

所以你的代码将是:

SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, 
mgap_growth AS growth,mgap_recovery,
(mgap_growth+mgap_recovery) total,
(SELECT SUM(t.mgap_growth) FROM mgap_orders t WHERE t.mgap_ska_id = ranked.mgap_ska_id ) AS total_mgap_growth
  FROM
  (SELECT mgap_ska_id,mgap_ska_id_name, account_manager_id, mgap_growth,
  mgap_recovery,(mgap_growth+mgap_recovery) total,
    @acid_rank := IF(@current_acid = account_manager_id, @acid_rank + 1, 1) 
    AS acid_rank,
       @current_acid := account_manager_id 
       FROM mgap_orders
       ORDER BY account_manager_id, mgap_growth DESC 
     ) ranked
     WHERE acid_rank <= 5