Beancount Query Language - SQL-achtige Financiële Query's

Beancount beschikt over een krachtige, SQL-achtige Query Language (BQL) waarmee je je financiële data met precisie kunt analyseren. Of je nu een snel rapport wilt genereren, een entry wilt debuggen of een complexe analyse wilt uitvoeren, het beheersen van BQL is essentieel om het volledige potentieel van je plaintext accounting ledger te benutten. Deze handleiding leidt je door de structuur, functies en best practices. 🔍

---

Query Structuur en Uitvoering

De kern van BQL is de bekende, SQL-geïnspireerde syntax. Query's worden uitgevoerd met behulp van de bean-query command-line tool, die je ledger file verwerkt en de resultaten direct in je terminal teruggeeft.

Basis Query Format

Een BQL query is samengesteld uit drie hoofdclausules: SELECT, FROM en WHERE.

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

• SELECT: Specificeert welke kolommen met data je wilt ophalen.
• FROM: Filtert volledige transacties voordat ze worden verwerkt.
• WHERE: Filtert de individuele posting lines nadat de transactie is geselecteerd.

Twee-Level Filter Systeem

Het begrijpen van het verschil tussen de FROM en WHERE clausules is cruciaal voor het schrijven van accurate query's. BQL gebruikt een twee-level filterproces.

1. Transactie Level (FROM) Deze clausule werkt op volledige transacties. Als een transactie aan de FROM conditie voldoet, wordt de volledige transactie (inclusief alle postings) doorgegeven aan de volgende stap. Dit is de primaire manier om data te filteren, omdat het de integriteit van het double-entry accounting systeem bewaart. Bijvoorbeeld, filteren FROM year = 2024 selecteert alle transacties die in 2024 hebben plaatsgevonden.

2. Posting Level (WHERE) Deze clausule filtert de individuele postings binnen de transacties die door de FROM clausule zijn geselecteerd. Dit is handig voor presentatie en om te focussen op specifieke legs van een transactie. Wees je er echter van bewust dat filteren op dit level de integriteit van een transactie in de output kan "breken", omdat je mogelijk slechts één kant van een entry ziet. Bijvoorbeeld, je zou alle postings naar je Expenses:Groceries account kunnen selecteren.

---

Data Model

Om je data effectief te bevragen, moet je begrijpen hoe Beancount het structureert. Een ledger is een lijst met directives, maar BQL focust primair op Transaction entries.

Transactie Structuur

Elke Transaction is een container met top-level attributen en een lijst met Posting objecten.

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

Beschikbare Kolom Types

Je kunt elke attribute van de transactie of zijn postings SELECT-eren.

1. Transactie Attributen Deze kolommen zijn hetzelfde voor elke posting binnen een enkele transactie.

   SELECT
       date,        -- De datum van de transactie (datetime.date)
       year,        -- Het jaar van de transactie (int)
       month,       -- De maand van de transactie (int)
       day,         -- De dag van de transactie (int)
       flag,        -- De transactie flag, bijv., "*" of "!" (str)
       payee,       -- De payee (str)
       narration,   -- De description of memo (str)
       tags,        -- Een set van tags, bijv., #trip-2024 (set[str])
       links        -- Een set van links, bijv., ^expense-report (set[str])

2. Posting Attributen Deze kolommen zijn specifiek voor elke individuele posting line.

   SELECT
       account,     -- De account naam (str)
       position,    -- Het volledige bedrag, inclusief units en cost (Position)
       units,       -- Het aantal en de valuta van de posting (Amount)
       cost,        -- De cost basis van de posting (Cost)
       price,       -- De prijs die in de posting is gebruikt (Amount)
       weight,      -- De positie geconverteerd naar zijn cost basis (Amount)
       balance      -- Het lopende totaal van units in de account (Inventory)

---

Query Functies

BQL bevat een suite van functies voor aggregatie en data transformatie, net als SQL.

Aggregatie Functies

Aggregatie functies vatten data samen over meerdere rijen. Wanneer gebruikt met GROUP BY, bieden ze gegroepeerde samenvattingen.

-- Tel het aantal postings
SELECT COUNT(*)

-- Tel de waarde van alle postings op (geconverteerd naar een gemeenschappelijke valuta)
SELECT SUM(position)

-- Vind de datum van de eerste en laatste transactie
SELECT FIRST(date), LAST(date)

-- Vind de minimum en maximum position values
SELECT MIN(position), MAX(position)

-- Groepeer op account om een som voor elk te krijgen
SELECT account, SUM(position) GROUP BY account

