زبان پرس‌وجوی Beancount (BQL): دفتر کل خود را پرس‌وجو کنید

Beancount دارای یک زبان پرس‌وجوی قدرتمند و شبیه SQL (BQL) است که به شما امکان می‌دهد داده‌های مالی خود را با دقت تجزیه، تحلیل و بررسی کنید. چه بخواهید یک گزارش سریع تولید کنید، چه یک ورودی را اشکال‌زدایی کنید، یا تحلیل پیچیده‌ای انجام دهید، تسلط بر BQL کلید باز کردن پتانسیل کامل دفتر کل حسابداری متن‌ساده شماست. این راهنما شما را با ساختار، توابع و بهترین شیوه‌های آن آشنا می‌کند. 🔍

دفتر کل زنده را کاوش کنید →

ساختار و اجرای پرس‌وجو​

هسته 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 تمام تراکنش‌هایی را که در سال ۲۰۲۴ رخ داده‌اند انتخاب می‌کند.

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)

دستورهای price که پرس‌وجوهای ارزش بازار از آن‌ها استفاده می‌کنند می‌توانند از ورودی‌های دستی، یک واکشی‌کننده نرخ محلی، یا قیمت‌های زنده در یک بارگذار سازگار بیایند. فیدهای مدیریت‌شده نحو پرس‌وجو را تغییر نمی‌دهند. ابزارهای محلی بالادستی به فایل‌های قیمت محلی نیاز دارند، و پرس‌وجوهای تاریخی همچنان به قیمت‌هایی در تاریخ درخواستی یا پیش از آن نیاز دارند.

ویژگی‌های پیشرفته​

فراتر از دستورهای پایه 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 بهینه‌سازی شده‌اند، اما مجموعه‌های نتیجه بسیار بزرگ می‌توانند RAM قابل توجهی مصرف کنند. 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";