メインコンテンツへスキップ
Beancount.io Logo
SQLでクエリを実行する

SQLでクエリを実行する

高度な財務データ分析のためのBeancountクエリ言語（BQL）を習得します。会計データをクエリして分析するためのSQL風構文を学びます。

Beancountには、財務データを正確にスライス・ダイス・分析できる、強力なSQL風クエリ言語（BQL）が搭載されています。簡単なレポートの生成、エントリのデバッグ、複雑な分析の実行など、BQLの習得はプレーンテキスト会計台帳の可能性を最大限に引き出す鍵となります。このガイドでは、その構造、関数、ベストプラクティスについて説明します。🔍

ブラウザでBeancount台帳に対してSQL風クエリを実行するbeancount.io BQLクエリエディタ

ライブ台帳を探索する →

クエリの構造と実行​

BQLの中核は、おなじみのSQLに着想を得た構文です。クエリはbean-queryコマンドラインツールを使用して実行され、台帳ファイルを処理して、結果をターミナルに直接返します。このガイドのすべてのクエリは、Beancount 3.2.3とbeanquery 0.2.0に対して実行されました。

基本的なクエリ形式​

BQLクエリは、SELECT、FROM、WHEREの3つの主要な句で構成されます。

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: 取得するデータの列を指定します。
  • FROM: 処理される前にトランザクション全体をフィルタリングします。
  • WHERE: トランザクションが選択された後に、個々のポスティング行をフィルタリングします。

2段階フィルタリングシステム​

正確なクエリを作成するには、FROM句とWHERE句の違いを理解することが重要です。BQLは2段階のフィルタリングプロセスを使用します。

  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"は、一致するポスティングごとに1行を返します。2つの証券購入がある台帳の場合、前者は完全なエントリを返し、後者は正確に2つの証券会社の行を返します。

データモデル​

データを効果的にクエリするには、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)

これらを組み合わせることで、強力なレポートを作成できます。たとえば、投資ポートフォリオの総原価と現在の市場価値を確認する場合です。市場価値には、各保有資産のpriceディレクティブが必要です（例：2024-12-01 price HOOL 175.00 USD）。両方の集約は勘定ごとにインベントリを返すため、各通貨は依然として個別にリストされます。

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文は、従来の台帳ビューと同様に、1つまたは複数の勘定の詳細な活動を示します。

-- 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句を使用すると構文エラーになります。各エントリの1つの側面に出力を絞り込むには、代わりにポスティングフィルタを指定してSELECTを使用します。一致するポスティングごとに1行を返します。

-- 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

パフォーマンスに関する考慮事項 ⚙️​

bean-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/ja/docs/Basics/beancount-query-language