表中按分组记录的运行总计 [英] Running total by grouped records in table
问题描述
我有一个这样的表(Oracle,10)
I have a table like this (Oracle, 10)
Account Bookdate Amount
1 20080101 100
1 20080102 101
2 20080102 200
1 20080103 -200
...
我需要的是一个新表,该表按Account asc和Bookdate asc按Account顺序分组,并具有一个总计字段,例如:
What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this:
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?
谢谢.
推荐答案
您真的需要额外的表吗?
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.
我使用的测试数据是:
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;
忘记添加;您指定要对表进行排序-这确实没有意义,并且让我认为您的意思确实是您想要查询/视图-排序是执行查询的结果,而不是查询中固有的内容该表(忽略索引组织表等).
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).
这篇关于表中按分组记录的运行总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!