跳转到主要内容

使用 SQL 进行查询

掌握 Beancount 查询语言（BQL）以进行高级财务数据分析。学习类似 SQL 的语法来查询和分析你的会计数据。

Beancount 拥有一种强大的、类似 SQL 的查询语言（BQL），让你能够精确地切分、筛选和分析财务数据。无论你是想生成快速报告、调试条目，还是执行复杂分析，掌握 BQL 都是充分发挥纯文本会计账本潜力的关键。本指南将带你了解其结构、函数和最佳实践。🔍

beancount.io BQL 查询编辑器在浏览器中对 Beancount 账本运行类似 SQL 的查询

探索实时账本 →

查询结构和执行​

BQL 的核心是其熟悉的、受 SQL 启发的语法。使用 bea query 运行查询：bea --file <账本> query "SELECT …" 会在终端中打印表格，而 bea query 不带参数则会打开交互式 shell。本指南中的每个查询都是在 Beancount 3.2.3 和 beanquery 0.2.0 上执行的。

基本查询格式​

一个 BQL 查询由三个主要子句组成：SELECT、FROM 和 WHERE。

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT：指定你想要检索的数据列。
  • FROM：在处理之前过滤整个交易。
  • WHERE：在交易被选中之后过滤单独的过账行。

两级过滤系统​

理解 FROM 和 WHERE 子句之间的区别对于编写准确的查询至关重要。BQL 使用两级过滤过程。

  1. 交易级别（FROM） 此子句作用于整个交易。如果一笔交易匹配 FROM 条件，则整个交易（包括其所有过账）将传递给下一阶段。这是过滤数据的主要方式，因为它保持了复式记账系统的完整性。例如，过滤 FROM year = 2024 会选中所有发生在 2024 年的交易。

  2. 过账级别（WHERE） 此子句过滤 FROM 子句选中的交易内部的单个过账。这对于展示和关注交易的特定部分很有用。然而，请注意，在此级别过滤可能会在输出中“破坏”交易的完整性，因为你可能只会看到一笔分录的一侧。例如，你可以选择所有指向 Expenses:Groceries 账户的过账。

具体来说，PRINT FROM year = 2024 返回完整的交易（每笔分录的两条腿），而 SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" 则为每个匹配的过账返回一行。在一个有两笔券商购买的账本上，前者返回完整的分录，后者则恰好返回两行券商行。

数据模型​

要有效查询数据，你需要了解 Beancount 如何组织数据。账本是一个指令列表，但 BQL 主要关注 Transaction 条目。

交易结构​

每笔 Transaction 都是一个容器，具有顶级属性和一个 Posting 对象列表。

Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
    ├── account
    ├── units
    ├── cost
    ├── price
    └── metadata

可用的列类型​

你可以 SELECT 交易或其过账的任何属性。

  1. 交易属性 这些列在单笔交易内的每个过账中都是相同的。

    SELECT
    date,        -- The date of the transaction (datetime.date)
    year,        -- The year of the transaction (int)
    month,       -- The month of the transaction (int)
    day,         -- The day of the transaction (int)
    flag,        -- The transaction flag, e.g., "*" or "!" (str)
    payee,       -- The payee (str)
    narration,   -- The description or memo (str)
    tags,        -- A set of tags, e.g., #trip-2024 (set[str])
    links        -- A set of links, e.g., ^expense-report (set[str])

  2. 过账属性 这些列特定于每个单独的过账行。

    SELECT
    account,           -- The account name (str)
    position,          -- The full amount, including units and cost (Position)
    units(position),   -- The number and currency of the posting (Amount)
    cost(position),    -- The cost basis of the posting (Amount)
    price,             -- The price used in the posting (Amount)
    weight,            -- The position converted to its cost basis (Amount)
    balance            -- The running total of units in the account (Inventory)

    units 和 cost 是接受 position 的函数。price、weight 和 balance 是普通列。

查询函数​

BQL 包含一套用于聚合和数据转换的函数，非常类似于 SQL。

聚合函数​

聚合函数跨多行汇总数据。当与 GROUP BY 一起使用时，它们提供分组摘要。

-- Count the number of postings
SELECT COUNT(*)
 
-- Sum all postings into one Inventory; currencies and lots are kept, not converted
SELECT SUM(position)
-- one row, e.g. (-2300.00 USD, 10 HOOL {150.00 USD, 2024-09-05}, 5 HOOL {160.00 USD, 2024-11-02})
 
-- Total one account in a single currency explicitly (positions without a price keep their currency)
SELECT SUM(CONVERT(position, 'USD')) WHERE account ~ "Assets:Checking"
-- one row, e.g. (2580.00 USD)
 
