SQL 按表中的分组记录运行总计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/439138/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Running total by grouped records in table
提问by Zsolt Botykai
I have a table like this (Oracle, 10)
我有一个这样的表 (Oracle, 10)
Account Bookdate Amount
1 20080101 100
1 20080102 101
2 20080102 200
1 20080103 -200
...
What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this:
我需要的是按 Account order by Account asc 和 Bookdate asc 分组的新表，其中包含一个运行总计字段，如下所示：
Account Bookdate Amount Running_total
1 20080101 100 100
1 20080102 101 201
1 20080103 -200 1
2 20080102 200 200
...
Is there a simple way to do it?
有没有简单的方法来做到这一点？
Thanks in advance.
提前致谢。
回答by William
Do you really need the extra table?
你真的需要额外的桌子吗？
You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.
您可以通过一个简单的查询获得所需的数据，如果您希望它看起来像一个表格，显然可以将其创建为一个视图。
This will get you the data you are looking for:
这将为您提供您正在寻找的数据：
select
account, bookdate, amount,
sum(amount) over (partition by account order by bookdate) running_total
from t
/
This will create a view to show you the data as if it were a table:
这将创建一个视图，向您显示数据，就好像它是一个表一样：
create or replace view t2
as
select
account, bookdate, amount,
sum(amount) over (partition by account order by bookdate) running_total
from t
/
If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.
如果你真的需要这张桌子，你的意思是你需要它不断更新吗？或只是一个？显然，如果它是一次性的，您可以使用上述查询“按选择创建表”。
Test data I used is:
我使用的测试数据是：
create table t(account number, bookdate date, amount number);
insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);
insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);
insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);
insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);
commit;
edit:
编辑：
forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).
忘了补充；你指定你想要对表进行排序 - 这真的没有意义，让我觉得你真的是想要查询/视图 - 排序是你执行的查询的结果，而不是固有的表（忽略索引组织表等）。
回答by Tom H
I'll start with this very important caveate: do NOT create a table to hold this data. When you do you will find that you need to maintain it which will become a never ending headache. Write a view to return the extra column if you want to do that. If you're working with a data warehouse then maybeyou would do something like this, but even then err on the side of a view unless you simply can't get the performance that you need with indexes,decent hardware, etc.
我将从这个非常重要的警告开始：不要创建一个表来保存这些数据。当你这样做时，你会发现你需要维护它，这将成为一个永无止境的头痛。如果您想这样做，请编写一个视图以返回额外的列。如果您正在使用数据仓库，那么也许您会做这样的事情，但即使如此，在视图方面也会出错，除非您根本无法通过索引、体面的硬件等获得所需的性能。
Here's a query that will return the rows the way that you need them.
这是一个查询，它将按照您需要的方式返回行。
SELECT
Account,
Bookdate,
Amount,
(
SELECT SUM(Amount)
FROM My_Table T2
WHERE T2.Account = T1.Account
AND T2.Bookdate <= T1.Bookdate
) AS Running_Total
FROM
My_Table T1
Another possible solution is:
另一种可能的解决方案是：
SELECT
T1.Account,
T1.Bookdate,
T1.Amount,
SUM(T2.Amount)
FROM
My_Table T1
LEFT OUTER JOIN My_Table T2 ON
T2.Account = T1.Account AND
T2.Bookdate <= T1.Bookdate
GROUP BY
T1.Account,
T1.Bookdate,
T1.Amount
Test them both for performance and see which works better for you. Also, I haven't thoroughly tested them beyond the example which you gave, so be sure to test some edge cases.
测试它们的性能，看看哪个更适合你。此外，除了您提供的示例之外，我还没有对它们进行彻底的测试，所以一定要测试一些边缘情况。
回答by tuinstoel
Use analytics, just like in your last question:
使用分析，就像在您的上一个问题中一样：
create table accounts
( account number(10)
, bookdate date
, amount number(10)
);
delete accounts;
insert into accounts values (1,to_date('20080101','yyyymmdd'),100);
insert into accounts values (1,to_date('20080102','yyyymmdd'),101);
insert into accounts values (2,to_date('20080102','yyyymmdd'),200);
insert into accounts values (1,to_date('20080103','yyyymmdd'),-200);
commit;
select account
, bookdate
, amount
, sum(amount) over (partition by account order by bookdate asc) running_total
from accounts
order by account,bookdate asc
/
output:
输出：
ACCOUNT BOOKDATE AMOUNT RUNNING_TOTAL
---------- -------- ---------- -------------
1 01-01-08 100 100
1 02-01-08 101 201
1 03-01-08 -200 1
2 02-01-08 200 200
