Poizvedba s SQL

Beancount vključuje močan, SQL-podoben poizvedbeni jezik (BQL), ki vam omogoča natančno rezanje, drobljenje in analizo vaših finančnih podatkov. Ne glede na to, ali želite ustvariti hitro poročilo, popraviti vnos ali izvesti kompleksno analizo, obvladavanje BQL je ključ za odklepanje polnega potenciala vaše plaintext računovodske knjige. Ta vodnik vas bo vodil skozi njegovo strukturo, funkcije in najboljše prakse. 🔍

Raziščite živo knjigo →

Struktura in izvedba poizvedbe​

Jedro BQL je njegova poznata, SQL-navdihnjena sintaksa. Poizvedbe se izvajajo z ukaznim orodjem bea query, ki obdeluje vašo knjižno datoteko in vrne rezultate neposredno v vaš terminal. Vsaka poizvedba v tem vodniku je bila izvedena proti Beancount 3.2.3 z beanquery 0.2.0.

Osnovni format poizvedbe​

BQL poizvedba je sestavljena iz treh glavnih stavkov: SELECT, FROM in WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;

• SELECT: Določi, katere stolpce podatkov želite pridobiti.
• FROM: Filtrira cele transakcije pred njihovo obdelavo.
• WHERE: Filtrira posamezne vnosne vrstice po tem, da je transakcija izbrana.

Dvonivojski sistem filtriranja​

Razumevanje razlike med stavkoma FROM in WHERE je ključno za pisanje natančnih poizvedb. BQL uporablja dvonivojski postopek filtriranja.

1. Nivo transakcije (FROM) Ta stavek deluje na cele transakcije. Če transakcija ustreza pogoj FROM, se cela transakcija (vključno z vsemi njenimi vnosi) preda naslednji stopnji. To je glavni način filtriranja podatkov, ker ohranjuje celovitost dvojno-knjižnega računovodskega sistema. Na primer, filtriranje FROM year = 2024 izbere vse transakcije, ki so se zgodile v letu 2024.

2. Nivo vnosa (WHERE) Ta stavek filtrira posamezne vnose znotraj transakcij, ki so bile izbrane z stavkom FROM. To je uporabno za predstavljanje in za osredotočanje na posebne noge transakcije. Vendar bodite pozorni, da filtriranje na tem nivoju lahko "zlomi" celovitost transakcije v izhodu, ker lahko vidite samo eno stranico vnosa. Na primer, lahko izberete vse vnose na vaš račun Expenses:Groceries.

Konkretno, PRINT FROM year = 2024 vrne cele transakcije (obe noge vsakega vnosa), medtem ko SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" vrne eno vrstico na vsak ustrezeni vnos. Na knjigi z dvema nakupoma pri borznem posredniku prvi vrne cele vnose, drugi pa vrne natanko dve vrstice borznega posrednika.

Podatkovni model​

Da bi učinkovito poizvedovali vaše podatke, morate razumeti, kako Beancount strukturira podatke. Knjiga je seznam direktiv, vendar BQL se osredotoča predvsem na vnose Transaction.

Struktura transakcije​

Vsaka Transaction je vsebnik z atributi na vrhu in seznamom objektov Posting.

Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
    ├── account
    ├── units
    ├── cost
    ├── price
    └── metadata

Razpoložljive vrste stolpcev​

Lahko SELECT-irate katere koli atribute iz transakcije ali njenih vnosov.

1. Atributi transakcije Ti stolpci so enaki za vsak vnos znotraj ene transakcije.

   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. Atributi vnosa Ti stolpci so posebni za vsako posamezno vnosno vrstico.

   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 in cost sta funkciji, ki sprejemata position. price, weight in balance so navadni stolpci.

Funkcije poizvedbe​

BQL vključuje nabor funkcij za agregacijo in transformacijo podatkov, podobno kot SQL.

Agregacijske funkcije​

Agregacijske funkcije povzemajo podatke čez več vrstic. Pri uporabi z GROUP BY zagotavljajo skupne povzetke.

