mysql中一组行的累计总和 [英] Cumulative sum over a set of rows in mysql
问题描述
我有一个复杂的查询(包含多个联接，联合)，该查询返回一组包含id，day，hr，amount的行.查询的输出如下所示:
I have a complex query(containing multiple joins, unions) that returns a set of rows containing id, day, hr, amount. The output of the query looks like this:
id day hr amount
1 1 1 10
1 1 2 25
1 1 3 30
1 2 1 10
1 2 2 40
1 2 2 30
2 1 1 10
2 1 2 15
2 1 3 30
2 2 1 10
2 2 2 20
2 2 2 30
我需要找到一天中每个小时的每个ID的累计总数.输出应该是这样的:
I need to find cumulative total for each id, for every hour of the day. The output should be like this:
id day hr amount cumulative total
1 1 1 10 10
1 1 2 25 35
1 1 3 30 65
1 2 1 10 10
1 2 2 40 50
1 2 2 30 80
2 1 1 10 10
2 1 2 15 25
2 1 3 30 55
2 2 1 10 10
2 2 2 20 30
2 2 2 30 60
我产生第一个输出的初始查询如下:
My initial query that produces the first output looks like this:
select id, day, hr, amount from
( //multiple joins on multiple tables)a
left join
(//unions on multiple tables)b
on a.id=b.id;
什么是SQL查询以获取第二个输出中所述的累加总和?解决方案中不应使用SET.
What's sql query to get the cumulative sum as described in the second output? SET should not be used in the solution.
谢谢.
推荐答案
UPDATE
MySQL 8.0引入了窗口函数"，其功能等同于SQL Server窗口函数"(具有由Transact-SQL OVER语法提供的分区和排序)和Oracle分析函数".
MySQL 8.0 introduces "window functions", functionality equivalent to SQL Server "window functions" (with partitioning and ordering provided by Transact-SQL OVER syntax), and Oracle "analytic functions".
MySQL参考手册12.21窗口函数 https://dev .mysql.com/doc/refman/8.0/en/window-functions.html
MySQL Reference Manual 12.21 Window Functions https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
此处提供的答案是针对8.0之前的MySQL版本的一种方法.
The answer provided here is an approach for MySQL versions prior to 8.0.
原始答案
MySQL不提供用于获取运行的累计和"的类型分析功能，例如其他DBMS(例如Oracle或SQL Server)中可用的分析功能.
MySQL doesn't provide the type analytic function you would use to get a running "cumulative sum", like the analytic functions available in other DBMS (like Oracle or SQL Server.)
但是，可以使用MySQL模拟某些分析功能.
But, it is possible to emulate some analytic functions, using MySQL.
(至少)有两种可行的方法:
There are (at least) two workable approaches:
一种方法是使用相关子查询来获取小计.这种方法在大型集合上可能很昂贵，并且如果外部查询的谓词很复杂，则可能会很复杂.这实际上取决于在多个表上进行多个连接"的复杂程度. (不幸的是，MySQL也不支持CTE.)
One is to use a correlated subquery to get the subtotal. This approach can be expensive on large sets, and complicated if the predicates on the outer query are complicated. It really depends on how complicated that "multiple joins on multiple tables" is. (Unfortunately, MySQL also does not not support CTEs either.)
另一种方法是利用MySQL用户变量来进行一些控制中断处理.这里的技巧"是对查询结果进行排序(使用ORDER BY)，然后将查询包装在另一个查询中.
The other approach is to make use of MySQL user variables, to do some control break processing. The "trick" here is to the results from your query sorted (using an ORDER BY) and then wrapping your query in another query.
我将举例说明后一种方法.
I'll give an example of the latter approach.
由于MySQL执行操作的顺序，必须先计算cumulative_total列，然后将当前行的id和day中的值保存到用户变量中.将此列放在第一位是最简单的.
Because of the order that MySQL performs operations, the cumulative_total column needs to be computed before the value from id and day from the current row are saved into user variables. It's just easiest to put this column first.
别名为i的内联视图(在下面的查询中)仅用于初始化用户变量，以防万一这些用户变量已在会话中设置.如果已经为它们分配了值，则我们想忽略其当前值，最简单的方法是对其进行初始化.
The inline view aliased as i (in the query below) is just there to initialize the user variables, just in case these are already set in the session. If those already have values assigned, we want to ignore their current values, and the easiest way to do that is to initialize them.
您的原始查询用括号括起来，并在下面的示例中被赋予别名c.原始查询的唯一更改是添加了ORDER BY子句，因此我们可以确保我们按顺序处理查询中的行.
Your original query gets wrapped in parenthesis, and is given an alias, c in the example below. The only change to your original query is the addition of an ORDER BY clause, so we can be sure that we process the rows from the query in sequence.
外部选择检查当前行中的id和day值是否与上一行匹配".如果是这样，我们将当前行中的amount添加到累计小计中.如果它们不匹配，那么我们将累计小计重置为零，并添加当前行中的金额(或更简单地说，只需分配当前行中的金额)即可.
The outer select checks whether the id and day value from the current row "match" the previous row. If they do, we add the amount from the current row to the cumulative subtotal. If they don't match, then we reset the the cumulative subtotal to zero, and add the amount from the current row (or, more simply, just assign the amount from the current row).
计算完总计后，我们将当前行中的id和day值保存到用户变量中，以便在处理下一行时可用.
After we have done the computation of the cumulative total, we save the id and day values from the current row into user variables, so they are available when we process the next row.
例如:
SELECT IF(@prev_id = c.id AND @prev_day = c.day
,@cumtotal := @cumtotal + c.amount
,@cumtotal := c.amount) AS cumulative_total
, @prev_id := c.id AS `id`
, @prev_day := c.day AS `day`
, c.hr
, c.amount AS `amount'
FROM ( SELECT @prev_id := NULL
, @prev_day := NULL
, @subtotal := 0
) i
JOIN (
select id, day, hr, amount from
( //multiple joins on multiple tables)a
left join
(//unions on multiple tables)b
on a.id=b.id
ORDER BY 1,2,3
) c
如果有必要以不同的顺序返回列，并以累积总数作为最后一列，则一个选择是将整个语句包装在一组括号中，并将该查询用作内联视图:
If it's necessary to return the columns in a different order, with cumulative total as the last column, then one option is to wrap that whole statement in a set of parens, and use that query as an inline view:
SELECT d.id
, d.day
, d.hr
, d.amount
, d.cumulative_total
FROM (
// query from above
) d
这篇关于mysql中一组行的累计总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
