Aller au contenu principal

Interroger avec SQL

Maîtrisez le langage de requête Beancount (BQL) pour une analyse avancée de vos données financières. Apprenez la syntaxe de type SQL pour interroger et analyser vos données comptables.

Beancount dispose d'un puissant langage de requête de type SQL (BQL) qui vous permet de découper, trancher et analyser vos données financières avec précision. Que vous souhaitiez générer un rapport rapide, déboguer une écriture ou effectuer une analyse complexe, maîtriser BQL est essentiel pour exploiter tout le potentiel de votre grand livre comptable en texte brut. Ce guide vous présentera sa structure, ses fonctions et ses bonnes pratiques. 🔍

Éditeur de requêtes BQL beancount.io exécutant des requêtes de type SQL sur un grand livre Beancount dans le navigateur

Explorez le grand livre en direct →

Structure et exécution des requêtes​

Le cœur de BQL repose sur une syntaxe familière inspirée de SQL. Les requêtes sont exécutées à l'aide de l'outil en ligne de commande bea query, qui traite votre fichier de grand livre et renvoie les résultats directement dans votre terminal. Chaque requête de ce guide a été exécutée avec Beancount 3.2.3 et beanquery 0.2.0.

Format de requête de base​

Une requête BQL se compose de trois clauses principales : SELECT, FROM et WHERE.

SELECT <target1>, <target2>, ...
FROM <entry-filter-expression>
WHERE <posting-filter-expression>;
  • SELECT : Spécifie les colonnes de données que vous souhaitez récupérer.
  • FROM : Filtre les transactions entières avant qu'elles ne soient traitées.
  • WHERE : Filtre les lignes d'écriture individuelles après que la transaction a été sélectionnée.

Système de filtrage à deux niveaux​

Comprendre la différence entre les clauses FROM et WHERE est crucial pour écrire des requêtes précises. BQL utilise un processus de filtrage à deux niveaux.

  1. Niveau transaction (FROM) Cette clause agit sur des transactions entières. Si une transaction correspond à la condition FROM, la transaction entière (y compris toutes ses écritures) est transmise à l'étape suivante. C'est le principal moyen de filtrer les données, car il préserve l'intégrité du système comptable en partie double. Par exemple, filtrer avec FROM year = 2024 sélectionne toutes les transactions survenues en 2024.

  2. Niveau écriture (WHERE) Cette clause filtre les écritures individuelles au sein des transactions sélectionnées par la clause FROM. Cela est utile pour la présentation et pour se concentrer sur des jambes spécifiques d'une transaction. Cependant, soyez conscient que filtrer à ce niveau peut « casser » l'intégrité d'une transaction dans la sortie, car vous pourriez ne voir qu'un seul côté d'une écriture. Par exemple, vous pourriez sélectionner toutes les écritures vers votre compte Expenses:Groceries.

Concrètement, PRINT FROM year = 2024 renvoie les transactions complètes (les deux jambes de chaque écriture), tandis que SELECT date, narration, account, position FROM year = 2024 WHERE account ~ "Assets:Broker" renvoie une ligne par écriture correspondante. Sur un grand livre avec deux achats chez le courtier, la première renvoie les écritures complètes et la seconde renvoie exactement les deux lignes du courtier.

Modèle de données​

Pour interroger efficacement vos données, vous devez comprendre comment Beancount les structure. Un grand livre est une liste de directives, mais BQL se concentre principalement sur les entrées Transaction.

Structure des transactions​

Chaque Transaction est un conteneur avec des attributs de niveau supérieur et une liste d'objets Posting.

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

Types de colonnes disponibles​

Vous pouvez effectuer un SELECT sur n'importe quel attribut de la transaction ou de ses écritures.

  1. Attributs de transaction Ces colonnes sont les mêmes pour chaque écriture au sein d'une même transaction.

    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. Attributs d'écriture Ces colonnes sont spécifiques à chaque ligne d'écriture individuelle.

    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 et cost sont des fonctions qui prennent position. price, weight et balance sont des colonnes simples.

