본문으로 건너뛰기
Beancount.io Logo
SQL로 쿼리하기

SQL로 쿼리하기

고급 금융 데이터 분석을 위해 Beancount Query Language(BQL)를 마스터하세요. SQL과 유사한 구문으로 회계 데이터를 쿼리하고 분석하는 방법을 배우세요.

Beancount는 강력한 SQL과 유사한 쿼리 언어(BQL)를 제공하여 금융 데이터를 정밀하게 슬라이스하고, 다이스하고, 분석할 수 있게 합니다. 빠른 리포트를 생성하거나, 항목을 디버깅하거나, 복잡한 분석을 수행하려는 경우, BQL을 마스터하는 것은 일반 텍스트 회계 원장의 잠재력을 최대한 활용하는 핵심입니다. 이 가이드는 BQL의 구조, 함수, 모범 사례를 안내합니다. 🔍

브라우저에서 Beancount 원장에 대해 SQL과 유사한 쿼리를 실행하는 beancount.io BQL 쿼리 편집기

라이브 원장 탐색 →

쿼리 구조 및 실행​

BQL의 핵심은 친숙한 SQL에서 영감을 받은 구문입니다. 쿼리는 bean-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: 트랜잭션이 선택된 후에 개별 포스팅 줄을 필터링합니다.

2단계 필터링 시스템​

정확한 쿼리를 작성하려면 FROM 및 WHERE 절의 차이를 이해하는 것이 중요합니다. BQL은 2단계 필터링 프로세스를 사용합니다.

  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

성능 고려 사항 ⚙️​

bean-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/ko/docs/Basics/beancount-query-language