Beancount Query Language - SQL-подобни финансови заявки

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

---

Структура и изпълнение на заявки

Ядрото на BQL е неговият познат, SQL-вдъхновен синтаксис. Заявките се изпълняват с помощта на инструмента от командния ред bean-query, който обработва вашия счетоводен файл и връща резултатите директно в терминала ви.

Основен формат на заявка

Една BQL заявка се състои от три основни клаузи: SELECT, FROM и WHERE.

SELECT <цел1>, <цел2>, ...
FROM <израз-филтър-запис>
WHERE <израз-филтър-постинг>;

• SELECT: Определя кои колони от данни искате да извлечете.
• FROM: Филтрира цели транзакции преди да бъдат обработени.
• WHERE: Филтрира отделните редове на постинга след като транзакцията е била избрана.

Двустепенна система за филтриране

Разбирането на разликата между клаузите FROM и WHERE е от решаващо значение за писане на точни заявки. BQL използва двустепенен процес на филтриране.

1. Ниво на транзакция (FROM) Тази клауза действа върху цели транзакции. Ако една транзакция отговаря на условието FROM, цялата транзакция (включително всички нейни постинги) се предава на следващия етап. Това е основният начин за филтриране на данни, тъй като запазва целостта на системата за двойно счетоводство. Например, филтрирането FROM year = 2024 избира всички транзакции, които са се случили през 2024 година.

2. Ниво на постинг (WHERE) Тази клауза филтрира отделните постинги в рамките на транзакциите, избрани от клаузата FROM. Това е полезно за представяне и за фокусиране върху конкретни клонове на транзакция. Въпреки това, имайте предвид, че филтрирането на това ниво може да "наруши" целостта на транзакция в изхода, тъй като може да видите само едната страна на запис. Например, можете да изберете всички постинги към вашия акаунт Expenses:Groceries.

---

Модел на данните

За да извършвате ефективно заявки към вашите данни, трябва да разберете как 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,       -- Броят и валутата на постинга (Amount)
       cost,        -- Базовата цена на постинга (Cost)
       price,       -- Цената, използвана в постинга (Amount)
       weight,      -- Позицията, конвертирана в нейната базова цена (Amount)
       balance      -- Общата сума на единиците в акаунта (Inventory)

---

Функции за заявки

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

Агрегиращи функции

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

-- Пребройте броя на постингите
SELECT COUNT(*)

-- Сумирайте стойността на всички постинги (конвертирани в обща валута)
SELECT SUM(position)

-- Намерете датата на първата и последната транзакция
SELECT FIRST(date), LAST(date)

-- Намерете минималните и максималните стойности на позицията
SELECT MIN(position), MAX(position)

-- Групирайте по акаунт, за да получите сума за всеки
SELECT account, SUM(position) GROUP BY account

Функции за позиция/инвентар

Колоната position е сложен обект. Тези функции ви позволяват да извлечете конкретни части от нея или да изчислите нейната пазарна стойност.

-- Извлечете само числото и валутата от позиция
SELECT UNITS(position)

-- Покажете общата цена на позиция
SELECT COST(position)

-- Покажете позицията, конвертирана в нейната базова цена (полезно за инвестиции)
SELECT WEIGHT(position)

-- Изчислете пазарната стойност, използвайки най-новите данни за цените
SELECT VALUE(position)

Можете да комбинирате тези за мощни отчети. Например, за да видите общата цена и текущата пазарна стойност на вашето инвестиционно портфолио:

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account

---

Разширени функции

Освен основните 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.

-- Отпечатайте всички транзакции, свързани с инвестиции, от 2024 г.
PRINT FROM year = 2024
WHERE account ~ "Assets:Investments"

-- Намерете транзакция по нейния уникален идентификатор (генериран от някои инструменти)
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Филтриращи изрази

Можете да изградите сложни филтри, използвайки логически оператори (AND, OR), регулярни изрази (~) и сравнения.

-- Намерете всички пътни разходи от втората половина на 2024 г.
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"

-- Намерете всички транзакции, свързани с почивка или бизнес
SELECT * FROM
    "vacation-2024" IN tags OR
    "business-trip" IN links

---

Съображения за производителността ⚙️

bean-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. Отстраняване на грешки Ако заявка не работи според очакванията, използвайте EXPLAIN, за да видите как Beancount я анализира. За да тествате филтър, използвайте SELECT DISTINCT, за да видите кои уникални стойности съвпадат.

   -- Вижте плана на заявката
   EXPLAIN SELECT date, account, position;
   
   -- Тествайте кои акаунти съвпадат с регулярен израз
   SELECT DISTINCT account
   WHERE account ~ "^Assets:.*";

3. Assertions за баланс Можете да използвате BQL, за да проверите двойно balance assertions във вашата счетоводна книга. Тази заявка трябва да върне точната сума, посочена във вашата последна проверка на баланса за този акаунт.

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