财务会计应用程序的数据库 II：基础设施

• 下载MySQL数据库创建脚本

引言

在上一篇文章中，我们讨论了财务会计应用程序数据库设计的通用概念，并为整个数据库定义了一个非常基础的路线图，例如：定义业务领域、需要满足的基本要求、主键使用策略、命名约定。我们还为可扩展性和基本查找代码设置了基础设施。

本文将致力于会计基础设施的设计：总账、会计科目表、（源）凭证和财务报表结构。

起初，我曾打算按照表依赖的顺序设计数据库模式（表、实体）。然而，在撰写本文时，我意识到，在不先实现总账的情况下，根本无法解释例如财务报表功能。

起初，我还计划在本篇文章中涵盖更多功能，例如人员档案、成本中心和公司档案。然而，在完成财务报表描述后，文章已经变得太长了。

会计基础和总账

在我们开始设计会计实体之前，我们需要了解财务会计的基础知识——财务会计的核心是如何运作的。

有两种基本的财务会计方法——单式记账法和复式记账法。单式记账法法律通常不允许使用。例如，在立陶宛，只有年收入低于45,000欧元的个体经营者（自然人）才能使用；在德国，只有年收入低于500,000欧元的个体经营者（自然人）才能使用。即使在允许灵活选择会计方法（例如英国）的国家，单式记账法也极少使用。因此，我们将实现复式记账法。

复式记账法基于会计基本方程

资产 = 负债 + 所有者权益 + 收入 - 费用

在方程中

• 资产是公司拥有的所有东西：现金、应收账款、用品、设备、其他公司的股票等。
• 负债是公司欠他人的所有东西：应付票据、应付账款、应付工资、税款等。
• 所有者权益是公司股东（所有者、投资者）拥有的所有东西：公司股票（不是其他公司的股票）、对公司的投资、留存收益等。

对于每笔记录的财务交易，基本会计方程都必须成立，这意味着该方程也可以写成

Δ资产 = Δ负债 + Δ所有者权益 + Δ收入 - Δ费用

该方程意味着每笔财务交易都可能改变公司资产、负债、所有者权益、收入或费用的金额（一项或多项）。财务会计将交易分解成与方程中的变量相对应的组成部分。因此，每笔交易都表示为公司财务状况的两次（或多次）改变。方程在实际操作中最容易理解（完整解释）。

从示例中可以看出，每笔交易都会改变方程的一个或多个变量，并且基本会计方程对于每次更改都保持真实。

用于存储示例数据的数据库表模式非常明显。

这种（过度）简化的解决方案的问题在于，它只允许按非常笼统的受影响变量类型进行过滤。例如，会计师只会看到现金购买交易对资产类别的增加和减少。会计师肯定希望区分更多细节，例如，不仅要知道交易增加了资产的价值，还要知道增加资产的确切类型。基本会计方程变量（类别）的子类型概念被称为“账户”，公司使用的所有账户的集合被称为“会计科目表”。简化的会计科目表如下所示：

传统的会计科目表是按照数字系统组织的。因此，每个主要类别都以某个数字开头，而该主要类别内的子类别都以相同的数字开头。如果流动资产按以数字2开头的数字分类，则现金账户可能标记为271，应收账款可能标记为241，用品可能标记为201，依此类推。而如果负债账户按以数字4开头的数字分类，则应付账款可能标记为443，短期债务可能标记为44，依此类推。这种标记约定允许将不同的资源归入同一类别。例如，如果银行账户按以数字271开头的数字分类，并且公司有多个银行账户，分别按27101、27102、27103等数字分类，会计师可以通过查询所有以数字271开头的账户来获得所有银行账户的总现金周转额。这在SQL中相当于WHERE account_id LIKE ‘271%’，消除了账户表中子-父账户引用的需求。然而，这并不一定意味着交易只能使用（修改）没有子账户的账户。有时使用子账户是为了区分一些常规交易。在这种情况下，常规交易使用（修改）父账户，而一些特殊交易使用子账户。这允许排除“除特定外所有”的类型。

一如既往，现实中有例外。我见过一些会计科目表使用字母作为账户编号，两部分编号（“代码与编号”）或完全忽略传统编号约定。然而，这些都是（罕见的）例外，在我看来，很难认为是好的做法。因此，我将只实现经典的账户编号约定，并为账户ID使用BIGINT类型。（BIGINT支持18位数字，而INT只支持9位）。在数据库设计术语中，它也是一个自然键。正如上一篇文章中所讨论的，我将将其用作主键。

如果复式记账法比实际发明晚了几百年，那么会计方法就会如此简单——只需将交易记录为各种账户的变动（增量）集合，并确保遵守基本会计方程。然而，当复式记账法发明时（大约公元X世纪，可能更早），负数概念还不太为人所知。因此，会计中不使用负数。取而代之的是借贷概念。该概念相对简单——我们不从账户中减去某个金额，而是做同样的事情，但用不同的名称来称呼它——借方或贷方。此外，为了反映基本方程的双方，我们根据账户的基本类型切换命名约定。

• 对于方程左边的账户（即资产），我们将增加称为“借方”，减少称为“贷方”；
• 对于方程右边的账户（即负债、所有者权益和收入），我们将增加称为“贷方”，减少称为“借方”；
• 对于费用账户，我们将增加称为“借方”，减少称为“贷方”；因为即使费用在方程的右边，它们也带有负号。

如果一个账户的总借方金额大于同一账户的总贷方金额，我们就说该账户有借方余额，其金额等于总借方金额减去总贷方金额（不足为奇）。

如果一个账户的总贷方金额大于同一账户的总借方金额，我们就说该账户有贷方余额，其金额等于总贷方金额减去总借方金额（再次不足为奇）。

由于借贷记账法的用法反映了基本会计方程，因此一笔交易中的总借方金额必须始终等于同一交易中的总贷方金额。通过将前面的示例转换为使用借贷记账法（资产和费用的加号用借方“D”替换；资产和费用的减号用贷方“C”替换；负债、所有者权益和收入的加号用贷方“C”替换；负债、所有者权益和收入的减号用借方“D”替换）可以轻松演示。

用于存储修改后示例数据的数据库模式与之前的非常相似——只需添加一个会计科目表，而不是变量类型枚举，并为条目类型添加一个额外的列。

您可以看到，我还进行了一些值得讨论的额外微小更改。

• 我们为交易表添加了transaction_date列，原因很明显。该列已索引，因为它将始终用作搜索参数。
• 我们使用表名accounts而不是chart_of_accounts，这是因为我们在上一篇文章中采用了命名约定。（存储实体的复数形式）
• 我们使用account_type字段，类型为INT，而不是ENUM(‘assets’, ‘liabilities’, ‘equity’, ‘revenues’, ‘expenses’)，以便应用程序提供更精细的枚举。枚举可能会随着应用程序的升级而改变。因此，它不能在数据库中定义为ENUM。为了数据完整性（防止非法/不存在的类型），可以使用技术查找表。但我对此尚未做出决定。
• 我们使用is_archived字段来存储账户，以便应用程序在查找控件中隐藏当前未使用的账户。
• 我们向accounts表添加了标准的审计跟踪字段，因为账户显然是父实体。
• 我们使用表名ledger_entries而不是*deltas*，因为（总）账是包含交易写入所有账户的条目的账本（ledger）的传统名称。
• 我们使用列名amount而不是delta，因为它不再是增量值。
• 我们在entry_type上添加了索引，因为它将广泛用作过滤器参数。