-- 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

Funkcije pozicije/inventarja​

Stolpec position je sestavljen objekt. Te funkcije vam omogočajo izlučiti posebne dele iz njega ali izračunati njegovo tržno vrednost.

-- 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)

Lahko jih kombinirate za močna poročila. Na primer, da vidite skupno ceno in trenutno tržno vrednost vašega naložbenega portfelja. Tržna vrednost potrebuje direktivo price za vsako posedovanje (na primer 2024-12-01 price HOOL 175.00 USD). Oba agregata vrneta Inventory na račun, tako da je vsaka valuta še vedno posebej navedena.

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)

Napredne funkcije​

Poleg osnovnih stavkov SELECT, BQL ponuja posebne ukaze za običajna finančna poročila.

Poročila o stanju​

Stavek BALANCES ustvarja bilancno poročilo ali poročilo o dohodkih za določeno obdobje.

-- 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"

Dnevniška poročila​

Stavek JOURNAL prikazuje podrobno dejavnost za enega ali več računov, podobno kot tradicionalni knjigovodni pogled.

-- 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

Operacije tiskanja​

Stavek PRINT je orodje za odpravljanje napak, ki izpiše cele, ustrezne transakcije v njihovem izvirnem formatu Beancount datoteke. Sprejema samo filter vnosov. Stavek WHERE je tukaj sintaksna napaka. Da zožite izhod na eno nogo vsakega vnosa, uporabite SELECT z filterjem vnosov namesto tega. Vrne eno vrstico na vsak ustrezeni vnos.

-- 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"

Filtrirni izrazi​

Lahko zgradite sofisticirane filtre z logičnimi operatorji (AND, OR), regularnimi izrazji (~) in primerjavami.

Nizovni literali uporabljajo enojne narekovaje. Dvojni narekovaji omejajo regularni izraz po ~.

-- 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

Premišljanja o zmogljivosti ⚙️​

bea query je zasnovan za učinkovitost, vendar razumevanje njegovega delovanja lahko vam pomaga pisati hitrejše poizvedbe na velikih knjigah.

1. Nalaganje podatkov: Beancount najprej razčleni celo vašo knjižno datoteko in razvrsti vse transakcije kronološko. Ta celotni nabor podatkov se hrani v pomnilniku.
2. Optimizacija poizvedbe: Poizvedbeni motor uporablja filtre v določenem vrstnem redu za maksimalno učinkovitost: FROM (transakcije) -> WHERE (vnosi) -> Agregacije. Filtriranje na nivoju FROM je najhitrejše, ker zmanjša nabor podatkov zgodaj.
3. Uporaba pomnilnika: Vse operacije se izvajajo v pomnilniku. Objekti Position in agregacije Inventory so optimizirani, vendar zelo veliki nabori rezultatov lahko porabijo veliko RAM-a. BQL ne uporablja diskovne začasne shrambe.

Najboljše prakse​

Sledite tem nasvetom, da pišete čiste, učinkovite in vzdržljive poizvedbe.

1. Organizacija poizvedbe Formatirajte vaše poizvedbe za berljivost, posebej kompleksne. Uporabite prelome vrstic in zamike za ločitev stavkov.

   -- A clean, readable query for all 2024 expenses
   SELECT
       date,
       account,
       position
   FROM
       year = 2024
   WHERE
       account ~ "Expenses"
   ORDER BY
       date DESC;

2. Odpravljanje napak Če poizvedba ne deluje, kot pričakovano, poženite majhen vzorec z LIMIT najprej. Da preizkusite filter, uporabite SELECT DISTINCT, da vidite, katere edinstvene vrednosti ustreza.

   -- 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. Preverjanja stanja Lahko uporabite BQL, da dvojno preverite preverjanja balance v vaši knjigi. Ta poizvedba mora vrniti natanko znesek, določen v vašem zadnjem preverjanju stanja za ta račun.

   -- 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";