Naar hoofdinhoud springen

Query met SQL

Beheers de Beancount Query Language (BQL) voor geavanceerde financiële data-analyse. Leer SQL-achtige syntax om uw boekhoudgegevens te bevragen en analyseren.

Beancount beschikt over een krachtige, SQL-achtige Query Language (BQL) waarmee u uw financiële gegevens nauwkeurig kunt selecteren, filteren en analyseren. Of u nu een snel rapport wilt genereren, een boeking wilt debuggen of complexe analyses wilt uitvoeren, het beheersen van BQL is essentieel om het volledige potentieel van uw plaintext-boekhouding te benutten. Deze handleiding leidt u door de structuur, functies en best practices. 🔍

beancount.io BQL-query-editor die SQL-achtige queries uitvoert op een Beancount-grootboek in de browser

Verken het live grootboek →

Querystructuur en uitvoering​

De kern van BQL is de vertrouwde, op SQL geïnspireerde syntax. Queries worden uitgevoerd met het bea query commandoregelprogramma, dat uw grootboekbestand verwerkt en de resultaten direct in uw terminal weergeeft. Elke query in deze handleiding is uitgevoerd met Beancount 3.2.3 en beanquery 0.2.0.

Basisqueryformaat​

Een BQL-query bestaat uit drie hoofdcomponenten: SELECT, FROM en WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Specificeert welke gegevenskolommen u wilt ophalen.
  • FROM: Filtert volledige transacties voordat ze worden verwerkt.
  • WHERE: Filtert de individuele boekingregels nadat de transactie is geselecteerd.

Tweeledig filtersysteem​

Het verschil tussen de FROM- en WHERE-clausules begrijpen is cruciaal voor het schrijven van nauwkeurige queries. BQL gebruikt een tweeledig filterproces.

  1. Transactieniveau (FROM) Deze clausule werkt op volledige transacties. Als een transactie voldoet aan de FROM-voorwaarde, wordt de volledige transactie (inclusief alle boekingen) doorgegeven aan de volgende fase. Dit is de primaire manier om gegevens te filteren, omdat het de integriteit van het dubbel boekhouden behoudt. Bijvoorbeeld, filteren op FROM year = 2024 selecteert alle transacties die in 2024 plaatsvonden.

  2. Boekingniveau (WHERE) Deze clausule filtert de individuele boekingen binnen de transacties die door de FROM-clausule zijn geselecteerd. Dit is handig voor presentatie en voor het focussen op specifieke delen van een transactie. Houd er echter rekening mee dat filteren op dit niveau de "integriteit" van een transactie in de uitvoer kan "verbreken", omdat u mogelijk slechts één kant van een boeking ziet. U kunt bijvoorbeeld alle boekingen naar uw Expenses:Groceries-rekening selecteren.

Concreet: PRINT FROM year = 2024 retourneert volledige transacties (beide zijden van elke boeking), terwijl SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" één rij per overeenkomende boeking retourneert. Op een grootboek met twee aankopen bij de broker retourneert de eerste de volledige boekingen en de tweede precies de twee brokerregels.

Datamodel​

Om uw gegevens effectief te kunnen bevragen, moet u begrijpen hoe Beancount gegevens structureert. Een grootboek is een lijst van richtlijnen, maar BQL richt zich voornamelijk op Transaction-boekingen.

Transactiestructuur​

Elke Transaction is een container met attributen op het hoogste niveau en een lijst van Posting-objecten.

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

Beschikbare kolomtypen​

U kunt elk attribuut van de transactie of de boekingen selecteren met SELECT.

  1. Transactie-attributen Deze kolommen zijn hetzelfde voor elke boeking binnen een enkele transactie.

    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. Boeking-attributen Deze kolommen zijn specifiek voor elke individuele boekingregel.

    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 en cost zijn functies die position als argument nemen. price, weight en balance zijn gewone kolommen.

Queryfuncties​

