Перейти к основному содержимому

Язык запросов Beancount (BQL): запрашивайте свой журнал

Запрашивайте свой журнал Beancount с помощью BQL, языка, похожего на SQL: синтаксис SELECT, столбцы, агрегатные функции и функции инвентаря, а также отчёты, запускаемые через bea query.

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

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

Изучите живой журнал →

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

В основе BQL лежит знакомый синтаксис, вдохновлённый SQL. Запускайте запросы с помощью bea query: bea --file <ledger> query "SELECT …" выводит таблицу в вашем терминале, а 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 использует двухуровневый процесс фильтрации.

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

  2. Уровень проводки (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 любой из атрибутов транзакции или её проводок.

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

    SELECT
    date,        -- Дата транзакции (datetime.date)
    year,        -- Год транзакции (int)
    month,       -- Месяц транзакции (int)
    day,         -- День транзакции (int)
    flag,        -- Флаг транзакции, например "*" или "!" (str)
    payee,       -- Получатель платежа (str)
    narration,   -- Описание или заметка (str)
    tags,        -- Набор тегов, например #trip-2024 (set[str])
    links        -- Набор ссылок, например ^expense-report (set[str])

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

    SELECT
    account,           -- Название счёта (str)
    position,          -- Полная сумма, включая единицы и стоимость (Position)
    units(position),   -- Количество и валюта проводки (Amount)
    cost(position),    -- Базовая стоимость проводки (Amount)
    price,             -- Цена, использованная в проводке (Amount)
    weight,            -- Позиция, приведённая к её базовой стоимости (Amount)
    balance            -- Текущий итог единиц на счёте (Inventory)

    units и cost — это функции, принимающие position. price, weight и balance — это обычные столбцы.

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

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

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

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

-- Подсчитать количество проводок
SELECT COUNT(*)
 
-- Просуммировать все проводки в один Inventory; валюты и лоты сохраняются, не конвертируются
SELECT SUM(position)
-- одна строка, например (-2300.00 USD, 10 HOOL {150.00 USD, 2024-09-05}, 5 HOOL {160.00 USD, 2024-11-02})
 
-- Явно итожировать один счёт в одной валюте (позиции без цены сохраняют свою валюту)
SELECT SUM(CONVERT(position, 'USD')) WHERE account ~ "Assets:Checking"
-- одна строка, например (2580.00 USD)
 
-- Найти дату первой и последней транзакции
SELECT FIRST(date), LAST(date)
 
-- Найти минимальное и максимальное значения позиции
SELECT MIN(position), MAX(position)
 
-- Сгруппировать по счёту, чтобы получить сумму для каждого
SELECT account, SUM(position) GROUP BY account

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

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

-- Извлечь только число и валюту из позиции
SELECT UNITS(position)
 
-- Показать общую стоимость позиции
SELECT COST(position)
 
-- Показать каждую проводку по её стоимости (это столбец; функции WEIGHT() не существует)
SELECT account, weight WHERE account ~ "Assets:Investments"
 
-- Вычислить рыночную стоимость, используя последние данные о ценах
-- (нужна директива price для актива; иначе позиция возвращается без изменений)
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
-- одна строка на счёт, например Assets:Broker:HOOL | (2300.00 USD) | (2625.00 USD)

Директивы price, используемые запросами рыночной стоимости, могут поступать из ручных записей, локального загрузчика котировок или Live Prices в совместимом загрузчике. Управляемые потоки данных не меняют синтаксис запроса. Локальным вышестоящим инструментам нужны локальные файлы цен, а историческим запросам по-прежнему нужны цены на запрашиваемую дату или раньше.

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

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

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

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

-- Сформировать простой балансовый отчёт на начало 2024 года
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"
 
-- Сформировать отчёт о прибылях и убытках за 2024 финансовый год
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

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

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

-- Показать всю активность по вашему текущему счёту по первоначальной стоимости
JOURNAL "Assets:Checking" AT COST
 
-- Показать все транзакции по 401k, отображая только единицы (акции)
JOURNAL "Assets:.*:401k" AT UNITS

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

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

-- Вывести все транзакции 2024 года полностью (каждую проводку каждой подходящей записи)
PRINT FROM year = 2024
 
-- Показать только инвестиционные проводки транзакций 2024 года
SELECT date, narration, account, position
FROM year = 2024
WHERE account ~ "Assets:Investments"
 
-- Найти транзакцию по её уникальному ID (генерируется некоторыми инструментами)
-- Возвращает подходящую запись или ничего, если ни одна не несёт этот ID
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

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

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

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

-- Найти все расходы на путешествия во втором полугодии 2024 года
-- SELECT * возвращает date, flag, payee, narration и position для каждой подходящей проводки
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"
 
-- Найти все транзакции, связанные с отпуском или командировкой
SELECT * FROM
    'vacation-2024' IN tags OR
    'business-trip' IN links

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

bea query разработан для эффективности, но понимание его операционного потока поможет вам писать более быстрые запросы на больших журналах.

  1. Загрузка данных: Beancount сначала разбирает весь ваш файл журнала и сортирует все транзакции хронологически. Весь этот набор данных удерживается в памяти.
  2. Оптимизация запроса: Движок запросов применяет фильтры в определённом порядке для максимальной эффективности: FROM (транзакции) -> WHERE (проводки) -> Агрегации. Фильтрация на уровне FROM самая быстрая, поскольку она сокращает набор данных на раннем этапе.
  3. Использование памяти: Все операции выполняются в памяти. Объекты Position и агрегации Inventory оптимизированы, но очень большие результирующие наборы могут потреблять значительный объём ОЗУ. BQL не использует дисковое временное хранилище.

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

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

  1. Организация запросов Форматируйте запросы для читаемости, особенно сложные. Используйте переносы строк и отступы для разделения предложений.

    -- Чистый, читаемый запрос для всех расходов 2024 года
SELECT
    date,
    account,
    position
FROM
    year = 2024
WHERE
    account ~ "Expenses"
ORDER BY
    date DESC;

  2. Отладка Если запрос не работает как ожидалось, сначала выполните небольшую выборку с LIMIT. Чтобы протестировать фильтр, используйте SELECT DISTINCT, чтобы увидеть, какие уникальные значения он находит.

    -- Предпросмотр первых строк во время итерации
SELECT date, account, position LIMIT 5;
 
-- Проверить, какие счета соответствуют регулярному выражению
SELECT DISTINCT account
WHERE account ~ "^Assets:.*";

  3. Проверки баланса Вы можете использовать BQL, чтобы перепроверить утверждения balance в вашем журнале. Этот запрос должен вернуть точную сумму, указанную в вашей последней проверке баланса для этого счёта.

    -- Проверить итоговый баланс вашего текущего счёта
SELECT account, sum(position)
FROM close ON 2025-01-01 -- Используйте дату из вашей директивы balance
WHERE account = "Assets:Checking";

Поделиться этой страницей

Поделиться в TelegramПоделиться в VKПоделиться в XПоделиться в LinkedInПоделиться в Facebook

Была ли эта страница полезной?

Язык запросов Beancount (BQL): запрашивайте свой журнал

Источник: https://beancount.io/ru/docs/Basics/beancount-query-language