就是这样。我们实现了复式记账法核心财务会计功能。实际上，这几乎是一个可用的模型。我们只需要进行一些更改，就可以使其完全符合会计师的期望。

• 添加按人员和成本中心进行的分析
• 添加源凭证概念
• 添加对税务报告的支持
• 添加对财务报表的支持

按人员和成本中心进行的分析

很明显，会计师会想按人员或成本中心过滤交易和账目条目（账户周转）。我们将在下一篇文章中详细讨论“人员”和“成本中心”实体。目前，重要的方面是添加这些外键的位置（表）。将其添加到交易级别很诱人。实际上，您会在网上找到的多个示例中看到这一点。然而，由于实际用例，这是一种错误的方法，例如：

• 一家增值税纳税人开出的简单发票有三笔账目条目：贷记一项收入账户（发票总额），贷记一项应交增值税账户（增值税金额），以及借记应收账款账户（发票金额加上增值税金额）。账户应收款项条目与客户（买家）相关是很自然的。然而，将同一客户与应交增值税账户关联至少是模糊的（增值税是付给国家的，而不是付给客户）。费用账户条目与某个成本中心相关是很自然的。然而，将同一成本中心与应交增值税账户关联至少是模糊的（这根本不是成本）。
• 多边抵消有两（或更多）笔账目条目：借记一位人员（交易方、供应商）的应付账款，贷记另一位人员（交易方、客户）的应收账款。显然，所有条目都与不同的人员有关。同样适用于工资单和其他自然上与多个人员相关的凭证。

因此，正确的方法是在ledger_entries表中添加分析。

源凭证概念

您可能还会想，如果交易表只有两列——date和description——而凭证显然由更多数据字段描述，那么模型如何几乎是完整的？原因在于“交易”和“（源）凭证”概念之间的区别。虽然每笔交易都与某个（源）凭证（例如，发票）相关，但（源）凭证可能没有关联的交易（例如，劳动合同、工时表等）或有多笔交易（例如，未赚取收入、应计费用）。因此：

• transactions表是documents表的子表，关系类型为1 -> 0…n
• 一个transaction自然没有特定字段，除了date和（很少使用）description。
• 一个transaction是完全通用的，即独立于特定凭证类型。

正如上一篇文章中所讨论的，（源）凭证实现应该是可扩展的，即扩展应该能够定义自己的凭证类型，同时使用基础凭证实现作为通用访问点。此要求也意味着基础凭证实现应该是通用的——表只应包含对于所有（源）凭证类型都明确通用的字段。这些要求导致我们得到一个非常简单的表模式：

税务报告支持

在总账层面，税务会计能做的有限。至少我从未找到一种方法将总账余额映射到税务报告。然而，最近出现了一项简单的税务报告要求。该要求是能够使用国家提供的账户分类法导出总账账户，即每个会计科目表中的账户都应具有国家定义的分类代码（参见SAF-T）。没有要求支持多个代码版本（适用于不同的SAF-T版本）。因此，该要求的实现与在accounts表中添加一个字段一样简单——official_code。该代码不影响任何实际税款，仅供税务检查员进行全面审计。这意味着它对输入错误不敏感，简单的文本字段就足够了。

实施所有更改后，（几乎）最终的模式（相关部分）将是如下所示：

其他总账实现

值得一提的是，还有其他可能的模式可以提供完全相同的总账功能（讨论请参见：需要有关复式记账数据库设计的帮助，复式记账会计系统的数据库模式设计，关系数据库中的复式记账）。我唯一不同意的选项是每行两个条目的模式，它使用两个账户字段和一个金额。贷方等于借方的规则是业务规则，而不是数据完整性规则。因此，它属于应用程序模型，不应在数据库中重复。此外，它与现实世界的账目条目不符，账目条目并非总是成对出现。（例如，简单发票产生以下账目条目：D241 – 1.210,00欧元；C500 – 1.000,00欧元；C4492 – 210,00欧元）。

对于其他替代总账模式，您也可以使用

• 位标志is_debit而不是枚举借方/贷方。
• 金额的符号作为借贷指示符，即正数表示借方，负数表示贷方。
• 列amount_debit和amount_credit而不是带借贷指示符的单个金额。

由于所有这些选项在语义上都是等效的，我已对它们进行了性能基准测试。为此，我为每种模式创建了包含100万笔交易和250万条账目条目的测试数据库。对于位标志版本，我还添加了位标志字段的索引。对于“金额符号”实现，我还为金额字段添加了索引。

基准测试在我的Dell Inspiron 15 Series 5000笔记本电脑上进行。

• 处理器：Intel Core i5-5200U，2.20GHz；
• 内存：4 GB；
• 操作系统：Win7 Pro；
• MySQL服务器：v. 5.7，innodb_buffer_pool_size = 1024MB。

以下查询（根据模式类型进行了调整）用于基准测试。

SELECT SQL_NO_CACHE t.transaction_date AS Date, t.description AS DescriptionOrAccountTitle, 
null as AmountDebit, null AS AmountCredit, t.id AS Reference, null AS IsLine
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
WHERE t.transaction_date BETWEEN '2018-01-01' AND '2018-06-30'
UNION
SELECT null AS Date, (CASE WHEN e.entry_type = 'D' THEN
a.account_name ELSE CONCAT('-  ', a.account_name) END) AS DescriptionOrAccountTitle,
(CASE WHEN e.entry_type = 'D' THEN e.amount ELSE null END) AS AmountDebit,
(CASE WHEN e.entry_type = 'C' THEN e.amount ELSE null END) AS AmountDebit,
t.id AS Reference, (CASE WHEN e.entry_type = 'D' THEN 1 ELSE 2 END) AS IsLine
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
WHERE t.transaction_date BETWEEN '2018-01-01' AND '2018-06-30'
ORDER BY Reference, IsLine;

SELECT SQL_NO_CACHE t.transaction_date, t.description, 
(CASE WHEN e.entry_type='D' THEN e.amount ELSE 0 END) AS DebitAmount,
(CASE WHEN e.entry_type='C' THEN e.amount ELSE 0 END) AS CreditAmount
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
WHERE t.transaction_date >= '2018-01-01' AND t.transaction_date <='2018-06-30'
AND e.account_id = 271
ORDER BY t.transaction_date;

SELECT SQL_NO_CACHE t.transaction_date, t.description,
SUM(CASE WHEN e.entry_type='D' THEN e.amount ELSE 0.0 END) AS Amount,
GROUP_CONCAT(DISTINCT CONCAT(e.entry_type, e.account_id)
SEPARATOR ', ') AS Entries
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
WHERE t.transaction_date >= '2018-01-01' AND t.transaction_date <='2018-06-30'
GROUP BY t.id ORDER BY t.transaction_date;

SELECT SQL_NO_CACHE e.account_id, a.account_name,
SUM(CASE WHEN e.entry_type='D' THEN e.amount ELSE -e.amount END) AS Balance
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
WHERE t.transaction_date <='2018-06-30' GROUP BY e.account_id
ORDER BY CAST(e.account_id AS CHAR);

