MySQL:计算空行的累积和

Question

当许多列具有空值时，如何计算列随时间的累积和？

我正在尝试以下操作，但我的查询超时且没有具体的错误消息:

SELECT t1.time_purchased, 
    t1.savings AS daily_savings,
    SUM(t2.savings) AS total_savings
FROM items AS t1, 
    items AS t2
WHERE t1.time_purchased >= t2.time_purchased
GROUP BY t1.time_purchased;

time_purchased 和 saving 行通常为空 - 这是导致错误的原因吗？如果是这样，我该怎么做才能跳过这些错误，同时仍将节省的金额添加到total_ savings中？

理想情况下，我想显示一段时间内的累积节省，与 time_purchased 无关。谢谢!

解决方案编辑:

感谢大家的帮助。最终的解决方案要求我的 FROM 语句从储蓄没有空值的表中进行选择，否则cumulative_sum 继续为空，因为我在某些情况下添加了空值。请参阅下面的解决方案:

SET @cumulative_sum := 0;
SELECT 
    time_purchased
    ,savings
    ,(@cumulative_sum := @cumulative_sum + savings) AS cumulative_sum
FROM (SELECT * FROM items WHERE savings IS NOT NULL) AS i
ORDER BY time_purchased;

Answer 1

在MySQL中，进行累加和的最简单方法是使用变量:

SELECT i.time_purchased,
       i.savings
       (@ds := @ds + i.savings) AS total_savings
FROM items i CROSS JOIN
     (SELECT @ds := 0) params 
ORDER BY i.time_purchased;

我不确定您想如何处理 NULL 值，但至少这不应该超时。