زبان پرس و جوی Beancount - پرس و جوهای مالی مشابه SQL

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

---

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

هسته اصلی BQL، سینتکس آشنا و الهام گرفته از SQL آن است. پرس و جوها با استفاده از ابزار خط فرمان bean-query اجرا می شوند، که فایل دفتر شما را پردازش می کند و نتایج را مستقیماً در ترمینال شما برمی گرداند.

فرمت پرس و جوی پایه

یک پرس و جوی 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 خود انتخاب کنید.

---

مدل داده

برای پرس و جوی موثر داده های خود، باید درک کنید که 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. تاییدیه های تراز می توانید از BQL برای بررسی مضاعف ادعاهای balance در دفتر خود استفاده کنید. این پرس و جو باید مقدار دقیق مشخص شده در آخرین بررسی تراز خود برای آن حساب را برگرداند.

   -- تأیید تراز نهایی حساب جاری خود
   SELECT account, sum(position)
   FROM close ON 2025-01-01 -- از تاریخ از دستورالعمل تراز خود استفاده کنید
   WHERE account = "Assets:Checking";