SELECT SQL_NO_CACHE e.account_id, a.account_name,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='D' _
         THEN e.amount ELSE 0.0 END) AS TotalDebitBefore,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='C' _
         THEN e.amount ELSE 0.0 END) AS TotalCreditBefore,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND _
    t.transaction_date < '2018-01-01' AND e.entry_type='D' _
    THEN e.amount ELSE 0.0 END) AS DebitFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01' _
         AND e.entry_type='C' THEN e.amount ELSE 0.0 END) AS CreditFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='D' _
         THEN e.amount ELSE 0.0 END) AS DebitSecondPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='C' _
         THEN e.amount ELSE 0.0 END) AS CreditSecondPeriod
FROM transactions t
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
WHERE t.transaction_date <='2018-12-31' GROUP BY e.account_id _
       ORDER BY CAST(e.account_id AS CHAR);

基准测试结果（平均每次查询运行10次，单位：秒）

结论——所有这些模式变体都具有相同的性能统计数据。因此，选择取决于个人品味。

作为附带结果，我们可以看到，总账模式对于每年有100,000笔交易的公司在10年运营中是可行的。当然，这样的公司不会使用我的笔记本电脑作为服务器。有了合适的硬件和SQL服务器配置，性能可能会提高一个数量级。而每秒查询几秒钟是可以接受的。

出于好奇，我还测试了使用BIGINT类型作为金额字段。它比DECIMAL更快的传言被证明是假的。性能相同。

财务报表概述

正如上一篇文章中所讨论的，财务会计是会计领域中与业务相关的财务交易的总结、分析和报告。这包括为公开使用准备财务报表。

财务报表（也称财务报告）是企业、个人或其他实体的财务活动和财务状况的正式记录。相关财务信息以结构化的形式呈现，并且易于理解。它们通常包括四个基本财务报表，以及管理层讨论和分析。

• 资产负债表（也称财务状况表）报告公司在特定时间点的资产、负债和所有者权益。（美国样本，英国样本，法国样本，立陶宛样本）
• 利润表（也称损益表、P&L报表、综合收益表、收入和费用报表）报告公司在规定时期内的收入、费用和利润。损益表提供了企业经营的信息。这包括销售额以及在规定时期内产生的各种费用。（美国样本，英国样本，法国样本，立陶宛样本）
• 所有者权益变动表（也称股东权益变动表、留存收益表）报告公司在规定时期内的所有者权益变动。（美国样本，英国样本，法国样本，立陶宛样本）
• 现金流量表报告公司在规定时期内的现金流量活动，特别是其经营、投资和融资活动。（美国样本，英国样本，法国样本，立陶宛样本）
• 财务报表附注包括一组详尽的财务报表附注以及管理层讨论与分析。附注通常更详细地描述资产负债表、利润表和现金流量表中的每个项目。

所有这些报告（除附注外）都有一定的格式（结构），由法律或会计准则设定。不同公司类型（按规模和法律形式，更不用说不同的司法管辖区）的格式（结构）不同。当相应的法律或会计准则发生变化时（它们确实会变化），格式（结构）也会发生变化。因此，所有这些报告格式（结构）都应以允许用户（会计师）设置其结构的方式实现。

在接下来的章节中，我们将逐一实现财务报表。我们将暂时跳过财务报表附注，因为它们需要分析特定的会计业务类型（固定资产、存货以及可能的其他业务）才能准备。

资产负债表和利润表

让我们从设计资产负债表和利润表功能开始。它们具有以下（基本）结构：

到目前为止，这应该看起来很熟悉。是的，这是会计科目表的层次视图。有些人会争辩说这实际上就是会计科目表，但有一些细微差别。如前所述，在会计科目表中，父账户和子账户都可以同时使用（借记或贷记）。而利润表和资产负债表是严格分层的：父行应始终包含所有子行的总和。另一个区别在于设定会计科目表和财务报表的权威机构。公司本身设定会计科目表。当然，有一些指导方针甚至模型账户应该被遵守，但最终决定权始终在于公司。另一方面，财务报表（结构、格式）由国家设定。这些差异意味着利润表和资产负债表结构应分开存储，并根据以下规则与账户相关联：

• 一个账户只能与资产负债表或利润表的一行相关联。否则，我们将获得资产、所有者权益或净收入的重复值。
• 所有账户都应与资产负债表或利润表的某一行相关联。否则，我们将无法遵守基本会计方程。另一方面，有时一些或所有账户暂时不与任何资产负债表或利润表的行相关联。例如，国家（政府）更改了强制性的财务报表结构，会计师必须在应用程序中重新构建它。重新构建结构后，一些行被删除。因此，相应的账户将失去关系。会计师必须转到会计科目表，并将新行重新分配给所有账户。因此，我们不能在账户关系（外键）上使用NOT NULL约束。遵守此规则由会计师负责。在这种情况下，这并不关键，因为该关系仅在获取财务报表时使用，这使得应用程序易于在请求时检查此规则。
• 只有当行没有子行时，账户才能分配给资产负债表或利润表的行。这一点很难在数据库中得到保证。乍一看，您可以添加一个检查约束来约束外键。然而，如果会计师修改了资产负债表或利润表的结构，并在以前没有子行的行上添加了一些子行，那么这将无济于事。即使您添加了对资产负债表或利润表行的检查约束，它最多只能做的是将相关的账户外键设置为null，这会带我们回到前面讨论的规则和相同的解决方案。由于该关系仅在获取财务报表时使用，因此应用程序应在执行实际获取之前检查此规则。

接下来要考虑的是获取资产负债表和利润表值的方法。

• 资产负债表行的值等于在请求期间结束日期与该行相关的所有账户余额的总和，即考虑所有先前期间。
• 利润表行等于在请求的利润表期间与该行相关的所有账户余额的总和，即不考虑任何先前期间。

所有这些值都可以从用于总账性能基准测试的账户级试算平衡表查询中获得。然而，为了计算和分配适当的值，应用程序需要知道该行属于资产负债表还是利润表，即我们需要存储行类型。我们还需要知道如何显示借贷余额。资产负债表和利润表都没有借贷的概念，只有一个纯数字。因此，我们需要存储一个指示符，是应将借方余额显示为正数，还是相反。

有了所有这些字段，我们就有足够的数据来获取资产负债表和利润表，并可以继续进行实际的SQL模式。

资产负债表和利润表的结构是分层的，显然，项目的顺序很重要。一个资产负债表或利润表行只能有一个父行。资产负债表和利润表的结构很少改变，但它们被查询的频率相对较高。此外，所有的资产负债表和利润表结构总是作为一个整体一起编辑。这种树的SQL模型非常适合嵌套集模型。

正如您很快会看到的，树模型的主要要求是能够以最简单的方式获取子节点。在嵌套集模型中，这就像检查行的左索引是否在父行的左索引和右索引之间（SELECT * FROM nested_set_model AS ParentNode LEFT JOIN nested_set_model AS ChildNode ON ChildNode.left_index BETWEEN ParentNode.left_index AND ParentNode.right_index）。闭包表模型和Joe Celko的修改后邻接列表模型需要额外的表，因此需要额外的连接，这显然是一个更复杂的解决方案。邻接列表模型需要递归查询或自定义函数，由于上一篇文章中讨论的简单技术要求，这不成问题。更不用说它比嵌套集实现复杂得多。路径枚举（物化路径）模型可以像嵌套模型一样执行子节点选择（连接）。然而，路径枚举模型需要复杂的插入，这使其更加复杂。因此，我将坚持使用嵌套集模型，因为它最适合这项任务。

