SQL でクエリ

Beancount は、強力な SQL ライクな Query Language (BQL) を備えており、財務データを正確に分析できます。クイックレポートの生成、エントリのデバッグ、複雑な分析の実行など、BQL をマスターすることは、プレーンテキスト会計台帳の可能性を最大限に引き出すための鍵となります。このガイドでは、その構造、機能、およびベストプラクティスについて説明します。🔍

beancount.io BQL query editor running SQL-like queries against a Beancount ledger in the browser

Explore the live ledger →

クエリの構造と実行

BQL の核心は、使い慣れた SQL に似た構文です。クエリは bean-query コマンドラインツールを使用して実行され、台帳ファイルを処理し、結果をターミナルに直接返します。

基本的なクエリ形式

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

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;

SELECT: 取得するデータの列を指定します。
FROM: トランザクション全体を処理する_前に_フィルタリングします。
WHERE: トランザクションが選択された_後_、個々の記帳行をフィルタリングします。

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

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

トランザクションレベル (FROM) この句は、トランザクション全体に作用します。トランザクションが FROM 条件に一致する場合、トランザクション全体 (すべての記帳を含む) が次のステージに渡されます。これは、複式簿記システムの整合性を維持するため、データをフィルタリングする主な方法です。たとえば、FROM year = 2024 をフィルタリングすると、2024 年に発生したすべてのトランザクションが選択されます。
記帳レベル (WHERE) この句は、FROM 句で選択されたトランザクション_内_の個々の記帳をフィルタリングします。これは、表示のため、およびトランザクションの特定のレッグに焦点を当てる場合に役立ちます。ただし、このレベルでフィルタリングすると、エントリの片側しか表示されないため、出力内のトランザクションの整合性が「損なわれる」可能性があることに注意してください。たとえば、Expenses:Groceries アカウントへのすべての記帳を選択できます。

データモデル

データを効果的にクエリするには、Beancount がデータをどのように構造化するかを理解する必要があります。台帳はディレクティブのリストですが、BQL は主に Transaction エントリに焦点を当てています。

トランザクション構造

各 Transaction は、トップレベルの属性と Posting オブジェクトのリストを含むコンテナです。

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

使用可能な列の型

トランザクションまたはその記帳から任意の属性を SELECT できます。

トランザクション属性 これらの列は、単一のトランザクション内のすべての記帳で同じです。

