پرش به محتوای اصلی

پرس‌وجو با SQL

با زبان پرس‌وجوی Beancount (BQL) برای تحلیل پیشرفته داده‌های مالی مسلط شوید. نحو شبیه SQL را برای پرس‌وجو و تحلیل داده‌های حسابداری خود بیاموزید.

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

ویرایشگر پرس‌وجوی BQL در beancount.io که پرس‌وجوهای شبیه 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: خطوط پستینگ فردی را بعد از انتخاب تراکنش فیلتر می‌کند.

سیستم فیلتر دو سطحی​

درک تفاوت بین بندهای 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,        -- 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)

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

فراتر از عبارات 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";

پرس‌وجو با SQL

منبع: https://beancount.io/fa/docs/Basics/beancount-query-language