总而言之，资产负债表和利润表功能的最终SQL模式（相关部分）是：

有了这个模式，我们现在可以以方便树对象结构的方式获取资产负债表和利润表的结构数据（例如，我当前会计解决方案中当前的资产负债表和利润表实现）。

SELECT node.id, node.line_type, (COUNT(parent.id) - 1) AS depth, _
node.visible_index, node.printed_no, node.line_text, node.line_type, node.left_index, _
node.right_index,
(SELECT COUNT(*) FROM accounts a WHERE a.balance_and_income_line_id = node.id) AS AccountsCount
FROM balance_and_income_lines AS parent
LEFT JOIN balance_and_income_lines AS node ON node.left_index _
        BETWEEN parent.left_index AND parent.right_index
GROUP BY node.id ORDER BY node.left_index;

分配的账目数量仅用于改善用户体验——警告用户他将要删除的行已关联一些账户。

接下来，有了这个模式，我们现在可以获取用户请求期间的实际资产负债表和利润表，例如，第一期为2017-01-01至2017-12-31，第二期为2018-01-01至2018-12-31。

SELECT line.printed_no as RowNo, line.line_text AS RowText,
(SELECT GROUP_CONCAT(CAST(a.id AS CHAR) SEPARATOR ', ') _
FROM accounts a WHERE a.balance_and_income_line_id = line.ID) AS RelatedAccounts,
SUM(CASE WHEN line.line_type <> 'balance_line' _
AND line.line_type <> 'income_line' THEN null ELSE
(CASE WHEN line.line_type = 'balance_line' _
THEN val.TotalDebitBefore - val.TotalCreditBefore + val.DebitFirstPeriod - _
val.CreditFirstPeriod ELSE (val.DebitFirstPeriod - val.ClosingDebitFirstPeriod)
- (val.CreditFirstPeriod - val.ClosingCreditFirstPeriod) END) END)
* (CASE WHEN line.value_type='D' THEN 1 ELSE -1 END) AS ValueFirstPeriod,
SUM(CASE WHEN line.line_type <> 'balance_line' _
AND line.line_type <> 'income_line' THEN null ELSE
(CASE WHEN line.line_type = 'balance_line' _
THEN val.TotalDebitBefore - val.TotalCreditBefore + val.DebitFirstPeriod - _
val.CreditFirstPeriod + val.DebitSecondPeriod - val.CreditSecondPeriod _
ELSE (val.DebitSecondPeriod - val.ClosingDebitSecondPeriod)
- (val.CreditSecondPeriod - val.ClosingCreditSecondPeriod) END) END)
* (CASE WHEN line.value_type='D' THEN 1 ELSE -1 END) AS ValueSecondPeriod
FROM balance_and_income_lines AS line
LEFT JOIN balance_and_income_lines AS child_line ON child_line.left_index _
BETWEEN line.left_index AND line.right_index
LEFT JOIN (
SELECT a.balance_and_income_line_id AS LineId, l.line_type AS LineType,
SUM(CASE WHEN t.transaction_date < '2017-01-01' _
AND e.entry_type='D' THEN e.amount ELSE 0.0 END) AS TotalDebitBefore,
SUM(CASE WHEN t.transaction_date < '2017-01-01' _
AND e.entry_type='C' THEN e.amount ELSE 0.0 END) AS TotalCreditBefore,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='D' _
THEN e.amount ELSE 0.0 END) AS DebitFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='C' _
THEN e.amount ELSE 0.0 END) AS CreditFirstPeriod,
SUM(CASE WHEN d.document_type = 13 AND t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='D' _
THEN e.amount ELSE 0.0 END) AS ClosingDebitFirstPeriod,
SUM(CASE WHEN d.document_type = 13 AND t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='C' _
THEN e.amount ELSE 0.0 END) AS ClosingCreditFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='D' _
THEN e.amount ELSE 0.0 END) AS DebitSecondPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='C' _
THEN e.amount ELSE 0.0 END) AS CreditSecondPeriod,
SUM(CASE WHEN d.document_type = 13 AND t.transaction_date >= '2018-01-01' _
AND e.entry_type='D' THEN e.amount ELSE 0.0 END) AS ClosingDebitSecondPeriod,
SUM(CASE WHEN d.document_type = 13 AND t.transaction_date >= '2018-01-01' _
AND e.entry_type='C' THEN e.amount ELSE 0.0 END) AS ClosingCreditSecondPeriod
FROM documents d
LEFT JOIN transactions t ON t.document_id = d.id
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
LEFT JOIN balance_and_income_lines l ON l.id = a.balance_and_income_line_id
WHERE t.transaction_date <='2018-12-31' GROUP BY a.balance_and_income_line_id
) AS val ON val.LineId = child_line.id OR (child_line.id=14 AND val.LineType = 'income_line')
GROUP BY line.id ORDER BY line.visible_index;

查询看起来有点庞大，但实际上它非常简单且速度快（在包含一百万笔交易的测试数据库上仅花费了20秒）。

• 繁重的工作由最后一个join中的子查询完成。该子查询获取用户请求期间的总借方和贷方账目周转额，并按资产负债表或利润表行进行分组。查询结果是一个小型虚拟表，最多包含几百行（因为资产负债表和利润表最多有几百行）。查询结果包含计算直接分配给账目条目的资产负债表或利润表行值所需的所有信息。如前所述，期间末的资产负债表行值等于期间末借方和贷方总条目数，而期间末的利润表行值等于期间内借方和贷方总条目数。因此，第一期的资产负债表行值等于TotalDebitBefore – TotalCreditBefore + DebitFirstPeriod - CreditFirstPeriod；第一期的利润表行值等于DebitFirstPeriod - CreditFirstPeriod。然而，利润表行实际上有一些修正分量。修正来自一个特殊的会计业务，称为期末结账。该业务每年（通常是日历年）执行一次，并有效地将所有利润表账户（收入和费用）归零，即该业务借记或贷记账户余额，使其最终余额为零。当然，会计师不喜欢看到所有空利润表。因此，我们需要反转该业务所做的条目。您应该注意到，结果值是借方余额，而在某些行上应将贷方余额显示为正数（例如，收入行）。为了解决这个问题，如果该行预期有贷方余额，我们就将值乘以负一。第二期也适用相同的方法。
• 接下来，我们需要处理那些应该汇总其子行值的聚合行。为此，我们使用自连接——对于每个资产负债表或利润表行，我们连接包括该行本身在内的所有子行。在嵌套集模型中，这是一项简单的任务——如果一行（的左索引）落在另一行（的左索引和右索引）之间，则第一行是第二行的子行。在这里，我们连接了我们之前的子查询结果集，该结果集包含每个资产负债表或利润表行的实际值。按父行对结果进行分组，您就可以得到实际的资产负债表和利润表。
• 最后要讨论的是子查询结果的奇怪连接条件：
  ON val.LineId = child_line.id OR (child_line.id=14 AND val.LineType = 'income_line')
  第一部分很简单——我们连接子行的余额。第二部分是为了处理期末结账的模拟。如本文前面所述，借贷余额仅对所有账户的总和有效：资产、所有者权益、负债、收入和费用。然而，在处理资产负债表和利润表时，我们将账户分为两部分：资产、所有者权益和负债计入资产负债表，而收入和费用计入利润表。反过来，这意味着资产负债表将不可避免地“不平衡”，即资产总额将不等于所有者权益和负债总额。在某些情况下，这是一个期望的结果，因为它揭示了没有期末结账的事实。如果您希望“按原样”获取余额，只需删除连接条件的第二部分。但是，在大多数实际情况下，会计师希望看到有效的资产负债表，就像在进行了期末结账之后一样。为了满足期望，我们需要模拟期末结账，即，将收入和费用的总余额移至所有者权益账户，通常标记为留存收益账户。在查询方面，这意味着我们需要识别持有留存收益账户值的余额行，并添加收入和费用的总余额。由于所有收入和费用账户最终都属于利润表行，因此我们可以轻松区分所需的余额，通过line_type字段。就是这样——我们只是模拟了期末结账。目前，持有留存收益账户值的余额行已硬编码到查询中。以后，留存收益账户将存储在公司简介中，这将使我们能够获取相关余额行的ID。

