Pular para o conteúdo principal

Consulta com SQL

Domine a Linguagem de Consulta Beancount (BQL) para análise avançada de dados financeiros. Aprenda a sintaxe semelhante a SQL para consultar e analisar seus dados contábeis.

O Beancount possui uma poderosa Linguagem de Consulta (BQL), semelhante a SQL, que permite fatiar, cortar e analisar seus dados financeiros com precisão. Seja para gerar um relatório rápido, depurar um lançamento ou realizar análises complexas, dominar a BQL é fundamental para desbloquear todo o potencial do seu livro-razão contábil em texto puro. Este guia abordará sua estrutura, funções e melhores práticas. 🔍

Editor de consultas BQL do beancount.io executando consultas semelhantes a SQL em um livro-razão Beancount no navegador

Explore o livro-razão ao vivo →

Estrutura da Consulta e Execução​

O núcleo da BQL é sua sintaxe familiar, inspirada em SQL. As consultas são executadas usando a ferramenta de linha de comando bea query, que processa seu arquivo de livro-razão e retorna os resultados diretamente no seu terminal. Todas as consultas neste guia foram executadas com Beancount 3.2.3 e beanquery 0.2.0.

Formato Básico da Consulta​

Uma consulta BQL é composta por três cláusulas principais: SELECT, FROM e WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT: Especifica quais colunas de dados você deseja recuperar.
  • FROM: Filtra transações inteiras antes de serem processadas.
  • WHERE: Filtra as linhas individuais de lançamento (postings) depois que a transação foi selecionada.

Sistema de Filtragem em Dois Níveis​

Entender a diferença entre as cláusulas FROM e WHERE é crucial para escrever consultas precisas. A BQL usa um processo de filtragem em dois níveis.

  1. Nível de Transação (FROM) Esta cláusula atua em transações inteiras. Se uma transação corresponder à condição FROM, a transação inteira (incluindo todos os seus lançamentos) é passada para o próximo estágio. Esta é a principal forma de filtrar dados, pois preserva a integridade do sistema contábil de partidas dobradas. Por exemplo, filtrar FROM year = 2024 seleciona todas as transações que ocorreram em 2024.

  2. Nível de Lançamento (WHERE) Esta cláusula filtra os lançamentos individuais dentro das transações selecionadas pela cláusula FROM. Isso é útil para apresentação e para focar em partes específicas de uma transação. No entanto, esteja ciente de que filtrar neste nível pode "quebrar" a integridade de uma transação na saída, pois você pode ver apenas um lado do lançamento. Por exemplo, você pode selecionar todos os lançamentos para sua conta Expenses:Groceries.

Concretamente, PRINT FROM year = 2024 retorna transações inteiras (ambos os lados de cada lançamento), enquanto SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" retorna uma linha por lançamento correspondente. Em um livro-razão com duas compras de corretora, o primeiro retorna os lançamentos completos e o segundo retorna exatamente as duas linhas da corretora.

Modelo de Dados​

Para consultar seus dados de forma eficaz, você precisa entender como o Beancount os estrutura. Um livro-razão é uma lista de diretivas, mas a BQL se concentra principalmente em lançamentos de Transaction.

Estrutura da Transação​

Cada Transaction é um contêiner com atributos de nível superior e uma lista de objetos Posting.

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

Tipos de Coluna Disponíveis​

Você pode usar SELECT em qualquer um dos atributos da transação ou de seus lançamentos.

  1. Atributos da Transação Essas colunas são as mesmas para cada lançamento dentro de uma única transação.

    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 do Lançamento Essas colunas são específicas para cada linha de lançamento 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 e cost são funções que recebem position. price, weight e balance são colunas simples.

Funções de Consulta​

A BQL inclui um conjunto de funções para agregação e transformação de dados, muito parecido com SQL.

Funções de Agregação​

As funções de agregação resumem dados em várias linhas. Quando usadas com GROUP BY, fornecem resumos 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

Funções de Posição/Inventário​

A coluna position é um objeto composto. Essas funções permitem extrair partes específicas dela ou calcular seu 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)

Você pode combinar essas funções para relatórios poderosos. Por exemplo, para ver o custo total e o valor de mercado atual do seu portfólio de investimentos. O valor de mercado precisa de uma diretiva price para cada participação (por exemplo, 2024-12-01 price HOOL 175.00 USD). Ambos os agregados retornam um Inventory por conta, então cada moeda ainda é listada separadamente.

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)

Recursos Avançados​

Além das declarações básicas SELECT, a BQL oferece comandos especializados para relatórios financeiros comuns.

Relatórios de Saldo​

A declaração BALANCES gera um balanço patrimonial ou demonstração de resultados para um 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"

Relatórios de Diário​

A declaração JOURNAL mostra a atividade detalhada de uma ou mais contas, semelhante a uma visão de livro-razão 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

Operações de Impressão​

A declaração PRINT é uma ferramenta de depuração que gera transações completas e correspondentes em seu formato original de arquivo Beancount. Ela aceita apenas um filtro de lançamento. Uma cláusula WHERE aqui é um erro de sintaxe. Para restringir a saída a uma parte de cada lançamento, use SELECT com um filtro de lançamento. Ela retorna uma linha por lançamento correspondente.

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

Expressões de Filtragem​

Você pode construir filtros sofisticados usando operadores lógicos (AND, OR), expressões regulares (~) e comparações.

Literais de string usam aspas simples. Aspas duplas delimitam a expressão regular após ~.

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

Considerações de Desempenho ⚙️​

bea query é projetado para eficiência, mas entender seu fluxo operacional pode ajudá-lo a escrever consultas mais rápidas em livros-razão grandes.

  1. Carregamento de Dados: O Beancount primeiro analisa seu arquivo de livro-razão inteiro e classifica todas as transações cronologicamente. Todo esse conjunto de dados é mantido na memória.
  2. Otimização de Consulta: O mecanismo de consulta aplica filtros em uma ordem específica para máxima eficiência: FROM (transações) -> WHERE (lançamentos) -> Agregações. Filtrar no nível FROM é mais rápido porque reduz o conjunto de dados no início.
  3. Uso de Memória: Todas as operações ocorrem na memória. Objetos Position e agregações Inventory são otimizados, mas conjuntos de resultados muito grandes podem consumir RAM significativa. A BQL não usa armazenamento temporário em disco.

Melhores Práticas​

Siga estas dicas para escrever consultas limpas, eficazes e fáceis de manter.

  1. Organização da Consulta Formate suas consultas para legibilidade, especialmente as complexas. Use quebras de linha e indentação para separar as 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. Depuração Se uma consulta não estiver funcionando como esperado, execute uma pequena amostra com LIMIT primeiro. Para testar um filtro, use SELECT DISTINCT para ver quais valores únicos ele corresponde.

    -- 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ções de Saldo Você pode usar a BQL para verificar novamente as balance assertions no seu livro-razão. Esta consulta deve retornar o valor exato especificado em sua última verificação de saldo para aquela conta.

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

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