按组 SQL 运行总计 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22155401/
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 Group SQL (Oracle)
提问by user1723699
I have a table in an Oracle db that has the following fields of interest: Location, Product, Date, Amount. I would like to write a query that would get a running total of amount by Location, Product, and Date. I put an example table below of what I would like the results to be.
我在 Oracle 数据库中有一个表，其中包含以下感兴趣的字段：位置、产品、日期、金额。我想编写一个查询，按位置、产品和日期获取总金额。我在下面放了一个示例表，说明我想要的结果。
I can get a running total but I can't get it to reset when I reach a new Location/Product. This is the code I have thus far, any help would be much appreciated, I have a feeling this is a simple fix.
我可以得到一个运行总数，但是当我到达一个新的位置/产品时我无法重置它。这是我迄今为止的代码，任何帮助将不胜感激，我觉得这是一个简单的修复。
select a.*, sum(Amount) over (order by Location, Product, Date) as Running_Amt
from Example_Table a
+----------+---------+-----------+------------+------------+
| Location | Product | Date | Amount |Running_Amt |
+----------+---------+-----------+------------+------------+
| A | aa | 1/1/2013 | 100 | 100 |
| A | aa | 1/5/2013 | -50 | 50 |
| A | aa | 5/1/2013 | 100 | 150 |
| A | aa | 8/1/2013 | 100 | 250 |
| A | bb | 1/1/2013 | 500 | 500 |
| A | bb | 1/5/2013 | -100 | 400 |
| A | bb | 5/1/2013 | -100 | 300 |
| A | bb | 8/1/2013 | 250 | 550 |
| C | aa | 3/1/2013 | 550 | 550 |
| C | aa | 5/5/2013 | -50 | 600 |
| C | dd | 10/3/2013 | 999 | 999 |
| C | dd | 12/2/2013 | 1 | 1000 |
+----------+---------+-----------+------------+------------+
回答by user1723699
Ah, I think I have figured it out.
啊，我想我已经想通了。
select a.*, sum(Amount) over (partition by Location, Product order by Date) as Running_Amt
from Example_Table a
回答by niccolo m.
from Advanced SQL Functions in Oracle 10g book, it has this example.
来自 Oracle 10g 书中的高级 SQL 函数，它有这个例子。
SELECT dte "Date", location, receipts,
SUM(receipts) OVER(ORDER BY dte
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) "Running total"
FROM store
WHERE dte < '10-Jan-2006'
ORDER BY dte, location
回答by Joe Trader
I could type out all the answer or send you to where I learned it. :)
我可以输入所有答案或将您发送到我学到的地方。:)
Check this out, it explains exactly what you are trying to do.
看看这个，它准确地解释了你想要做什么。
http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server
http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server