在实际场景中，资产负债表和利润表通常与账户级别的余额一起获取，该余额使用相同的子查询，但按账户分组而不是按资产负债表或利润表行分组。由于繁重的工作是由子查询完成的，因此为账户级别余额和资产负债表及利润表执行单独的查询基本上使数据库的工作量加倍。因此，在应用程序层面，我选择了只获取账户级别的余额，并在应用程序内部进行资产负债表和利润表的转换。这种方法几乎是使用两个繁重查询的两倍快。

现金流量表

现金流量表是企业发布的财务报表之一，它描述了组织内外的现金流动。它通过显示公司内部资金的流动情况，充当利润表和资产负债表之间的桥梁。

现金流量表中分为以下三个方面：

• 经营活动。这些构成企业的创收活动。经营活动的例子包括因产品销售、特许权使用费、佣金、罚款、诉讼、供应商和贷方发票以及工资而收付的现金。
• 投资活动。这些构成购买长期资产的付款以及出售这些资产获得的现金。投资活动的例子是购买固定资产以及购买或出售其他实体发行的证券。
• 融资活动。这些构成改变企业所有者权益或借款的活动。例子是出售公司股票、回购股票以及股息支付。

有两种呈现现金流量表的方法——直接法和间接法。

直接法本质上是对单独的现金业务的简单分类。因此，它无法从账目中推导出来。您可以在账目本身中添加用于现金流量表的现金业务分类。然而，这将给会计师带来大量工作——他必须对每笔现金交易进行分类。作为副作用，这将使得从电子银行、POS和其他类似系统自动导入现金业务成为不可能。显然，对于中小型企业来说，这有点过于复杂了。因此，我将不直接使用直接法来实现现金流量表。是的，根据公认会计准则，可以通过间接方式使用直接法呈现现金流量表。然而，走从间接法到直接法需要会计科目表以特定顺序构建以收集不同类型的信息，例如，所有应付账款（应交增值税、应付账款等）的结构应与收入账户相同，这在实际情况中很少见。在我们的例子中，我们不能要求公司绘制特定的会计科目表来支持通过间接方式呈现现金流量表。因此，我们也将不使用直接法来实现现金流量表。

值得注意的是，尽管标准制定机构鼓励使用直接法，但它很少使用，原因很简单，因为它难以收集信息；公司根本不以所需格式收集和存储信息。因此，我们的应用程序不会因为不使用直接法来实现现金流量表而落后于其他会计应用程序。

间接法基于对净利润的修改，净利润是利润表的结果，使用以下（近似）规则：

• 当资产（除现金外）增加时，现金账户减少。
• 当资产（除现金外）减少时，现金账户增加。
• 当负债增加时，现金账户增加。
• 当负债减少时，现金账户减少。
• 当所有者权益增加时，现金账户增加。
• 当所有者权益减少时，现金账户减少。

现金流量表中的现金流等于：

(净利润 + 非现金费用 – 非现金收入)

– (Δ 资产 – Δ 现金 + Δ 折旧与摊销 – Δ 重估 – Δ 应计收入)

+ (Δ 负债 – Δ 应计费用)

+ (Δ 所有者权益 - Δ 重估)

基本会计方程告诉我们：

Δ负债 + Δ所有者权益 + Δ收入 – Δ费用 – Δ资产 = 0

因此，我们可以从现金流量公式中消除这些项。结果公式是：

(非现金费用 – 非现金收入)

– (– Δ 现金 + Δ 折旧与摊销 – Δ 重估 – Δ 应计收入)

+ (– Δ 应计费用)

+ (- Δ 重估)

=

非现金费用 – 非现金收入 + Δ 现金 - Δ 折旧与摊销 + Δ 应计收入 – Δ 应计费用

由于非现金费用实际上是折旧与摊销加上应计费用，非现金收入实际上是应计收入，该方程变得微不足道：

现金流量 = Δ 现金

因此，如果我们唯一需要的是最终一行，我们只需检查总现金账户余额的变化。然而，细节决定成败。现金流量表不是关于最终一行（这是显而易见的），而是关于显示从利润表和资产负债表到实际现金的转换方式，即现金流量表以最展开的方式表示方程。

让我们看看如何逐行计算现金流量表（详细示例请参见现金流量表（解释），如何使用间接法准备现金流量表）。

• 净利润可以通过计算所有收入和费用账户的总贷方余额来得出。我们可以通过汇总所有类型为income_line的行，从资产负债表和利润表查询中获得此信息。
• 非现金费用和收入可以通过将相关的收入和费用账户与代表这些费用的现金流量表行相关联来计算，例如，折旧费用账户。在这种情况下，应将借方余额变动加到净利润中。
• 资产价值变动可以通过将资产成本账户与代表资产价值变动的现金流量表行相关联来计算；资产的折旧、摊销和重估以及应计收入在特殊账户中核算，这些账户可以被简单地忽略（不分配给任何现金流量表行），从而有效地从资产价值变动中消除其值。资产的借方余额变动意味着资产增加，而要获得更多资产，您需要花费一些现金。因此，应将借方余额变动从净利润中减去。
• 负债价值变动可以通过将负债账户与代表负债变动的现金流量表行相关联来计算；应计费用在特殊账户中核算，这些账户可以被简单地忽略（不分配给任何现金流量表行），从而有效地从负债价值变动中消除其值。负债的借方余额变动意味着负债减少，而要减少负债，您需要花费一些现金。因此，应将借方余额变动从净利润中减去。
• 所有者权益价值变动可以通过将所有者权益账户与代表所有者权益价值变动的现金流量表行相关联来计算；重估在特殊的资本账户中核算，这些账户可以被简单地忽略（不分配给任何现金流量表行），从而有效地从所有者权益价值变动中消除其值。计算所有者权益价值变动时，应忽略期末结账业务（就像利润表一样），因为它将净利润作为留存收益加到所有者权益中，而这不影响现金。所有者权益的借方余额变动意味着所有者权益减少，这反过来意味着公司减少了其名义资本或支付了股息，即向其所有者支付了资金。因此，应将借方余额变动从净利润中减去。

