I am designing a database which will hold transaction level data. It will work the same as a bank account - debits/credits to an Account Number.
我正在设计一个包含事务级数据的数据库。它的工作方式与银行账户相同 - 账户号码的借方/贷方。
What is the best / most efficient way of obtaining the aggregation of these transactions.
获得这些交易汇总的最佳/最有效方式是什么。
I was thinking about using a summary table and then adding these to a list of today's transactions in order to derive how much each account has (i.e there balance).
我正在考虑使用汇总表，然后将这些汇总表添加到今天的交易清单中，以便得出每个账户有多少（即有余额）。
I want this to be scalable (ie. 1 billion transactions) so don't want to have to perform database hits to the main fact table as it will need to find all the debits/credits associate with a desired account number scanning potentially a billion rows.
我希望这是可扩展的（即10亿次交易），因此不希望必须对主要事实表执行数据库命中，因为它需要找到与所需帐号相关联的所有借记/信用可能扫描十亿行。
Thanks, any help or resources would be awesome.
谢谢，任何帮助或资源都会很棒。
2 个解决方案
#1
3
(Have been working in Banks for almost 10years. Here is how it is actually done).
（已在银行工作了近10年。以下是实际完成的方式）。
TLDR: your idea is good.
TLDR：你的想法很好。
Every now and then you store the balance somewhere else ("carry forward balance"). E.g. every month or so (or aver a given number of transactions). To calculate the actual balance (or any balance in the past) you accumulate all relevant transactions going back in time until the most recent balance you kept ("carry forward balance"), which you need to add, of course.
时不时地将余额储存在其他地方（“结转余额”）。例如。每个月左右（或者给定数量的交易）。要计算实际余额（或过去的任何余额），您需要累计所有相关的交易，直到您保留的最新余额（“结转余额”），当然需要添加。
The "current" balance is not kept anywhere. Just alone for the locking problems you would have if you'd update this balance all the time. (In real banks you'll hit some bank internal accounts with almost every single transactions. There are plenty of bank internal accounts to be able to get the figures required by law. These accounts are hit very often and thus would cause locking issues when you'd like to update them with every transaction. Instead every transactions is just insert — even the carry forward balances are just inserts).
“当前”余额不会保留在任何地方。如果你一直在更新这个平衡，那么你可能会遇到锁定问题。 （在真实银行中，您几乎每笔交易都会触及一些银行内部账户。有很多银行内部账户能够获得法律要求的数据。这些账户经常受到影响，因此会导致锁定问题我希望每次交易都更新它们。相反，每个交易都只是插入 - 即使结转余额只是插入）。
Also in real banks you have many use cases which make this approach more favourable:
同样在真实银行中，您有许多用例使这种方法更有利：
- Being able to get back dated balances at any time - Being able to get balances based on different dates for any time (e.g. value date vs. transaction date).
- 能够随时取回日期余额 - 能够在任何时间根据不同日期获得余额（例如，价值日期与交易日期）。
- Reversals/Cancellations are a fun of it's own. Imagine to reverse a transaction from two weeks ago and still keep all of the above going.
- 逆转/取消是一种乐趣。想象一下，从两周前开始扭转交易，并保持上述所有目标。
You see, this is a long story. However, the answer to your question is: yes, you cannot accumulate an ever increasing number of transactions, you need to keep intermediate balances to limit the number to accumulate if needed. Hitting the main table for a limited number of rows, should be no issue.
你知道，这是一个很长的故事。但是，您的问题的答案是：是的，您无法累积越来越多的交易，您需要保留中间余额以限制在需要时累积的数量。点击主表有限数量的行，应该没有问题。
Make sure your main query uses an Index-Only Scan.
确保您的主查询使用仅索引扫描。
#2
-3
Do an Object Oriented Design, Create table for objects example Account, Transaction etc. Here's a good website for your reference. But there's a lot more on the web discussing OODBMS. The reference I gave is just my basis when I started doing an OODBMS.
做一个面向对象的设计，创建对象的表示例帐户，交易等。这是一个很好的网站供您参考。但是在网上讨论OODBMS的内容还有很多。当我开始做OODBMS时，我给出的参考仅仅是我的基础。
#1
3
(Have been working in Banks for almost 10years. Here is how it is actually done).
（已在银行工作了近10年。以下是实际完成的方式）。
TLDR: your idea is good.
TLDR：你的想法很好。
Every now and then you store the balance somewhere else ("carry forward balance"). E.g. every month or so (or aver a given number of transactions). To calculate the actual balance (or any balance in the past) you accumulate all relevant transactions going back in time until the most recent balance you kept ("carry forward balance"), which you need to add, of course.
时不时地将余额储存在其他地方（“结转余额”）。例如。每个月左右（或者给定数量的交易）。要计算实际余额（或过去的任何余额），您需要累计所有相关的交易，直到您保留的最新余额（“结转余额”），当然需要添加。
The "current" balance is not kept anywhere. Just alone for the locking problems you would have if you'd update this balance all the time. (In real banks you'll hit some bank internal accounts with almost every single transactions. There are plenty of bank internal accounts to be able to get the figures required by law. These accounts are hit very often and thus would cause locking issues when you'd like to update them with every transaction. Instead every transactions is just insert — even the carry forward balances are just inserts).
“当前”余额不会保留在任何地方。如果你一直在更新这个平衡，那么你可能会遇到锁定问题。 （在真实银行中，您几乎每笔交易都会触及一些银行内部账户。有很多银行内部账户能够获得法律要求的数据。这些账户经常受到影响，因此会导致锁定问题我希望每次交易都更新它们。相反，每个交易都只是插入 - 即使结转余额只是插入）。
Also in real banks you have many use cases which make this approach more favourable:
同样在真实银行中，您有许多用例使这种方法更有利：
- Being able to get back dated balances at any time - Being able to get balances based on different dates for any time (e.g. value date vs. transaction date).
- 能够随时取回日期余额 - 能够在任何时间根据不同日期获得余额（例如，价值日期与交易日期）。
- Reversals/Cancellations are a fun of it's own. Imagine to reverse a transaction from two weeks ago and still keep all of the above going.
- 逆转/取消是一种乐趣。想象一下，从两周前开始扭转交易，并保持上述所有目标。
You see, this is a long story. However, the answer to your question is: yes, you cannot accumulate an ever increasing number of transactions, you need to keep intermediate balances to limit the number to accumulate if needed. Hitting the main table for a limited number of rows, should be no issue.
你知道，这是一个很长的故事。但是，您的问题的答案是：是的，您无法累积越来越多的交易，您需要保留中间余额以限制在需要时累积的数量。点击主表有限数量的行，应该没有问题。
Make sure your main query uses an Index-Only Scan.
确保您的主查询使用仅索引扫描。
#2
-3
Do an Object Oriented Design, Create table for objects example Account, Transaction etc. Here's a good website for your reference. But there's a lot more on the web discussing OODBMS. The reference I gave is just my basis when I started doing an OODBMS.
做一个面向对象的设计，创建对象的表示例帐户，交易等。这是一个很好的网站供您参考。但是在网上讨论OODBMS的内容还有很多。当我开始做OODBMS时，我给出的参考仅仅是我的基础。