Към основното съдържание

Beancount Query Language (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: Филтрира отделните редове на записите (postings) след като транзакцията е избрана.

Система за филтриране на две нива​

Разбирането на разликата между клаузите 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"
 
-- Изчисляване на пазарната стойност, използвайки най-новите ценови данни
-- (нужна е ценова директива за притежанието; в противен случай позицията се връща непроменена)
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)

Ценовите директиви, използвани от заявките за пазарна стойност, могат да идват от ръчни записи, локален извлекател на котировки или 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"
 
-- Намиране на транзакция по нейния уникален идентификатор (генериран от някои инструменти)
-- Връща съответстващото вписване или нула реда, когато нищо не носи този идентификатор
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 са оптимизирани, но много големи набори от резултати могат да консумират значителна RAM памет. 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 -- Използвайте датата от вашата директива за баланс
WHERE account = "Assets:Checking";

Споделете тази страница

Споделяне в FacebookСподеляне в TelegramСподеляне в XСподеляне в LinkedInСподеляне в WhatsApp

Беше ли полезна тази страница?

Beancount Query Language (BQL): справки в главната книга

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