由于所需的分类细节，现金流量表的呈现方式还有一些更棘手的问题。

• 将收入和费用分组到经营活动、投资活动和融资活动中。例如，公司收到其他公司的股息；如果这些收入被归类为投资活动，则收入金额应从经营活动（现金流量表中的一行）中减去，并加到投资活动（现金流量表的另一行）中。这给我们带来了为一笔账户分配两个现金流量表行的要求（可能性）。在这种情况下，经营活动行中的借方余额变动将增加收入；经营活动行中的借方余额变动将减少收入。
• 固定资产购置和销售的独立现金流量表行。这导致为一笔账户分配两个现金流量表行的相同要求。但是，在这种情况下，一行现金流量表应只考虑借方余额变动（并忽略贷方变动），而另一行现金流量表应只考虑贷方余额变动（并忽略借方变动）。这给我们带来了指定所需余额类型的要求，即部分余额。
• 应报告期初和期末的现金及现金等价物总额。这给我们带来了指定另一种所需余额类型的要求，即期末余额，而不是像其他报表行中使用的那样按期计算余额。

简单，但好得不像真的。不幸的是（对于开发者来说），公认会计准则要求排除许多非现金交易。例如，公司以对公司的未结债务为代价获得某些固定资产。如果我们像上面描述的那样呈现现金流量表，债务的贷方变动将与固定资产的借方变动相抵消，即不会影响最终结果。然而，此类操作不涉及任何现金，因此根据公认会计准则的要求，应将其排除在现金流量表之外。在某些情况下，此类非现金交易甚至可能使现金流量表无效。例如，我们不考虑固定资产重估账户的余额变动；然而，重估结果可用于增加公司名义资本。如果我们像上面描述的那样处理所有者权益的增加，我们将使报表无效，显示所有者权益的增加（a）“凭空”出现，以及（b）与现金无关。为了进一步使问题复杂化，您甚至无法通过凭证类型来区分非现金交易。例如，固定资产销售（或交换）被形式化为发票，发票通常是现金凭证，更不用说一张发票可能包含一些现金项目和一些非现金项目。

底线——我们无法仅使用总账功能来呈现正确的现金流量表。个别凭证类型的功能也无济于事；同一凭证（类型）在不同时间可以是现金、非现金或两者的混合。要呈现正确的现金流量表，我们需要深入了解业务交易、它们之间的相互关系以及背景。然而，在这个复杂的情况下，有一点是好的：需要对现金流量表进行特殊调整的非现金交易相对较少。因此，手动进行调整不会造成不合理的努力。实际上，这样我们就遵循了会计师手动编制现金流量表的方法：首先使用总账数据（换句话说——资产负债表和利润表）创建初步报表，然后添加无法从账目数据中推导出的调整。

在我们的数据模型中，我们使用上述方法呈现初步现金流量表，即根据账户类型的特定规则，使用账目余额设置现金流量表行的值。因此，调整应该以某种方式调整账目余额。改变账户余额的常见方法是借记或贷记它。例如，如果一笔交易产生不期望的借方条目，我们就添加一个相同金额的贷方条目，反之亦然。然而，我们不能为此目的使用总账，因为在这种情况下，调整只应影响现金流量表，即调整不是财务交易（不改变公司的财务状况）。因此，解决方案是添加一个新的数据实体（数据库表）——cash_flow_adjustments——它具有与ledger_entries几乎相同的字段：id, account id, entry type（借方/贷方）和amount。为了识别现金流量调整的（候选）父项，让我们考虑以下示例（用例）：

正如您所见，所做的调整使我们能够在任何期间内呈现正确的现金流量表。如果期间跳过了完整销售周期的中间，它将不会显示由于销售而导致的现金流量增加。如果期间涵盖了整个销售周期，它将显示由于销售而导致的现金流量增加。如果存在部分付款，则现金流量表在任何中间期间都将保持正确。

值得注意的是，现金流量调整总是调整某些账目交易。调整永远不会“凭空”发生，因为要调整某物，您应该有要调整的对象。从基本会计方程可以得出，如果方程的其他部分保持不变，现金永远不会改变。因此，虽然有点违反直觉，但现金流量调整的父实体是一笔交易（该交易改变了基本会计方程的某些部分/变量，但由于某种业务背景而未影响现金部分）。

总而言之，我们对现金流量表模型有以下要求：

• 现金流量表行应具有以下值类型：借方和贷方。它定义了在报表行中哪种类型的余额被视为正值。
• 现金流量表行应具有以下余额类型：完整、按期、仅借方和仅贷方。它定义了在计算余额时包含哪种类型的账目周转。
• 现金流量表行应具有标志is_net_income。显然，报表中只有一个行可以将其标志设置为true（“1”）。它定义了一个完全不同的算法（子查询）应用于获取值。
• 现金流量表是一个分层文档，类似于利润表。因此，我们使用嵌套集模型，即添加技术字段——left_index, right_index, visible_index和line_type——它们与利润表的用途相同。
• 会计科目表中的每个账户应最多能关联两个现金流量表行（0…2）。这导致了多对多关系和一个技术表cash_flow_line_assignments。
• 需要一种特殊类型的实体——cash_flow_adjustments——它允许会计师为非现金交易添加调整。

由此产生的模式（相关片段）是：

有了这个模式，我们现在可以像为资产负债表和利润表一样，获取现金流量表的结构数据。

接下来，我们现在可以获取用户请求期间的实际现金流量表，例如，第一期为2017-01-01至2017-12-31，第二期为2018-01-01至2018-12-31。

