Saltar al contenido principal

Consultas con SQL

Domina el Lenguaje de Consultas de Beancount (BQL) para análisis avanzados de datos financieros. Aprende la sintaxis similar a SQL para consultar y analizar tus datos contables.

Beancount cuenta con un potente Lenguaje de Consultas (BQL) similar a SQL, que te permite rebanar, cortar y analizar tus datos financieros con precisión. Ya sea que quieras generar un informe rápido, depurar un asiento, o realizar análisis complejos, dominar BQL es clave para desbloquear todo el potencial de tu libro mayor en texto plano. Esta guía te llevará a través de su estructura, funciones y mejores prácticas. 🔍

Editor de consultas BQL de beancount.io ejecutando consultas tipo SQL contra un libro mayor de Beancount en el navegador

Explora el libro mayor en vivo →

Estructura de Consultas y Ejecución​

El núcleo de BQL es su sintaxis familiar, inspirada en SQL. Las consultas se ejecutan mediante la herramienta de línea de comandos bea query, que procesa tu archivo de libro mayor y devuelve los resultados directamente en tu terminal. Cada consulta en esta guía se ejecutó con Beancount 3.2.3 y beanquery 0.2.0.

Formato Básico de Consulta​

Una consulta BQL se compone de tres cláusulas principales: SELECT, FROM y WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Especifica qué columnas de datos deseas recuperar.
  • FROM: Filtra transacciones completas antes de que sean procesadas.
  • WHERE: Filtra las líneas individuales de asiento después de que la transacción haya sido seleccionada.

Sistema de Filtrado de Dos Niveles​

Entender la diferencia entre las cláusulas FROM y WHERE es crucial para escribir consultas precisas. BQL utiliza un proceso de filtrado de dos niveles.

  1. Nivel de Transacción (FROM) Esta cláusula actúa sobre transacciones completas. Si una transacción coincide con la condición FROM, la transacción completa (incluyendo todos sus asientos) se pasa a la siguiente etapa. Esta es la forma principal de filtrar datos, ya que preserva la integridad del sistema contable de partida doble. Por ejemplo, filtrar FROM year = 2024 selecciona todas las transacciones que ocurrieron en 2024.

  2. Nivel de Asiento (WHERE) Esta cláusula filtra los asientos individuales dentro de las transacciones seleccionadas por la cláusula FROM. Esto es útil para la presentación y para centrarse en patas específicas de una transacción. Sin embargo, ten en cuenta que filtrar a este nivel puede "romper" la integridad de una transacción en la salida, ya que podrías ver solo un lado de un asiento. Por ejemplo, podrías seleccionar todos los asientos de tu cuenta Expenses:Groceries.

Concretamente, PRINT FROM year = 2024 devuelve transacciones completas (ambas patas de cada asiento), mientras que SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" devuelve una fila por cada asiento coincidente. En un libro mayor con dos compras de corretaje, la primera devuelve los asientos completos y la segunda devuelve exactamente las dos filas de corretaje.

Modelo de Datos​

Para consultar tus datos de manera efectiva, necesitas entender cómo Beancount los estructura. Un libro mayor es una lista de directivas, pero BQL se centra principalmente en las entradas Transaction.

Estructura de Transacción​

Cada Transaction es un contenedor con atributos de nivel superior y una lista de objetos Posting.

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

Tipos de Columnas Disponibles​

Puedes SELECT cualquiera de los atributos de la transacción o de sus asientos.

  1. Atributos de Transacción Estas columnas son las mismas para cada asiento dentro de una sola transacción.

    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. Atributos de Asiento Estas columnas son específicas de cada línea de asiento 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 y cost son funciones que toman position. price, weight y balance son columnas simples.

Funciones de Consulta​

BQL incluye un conjunto de funciones para agregación y transformación de datos, muy similares a SQL.

Funciones de Agregación​

Las funciones de agregación resumen datos a través de múltiples filas. Cuando se usan con GROUP BY, proporcionan resúmenes agrupados.

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

Funciones de Posición/Inventario​

La columna position es un objeto compuesto. Estas funciones te permiten extraer partes específicas de él o calcular su valor de mercado.

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

Puedes combinar estas funciones para informes poderosos. Por ejemplo, para ver el costo total y el valor de mercado actual de tu cartera de inversiones. El valor de mercado necesita una directiva price para cada participación (por ejemplo 2024-12-01 price HOOL 175.00 USD). Ambos agregados devuelven un Inventario por cuenta, por lo que cada moneda aún se lista por separado.

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)

Funciones Avanzadas​

Más allá de las declaraciones SELECT básicas, BQL ofrece comandos especializados para informes financieros comunes.

Informes de Balance​

La declaración BALANCES genera un balance general o estado de resultados para un período específico.

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

La declaración JOURNAL muestra la actividad detallada de una o más cuentas, similar a una vista de libro mayor 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

Operaciones de Impresión​

La declaración PRINT es una herramienta de depuración que genera transacciones completas y coincidentes en su formato original de archivo Beancount. Solo acepta un filtro de entrada. Una cláusula WHERE es un error de sintaxis aquí. Para limitar la salida a una pata de cada asiento, usa SELECT con un filtro de asiento en su lugar. Devuelve una fila por cada asiento coincidente.

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

Expresiones de Filtrado​

Puedes construir filtros sofisticados utilizando operadores lógicos (AND, OR), expresiones regulares (~) y comparaciones.

Los literales de cadena usan comillas simples. Las comillas dobles delimitan la expresión regular despué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

Consideraciones de Rendimiento ⚙️​

bea query está diseñado para la eficiencia, pero entender su flujo operativo puede ayudarte a escribir consultas más rápidas en libros mayores grandes.

  1. Carga de Datos: Beancount primero analiza todo tu archivo de libro mayor y ordena todas las transacciones cronológicamente. Todo este conjunto de datos se mantiene en memoria.
  2. Optimización de Consultas: El motor de consultas aplica filtros en un orden específico para máxima eficiencia: FROM (transacciones) -> WHERE (asientos) -> Agregaciones. Filtrar a nivel FROM es lo más rápido porque reduce el conjunto de datos temprano.
  3. Uso de Memoria: Todas las operaciones se realizan en memoria. Los objetos Position y las agregaciones Inventory están optimizados, pero conjuntos de resultados muy grandes pueden consumir una RAM significativa. BQL no utiliza almacenamiento temporal basado en disco.

Mejores Prácticas​

Sigue estos consejos para escribir consultas limpias, efectivas y mantenibles.

  1. Organización de Consultas Formatea tus consultas para mayor legibilidad, especialmente las complejas. Usa saltos de línea e indentación para separar las cláusulas.

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

  2. Depuración Si una consulta no funciona como se espera, ejecuta primero una muestra pequeña con LIMIT. Para probar un filtro, usa SELECT DISTINCT para ver qué valores únicos coincide.

    -- 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. Aserciones de Balance Puedes usar BQL para verificar dos veces las aserciones de balance en tu libro mayor. Esta consulta debería devolver el monto exacto especificado en tu última verificación de balance para esa cuenta.

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

Consultas con SQL

Fuente: https://beancount.io/es/docs/Basics/beancount-query-language