Перейти до основного вмісту

Мова запитів Beancount (BQL): запитуйте свій журнал

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

Beancount має потужну мову запитів, подібну до SQL (BQL), яка дозволяє точно нарізати, розкладати та аналізувати ваші фінансові дані. Чи хочете ви швидко згенерувати звіт, налагодити проведення чи виконати складний аналіз, опанування BQL є ключем до розкриття повного потенціалу вашого журналу у форматі простого тексту. Цей посібник проведе вас через його структуру, функції та найкращі практики. 🔍

Редактор запитів beancount.io BQL, що виконує 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,        -- 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. Атрибути проводки Ці стовпці є специфічними для кожного окремого рядка проводки.

    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)

Директиви ціни, які використовуються запитами ринкової вартості, можуть походити з ручних записів, локального завантажувача котирувань або живих цін у сумісному завантажувачі. Керовані потоки даних не змінюють синтаксис запиту. Локальні upstream-інструменти потребують локальних файлів цін, а історичні запити все ще потребують цін на вказану дату або раніше.

Розширені можливості​

Окрім базових операторів 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

Міркування щодо продуктивності ⚙️​

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

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

Найкращі практики​

Дотримуйтесь цих порад, щоб писати чисті, ефективні та зручні в підтримці запити.

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

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

  2. Налагодження Якщо запит не працює як очікувалося, спочатку виконайте невеликий зразок з 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:.*";

  3. Твердження про залишки Ви можете використовувати 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";

Поділитися цією сторінкою

Поділитися в TelegramПоділитися в FacebookПоділитися в XПоділитися в LinkedInПоділитися в Threads

Чи була ця сторінка корисною?

Мова запитів Beancount (BQL): запитуйте свій журнал

Джерело: https://beancount.io/uk/docs/Basics/beancount-query-language