SELECT line.printed_no as RowNo, line.line_text AS RowText,
SUM(
CASE WHEN line.line_type<>'line' THEN NULL
WHEN line.is_net_income > 0 THEN
(SELECT SUM(CASE WHEN e.entry_type='D' THEN -e.amount ELSE e.amount END)
FROM documents d
LEFT JOIN transactions t ON t.document_id = d.id
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
LEFT JOIN balance_and_income_lines l ON l.id = a.balance_and_income_line_id
WHERE t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01'
AND d.document_type<>13 AND l.line_type='income_line')
ELSE
(CASE WHEN child_line.id IS NULL THEN
(CASE line.balance_type
WHEN 'total' THEN val.TotalDebitBefore - val.TotalCreditBefore + _
                  val.DebitFirstPeriod - val.CreditFirstPeriod
WHEN 'per_period' THEN val.DebitFirstPeriod - val.CreditFirstPeriod
WHEN 'D' THEN val.DebitFirstPeriod
ELSE - val.CreditFirstPeriod
END) * (CASE WHEN line.value_type='D' THEN 1 ELSE -1 END)
ELSE
(CASE child_line.balance_type
WHEN 'total' THEN val.TotalDebitBefore - val.TotalCreditBefore + _
                  val.DebitFirstPeriod - val.CreditFirstPeriod
WHEN 'per_period' THEN val.DebitFirstPeriod - val.CreditFirstPeriod
WHEN 'D' THEN val.DebitFirstPeriod
ELSE - val.CreditFirstPeriod
END) * (CASE WHEN child_line.value_type='D' THEN 1 ELSE -1 END)
END)
END) AS FirstPeriodValue,
SUM(
CASE WHEN line.line_type<>'line' THEN NULL
WHEN line.is_net_income > 0 THEN
(SELECT SUM(CASE WHEN e.entry_type='D' THEN -e.amount ELSE e.amount END)
FROM documents d
LEFT JOIN transactions t ON t.document_id = d.id
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
LEFT JOIN balance_and_income_lines l ON l.id = a.balance_and_income_line_id
WHERE t.transaction_date >= '2018-01-01' AND t.transaction_date <= '2018-12-31'
AND d.document_type<>13 AND l.line_type='income_line')
ELSE
(CASE WHEN child_line.id IS NULL THEN
(CASE line.balance_type
WHEN 'total' THEN val.TotalDebitBefore - val.TotalCreditBefore + val.DebitFirstPeriod
- val.CreditFirstPeriod + val.DebitSecondPeriod - CreditSecondPeriod
WHEN 'per_period' THEN val.DebitSecondPeriod - val.CreditSecondPeriod
WHEN 'D' THEN val.DebitSecondPeriod
ELSE - val.CreditSecondPeriod
END) * (CASE WHEN line.value_type='D' THEN 1 ELSE -1 END)
ELSE
(CASE child_line.balance_type
WHEN 'total' THEN val.TotalDebitBefore - val.TotalCreditBefore + val.DebitFirstPeriod
- val.CreditFirstPeriod + val.DebitSecondPeriod - CreditSecondPeriod
WHEN 'per_period' THEN val.DebitSecondPeriod - val.CreditSecondPeriod
WHEN 'D' THEN val.DebitSecondPeriod
ELSE - val.CreditSecondPeriod
END) * (CASE WHEN child_line.value_type='D' THEN 1 ELSE -1 END)
END)
END) AS SecondPeriodValue
FROM cash_flow_lines AS line
LEFT JOIN cash_flow_lines AS child_line ON child_line.left_index _
                          BETWEEN line.left_index AND line.right_index
LEFT JOIN (
SELECT c.cash_flow_line_id AS LineId,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='D' _
         THEN e.amount ELSE 0.0 END) AS TotalDebitBefore,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='C' _
         THEN e.amount ELSE 0.0 END) AS TotalCreditBefore,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01'
AND e.entry_type='D' AND d.document_type <> 13 THEN e.amount ELSE 0.0 END) AS DebitFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01'
AND e.entry_type='C' AND d.document_type <> 13 _
                     THEN e.amount ELSE 0.0 END) AS CreditFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='D'
AND d.document_type <> 13 THEN e.amount ELSE 0.0 END) AS DebitSecondPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='C'
AND d.document_type <> 13 THEN e.amount ELSE 0.0 END) AS CreditSecondPeriod
FROM documents d
LEFT JOIN transactions t ON t.document_id = d.id
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN cash_flow_line_assignments c ON c.account_id = e.account_id
WHERE t.transaction_date <='2018-12-31' AND NOT c.id IS NULL GROUP BY c.cash_flow_line_id
UNION ALL
SELECT c.cash_flow_line_id AS LineId,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='D' _
         THEN e.amount ELSE 0.0 END) AS TotalDebitBefore,
SUM(CASE WHEN t.transaction_date < '2017-01-01' AND e.entry_type='C' _
         THEN e.amount ELSE 0.0 END) AS TotalCreditBefore,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01'
AND e.entry_type='D' THEN e.amount ELSE 0.0 END) AS DebitFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND t.transaction_date < '2018-01-01'
AND e.entry_type='C' THEN e.amount ELSE 0.0 END) AS CreditFirstPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='D'
THEN e.amount ELSE 0.0 END) AS DebitSecondPeriod,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='C'
THEN e.amount ELSE 0.0 END) AS CreditSecondPeriod
FROM transactions t
LEFT JOIN cash_flow_adjustments e ON e.transaction_id = t.id
LEFT JOIN cash_flow_line_assignments c ON c.account_id = e.account_id
WHERE t.transaction_date <='2018-12-31' AND NOT c.id IS NULL GROUP BY c.cash_flow_line_id
) AS val ON val.LineId = child_line.id
GROUP BY line.id ORDER BY line.visible_index;

查询比我们用于资产负债表和利润表的查询更复杂。它也慢了近一倍，因为我们使用了两个繁重的子查询而不是一个。查询包含以下阶段：

• 首先，我们获取所需期间的账目周转额，并按现金流量表行进行分组。这与资产负债表和利润表基本相同，只是分组标准不同。唯一的显著区别是我们如何处理账目期末结账操作。对于现金流量呈现目的，期末结账操作应始终排除，我们不单独选择其值；相反，我们直接从账目周转额中排除它们。这是一个繁重的（子）查询，因为账目表很大。
• 接下来，我们获取所需期间的现金流量调整周转额，并按现金流量表行进行分组。这是一个相对较轻的（子）查询，因为现金流量调整相对较少，因此表的大小很小。
• 接下来，我们执行UNION ALL，因为这两种周转额对于现金流量表具有相同的含义。
• 接下来，我们执行父查询——以与我们为资产负债表和利润表所做的方式相同的方式获取现金流量表树。查询使用了前面查询获取的非常小的源集。因此，它非常快。
• 最后，我们使用前面子查询的聚合值来组装实际现金流量表行值。现金流量表的业务逻辑比资产负债表复杂得多。因此，我们有一个更复杂的CASE。第一个CASE选择用于报表行类型的算法。

1. 如果是标题行，则无需计算；
2. 如果是净利润行，我们使用特殊子查询获取净利润，如前所述；这是一个繁重的子查询，因为账目表很大。
3. 如果是其他报表行，我们使用前面子查询的聚合值。通用报表行的CASE选择用于父行和子行的算法。与资产负债表不同，现金流量表父行没有特定的余额类型。例如，经营性现金流量行获取净利润（贷方余额）并加上折旧（借方余额），这会增加总组的值。因此，对于父行，我们需要使用子行计算的值，而不管余额类型如何。
   • 如果是子行（没有子行的行），我们使用行设置（值类型和余额类型）来计算值；
   • 如果是父行（有子行的行），我们使用子行设置来计算值。

在实际场景中，财务报表（包括现金流量表）通常与账户级别的余额一起获取，后者使用相同的子查询，但按账户分组，而不是按资产负债表、损益表或现金流量表行分组。由于子查询承担了繁重的工作，为账户级别余额和资产负债表、损益表及现金流量表分别执行查询，实际上将数据库工作量增加了两倍，因为数据非常相似。因此，在应用程序级别，我选择只获取账户级别的余额，并在应用程序内部进行资产负债表、损益表和现金流量表的转换。这种方法比使用三个繁重的查询快至少三倍。

所有者权益变动表

本文中要实现的最后一个财务报表是所有者权益变动表。在《美国公认会计原则》中，所有者权益变动表通常被称为留存收益表，它通过展示构成股东权益的储备金的变动，详细说明了在一个会计期间内所有者权益的变化。

股东权益由以下组成部分构成：

• 股份面值
• 股份平价（当发行股份的认购/购买价格高于面值时）
• 库存股（公司自身持有的股份）
• 留存收益
• 重估储备
• 其他储备

这些组成部分（如有需要，可更详细地说明）始终反映在会计科目表中，即每个权益组成部分都有一个专用的账户。

