SQL会计系统中的层次汇总 [英] Hierarchical roll-up in SQL accounting system
问题描述
我正在尝试通过会计系统中的一般日记帐分录制作年度报告（资产负债表和损益表）。
I'm trying to make annual reports (Balance Sheet and Profit & Loss) from general journal entries in an accounting system.
普通日记帐表（已简化） ）包括：
The general journal table (simplified) includes:
CREATE TABLE `sa_general_journal` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Date` timestamp NOT NULL DEFAULT current_timestamp(),
`Item` varchar(1024) NOT NULL DEFAULT '',
`Amount` decimal(9,2) NOT NULL DEFAULT 0.00,
`Source` int(10) unsigned NOT NULL,
`Destination` int(10) unsigned NOT NULL,
PRIMARY KEY (`ID`),
KEY `Date` (`Date`),
KEY `Source` (`Source`),
KEY `Destination` (`Destination`),
CONSTRAINT `sa_credit-account` FOREIGN KEY (`Destination`) REFERENCES `sa_accounts` (`ID`),
CONSTRAINT `sa_debit-account` FOREIGN KEY (`Source`) REFERENCES `sa_accounts` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=21561 DEFAULT CHARSET=utf8;
其中通常 Amount （但不一定） ）未签名，并且是从来源帐户或类别转移到目的地类别的金额。
where Amount is typically (but not necessarily) unsigned, and is an amount that is transferred from the Source account or category to the Destination category.
会计科目表（简体）包括：
A chart of accounts (simplified) includes:
CREATE TABLE `sa_accounts` (
`ID` int(10) unsigned NOT NULL,
`Super` int(10) unsigned,
`Name` varchar(255) NOT NULL,
`Type` enum('Asset','Liability','Income','Expense'),
`Report` enum('BS','PL'), -- for "Balance Sheet" or "Profit & Loss"
PRIMARY KEY (`ID`),
KEY `Super` (`Super`),
CONSTRAINT `Super` FOREIGN KEY (`Super`) REFERENCES `sa_accounts` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中 ID 是一个七位整数在1,000,000到8,999,999之间，对于未分配的资金则单独输入零。
where ID is a seven-digit integer between 1,000,000 and 8,999,999, with a separate entry of zero for unallocated funds.
帐户 ID 在典型的GAAP编号帐户方案中，有1,000,000个是顶级帐户：
Account IDs that are divisible by 1,000,000 are "top level" accounts in a typical GAAP numbered-account scheme:
INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
VALUES
(0, NULL, "Not yet allocated", NULL, NULL),
(1000000, NULL, "Assets", "Asset", "BS"),
(2000000, NULL, "Liabilities", "Liability", "BS"),
(3000000, NULL, "Equity", "Liability", "BS"),
(4000000, NULL, "Income", "Income", "PL"),
(5000000, NULL, "Expenses", "Expense", "PL"),
(6000000, NULL, "Operating Expenses", "Expense", "PL"),
(7000000, NULL, "Other Expenses", "Expense", "PL"),
(8000000, NULL, "Other Income", "Income", "PL");
这些汇总帐户是抽象帐户，通常（但不一定）没有实际分配的任何内容给他们。相反，子帐户会收到实际的分配：
These roll-up accounts are abstract, and typically (but not necessarily) do not have anything actually allocated to them. Rather, sub-accounts receive actual allocations:
INSERT INTO sa_account (`ID`, `Super`, `Name`, `Type`, `Report`)
VALUES
(1010000, 1000000, "Cash", "Asset", "BS"),
(1010001, 1010000, "Cash", "Asset", "BS"),
(1010011, 1010000, "Chequing", "Asset", "BS"),
(1019999, 1010000, "Test bank account", "Asset", "BS"),
-- ...
(2100000, 2000000, "Accounts Payable", "Liability", "BS"),
(2050000, 2100000, "Lines of credit", "Liability", "BS"),
(2052008, 2050000, "Mastercard -2008", "Liability", "BS"),
(2054710, 2050000, "Visa -4710", "Liability", "BS"),
-- ...
(3200000, 3000000, "Shareholder Equity", "Liability", "BS"),
(3300000, 3000000, "Rent to own", "Liability", "BS"),
-- ...
(4050000, 4000000, "Dairy income", "Income", "PL"),
(4050001, 4050000, "Animals sold", "Income", "PL"),
(4050002, 4050000, "Milk sold", "Income", "PL"),
(4050003, 4050000, "Cheese sold", "Income", "PL"),
(4059999, 4050000, "Test income source", "Income", "PL"),
-- ...
(5050000, 5000000, "Dairy expense", "Expense", "PL"),
(5050001, 5000000, "Animals bought", "Expense", "PL"),
(5050002, 5000000, "Feed bought", "Expense", "PL"),
(5059999, 5000000, "Test expense destination", "Expense", "PL");
-- ...
这些子帐户是指（通过超级）到某个层次结构关系中的其他帐户。请注意，顶级帐户在 Super 列中为NULL。
These sub-accounts refer (via Super) to some other account in a hierarchical relationship. Note that the top-level accounts have NULL in the Super column.
所以这是一些测试常规日记帐分录：
So here's some test general journal entries:
INSERT INTO sa_general_journal (`ID`, `Date`, `Item`, `Amount`, `Source`, `Destination`)
VALUES (NULL, "2020-05-03", "Test income transaction", 10.10, 4059999, 1009999),
(NULL, "2020-05-03", "Test expense transaction", 1.01, 1009999, 5059999);
借助尼克，我能够来源和目的地的差来汇总普通日记帐分录code>帐户，使用以下代码：
With the help of Nick, I was able to use a Common Table Expression to sum up general journal entries by the difference of Source and Destination accounts, using the following code:
WITH CTE1 AS (
SELECT
Source AS account,
0 AS TYPE,
-Amount AS Amount
FROM sa_general_journal
UNION ALL
SELECT
Destination,
1,
Amount
FROM sa_general_journal gj
)
SELECT
acc.ID `Account`,
acc.Super,
acc.Name,
SUM(CASE WHEN CTE1.type = 0 THEN Amount END) AS Debits,
SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
SUM(Amount) AS Net
FROM CTE1
JOIN sa_accounts acc ON CTE1.account = acc.ID
-- WHERE acc.Report = "BS"
-- WHERE acc.Report = "PL"
GROUP BY acc.ID
到目前为止，太好了！
So far, so good! This was a huge help in my understanding of how Common Table Expressions can be used!
这对我理解通用表表达式的使用有很大帮助！但是现在，我想将子帐户汇总到抽象帐户中，得到与以下内容类似的预期结果：
But now, I want to "roll up" the sub-accounts into the abstract accounts, giving a desired result similar to this:
<table>
<th>ID</th><th>Name</th><th>Debits</th><th>Credits</th><th>Net</th><th></th><th></th></tr>
<tr><td>1000000</td><td>Cash</td><td>-1.01</td><td>10.10</td><td>9.09</td><td></td><td></td></tr>
<tr><td>1009999</td><td>Cash -> Test chequing account</td><td>-1.01</td><td>10.10</td><td></td><td></td><td>9.09</td></tr>
<tr><td>4000000</td><td>Income</td><td>-10.10</td><td><i>NULL</i></td><td>-10.10</td><td></td><td></td></tr>
<tr><td>4050000</td><td>Income -> Dairy Income</td><td>-10.10</td><td><i>NULL</i></td><td></td><td>-10.10</td><td></td></tr>
<tr><td>4059999</td><td>Income -> Dairy Income -> Test income transaction</td><td>-10.10</td><td><i>NULL</i></td><td></td><td></td><td>-10.10</td></tr>
<tr><td>5000000</td><td>Expenses</td><td>-10.10</td><td><i>NULL</i></td><td>-10.10</td><td></td><td></td></tr>
<tr><td>5050000</td><td>Expenses -> Dairy Expenses</td><td>-10.10</td><td><i>NULL</i></td><td></td><td>-10.10</td><td></td></tr>
<tr><td>5059999</td><td>Expenses -> Dairy Expenses -> Test expense transaction</td><i>NULL</i></td><td>1.01</td><td></td><td></td><td>1.01</td></tr>
</table>
经过几次错误的开始之后，我想到了一个简单的想法，就是在上面的代码周围简单地包裹一个WITH RECURSIVE，但是将具有相同的 Super 列：
After a few false starts, I came up with the following naive idea of simply wrapping a WITH RECURSIVE around the above code, but summing up the sub-accounts that have the same Super column:
WITH RECURSIVE CTE2 AS
(WITH CTE1 AS (
SELECT
Source AS account,
0 AS TYPE,
-Amount AS Amount
FROM sa_general_journal
UNION ALL
SELECT
Destination,
1,
Amount
FROM sa_general_journal gj
)
SELECT
acc.ID `Account`,
acc.Super,
acc.Name,
SUM(CASE WHEN CTE1.type = 0 THEN Amount END) AS Debits,
SUM(CASE WHEN CTE1.type = 1 THEN Amount END) AS Credits,
SUM(Amount) AS Net
FROM CTE1
JOIN sa_accounts acc ON CTE1.account = acc.ID
-- WHERE acc.Report = "BS"
-- WHERE acc.Report = "BS"
GROUP BY acc.ID
UNION ALL
SELECT
Name,
SUM(CTE2.Debits),
SUM(CTE2.Credits),
SUM(CTE2.Net)
FROM CTE2
WHERE CTE2.`Super` IS NOT NULL)
SELECT * FROM CTE2
我知道最后一个SELECT有问题。如我所说，这是我的第一次尝试，但是我似乎遇到了无法克服的障碍。
I'm aware the last SELECT has problems; as I said, this was my first attempt, but I appear to have run into an insurmountable roadblock.
在执行前面的代码时，我收到查询失败。表'CTE2违反了强加于递归定义的代码。错误代码4008。经过大量的搜索，才能确定在此类查询的递归部分中不允许使用聚合查询（SUM等）。
When the preceding code is executed, I get "Query Failed. Restrictions imposed on recursive definitions are violated for table 'CTE2. Error code 4008." It took quite a bit of searching to figure out that aggregate queries (SUM, etc.) are not allowed in the recursive part of such a query. Sigh.
我读到有了RECURSIVE，SQL就可以与Turing兼容，因此必须可以执行我要寻找的操作，但是没有SUM（）在递归查询中，很难想象如何解决这个问题！
I've read that WITH RECURSIVE, SQL becomes Turing-compatible, so it must be possible to do what I'm looking for, but without SUM() in a recursive query, it's hard to imagine how to solve this!
推荐答案
此查询应为您提供所需的结果。它基于上一个问题的答案，并添加了一个递归CTE，该CTE可将每个交易复制到层次结构中位于其上方的所有帐户。然后在最终查询中汇总每个帐户的值：
This query should give you the results you want. It is based on the answer to your previous question, with the addition of a recursive CTE that copies each transaction to all the accounts above it in the hierarchy. Values for each account are then summed in the final query:
WITH RECURSIVE xfers AS (
SELECT Source AS account,
0 AS TYPE,
-Amount AS Amount
FROM sa_general_journal
UNION ALL
SELECT Destination,
1,
Amount
FROM sa_general_journal gj
),
dbcr AS (
SELECT
acc.ID `Account`,
acc.Super,
acc.Name,
COALESCE(SUM(CASE WHEN x.type = 0 THEN Amount END), 0) AS Debits,
COALESCE(SUM(CASE WHEN x.type = 1 THEN Amount END), 0) AS Credits,
COALESCE(SUM(Amount), 0) AS Net
FROM sa_accounts acc
LEFT JOIN xfers x ON x.account = acc.ID
-- WHERE acc.Report = "BS"
-- WHERE acc.Report = "PL"
GROUP BY acc.ID
),
summary AS (
SELECT *
FROM dbcr
WHERE Net != 0
UNION ALL
SELECT d.Account, d.Super, d.Name, s.Debits, s.Credits, s.Net
FROM dbcr d
JOIN summary s ON d.Account = s.Super
WHERE s.Super IS NOT NULL
)
SELECT Account, Super, Name,
SUM(Debits) AS Debits,
SUM(Credits) AS Credits,
SUM(Net) AS Net
FROM summary
GROUP BY Account, Super, Name
ORDER BY Account
输出（用于我的扩展演示）：
Output (for my expanded demo):
Account Super Name Debits Credits Net
1000000 null Assets -6.31 10.1 3.79
1010000 1000000 Cash -6.31 10.1 3.79
1010011 1010000 Chequing -5.3 0 -5.3
1019999 1010000 Test bank account -1.01 10.1 9.09
4000000 null Income -10.1 0 -10.1
4050000 4000000 Dairy income -10.1 0 -10.1
4059999 4050000 Test income source -10.1 0 -10.1
5000000 null Expenses 0 6.31 6.31
5050002 5000000 Feed bought 0 5.3 5.3
5059999 5000000 Test expense dest 0 1.01 1.01
这篇关于SQL会计系统中的层次汇总的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
