Запросы с помощью SQL

Beancount включает мощный SQL-подобный язык запросов (BQL), который позволяет точно и гибко анализировать ваши финансовые данные. Нужно ли вам создать быстрый отчет, отладить проводку или выполнить сложный анализ, освоение BQL — ключ к раскрытию полного потенциала вашего текстового учета. Это руководство познакомит вас с его структурой, функциями и лучшими практиками. 🔍

Редактор запросов BQL на beancount.io, выполняющий SQL-подобные запросы к реестру Beancount в браузере

Изучите живой реестр →

Структура запросов и их выполнение

В основе BQL лежит знакомый SQL-подобный синтаксис. Запросы выполняются с помощью инструмента командной строки bea query, который обрабатывает ваш файл реестра и выводит результаты прямо в терминал. Все запросы в этом руководстве были выполнены с Beancount 3.2.3 и beanquery 0.2.0.

Базовый формат запроса

Запрос BQL состоит из трех основных предложений: SELECT, FROM и WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;

SELECT: Определяет, какие столбцы данных вы хотите получить.
FROM: Фильтрует целые транзакции до их обработки.
WHERE: Фильтрует отдельные строки проводок после выбора транзакции.

Двухуровневая система фильтрации

Понимание разницы между предложениями FROM и WHERE имеет решающее значение для написания точных запросов. BQL использует двухуровневый процесс фильтрации.

Уровень транзакций (FROM) Это предложение действует на целые транзакции. Если транзакция соответствует условию FROM, вся транзакция (включая все ее проводки) передается на следующий этап. Это основной способ фильтрации данных, так как он сохраняет целостность системы двойной записи. Например, фильтр FROM year = 2024 выбирает все транзакции, произошедшие в 2024 году.
Уровень проводок (WHERE) Это предложение фильтрует отдельные проводки внутри транзакций, выбранных предложением FROM. Это полезно для представления данных и фокусировки на конкретных статьях транзакции. Однако имейте в виду, что фильтрация на этом уровне может "нарушить" целостность транзакции в выводе, так как вы можете увидеть только одну сторону записи. Например, вы можете выбрать все проводки по счету Expenses:Groceries.

Конкретно, PRINT FROM year = 2024 возвращает целые транзакции (обе стороны каждой записи), тогда как SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" возвращает по одной строке на каждую подходящую проводку. В реестре с двумя покупками через брокера первый запрос вернет полные записи, а второй — ровно две строки по этим проводкам.

Модель данных

Чтобы эффективно запрашивать данные, необходимо понимать, как Beancount структурирует их. Реестр — это список директив, но BQL в первую очередь фокусируется на записях Transaction.

Структура транзакции

Каждая Transaction — это контейнер с атрибутами верхнего уровня и списком объектов Posting.

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

Доступные типы столбцов

Вы можете выбрать SELECT любой из атрибутов транзакции или проводки.

Атрибуты транзакции Эти столбцы одинаковы для всех проводок в рамках одной транзакции.

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

Атрибуты проводки Эти столбцы специфичны для каждой отдельной строки проводки.

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 и cost — это функции, принимающие position. price, weight и balance — обычные столбцы.

Функции запросов

BQL включает набор функций для агрегации и трансформации данных, аналогично SQL.

Агрегатные функции

Агрегатные функции обобщают данные по нескольким строкам. При использовании с GROUP BY они предоставляют сгруппированные сводки.

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

Функции для позиций и инвентаря

Столбец position — это составной объект. Эти функции позволяют извлекать из него отдельные части или вычислять рыночную стоимость.

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

Вы можете комбинировать эти функции для создания мощных отчетов. Например, чтобы узнать общую стоимость и текущую рыночную стоимость вашего инвестиционного портфеля. Для рыночной стоимости требуется директива price для каждой позиции (например, 2024-12-01 price HOOL 175.00 USD). Обе агрегатные функции возвращают объект Inventory по каждому счету, поэтому каждая валюта будет указана отдельно.

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)

Расширенные возможности

Помимо базовых операторов SELECT, BQL предлагает специализированные команды для типовых отчетов.

Отчеты о балансе

Оператор BALANCES формирует балансовый отчет или отчет о прибылях и убытках за определенный период.

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

Журналы операций

Оператор JOURNAL показывает детальную активность по одному или нескольким счетам, аналогично традиционному журналу операций.

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

Операции вывода

Оператор PRINT — это инструмент для отладки, который выводит полные, соответствующие условиям транзакции в их исходном формате файла Beancount. Он принимает только фильтр записей. Предложение WHERE здесь является синтаксической ошибкой. Чтобы ограничить вывод одной стороной каждой записи, используйте SELECT с фильтром проводок. Он возвращает по одной строке на каждую подходящую проводку.

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

Выражения фильтрации

Вы можете создавать сложные фильтры с помощью логических операторов (AND, OR), регулярных выражений (~) и сравнений.

Строковые литералы заключаются в одинарные кавычки. Двойные кавычки используются для разделения регулярного выражения после ~.

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

Лучшие практики

Следуйте этим советам, чтобы писать чистые, эффективные и поддерживаемые запросы.

Организация запросов Форматируйте запросы для удобочитаемости, особенно сложные. Используйте разрывы строк и отступы для разделения предложений.
```
-- A clean, readable query for all 2024 expenses
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;
```
Отладка Если запрос работает не так, как ожидалось, сначала выполните небольшой пример с LIMIT. Чтобы проверить фильтр, используйте SELECT DISTINCT, чтобы увидеть, какие уникальные значения он сопоставляет.
```
-- 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:.*";
```
Проверка баланса Вы можете использовать BQL для перекрестной проверки директив balance в вашем реестре. Этот запрос должен вернуть точную сумму, указанную в вашей последней проверке баланса для этого счета.
```
-- 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";
```

Структура запросов и их выполнение​

Базовый формат запроса​

Двухуровневая система фильтрации​

Модель данных​

Структура транзакции​

Доступные типы столбцов​

Функции запросов​

Агрегатные функции​

Функции для позиций и инвентаря​

Расширенные возможности​

Отчеты о балансе​

Журналы операций​

Операции вывода​

Выражения фильтрации​

Рекомендации по производительности ⚙️​

Лучшие практики​