Salta al contenuto principale

Interrogare con SQL

Padroneggia il Beancount Query Language (BQL) per l'analisi avanzata dei dati finanziari. Impara la sintassi simile a SQL per interrogare e analizzare i tuoi dati contabili.

Beancount dispone di un potente linguaggio di interrogazione (BQL) simile a SQL che ti permette di sezionare, analizzare e interpretare con precisione i tuoi dati finanziari. Che tu voglia generare un rapido report, eseguire il debug di una registrazione o effettuare analisi complesse, padroneggiare BQL è fondamentale per sfruttare appieno il potenziale del tuo registro contabile in testo semplice. Questa guida ti illustrerà la sua struttura, le funzioni e le migliori pratiche. 🔍

Editor di query BQL di beancount.io che esegue interrogazioni simili a SQL su un registro Beancount nel browser

Esplora il registro live →

Struttura ed Esecuzione delle Query​

Il cuore di BQL è la sua sintassi familiare, ispirata a SQL. Le query vengono eseguite utilizzando lo strumento da riga di comando bea query, che elabora il tuo file di registro e restituisce i risultati direttamente nel terminale. Ogni query in questa guida è stata eseguita con Beancount 3.2.3 e beanquery 0.2.0.

Struttura Base di una Query​

Una query BQL è composta da tre clausole principali: SELECT, FROM e WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Specifica quali colonne di dati vuoi recuperare.
  • FROM: Filtra intere transazioni prima che vengano elaborate.
  • WHERE: Filtra le singole righe di registrazione dopo che la transazione è stata selezionata.

Sistema di Filtraggio su Due Livelli​

Comprendere la differenza tra le clausole FROM e WHERE è fondamentale per scrivere interrogazioni accurate. BQL utilizza un processo di filtraggio su due livelli.

  1. Livello Transazione (FROM) Questa clausola agisce su intere transazioni. Se una transazione soddisfa la condizione FROM, l'intera transazione (incluse tutte le sue registrazioni) viene passata alla fase successiva. Questo è il metodo principale per filtrare i dati, poiché preserva l'integrità del sistema di contabilità a partita doppia. Ad esempio, filtrare FROM year = 2024 seleziona tutte le transazioni avvenute nel 2024.

  2. Livello di Registrazione (WHERE) Questa clausola filtra le singole registrazioni all'interno delle transazioni selezionate dalla clausola FROM. È utile per la presentazione e per concentrarsi su specifiche partite di una transazione. Tuttavia, tieni presente che il filtraggio a questo livello può "rompere" l'integrità di una transazione nell'output, poiché potresti vedere solo un lato di una registrazione. Ad esempio, potresti selezionare tutte le registrazioni sul tuo conto Spese:Alimentari.

Concretamente, PRINT FROM year = 2024 restituisce intere transazioni (entrambe le sezioni di ogni registrazione), mentre SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" restituisce una riga per ogni registrazione corrispondente. Su un registro con due acquisti tramite broker, la prima restituisce le voci complete e la seconda esattamente le due righe del broker.

Modello dei Dati​

Per interrogare i tuoi dati in modo efficace, devi capire come Beancount li struttura. Un registro è un elenco di direttive, ma BQL si concentra principalmente sulle voci Transaction.

Struttura della Transazione​

Ogni Transaction è un contenitore con attributi di livello superiore e un elenco di oggetti Posting.

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

Tipi di Colonne Disponibili​

Puoi SELECT qualsiasi attributo della transazione o delle sue registrazioni.

  1. Attributi della Transazione Queste colonne sono le stesse per ogni registrazione all'interno di una singola transazione.

    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. Attributi della Registrazione Queste colonne sono specifiche per ogni riga di registrazione individuale.

    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 e cost sono funzioni che prendono position. price, weight e balance sono colonne semplici.

Funzioni di Query​

BQL include una suite di funzioni per l'aggregazione e la trasformazione dei dati, molto simile a SQL.

Funzioni di Aggregazione​

Le funzioni di aggregazione riassumono i dati su più righe. Se usate con GROUP BY, forniscono riepiloghi raggruppati.

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

Funzioni di Posizione/Inventario​

La colonna position è un oggetto composito. Queste funzioni ti permettono di estrarne parti specifiche o di calcolarne il valore di mercato.

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

Puoi combinarle per ottenere report più complessi. Ad esempio, per vedere il costo totale e il valore di mercato attuale del tuo portafoglio di investimenti. Il valore di mercato richiede una direttiva price per ogni partecipazione, quindi ogni valuta viene riportata separatamente.

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)

Funzionalità Avanzate​

Oltre alle semplici istruzioni SELECT, BQL offre comandi specializzati per report finanziari comuni.

Report di Bilancio​

L'istruzione BALANCES genera un prospetto patrimoniale o un conto economico per un periodo specifico.

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

Report di Giornale​

L'istruzione JOURNAL mostra l'attività dettagliata per uno o più conti, simile alla vista tradizionale di un libro mastro.

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

Operazioni di Stampa​

L'istruzione PRINT è uno strumento di debug che restituisce le transazioni complete e corrispondenti nel loro formato originale di file Beancount. Accetta solo un filtro di registrazione. Una clausola WHERE qui è un errore di sintassi. Per restringere l'output a una sola partita di ogni registrazione, usa SELECT con un filtro sulle registrazioni. Restituisce una riga per ogni registrazione corrispondente.

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

Espressioni di Filtraggio​

Puoi costruire filtri sofisticati utilizzando operatori logici (AND, OR), espressioni regolari (~) e confronti.

Le stringhe letterali sono racchiuse tra virgolette singole. Le virgolette doppie delimitano l'espressione regolare dopo ~.

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

Considerazioni sulle Prestazioni​

bea query è progettato per l'efficienza, ma comprendere il suo flusso di lavoro può aiutarti a scrivere query più veloci su registri di grandi dimensioni.

  1. Caricamento dei Dati: Beancount prima analizza l'intero file di registro e ordina cronologicamente tutte le transazioni. L'intero set di dati viene mantenuto in memoria.
  2. Ottimizzazione della Query: Il motore applica i filtri in un ordine specifico per le prestazioni: FROM (transazioni) → WHERE (registrazioni) → Aggregazioni. Il filtraggio a livello FROM è il più rapido, poiché riduce il set di dati nelle fasi iniziali.
  3. Utilizzo della Memoria: Tutte le operazioni avvengono in memoria. Le Position e gli inventari sono ottimizzati, ma set di risultati molto grandi possono consumare molta memoria. Non viene utilizzato spazio su disco per l'archiviazione temporanea.

Migliori Pratiche​

Per scrivere query pulite, efficaci e manutenibili, segui queste linee guida.

  1. Formattazione Formatta le tue query per la leggibilità, separando le clausole su righe diverse.

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

  2. Debug Se una query non restituisce i risultati attesi, usa LIMIT per ridurre l'output e SELECT DISTINCT per vedere i valori univoci.

    -- 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. Verifica dei Saldi Puoi utilizzare BQL per verificare i saldi dei tuoi conti. Ad esempio, per controllare il saldo di un conto a una data specifica:

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

Interrogare con SQL

Fonte: https://beancount.io/it/docs/Basics/beancount-query-language