一个会计期间内股东权益的变动包括以下要素（详情请参阅股东权益（解释））：

• 会计期间内的净利润或净亏损
• 股份数量或/和面值的增加或减少
• 储备金的增加或减少
• 支付给股东的股息
• 直接在权益中确认的损益
• 会计政策变更的影响
• 前期错误更正的影响

所有者权益变动表的目的是展示各种业务活动对权益组成部分所做的变动。因此，所有者权益变动表的结构与资产负债表、损益表和现金流量表根本不同。所有者权益变动表不是采用分层结构，而是采用矩阵结构，其中列代表权益组成部分，行代表权益变动（业务）的类型。所有者权益变动表的简化示例如下所示：

如您所见，行包含了一些按交易（凭证）类型（例如股息）和权益类型/账户（例如留存收益）分类的交易，这些交易按期间进一步分组，从而创建了矩阵的第三个维度。我们无法仅凭账簿数据对交易进行分类。我们也无法直接将交易（凭证）与所有者权益变动表的行关联起来，因为报表结构的变化将导致报表无法以一致的方式呈现。如果交易与多个不同的结构相关联，则无法使用单个报表结构来比较两个期间。因此，行只能与（源）凭证类型相关联。这将迫使我们实施细粒度的股权相关凭证类型。这（尤其是考虑到扩展）是可能的，（源）凭证类型的详细程度甚至对于所有者权益变动表来说过于详细。因此，有几种凭证类型可能会分配给单个所有者权益变动表行。

每个所有者权益变动表的列代表一种权益类型。如前所述，权益结构在会计科目表中体现。然而，会计科目表可能提供比所有者权益变动表所需更详细的结构。因此，每个所有者权益变动表的列可以分配给会计科目表中的一个或多个账户，但不能反之。

总而言之，为了实现所有者权益变动表，我们需要：

• 一个所有者权益变动表列列表，这些列与会计科目表中的一个或多个账户相关联。权益余额始终是贷方类型。因此，我们无需为此报表定义余额类型。
• 一个所有者权益变动表行（项目）列表，这些行与一个或多个（源）凭证类型相关联。这就引出了一个技术性`equity_line_assignments`。
• 正如您在示例中看到的，有七种所有者权益变动表行类型定义了报表的整体结构：`initial_balance`、`cumulative_delta`、`zero_balance`、`first_delta`、`first_balance`、`second_delta`、`second_balance`。

由此产生的模式（相关片段）是：

有了这个模式，我们现在就可以获取用户请求期间的实际所有者权益变动表了，例如，第一个期间为 2017-01-01 至 2017-12-31，第二个期间为 2018-01-01 至 2018-12-31。

SELECT l.printed_no AS LineNo, l.line_text AS LineText, _
            l.line_type AS LineType, v.ColumnText, v.ColumnIndex,
SUM(CASE line_type
WHEN 'initial_balance' THEN v.BalanceBefore
WHEN 'cumulative_delta' THEN v.CumulativeDelta
WHEN 'zero_balance' THEN v.BalanceBefore + v.CumulativeDelta
WHEN 'first_delta' THEN v.FirstPeriodDelta
WHEN 'first_balance' THEN v.BalanceBefore + v.CumulativeDelta + v.FirstPeriodDelta
WHEN 'second_delta' THEN v.SecondPeriodDelta
ELSE v.BalanceBefore + v.CumulativeDelta + v.FirstPeriodDelta + v.SecondPeriodDelta END) _
                        AS Value
FROM equity_lines l
LEFT JOIN (
SELECT c.id AS ColumnId, c.column_text AS ColumnText, _
               c.visible_index AS ColumnIndex, l.id AS LineId,
SUM(CASE WHEN t.transaction_date < '2017-01-01' _
AND e.entry_type='C' THEN e.amount ELSE 0.0 END)
- SUM(CASE WHEN t.transaction_date < '2017-01-01' _
AND e.entry_type='D' THEN e.amount ELSE 0.0 END) AS BalanceBefore,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='C' _
AND l.line_type = 'first_delta' THEN e.amount ELSE 0.0 END)
- SUM(CASE WHEN t.transaction_date >= '2017-01-01' _
AND t.transaction_date < '2018-01-01' AND e.entry_type='D' _
AND l.line_type = 'first_delta' THEN e.amount ELSE 0.0 END) AS FirstPeriodDelta,
SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='C' _
AND l.line_type = 'second_delta' THEN e.amount ELSE 0.0 END)
- SUM(CASE WHEN t.transaction_date >= '2018-01-01' AND e.entry_type='D' _
AND l.line_type = 'second_delta' THEN e.amount ELSE 0.0 END) AS SecondPeriodDelta,
SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND e.entry_type='C' _
AND l.line_type = 'cumulative_delta' THEN e.amount ELSE 0.0 END)
- SUM(CASE WHEN t.transaction_date >= '2017-01-01' AND e.entry_type='D' _
AND l.line_type = 'cumulative_delta' THEN e.amount ELSE 0.0 END) AS CumulativeDelta
FROM documents d
LEFT JOIN equity_line_assignments s ON d.document_type=s.document_type
AND (d.extended_document_type_id = s.extended_document_type_id
OR (d.extended_document_type_id IS NULL AND s.extended_document_type_id IS NULL))
LEFT JOIN equity_lines l ON l.id = s.equity_line_id
LEFT JOIN transactions t ON t.document_id = d.id
LEFT JOIN ledger_entries e ON e.transaction_id = t.id
LEFT JOIN accounts a ON a.id = e.account_id
LEFT JOIN equity_columns c ON c.id = a.equity_column_id
WHERE t.transaction_date <='2018-12-31' _
AND NOT l.id IS NULL AND NOT c.id IS NULL GROUP BY l.id, c.id
) AS v ON v.LineId=l.id OR l.line_type IN('initial_balance', _
'zero_balance', 'first_balance', 'second_balance')
GROUP BY l.id, v.ColumnId ORDER BY l.line_type, l.visible_index, v.ColumnIndex;

此查询比我们之前用于其他财务报表的查询要简单。它也很快，因为我们过滤掉了大量的（源）凭证和账簿账户。查询包含以下两个阶段：

• 首先，我们获取所需期间的账簿账户周转额，并按所有者权益变动表列和行进行分组。由于凭证类型始终与 0…1 个所有者权益变动表行相关联，因此按凭证类型和按所有者权益变动表行分组在功能上是等效的。
• 接下来，我们根据所有者权益变动表行的类型计算每个行和列的实际值。

该查询除其他外使用按凭证类型分组，这无法从账簿账户级别的余额推导出来。因此，我们将无法重用账簿账户级别的余额，而必须使用专用于所有者权益变动表的查询。

结论

在本文中，我们为核心会计基础设施开发了数据库模式：总账、会计科目表、（源）凭证和财务报表。

我们还证明了应用程序模式能够以可接受的性能处理一百万份凭证。这意味着该应用程序将适用于每年高达 100,000 次操作的公司。

下一篇文章将致力于完成会计基础设施的设计：公司简介、人员简介、成本中心、银行和现金账户以及所有专门会计凭证中使用的所有其他实体。

历史

• 2019年7月28日：初始版本
• 2019年8月9日：在适当的地方切换到 `VARCHAR` 类型