mysql - 通过 SUM() 递增 MySql 变量不正确

Question

我正在尝试组合一个查询，该查询按日期对记录进行分组以及该特定日期的总计(一天中可以有多个条目)，但我还需要一个运行总计，我打算使用一个 MySQL 变量来.我的问题是累计总计列似乎仅包含该日期的 SUM()。

所以这对每日总计来说效果很好

SELECT
YEAR(log_at) as year,
MONTH(log_at) as month,
DAY(log_at) as day,
SUM(ev) as dailyTotal,
count(*) as count
FROM tracks
WHERE user_id = 1
GROUP BY year, month, day
ORDER BY year, month, day ASC

在其中添加我认为将是一个相当简单的变量以跟踪运行总数

SET @cumulative := 0;

SELECT
YEAR(log_at) as year,
MONTH(log_at) as month,
DAY(log_at) as day,
SUM(ev) as dailyTotal,
@cumulative := @cumulative + sum(ev) AS cumulative,
count(*) as count
FROM tracks
WHERE user_id = 1
GROUP BY year, month, day
ORDER BY year, month, day ASC

累积变量只包含当天的总数。但是如果我将它改为递增 1 而不是 SUM() 它似乎可以正常工作

非常感谢任何有关实现所需行为的建议!

Answer 1

您必须首先创建整个选择，然后才进行累积操作，因为您不能同时附加 GROUP BY 的列的总和(我真的不知道为什么面团)

SELECT 
`year`,
`month`,
`day`,
`dailyTotal`,
@cum := @cum + `dailyTotal` as `cumulative`,
`count`
FROM
(
SELECT
YEAR(log_at) as year,
MONTH(log_at) as month,
DAY(log_at) as day,
SUM(ev) as dailyTotal,
count(*) as count
FROM tracks
WHERE user_id = 1
GROUP BY year, month, day
ORDER BY year, month, day ASC
) a
JOIN (SELECT @cum := 0) b

我用不太密集的表格对其进行了测试……也许我在这里弄错了。但我希望你能理解这个理论。