Fonctions de requête​

BQL comprend une suite de fonctions pour l'agrégation et la transformation de données, à la manière de SQL.

Fonctions d'agrégation​

Les fonctions d'agrégation résument les données sur plusieurs lignes. Utilisées avec GROUP BY, elles fournissent des résumés groupés.

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

Fonctions de position/inventaire​

La colonne position est un objet composite. Ces fonctions vous permettent d'en extraire des parties spécifiques ou de calculer sa valeur de marché.

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

Vous pouvez les combiner pour créer des rapports puissants. Par exemple, pour voir le coût total et la valeur de marché actuelle de votre portefeuille d'investissement. La valeur de marché nécessite une directive price pour chaque titre détenu (par exemple 2024-12-01 price HOOL 175.00 USD). Les deux agrégats renvoient un inventaire par compte, donc chaque devise est encore listée séparément.

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)

Fonctionnalités avancées​

Au-delà des instructions SELECT de base, BQL offre des commandes spécialisées pour les rapports financiers courants.

Rapports de solde​

L'instruction BALANCES génère un bilan ou un compte de résultat pour une période spécifique.

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

Rapports de journal​

L'instruction JOURNAL affiche l'activité détaillée d'un ou plusieurs comptes, à l'image d'une vue de grand livre traditionnelle.

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

Opérations d'impression​

L'instruction PRINT est un outil de débogage qui génère les transactions complètes correspondantes dans leur format de fichier Beancount d'origine. Elle n'accepte qu'un filtre d'entrée. Une clause WHERE constitue ici une erreur de syntaxe. Pour limiter la sortie à une seule jambe de chaque écriture, utilisez SELECT avec un filtre d'écriture à la place. Elle renvoie une ligne par écriture correspondante.

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

Expressions de filtrage​

Vous pouvez construire des filtres sophistiqués à l'aide d'opérateurs logiques (AND, OR), d'expressions régulières (~), et de comparaisons.

Les littéraux de chaîne utilisent des guillemets simples. Les guillemets doubles délimitent l'expression régulière après ~.

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

Considérations de performance ⚙️​

bea query est conçu pour l'efficacité, mais comprendre son flux opérationnel peut vous aider à écrire des requêtes plus rapides sur de grands grands livres.

  1. Chargement des données : Beancount analyse d'abord l'intégralité de votre fichier de grand livre et trie toutes les transactions chronologiquement. L'ensemble de ces données est conservé en mémoire.
  2. Optimisation des requêtes : Le moteur de requête applique les filtres dans un ordre spécifique pour une efficacité maximale : FROM (transactions) -> WHERE (écritures) -> Agrégations. Filtrer au niveau FROM est le plus rapide car cela réduit l'ensemble de données dès le début.
  3. Utilisation de la mémoire : Toutes les opérations se déroulent en mémoire. Les objets Position et les agrégations Inventory sont optimisés, mais de très grands ensembles de résultats peuvent consommer une RAM importante. BQL n'utilise pas de stockage temporaire sur disque.

Bonnes pratiques​

Suivez ces conseils pour écrire des requêtes propres, efficaces et maintenables.

  1. Organisation des requêtes Formatez vos requêtes pour les rendre lisibles, surtout les plus complexes. Utilisez des sauts de ligne et des indentations pour séparer les clauses.

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

  2. Débogage Si une requête ne fonctionne pas comme prévu, exécutez d'abord un petit échantillon avec LIMIT. Pour tester un filtre, utilisez SELECT DISTINCT pour voir quelles valeurs uniques il correspond.

    -- 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. Vérifications de solde Vous pouvez utiliser BQL pour vérifier les assertions de balance de votre grand livre. Cette requête devrait renvoyer le montant exact spécifié dans votre dernière vérification de solde pour ce compte.

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

Interroger avec SQL

Source : https://beancount.io/fr/docs/Basics/beancount-query-language