mysql - 添加累积(运行总计？)列的优化？

Question

我是 sql 的新手，到目前为止，这个论坛一直是我的生命线。感谢您在这个伟大的平台上创建和分享。

我目前正在处理大型数据集，希望得到一些指导。

数据表 (existing_table) 有 400 万行，如下所示:

id  date   sales_a   sales_b   sales_c   sales_d   sales_e

请注意，有多行具有相同的日期。

我想要做的是在此表中再添加 5 列(cumulative_sales_a、cumulative_sales_b 等)，其中包含 a、b 的累计销售额, c, 等直到特定日期(这将按日期分组)。我使用以下代码来执行此操作:

create table new_cumulative  
select t.id, t.date, t.sales_a, t.sales_b, t.sales_c, t.sales_d, t.sales_e,   
(select sum(x.sales_a) from existing_table x where x.id = t.id and x.date <= t.date) as cumulative_sales_a,  
(select sum(x.sales_b) from existing_table x where x.id = t.id and x.date <= t.date) as cumulative_sales_b,  
(select sum(x.sales_c) from existing_table x where x.id = t.id and x.date <= t.date) as cumulative_sales_c,  
(select sum(x.sales_d) from existing_table x where x.id = t.id and x.date <= t.date) as cumulative_sales_d,  
(select sum(x.sales_e) from existing_table x where x.id = t.id and x.date <= t.date) as cumulative_sales_e  
from existing_table t  
group by t.id, t.date;

在运行此查询之前，我已经在“id”列上创建了一个索引。

虽然我得到了想要的输出，但这个查询花了将近 11 个小时才完成。

我想知道我是否在这里做错了什么，以及是否有更好(更快)的方式来运行此类查询。

感谢您的帮助。

Answer 1

一些查询本质上是昂贵的，需要很长时间才能执行。在这种特殊情况下，您可以避免使用 5 个子查询:

SELECT a.*, b.cumulative_sales_a, b.cumulative_sales_b, ...
FROM 
(
 select t.id, t.`date`, t.sales_a, t.sales_b, t.sales_c, t.sales_d, t.sales_e
 from existing_table t  
 GROUP BY t.id,t.`date`
)a
LEFT JOIN 
(
  select x.id, x.date, sum(x.sales_a) as  cumulative_sales_a,
  sum(x.sales_b) as cumulative_sales_b, ...
  FROM existing_table x 
  GROUP BY x.id, x.`date`
)b ON (b.id = a.id AND b.`date` <=a.`date`)

这也是一个昂贵的查询，但它应该有一个比你原来的更好的执行计划。另外，我不确定

select t.id, t.`date`, t.sales_a, t.sales_b, t.sales_c, t.sales_d, t.sales_e
 from existing_table t  
 GROUP BY t.id,t.`date`

给你你想要的 - 例如，如果你有 5 条具有相同 ID 和日期的记录，它将从这 5 条记录中的任何一条中获取其他字段(sales_a、sales_b 等)的值...