-- Find the date of the first and last transaction
SELECT FIRST(date), LAST(date)
 
-- Find the minimum and maximum position values
SELECT MIN(position), MAX(position)
 
-- Group by account to get a sum for each
SELECT account, SUM(position) GROUP BY account

头寸/库存函数​

position 列是一个复合对象。这些函数让你能够提取其特定部分或计算其市场价值。

-- Extract just the number and currency from a position
SELECT UNITS(position)
 
-- Show the total cost of a position
SELECT COST(position)
 
-- Show each posting at its cost value (a column; there is no WEIGHT() function)
SELECT account, weight WHERE account ~ "Assets:Investments"
 
-- Calculate the market value using the latest price data
-- (needs a price directive for the holding; otherwise the position is returned unchanged)
SELECT VALUE(position)

你可以组合这些函数来制作强大的报告。例如，查看投资组合的总成本及其当前市场价值。市场价值需要每个持仓的价格指令（例如 2024-12-01 price HOOL 175.00 USD）。两个聚合都返回每个账户的 Inventory，因此每种货币仍然分别列出。

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account
-- one row per account, e.g. Assets:Broker:HOOL | (2300.00 USD) | (2625.00 USD)

高级功能​

除了基本的 SELECT 语句外，BQL 还提供了用于常见财务报告的专业命令。

余额报告​

BALANCES 语句为特定时期生成资产负债表或损益表。

-- Generate a simple balance sheet as of the start of 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- Generate an income statement for the 2024 fiscal year
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

日记账报告​

JOURNAL 语句显示一个或多个账户的详细活动，类似于传统的账本视图。

-- Show all activity in your checking account at its original cost
JOURNAL "Assets:Checking" AT COST
 
-- Show all 401k transactions, displaying only the units (shares)
JOURNAL "Assets:.*:401k" AT UNITS

打印操作​

PRINT 语句是一个调试工具，它以其原始 Beancount 文件格式输出完整的匹配交易。它只接受条目过滤器。在这里使用 WHERE 子句是语法错误。若要将输出缩小到每条分录的一条腿，请改用带过账过滤器的 SELECT。它为每个匹配的过账返回一行。

-- Print all 2024 transactions in full (every posting of each matching entry)
PRINT FROM year = 2024
 
-- Show only the investment postings of 2024 transactions
SELECT date, narration, account, position
FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- Find a transaction by its unique ID (generated by some tools)
-- Returns the matching entry, or no rows when nothing carries that ID
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

过滤表达式​

你可以使用逻辑运算符（AND、OR）、正则表达式（~）和比较来构建复杂的过滤器。

字符串字面量使用单引号。双引号界定 ~ 后的正则表达式。

-- Find all travel expenses from the second half of 2024
-- SELECT * returns date, flag, payee, narration and position per matching posting
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- Find all transactions related to a vacation or business
SELECT * FROM
    'vacation-2024' IN tags OR
    'business-trip' IN links

性能考量 ⚙️​

bea query 设计用于高效运行，但了解其操作流程可以帮助你在大型账本上编写更快的查询。

  1. 数据加载：Beancount 首先解析你的整个账本文件，并将所有交易按时间顺序排序。整个数据集保存在内存中。
  2. 查询优化：查询引擎按特定顺序应用过滤器以实现最大效率：FROM（交易） -> WHERE（过账） -> 聚合。在 FROM 级别进行过滤最快，因为它能及早减少数据集。
  3. 内存使用：所有操作都在内存中执行。Position 对象和 Inventory 聚合已优化，但非常大的结果集可能消耗大量 RAM。BQL 不使用基于磁盘的临时存储。

最佳实践​

遵循这些提示，编写干净、高效且可维护的查询。

  1. 查询组织 格式化你的查询以提高可读性，尤其是复杂查询。使用换行和缩进来分隔子句。

    -- A clean, readable query for all 2024 expenses
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

  2. 调试 如果查询未按预期工作，请先使用 LIMIT 运行一个小样本。要测试过滤器，请使用 SELECT DISTINCT 查看它匹配哪些唯一值。

    -- Preview the first rows while iterating
SELECT date, account, position LIMIT 5;
 
-- Test which accounts match a regular expression
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

  3. 余额断言 你可以使用 BQL 来双重检查账本中的 balance 断言。此查询应返回你上次对该账户进行余额检查时指定的确切金额。

    -- Verify the final balance of your checking account
SELECT account, sum(position)
FROM close ON 2025-01-01 -- Use the date from your balance directive
WHERE account = "Assets:Checking";

使用 SQL 进行查询

来源：https://beancount.io/zh/docs/Basics/beancount-query-language