Position/Inventory Functies

De position kolom is een samengesteld object. Deze functies laten je specifieke delen ervan extraheren of de marktwaarde ervan berekenen.

-- Extraheer alleen het aantal en de valuta van een positie
SELECT UNITS(position)

-- Toon de totale cost van een positie
SELECT COST(position)

-- Toon de positie geconverteerd naar zijn cost basis (handig voor investeringen)
SELECT WEIGHT(position)

-- Bereken de marktwaarde met behulp van de laatste price data
SELECT VALUE(position)

Je kunt deze combineren voor krachtige rapporten. Bijvoorbeeld, om de totale cost en huidige marktwaarde van je investeringsportfolio te zien:

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

---

Geavanceerde Functies

Naast basis SELECT statements, biedt BQL gespecialiseerde commands voor veelvoorkomende financiële rapporten.

Balance Rapporten

Het BALANCES statement genereert een balance sheet of income statement voor een specifieke periode.

-- Genereer een simpele balance sheet per het begin van 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"

-- Genereer een income statement voor het 2024 fiscale jaar
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Journal Rapporten

Het JOURNAL statement toont de gedetailleerde activiteit voor een of meer accounts, vergelijkbaar met een traditionele ledger view.

-- Toon alle activiteit in je checking account tegen zijn originele cost
JOURNAL "Assets:Checking" AT COST

-- Toon alle 401k transacties, en toon alleen de units (shares)
JOURNAL "Assets:.*:401k" AT UNITS

Print Operaties

Het PRINT statement is een debugging tool dat volledige, overeenkomende transacties in hun originele Beancount file format output.

-- Print alle investerings-gerelateerde transacties van 2024
PRINT FROM year = 2024
WHERE account ~ "Assets:Investments"

-- Vind een transactie op basis van zijn unieke ID (gegenereerd door sommige tools)
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Filter Expressies

Je kunt geavanceerde filters bouwen met behulp van logische operatoren (AND, OR), regular expressions (~) en vergelijkingen.

-- Vind alle travel expenses van de tweede helft van 2024
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"

-- Vind alle transacties gerelateerd aan een vacation of business
SELECT * FROM
    "vacation-2024" IN tags OR
    "business-trip" IN links

---

Performance Overwegingen ⚙️

bean-query is ontworpen voor efficiëntie, maar het begrijpen van zijn operationele flow kan je helpen om snellere query's te schrijven op grote ledgers.

1. Data Laden: Beancount parset eerst je volledige ledger file en sorteert alle transacties chronologisch. Deze volledige dataset wordt in het geheugen gehouden.
2. Query Optimalisatie: De query engine past filters toe in een specifieke volgorde voor maximale efficiëntie: FROM (transacties) -> WHERE (postings) -> Aggregaties. Filteren op het FROM level is het snelst omdat het de dataset vroeg reduceert.
3. Geheugen Gebruik: Alle operaties gebeuren in-memory. Position objecten en Inventory aggregaties zijn geoptimaliseerd, maar zeer grote result sets kunnen significant RAM verbruiken. BQL gebruikt geen disk-based temporary storage.

---

Best Practices

Volg deze tips om schone, effectieve en onderhoudbare query's te schrijven.

1. Query Organisatie Formatteer je query's voor leesbaarheid, vooral complexe. Gebruik line breaks en indentation om clausules te scheiden.

   -- Een schone, leesbare query voor alle 2024 expenses
   SELECT
       date,
       account,
       position
   FROM
       year = 2024
   WHERE
       account ~ "Expenses"
   ORDER BY
       date DESC;

2. Debugging Als een query niet werkt zoals verwacht, gebruik dan EXPLAIN om te zien hoe Beancount het parset. Om een filter te testen, gebruik SELECT DISTINCT om te zien welke unieke waarden het matcht.

   -- Zie het query plan
   EXPLAIN SELECT date, account, position;
   
   -- Test welke accounts overeenkomen met een regular expression
   SELECT DISTINCT account
   WHERE account ~ "^Assets:.*";

3. Balance Assertions Je kunt BQL gebruiken om de balance assertions in je ledger te controleren. Deze query zou het exacte bedrag moeten teruggeven dat is gespecificeerd in je laatste balance check voor dat account.

   -- Verifieer het uiteindelijke balance van je checking account
   SELECT account, sum(position)
   FROM close ON 2025-01-01 -- Gebruik de datum van je balance directive
   WHERE account = "Assets:Checking";