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

Мова запитів Bean-Query (BQL)

Опануйте мову запитів Beancount (BQL) для розширеного аналізу фінансових даних. Вивчіть SQL-подібний синтаксис для запитів та аналізу ваших даних обліку.

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

Інтерфейс запитів BQL, що виконує 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: Фільтрує окремі записи (postings) після того, як транзакцію було вибрано.

Дворівнева система фільтрації​

Розуміння різниці між частинами FROM і WHERE є ключем до написання точних запитів. BQL використовує дворівневий процес фільтрації.

  1. Рівень транзакцій (FROM) Ця частина діє на цілі транзакції. Якщо транзакція відповідає умові FROM, то вся транзакція (включно з усіма її записами) передається на наступний етап. Це основний спосіб фільтрації даних, оскільки він зберігає цілісність системи подвійного запису. Наприклад, FROM year = 2024 вибере всі транзакції, що відбулися у 2024 році.

  2. Рівень записів (WHERE) Ця частина фільтрує окремі записи всередині транзакцій, вибраних частиною FROM. Це корисно для відображення та зосередження на конкретних аспектах транзакції. Однак слід пам'ятати, що фільтрація на цьому рівні може "порушити" цілісність відображення транзакції, оскільки ви можете бачити лише одну сторону запису. Наприклад, ви можете вибрати всі записи для рахунку Витрати:Продукти.

Конкретніше, PRINT FROM year = 2024 повертає цілі транзакції (обидві частини кожного запису), тоді як SELECT дата, опис, рахунок, сума FROM year = 2024 WHERE рахунок ~ "Активи:Брокер" повертає один рядок на відповідний запис. У реєстрі з двома покупками через брокера перший запит поверне повні записи, а другий – рівно два рядки брокера.

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

Щоб ефективно запитувати ваші дані, важливо розуміти, як структурований 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])
  1. Атрибути записів Ці стовпці є специфічними для кожного окремого рядка запису.
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 AAPL 175.00 USD). Обидві агрегації повертають дані по кожному рахунку, тому кожна валюта все одно буде вказана окремо.

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. Вона приймає лише фільтр у частині FROM. Використання WHERE тут є синтаксичною помилкою. Щоб звузити вибір до однієї частини запису, використовуйте SELECT з фільтром у частині WHERE. Вона повертає один рядок на відповідний запис.

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

Мова запитів Bean-Query (BQL)

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