SELECT
    date,        -- トランザクションの日付 (datetime.date)
    year,        -- トランザクションの年 (int)
    month,       -- トランザクションの月 (int)
    day,         -- トランザクションの日 (int)
    flag,        -- トランザクション フラグ (例: "*" または "!") (str)
    payee,       -- 支払人 (str)
    narration,   -- 説明またはメモ (str)
    tags,        -- タグのセット (例: #trip-2024) (set[str])
    links        -- リンクのセット (例: ^expense-report) (set[str])

記帳属性 これらの列は、個々の記帳行に固有です。

SELECT
    account,     -- アカウント名 (str)
    position,    -- 金額、単位、原価を含む全額 (Position)
    units,       -- 記帳の数と通貨 (Amount)
    cost,        -- 記帳の原価基準 (Cost)
    price,       -- 記帳で使用される価格 (Amount)
    weight,      -- 原価基準に変換されたポジション (Amount)
    balance      -- アカウント内の単位の実行合計 (Inventory)

クエリ関数

BQL には、SQL と同様に、集計およびデータ変換のための関数スイートが含まれています。

集計関数

集計関数は、複数の行にわたるデータを集計します。 GROUP BY とともに使用すると、グループ化されたサマリーが提供されます。

-- 記帳の数をカウントする
SELECT COUNT(*)
 
-- すべての記帳の値 (共通通貨に変換) を合計する
SELECT SUM(position)
 
-- 最初と最後のトランザクションの日付を見つける
SELECT FIRST(date), LAST(date)
 
-- 最小値と最大値のポジション値を見つける
SELECT MIN(position), MAX(position)
 
-- アカウントごとにグループ化して合計を取得する
SELECT account, SUM(position) GROUP BY account

ポジション/在庫関数

position 列は複合オブジェクトです。これらの関数を使用すると、その特定の部分を抽出したり、市場価値を計算したりできます。

-- ポジションから数値と通貨のみを抽出する
SELECT UNITS(position)
 
-- ポジションの総コストを表示する
SELECT COST(position)
 
-- ポジションを原価基準に変換して表示する (投資に役立ちます)
SELECT WEIGHT(position)
 
-- 最新の価格データを使用して市場価値を計算する
SELECT VALUE(position)

これらを組み合わせることで、強力なレポートを作成できます。たとえば、投資ポートフォリオの総コストと現在の市場価値を表示するには、次のようにします。

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account

高度な機能

基本的な SELECT ステートメントに加えて、BQL は一般的な財務レポート用の特殊なコマンドを提供します。

残高レポート

BALANCES ステートメントは、特定の期間の貸借対照表または損益計算書を生成します。

-- 2024 年初めの簡単な貸借対照表を生成する
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- 2024 会計年度の損益計算書を生成する
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

ジャーナルレポート

JOURNAL ステートメントは、従来の台帳ビューと同様に、1 つ以上のアカウントの詳細なアクティビティを表示します。

-- 普通預金口座のすべてのアクティビティを元のコストで表示する
JOURNAL "Assets:Checking" AT COST
 
-- 401k トランザクションをすべて表示し、単位 (シェア) のみを表示する
JOURNAL "Assets:.*:401k" AT UNITS

印刷操作

PRINT ステートメントは、一致するトランザクション全体を元の Beancount ファイル形式で出力するデバッグツールです。

-- 2024 年の投資関連のトランザクションをすべて印刷する
PRINT FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- 一意の ID (一部のツールで生成) でトランザクションを検索する
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

フィルタリング式

論理演算子 (AND、OR)、正規表現 (~)、および比較を使用して、高度なフィルターを作成できます。

-- 2024 年後半のすべての旅行費用を見つける
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- 休暇またはビジネスに関連するすべてのトランザクションを見つける
SELECT * FROM
    "vacation-2024" IN tags OR
    "business-trip" IN links

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

bean-query は効率性を考慮して設計されていますが、その運用フローを理解すると、大規模な台帳でより高速なクエリを作成するのに役立ちます。

データのロード: Beancount は最初に台帳ファイル全体を解析し、すべてのトランザクションを時系列順にソートします。このデータセット全体がメモリに保持されます。
クエリの最適化: クエリエンジンは、FROM (トランザクション) -> WHERE (記帳) -> 集計の順にフィルターを適用して、最大限の効率を実現します。 FROM レベルでのフィルタリングは、データセットを早期に削減するため、最も高速です。
メモリ使用量: すべての操作はメモリ内で行われます。 Position オブジェクトと Inventory 集計は最適化されていますが、非常に大きな結果セットは大量の RAM を消費する可能性があります。 BQL はディスクベースの一時ストレージを使用しません。

ベストプラクティス

クリーンで効果的、かつ保守しやすいクエリを作成するには、次のヒントに従ってください。

クエリの構成 特に複雑なクエリの場合は、読みやすさを考慮してクエリをフォーマットします。改行とインデントを使用して句を区切ります。

-- 2024 年のすべての費用に対するクリーンで読みやすいクエリ
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

デバッグ クエリが期待どおりに動作しない場合は、EXPLAIN を使用して、Beancount がクエリをどのように解析するかを確認します。フィルターをテストするには、SELECT DISTINCT を使用して、一致する一意の値を確認します。
```
-- クエリ プランを表示する
EXPLAIN SELECT date, account, position;
 
-- 正規表現に一致するアカウントをテストする
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";
```
残高アサーション BQL を使用して、台帳内の balance アサーションをダブルチェックできます。このクエリは、そのアカウントの最後の残高チェックで指定された正確な金額を返す必要があります。
```
-- 普通預金口座の最終残高を確認する
SELECT account, sum(position)
FROM close ON 2025-01-01 -- 残高ディレクティブの日付を使用する
WHERE account = "Assets:Checking";
```

クエリの構造と実行​

基本的なクエリ形式​

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

データ モデル​

トランザクション構造​

使用可能な列の型​

クエリ関数​

集計関数​

ポジション/在庫関数​

高度な機能​

残高レポート​

ジャーナル レポート​

印刷操作​

フィルタリング式​

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

ベスト プラクティス​