BQL bevat een reeks functies voor aggregatie en datatransformatie, vergelijkbaar met SQL.

Aggregatiefuncties​

Aggregatiefuncties vatten gegevens samen over meerdere rijen. In combinatie met GROUP BY bieden ze gegroepeerde overzichten.

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

Positie-/inventarisfuncties​

De position-kolom is een samengesteld object. Deze functies stellen u in staat specifieke delen ervan te extraheren of de marktwaarde ervan te berekenen.

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

U kunt deze combineren voor krachtige rapporten. Bijvoorbeeld om de totale kostprijs en actuele marktwaarde van uw beleggingsportefeuille te zien. De marktwaarde vereist een price-richtlijn voor elke positie (bijvoorbeeld 2024-12-01 price HOOL 175.00 USD). Beide aggregaten retourneren een Inventory per rekening, zodat elke valuta afzonderlijk wordt weergegeven.

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)

Geavanceerde functies​

Naast basis-SELECT-instructies biedt BQL gespecialiseerde commando's voor veelvoorkomende financiële rapporten.

Balansrapporten​

De BALANCES-instructie genereert een balans of winst-en-verliesrekening voor een specifieke periode.

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

Journaalrapporten​

De JOURNAL-instructie toont de gedetailleerde activiteit voor één of meerdere rekeningen, vergelijkbaar met een traditioneel grootboekoverzicht.

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

Printbewerkingen​

De PRINT-instructie is een debugtool die volledige, overeenkomende transacties in hun oorspronkelijke Beancount-bestandsformaat uitvoert. Het accepteert alleen een ingangsfilter. Een WHERE-clausule is hier een syntaxfout. Om de uitvoer te beperken tot één kant van elke boeking, gebruikt u SELECT met een boekingsfilter. Dit retourneert één rij per overeenkomende boeking.

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

Filterexpressies​

U kunt geavanceerde filters bouwen met logische operatoren (AND, OR), reguliere expressies (~) en vergelijkingen.

Stringliteral gebruiken enkele aanhalingstekens. Dubbele aanhalingstekens begrenzen de reguliere expressie na ~.

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

Prestatieoverwegingen ⚙️​

bea query is ontworpen voor efficiëntie, maar inzicht in de operationele stroom kan u helpen snellere queries te schrijven op grote grootboeken.

  1. Gegevens laden: Beancount parseert eerst uw volledige grootboekbestand en sorteert alle transacties chronologisch. De volledige dataset wordt in het geheugen bewaard.
  2. Queryoptimalisatie: De query-engine past filters in een specifieke volgorde toe voor maximale efficiëntie: FROM (transacties) -> WHERE (boekingen) -> Aggregaties. Filteren op FROM-niveau is het snelst omdat het de dataset vroeg reduceert.
  3. Geheugengebruik: Alle bewerkingen vinden in het geheugen plaats. Position-objecten en Inventory-aggregaties zijn geoptimaliseerd, maar zeer grote resultaatsets kunnen aanzienlijk RAM-geheugen verbruiken. BQL gebruikt geen tijdelijke opslag op schijf.

Best practices​

Volg deze tips om schone, effectieve en onderhoudbare queries te schrijven.

  1. Query-organisatie Formatteer uw queries voor leesbaarheid, vooral complexe. Gebruik regeleinden en inspringing om componenten te scheiden.

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

  2. Debuggen Als een query niet werkt zoals verwacht, voer dan eerst een klein voorbeeld uit met LIMIT. Om een filter te testen, gebruikt u SELECT DISTINCT om te zien welke unieke waarden het matcht.

    -- 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. Balanscontroles U kunt BQL gebruiken om de balance-controles in uw grootboek dubbel te controleren. Deze query moet het exacte bedrag retourneren dat is opgegeven in uw laatste balanscontrole voor die rekening.

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

Query met SQL

Bron: https://beancount.io/nl/docs/Basics/beancount-query-language