Salta al contingut principal

Consulta amb SQL

Domina el Beancount Query Language (BQL) per a l'anàlisi avançada de dades financeres. Aprèn la sintaxi tipus SQL per consultar i analitzar les teves dades comptables.

Beancount inclou un potent llenguatge de consulta tipus SQL (BQL) que et permet segmentar, disseccionar i analitzar les teves dades financeres amb precisió. Tant si vols generar un informe ràpid, depurar una entrada o realitzar anàlisis complexes, dominar el BQL és clau per desbloquejar tot el potencial del teu llibre major comptable en text pla. Aquesta guia t'explicarà la seva estructura, funcions i bones pràctiques. 🔍

Editor de consultes BQL de beancount.io executant consultes tipus SQL contra un llibre major de Beancount al navegador

Explora el llibre major en directe →

Estructura i execució de consultes​

El nucli del BQL és la seva sintaxi familiar inspirada en SQL. Executa consultes amb bea query: bea --file <ledger> query "SELECT …" imprimeix la taula al teu terminal, i bea query sense arguments obre la shell interactiva. Cada consulta d'aquesta guia s'ha executat amb Beancount 3.2.3 i beanquery 0.2.0.

Format bàsic de consulta​

Una consulta BQL es compon de tres clàusules principals: SELECT, FROM i WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Especifica quines columnes de dades vols recuperar.
  • FROM: Filtra transaccions senceres abans que siguin processades.
  • WHERE: Filtra les línies individuals de càrrecs després que la transacció hagi estat seleccionada.

Sistema de filtratge en dos nivells​

Entendre la diferència entre les clàusules FROM i WHERE és crucial per escriure consultes precises. BQL utilitza un procés de filtratge en dos nivells.

  1. Nivell de transacció (FROM) Aquesta clàusula actua sobre transaccions senceres. Si una transacció coincideix amb la condició FROM, la transacció sencera (incloent tots els seus càrrecs) passa a l'etapa següent. Aquesta és la manera principal de filtrar dades, ja que preserva la integritat del sistema comptable de doble entrada. Per exemple, filtrar FROM year = 2024 selecciona totes les transaccions que van tenir lloc el 2024.

  2. Nivell de càrrecs (WHERE) Aquesta clàusula filtra els càrrecs individuals dins de les transaccions seleccionades per la clàusula FROM. Això és útil per a la presentació i per centrar-se en parts específiques d'una transacció. Tanmateix, tingues en compte que filtrar en aquest nivell pot "trencar" la integritat d'una transacció a la sortida, ja que només podries veure un costat d'una entrada. Per exemple, podries seleccionar tots els càrrecs al teu compte Expenses:Groceries.

Concretament, PRINT FROM year = 2024 retorna transaccions senceres (ambdós costats de cada entrada), mentre que SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" retorna una fila per cada càrrec que coincideix. En un llibre major amb dues compres de corredoria, la primera retorna les entrades completes i la segona retorna exactament les dues files de la corredoria.

Model de dades​

Per consultar les teves dades de manera efectiva, has d'entendre com estructura Beancount les dades. Un llibre major és una llista de directives, però el BQL se centra principalment en les entrades Transaction.

Estructura de la transacció​

Cada Transaction és un contenidor amb atributs de nivell superior i una llista d'objectes Posting.

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

Tipus de columnes disponibles​

Pots fer SELECT de qualsevol dels atributs de la transacció o dels seus càrrecs.

  1. Atributs de la transacció Aquestes columnes són les mateixes per a cada càrrec dins d'una única transacció.

    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. Atributs dels càrrecs Aquestes columnes són específiques per a cada línia de càrrec individual.

    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 i cost són funcions que prenen position. price, weight i balance són columnes simples.

Funcions de consulta​

BQL inclou un conjunt de funcions per a l'agregació i la transformació de dades, molt similar a SQL.

Funcions d'agregació​

Les funcions d'agregació resumeixen dades a través de múltiples files. Quan s'utilitzen amb GROUP BY, proporcionen resums agrupats.

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

Funcions de posició/inventari​

La columna position és un objecte compost. Aquestes funcions et permeten extreure'n parts específiques o calcular-ne el valor de mercat.

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

Pots combinar-les per a informes potents. Per exemple, per veure el cost total i el valor de mercat actual de la teva cartera d'inversions. El valor de mercat necessita una directiva price per a cada participació (per exemple 2024-12-01 price HOOL 175.00 USD). Ambdós agregats retornen un Inventari per compte, de manera que cada moneda es llista per separat.

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)

Funcions avançades​

Més enllà de les sentències SELECT bàsiques, BQL ofereix comandaments especialitzats per a informes financers habituals.

Informes de balanç​

La sentència BALANCES genera un balanç de situació o un compte de resultats per a un període específic.

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

Informes de diari​

La sentència JOURNAL mostra l'activitat detallada d'un o més comptes, similar a una vista de llibre major tradicional.

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

Operacions d'impressió​

La sentència PRINT és una eina de depuració que genera transaccions completes i coincidents en el seu format de fitxer Beancount original. Només accepta un filtre d'entrada. Una clàusula WHERE és un error de sintaxi aquí. Per reduir la sortida a un costat de cada entrada, utilitza SELECT amb un filtre de càrrecs. Retorna una fila per cada càrrec que coincideix.

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

Expressions de filtratge​

Pots construir filtres sofisticats utilitzant operadors lògics (AND, OR), expressions regulars (~) i comparacions.

Les cadenes de text utilitzen cometes simples. Les cometes dobles delimiten l'expressió regular després de ~.

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

Consideracions de rendiment ⚙️​

bea query està dissenyat per a l'eficiència, però entendre el seu flux operatiu pot ajudar-te a escriure consultes més ràpides en llibres majors grans.

  1. Càrrega de dades: Beancount primer analitza tot el teu fitxer de llibre major i ordena cronològicament totes les transaccions. Aquest conjunt de dades sencer es manté en memòria.
  2. Optimització de consultes: El motor de consultes aplica els filtres en un ordre específic per a la màxima eficiència: FROM (transaccions) -> WHERE (càrrecs) -> Agregacions. Filtrar al nivell FROM és el més ràpid perquè redueix el conjunt de dades aviat.
  3. Ús de memòria: Totes les operacions es fan en memòria. Els objectes Position i les agregacions Inventory estan optimitzats, però conjunts de resultats molt grans poden consumir una quantitat significativa de RAM. BQL no utilitza emmagatzematge temporal basat en disc.

Bones pràctiques​

Segueix aquests consells per escriure consultes netes, efectives i mantenibles.

  1. Organització de consultes Formata les teves consultes per a la llegibilitat, especialment les complexes. Utilitza salts de línia i sagnats per separar les clàusules.

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

  2. Depuració Si una consulta no funciona com s'espera, executa primer una petita mostra amb LIMIT. Per provar un filtre, utilitza SELECT DISTINCT per veure quins valors únics coincideix.

    -- 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. Comprovacions de saldo Pots utilitzar BQL per verificar les comprovacions de balance al teu llibre major. Aquesta consulta hauria de retornar l'import exacte especificat en la teva última comprovació de saldo per a aquest compte.

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

Consulta amb SQL

Font: https://beancount.io/ca/docs/Basics/beancount-query-language